python连接服务器informix_如何在Linux上使用Python和Informix进行通信？

I have been at this for a while, trying all kinds of different packages from openSource, IBM, and many others. I have not yet found one that works without some sort of confusing install method that I can not get to work, or some sort of integration with other third-party pieces that I can not seem to get working.

I am simply trying to perform SQL statements on a Informix Server using Python. No different than mySQL and other tools. Using cursors or full result dumps, really do not care. I want to be able to formalize a query string statically or dynamically and then tell whatever tools/module to execute said query and return results (if any).

I have tried:

IBM Informix Client SDK

pymssql

unixODBC

Looked at but do not want to use Jython (JPython).

What I have managed:

I have been able to install and get the IBM Informix Client SDK installed and working. I can connect to my Informix DB server and perform queries.

I have mySQL working and connecting and querying.

I have written a Java program to perform queries using a Java driver, compiled it, combined it with a bash script to perform queries and email results.

I am just stumped. Looking for assistance on what to download (URLs), how to go about installing it (tips and tricks, environment variables, where to install it, etc..) I want to have something that does not depend on Java or writing Java, etc. I am looking for a solution that may will give me the ability to write Python to query, insert, update, and delete from an Informix database and tables. I want to combine my previously written Java and Bash script into a Python script.

Frustrated and looking for any assistance.

Thank you for listening and please ask questions if you do not understand my plea.

解决方案

Informix on Linux is a bag of pain. My personal setup to get Informix-connect to work with CPython3 is stacking the Informix Client SDK with unixODBC and pyodbc. There are some hoops to jump through, none of which are documented. Almost all the setup is completely useless yet required to prevent some parts of the Informix-driver to bail out. Note that some options are case- and space-sensitive (Description=Informix != description = Informix).

Install the Informix Client SDK. You don't need all the garbage that comes in the package, just Informix Connect. I assume you use the default path /opt/IBM/informix

Add /opt/IBM/informix/lib/cli and /opt/IBM/informix/lib/esql to your dynamic linker lookup paths. On Fedora you can do this by putting them in a new file /etc/ld.so.conf.d/informix.conf

Create a new /etc/odbc.ini and add the following:

[ODBC Data Sources]

Infdrv1=IBM INFORMIX ODBC DRIVER

[Infdrv1]

Driver=/opt/IBM/informix/lib/cli/iclit09b.so

Description=Informix

Database=WHATEVER_YOUR_DB_NAME_IS

Servername=WHATEVER_YOUR_SERVER_NAME_IS

CLIENT_LOCALE=en_us.8859-1 # MAY BE DIFFERENT

DB_LOCALE=en_us.819 # MAY BE DIFFERENT

[ODBC]

UNICODE=UCS-2

Create a new /etc/odbcinst.ini and add the following

[IBM INFORMIX ODBC DRIVER]

Description=Informix Driver

Driver=libifcli.so

You need to set the environment variables INFORMIXDIR and ODBCINI. On Fedora you may add a new file /etc/profile.d/informix.sh and add

export INFORMIXDIR=/opt/IBM/informix

export ODBCINI=/etc/odbc.ini

Edit /opt/IBM/informix/etc/sqlhosts and put your basic connection information there. In the most simple case it has only one line that reads

YOUR_SERVER_NAME\tonsoctcp\tYOUR_DB_NAME\tpdap-np

Note that pdap-np is actually port 1526 which is also the Informix "Turbo"-Driver tcp port. See your /etc/services

Create an empty .odbc.ini in your $HOME e.g. by touch $HOME/.odbc.ini. It needs to be there. It needs to be 0 bytes. I love this part.

Install unixODBC and pyodbc from your favorite repository.

Remember to get your env-changes going, e.g. via reboot. You can now connect like this:

import pyodbc

DRIVER = 'IBM INFORMIX ODBC DRIVER'

SERVER = 'YOUR_SERVER_NAME'

DATABASE = 'YOUR_DB_NAME'

constr = 'DRIVER={%s};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % (DRIVER, SERVER, DATABASE, USER, PASS)

con = pyodbc.connect(constr, autocommit=False)

From there on you can get your cursor, execute queries, fetch results and such. Note that there are numerous bugs in quirks in IBM's ODBC-driver, out of my head:

Rows that contain NULLs may cause a segfault as the IBM driver puts a 32bit int where a 64bit int is expected to signal the value being null. In case you are affected by this, you need to patch unixODBC for all possible column types to deal with this.

Columns without names cause the driver to segfault (e.g. SELECT COUNT(*) FROM foobar needs to be SELECT COUNT(*) AS c FROM foobar).

Make sure your encoding actually works as expected. UTF8 is something not enterprise-enough for IBM and UCS-2 is the only thing I got to work.