[Python] How do I access AS/400 using SQLAlchemy?

Discussão em 'Python' iniciado por Stack, Maio 16, 2025.

  1. Stack

    Stack Membro Participativo

    Short version: Please tell me how to connect to AS/400s via SQLAlchemy.

    Long version

    My ultimate goal is to join data from SQL Server and AS/400 to be displayed in a Flask Python application. My approach has been to get the data from each database into Pandas dataframes, which can then be joined and output as JSON. If anyone has a better approach, feel free to leave a comment. The problem with the way I'm trying to do this is that Pandas.read_sql_query() relies on SQLAlchemy, and getting SQLAlchemy to work with AS/400 is proving quite difficult.

    • The AS/400 is version 7.2, though another I will likely try to connect to is version 5.1.
    • I'm trying to access it from my computer, which is running Windows 7 and has i Access 7.1, Python 2.7, and Python modules including pyodbc and ibm_db_sa.

    Without sqlalchemy, pyodbc works just fine:

    CONNECTION_STRING = (
    "driver={iSeries Access ODBC Driver};"
    "system=ip_address;"
    "database=database_name;"
    "uid=username;"
    "pwd=password;"
    )
    pyodbc.connect(CONNECTION_STRING)
    # Queries work fine after this.


    I've read these resources, among others, and tried to apply their techniques:


    Below are some of the failed attempts and corresponding error messages that I've collected. I don't know what to put for the first part ("something+something//..."), which port to specify (446? 8471? something else? nothing?), whether to use the server's name or IP address, or whether to use the connection-string style argument for create_engine(), so I've just been trying every combination I can think of. I tried modifying the AS400Dialect_pyodbc class as suggested in the second link above, after which I tried rerunning some of the failed attempts again. I may keep trying things, but I'm just spinning my wheels at this point.

    from sqlalchemy import create_engine

    CONNECTION_STRING = (
    "driver={iSeries Access ODBC Driver};"
    "system=ip_address;"
    "database=database_name;"
    "uid=username;"
    "pwd=password;"
    )

    create_engine('ibm_db_sa+pyodbc://username:password@ip_address:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.InterfaceError (pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 43, in
    ​

    create_engine('ibm_db_sa://username:password@ip_address:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N The database alias or database name "database_name " was not found at the remote node. SQLSTATE=08004\r SQLCODE=-30061 (Background on this error at: http://sqlalche.me/e/e3q8) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 43, in
    ​

    create_engine('ibm_db_sa://username:password@server_name:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N The remote host "server_name" was not found. SQLSTATE=08001\r SQLCODE=-1336 (Background on this error at: http://sqlalche.me/e/e3q8create_engine('ibm_db_sa://username:password@ip_address:446/server_name.database_name').connect()
    ​

    create_engine('ibm_db_sa://username:password@ip_address:446/server_name.database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N The database alias or database name "server_name.database_name " was not found at the remote node. SQLSTATE=08004\r SQLCODE=-30061 (Background on this error at: http://sqlalche.me/e/e3q8)
    ​

    create_engine('db2+ibm_db://username:password@ip_address:446/server_name.database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N The database alias or database name "server_name.database_name " was not found at the remote node. SQLSTATE=08004\r SQLCODE=-30061 (Background on this error at: http://sqlalche.me/e/e3q8) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 45, in
    ​

    create_engine('db2+ibm_db://username:password@ip_address:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N The database alias or database name "database_name " was not found at the remote node. SQLSTATE=08004\r SQLCODE=-30061 (Background on this error at: http://sqlalche.me/e/e3q8) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 45, in
    ​

    create_engine('db2+ibm_db://username:password@ip_address/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "ip_address". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "", "". SQLSTATE=08001\r SQLCODE=-30081 (Background on this error at: http://sqlalche.me/e/e3q8)
    ​

    create_engine('db2+ibm_db://username:password@server_name:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N The remote host "server_name" was not found. SQLSTATE=08001\r SQLCODE=-1336 (Background on this error at: http://sqlalche.me/e/e3q8)
    ​

    create_engine('db2+ibm_db://username:password@server_name/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N The remote host "server_name" was not found. SQLSTATE=08001\r SQLCODE=-1336 (Background on this error at: http://sqlalche.me/e/e3q8)
    ​

    create_engine('db2+pyodbc://username:password@ip_address:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.InterfaceError (pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 45, in
    ​

    create_engine('db2://username:password@ip_address:446/database_name').connect()


    Exception has occurred: sqlalchemy.exc.OperationalError (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N The database alias or database name "database_name " was not found at the remote node. SQLSTATE=08004\r SQLCODE=-30061 (Background on this error at: http://sqlalche.me/e/e3q8) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 45, in
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('ibm_db_sa+pyodbc:///?odbc_connect={}'.format(quoted)).connect()


    Unable to open 'hashtable_class_helper.pxi': File not found (file:///c:/git/dashboards/pandas/_libs/hashtable_class_helper.pxi).
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('ibm_db_sa:///?odbc_connect={}'.format(quoted)).connect()


    Exception has occurred: sqlalchemy.exc.InterfaceError (ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError: connect expects the first five arguments to be of type string or unicode (Background on this error at: http://sqlalche.me/e/rvf5) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 43, in
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('ibm_db:///?odbc_connect={}'.format(quoted)).connect()


    Exception has occurred: sqlalchemy.exc.NoSuchModuleError Cant load plugin: sqlalchemy.dialects:ibm_db
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('db2:///?odbc_connect={}'.format(quoted)).connect()


    Exception has occurred: sqlalchemy.exc.InterfaceError (ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError: connect expects the first five arguments to be of type string or unicode (Background on this error at: http://sqlalche.me/e/rvf5) File "C:\Git\dashboards\web_app\pandas db2 test.py", line 45, in
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('db2+ibm_db:///?odbc_connect={}'.format(quoted)).connect()


    Exception has occurred: sqlalchemy.exc.InterfaceError (ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError: connect expects the first five arguments to be of type string or unicode (Background on this error at: http://sqlalche.me/e/rvf5)
    ​

    quoted = urllib.quote_plus(CONNECTION_STRING)
    create_engine('db2+ibm_db_sa:///?odbc_connect={}'.format(quoted)).connect()


    Exception has occurred: sqlalchemy.exc.NoSuchModuleError Cant load plugin: sqlalchemy.dialects:db2.ibm_db_sa
    ​

    Continue reading...
    Stack, Maio 16, 2025
    #1

Compartilhe esta Página