Python3多种方案连接Informix数据库
最近需要使用Python连接IBM的Informix数据库
系统环境
Cent7 OS Python3.7.2 Informix数据库使用DBeaver作为客户端尝试连接Informix,确认了本地连接到数据库是没有问题的,这里假定连接信息如下
Host: 127.0.0.1 Port: 50000 Username: chancel Password: chancel Database: my-test在Python端采用多个方式进行连接,遇到了各种各样奇怪的问题,包括超时错误、数据库不存在、驱动错误等等
考虑到DBeaver是采用JDBC进行连接的,连接并没有出现问题,最后用Python+JDBC的方式来连接数据库方才成功
如果DBeaver连接没有问题,而其他库都跟我一样或多或少有问题,那么可优先考虑JayDeBeApi的连接方式
2. 方案2.1. JayDeBeApi(推荐)
安装jaydebeapi
TEXTcontent_copypip install JayDeBeApi
jdbc驱动需要手动下载
informix-complete-jdbc下载后解压到指定目录,我这里存放于
/home/chancel/codes/python/informix/informix-jdbc-complete-4.50.4.1.jarPython连接代码如下
Pythoncontent_copyimport jaydebeapi conn = jaydebeapi.connect("com.informix.jdbc.IfxDriver", "jdbc:informix-sqli://127.0.0.1:50000/my-test:INFORMIXSERVER=my-ids", ["chancel", "chancel"], "/home/chancel/codes/python/informix/informix-jdbc-complete-4.50.4.1.jar") curs = conn.cursor() print(curs)
连接正常,可以正常查询数据
2.2. SQLAlchemy
安装SQLAlchemy
Bashcontent_copypip3 install sqlalchemy sqlalchemy-informix
注意要安装sqlalchemy-informix,否则将出现以下错误
TEXTcontent_copyException has occurred: NoSuchModuleError
Can't load plugin: sqlalchemy.dialects:informix
编写连接的代码
Pythoncontent_copyfrom sqlalchemy import create_engine engine = create_engine("informix:///?Server=127.0.0.1&;Port=50000&User=chancel&Password=chancel&Database=my-test") conn = engine.connect() print(conn)
执行后错误如下,显示数据库连接不存在,多次尝试后仍出现这个错误无法解决
TEXTcontent_copyException has occurred: OperationalError
(ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL1024N A database connection does not exist. SQLSTATE=08003 SQLCODE=-1024
2.3. ifpyx
实现可参考Python仓库:OpenInformix/IfxPy - github.com
按照说明文档不但需要ODBC Diver还需要部署CSDK,部署成本较高
参考代码
Pythoncontent_copyimport IfxPy ConStr = "SERVER=ids0;DATABASE=my-test;HOST=127.0.0.1;SERVICE=50000;UID=chancel;PWD=chancel;"informix_password) conn = IfxPy.connect( ConStr, "", "") print(conn) IfxPy.close(conn)
执行后仍然出错,显示缺少so库文件,我安装的是较为高版本的IBM Clent SDK,没有这个库里对应的SO文件
检阅文档后感觉问题可能出在CSDK安装上
2.4. ibm_db
实现可参考Python仓库:ibmdb/python-ibmdb - github.com
安装ibm_db
Bashcontent_copypip3 install ibm_db
用代码验证能否连接
Pythoncontent_copyimport ibm_db conn_str='database=my-test;hostname=127.0.0.1;port=50000;protocol=sockets;uid=chancel;pwd=chancel' ibm_db_conn = ibm_db.connect(conn_str,'','') print(ibm_db_conn)
执行出错,多次尝试后仍然提示数据库名不存在,原因不明
TEXTcontent_copyException has occurred: Exception
[IBM][CLI Driver] SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705 SQLCODE=-1013
2.5. pydoc
实现参考Python仓库:mkleehammer/pyodbc - github.com
按照官方文档说明,我们需要借助ODBC的驱动才能使用,不同安装系统可以参考文档说明
pyodbc - Install
对于Ubuntu1804
Bashcontent_copysudo apt install python3-pip python3-dev unixodbc-dev pip3 install --user pyodbc
对于Cent7OS
Bashcontent_copysudo yum install epel-release sudo yum install python-pip gcc-c++ python-devel unixODBC-devel pip install --user pyodbc
检查安装情况
Bashcontent_copyodbcinst -j # 输出如下 unixODBC 2.3.6 DRIVERS............: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources USER DATA SOURCES..: /home/chancel/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
用代码测试连接
Pythoncontent_copyimport pyodbc cnxn = pyodbc.connect('DRIVER={CData ODBC Driver for Informix};Server=127.0.0.1;Port=50000;Server=my_ids;User=chancel;Password=chancel;Database=my-test;') print(cnxn)
提示"CData ODBC Driver for Informix file not found"
Ubuntu1804并未出现此情况,只在Cent7OS下出现,经验证,Cen7OS需要手动在/etc下创建odbcinst.ini与odbc.ini文件
Bashcontent_copysudo ln -s /etc/unixODBC/odbcinst.ini /etc/odbcinst.ini sudo ln -s /etc/unixODBC/odbc.ini /etc/odbc.ini
运行不再出现异常,但很可惜,一直显示连接超时,猜测与Informix的数据库版本有关
TEXTcontent_copyException has occurred: OperationalError
('08001', '[08001] [unixODBC]It timed out to get data. (-1) (SQLDriverConnect)')
3. 参考资料
文章内容参考了以下文章
https://www.cdata.com/kb/tech/ibminformix-odbc-python-linux.rst https://stackoverflow.com/questions/55142353/cannot-connect-to-informix-through-odbc-on-linux-machine https://stackoverflow.com/questions/24906016/exception-value-08001-08001-unixodbcfreetdssql-serverunable-to-con https://www.cdata.com/kb/tech/ibminformix-python-sqlalchemy.rst文章来源:
Author:chancel
link:http://www.chancel.me/markdown/python3-multiple-solutions-connect-informix-database