如何使用 SQLAlchemy 访问 AS/400? [英] How do I access AS/400 using SQLAlchemy?

问题描述

简短版本: 请告诉我如何通过 SQLAlchemy 连接到 AS/400.

长版

我的最终目标是将来自 SQL Server 和 AS/400 的数据连接起来以在 Flask Python 应用程序中显示.我的方法是将每个数据库中的数据放入 Pandas 数据帧中，然后将其连接起来并作为 JSON 输出.如果有人有更好的方法，请随时发表评论.我尝试这样做的方式的问题在于 Pandas.read_sql_query() 依赖于 SQLAlchemy，而事实证明让 SQLAlchemy 与 AS/400 一起工作非常困难.

• AS/400 是 7.2 版，但我可能会尝试连接的另一个是 5.1 版.
• 我正在尝试从我的计算机访问它，我的计算机运行的是 Windows 7，并且有 i Access 7.1、Python 2.7 和 Python 模块，包括 pyodbc 和 ibm_db_sa.

没有 sqlalchemy，pyodbc 工作得很好:

CONNECTION_STRING = (驱动程序={iSeries 访问 ODBC 驱动程序}；"系统= ip_address;"数据库=数据库名称；"uid=用户名；"密码=密码；")pyodbc.connect(CONNECTION_STRING)# 在此之后查询工作正常.

我阅读了这些资源以及其他资源，并尝试应用他们的技术:

• https://pypi.org/project/ibm_db_sa/
• 连接到 IBM AS400 服务器以进行数据库操作挂起
• SqlAlchemy 相当于使用 FreeTDS 的 pyodbc 连接字符串

以下是我收集的一些失败的尝试和相应的错误消息.我不知道第一部分放什么(something+something//...")，指定哪个端口(446?8471?别的什么?什么都没有?)，是否使用服务器的名称或 IP 地址，或者是否使用 create_engine() 的连接字符串样式参数，所以我一直在尝试我能想到的所有组合.我尝试按照上面第二个链接中的建议修改 AS400Dialect_pyodbc 类，之后我再次尝试重新运行一些失败的尝试.我可能会继续尝试，但此时我只是在转动我的轮子.

from sqlalchemy import create_engineCONNECTION_STRING = (驱动程序={iSeries 访问 ODBC 驱动程序}；"系统= ip_address;"数据库=数据库名称；"uid=用户名；"密码=密码；")create_engine('ibm_db_sa+pyodbc://username:password@ip_address:446/database_name').connect()

<块引用>

发生异常:sqlalchemy.exc.InterfaceError(pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] 未找到数据源名称且未指定默认驱动程序 (0) (SQLDriverConnect)')(此错误的背景:http://sqlalche.me/e/rvf5)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 43 行，

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N 在远程节点上找不到数据库别名或数据库名称database_name".SQLSTATE=08004\r SQLCODE=-30061(此错误的背景:http://sqlalche.me/e/e3q8)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 43 行，

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N 未找到远程主机server_name".SQLSTATE=08001\r SQLCODE=-1336(此错误的背景: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()

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N 在远程节点上找不到数据库别名或数据库名称server_name.database_name".SQLSTATE=08004\r SQLCODE=-30061(此错误的背景:http://sqlalche.me/e/e3q8)

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N 在远程节点上找不到数据库别名或数据库名称server_name.database_name".SQLSTATE=08004\r SQLCODE=-30061(此错误的背景:http://sqlalche.me/e/e3q8)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 45 行，

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N 在远程节点上找不到数据库别名或数据库名称database_name".SQLSTATE=08004\r SQLCODE=-30061(此错误的背景:http://sqlalche.me/e/e3q8)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 45 行，

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30081N 检测到通信错误.正在使用的通信协议:TCP/IP".正在使用的通信 API:SOCKETS".检测到错误的位置:ip_address".检测到错误的通信功能:连接".协议特定的错误代码:10061"、"、".SQLSTATE=08001\r SQLCODE=-30081(此错误的背景:http://sqlalche.me/e/e3q8)

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N 未找到远程主机server_name".SQLSTATE=08001\r SQLCODE=-1336(此错误的背景:http://sqlalche.me/e/e3q8)

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1336N 未找到远程主机server_name".SQLSTATE=08001\r SQLCODE=-1336(此错误的背景:http://sqlalche.me/e/e3q8)

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

<块引用>

发生异常:sqlalchemy.exc.InterfaceError(pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] 未找到数据源名称且未指定默认驱动程序 (0) (SQLDriverConnect)')(此错误的背景:http://sqlalche.me/e/rvf5)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 45 行，

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

<块引用>

发生异常:sqlalchemy.exc.OperationalError(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30061N 在远程节点上找不到数据库别名或数据库名称database_name".SQLSTATE=08004\r SQLCODE=-30061(此错误的背景:http://sqlalche.me/e/e3q8)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 45 行，

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

<块引用>

无法打开hashtable_class_helper.pxi":找不到文件(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()

<块引用>

发生异常:sqlalchemy.exc.InterfaceError(ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError:connect 期望前五个参数是字符串或 unicode 类型(此错误的背景:http://sqlalche.me/e/rvf5)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 43 行，

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

<块引用>

发生异常:sqlalchemy.exc.NoSuchModuleError无法加载插件:sqlalchemy.dialects:ibm_db

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

<块引用>

发生异常:sqlalchemy.exc.InterfaceError(ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError:connect 期望前五个参数是字符串或 unicode 类型(此错误的背景:http://sqlalche.me/e/rvf5)文件C:\Git\dashboards\web_app\pandas db2 test.py"，第 45 行，

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

<块引用>

发生异常:sqlalchemy.exc.InterfaceError(ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError:connect 期望前五个参数是字符串或 unicode 类型(此错误的背景:http://sqlalche.me/e/rvf5)

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

<块引用>

发生异常:sqlalchemy.exc.NoSuchModuleError无法加载插件:sqlalchemy.dialects:db2.ibm_db_sa

解决方案

我终于让它工作了，虽然有点尴尬.我在我的项目中创建了一个空白文件来安抚我收到的这条消息，以回应我的问题中显示的尝试之一:

<块引用>

无法打开 'hashtable_class_helper.pxi':找不到文件 (file:///c:/git/dashboards/pandas/_libs/hashtable_class_helper.pxi).

(我的项目文件夹是 C:/Git/dashboards，所以我创建了路径的其余部分.)

有了那个文件，下面的代码现在对我有用.engine.connect() 有效，但我运行了一个实际查询以进一步验证它是否有效.作为记录，无论 ibm_db_sa 模块是否按照我的问题中的一个链接中的建议进行修改，它似乎都可以工作，因此我建议不要理会该模块.请注意，虽然它们不是直接导入的，但您需要安装以下模块:pyodbc、ibm_db_sa，以及可能的 future(我忘记了).

导入urllib将熊猫导入为 pd从 sqlalchemy 导入 create_engineCONNECTION_STRING = (驱动程序={iSeries 访问 ODBC 驱动程序}；"系统= ip_address;"数据库=数据库名称；"uid=用户名；"密码=密码；")SQL="""\选择MPBASE 作为 BASEPA，COALESCE(SUM(MPQTY), 0) 作为 PWIP从 FUTMODS.MPPRODMPOPT <>'*'按 MPBASE 分组"""引用 = urllib.quote_plus(CONNECTION_STRING)engine = create_engine('ibm_db_sa+pyodbc:///?odbc_connect={}'.format(quoted))df = pd.read_sql_query(SQL,引擎，index_col='basepa')打印文件

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:

• https://pypi.org/project/ibm_db_sa/
• Connecting to IBM AS400 server for database operations hangs
• SqlAlchemy equivalent of pyodbc connect string using FreeTDS

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

解决方案

I finally got it working, though it's a bit awkward. I created a blank file in my project to appease this message that I was receiving in response to one of the attempts shown in my question:

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

(My project folder is C:/Git/dashboards, so I created the rest of the path.)

With that file present, the code below now works for me. engine.connect() works, but I ran an actual query for further verification that it was working. For the record, it seems to work regardless of whether the ibm_db_sa module is modified as suggested in one of the links in my question, so I would recommend leaving that module alone. Note that although they aren't imported by directly, you need these modules installed: pyodbc, ibm_db_sa, and possibly future (I forget).

import urllib
import pandas as pd
from sqlalchemy import create_engine

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

SQL= """\
SELECT
    MPBASE AS BASEPA,
    COALESCE(SUM(MPQTY), 0) AS PWIP
FROM FUTMODS.MPPROD
WHERE MPOPT <> '*'
GROUP BY MPBASE
"""

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

df = pd.read_sql_query(
    SQL,
    engine,
    index_col='basepa'
)
print df

这篇关于如何使用 SQLAlchemy 访问 AS/400?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！