Python3 connection to Informix database

1. Background

I recently needed to use Python to connect to an IBM Informix database.

System environment.

Cent7 OS
Python 3.7.2
Informix database

I tried to connect to Informix using DBeaver as a client and confirmed that there is no problem connecting to the database locally. The following connection information is assumed

Host: 127.0.0.1
Port: 50000
Username: chancel
Password: chancel
Database: my-test

I used multiple connection methods on the Python side and encountered various strange problems, including timeout errors, non-existent databases, driver errors, and so on.

Considering that DBeaver is using JDBC to connect, there is no problem to connect, finally I use Python+JDBC to connect to the database successfully.

If DBeaver has no connection problems and other libraries have more or less the same problems as me, then you can give priority to JayDeBeApi connection method.

2. Programs

2.1. JayDeBeApi (recommended)

Install jaydebeapi.

`1`	`pip install JayDeBeApi`

The jdbc driver needs to be downloaded manually

informix-complete-jdbc

Download and extract to the specified directory, I store it in

`1`	`/home/chancel/codes/python/informix/informix-jdbc-complete-4.50.4.1.jar`

The Python connection code is as follows.

import 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)

The connection is normal and the data can be queried normally.

2.2. SQLAlchemy

Install SQLAlchemy.

`1`	`pip3 install sqlalchemy sqlalchemy-informix`

Be careful to install sqlalchemy-informix, otherwise the following error will occur.

1
2

Exception has occurred: NoSuchModuleError
Can't load plugin: sqlalchemy.dialects:informix

Write the code for the connection.

from 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)

The error after execution is as follows, showing that the database connection does not exist, and this error still occurs after several attempts and cannot be resolved.

1
2

Exception 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

The implementation can be found in the Python repository: OpenInformix/IfxPy - github.com.

According to the documentation not only need ODBC Diver but also need to deploy CSDK, the deployment cost is higher.

Reference code.

import 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)

After execution it still gives an error and shows a missing so library file. I have installed a higher version of IBM Clent SDK and there is no SO file corresponding to this library.

After reviewing the documentation, I feel that the problem may lie in the CSDK installation.

2.4. ibm_db

The implementation can be found in the Python repository: ibmdb/python-ibmdb - github.com.

Install ibm_db.

`1`	`pip3 install ibm_db`

Verify that the connection is possible with the code.

import 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)

Execution error, after several attempts, it still indicates that the database name does not exist, the reason is unknown.

1
2

Exception 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

Implementation reference Python repository: mkleehammer/pyodbc - github.com.

According to the official documentation, we need to use the ODBC driver to use it, you can refer to the documentation for different installation systems.

pyodbc - Install.

For Ubuntu 1804.

1
2

sudo apt install python3-pip python3-dev unixodbc-dev
pip3 install --user pyodbc

For CentOS7.

1
2
3

sudo yum install epel-release
sudo yum install python-pip gcc-c++ python-devel unixODBC-devel
pip install --user pyodbc

Check the installation.

odbcinst -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

Test the connection with code.

1
2
3

import 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)

Prompt “CData ODBC Driver for Informix file not found”.

This does not happen with Ubuntu 1804, but only with CentOS 7. It is verified that CentOS7 needs to manually create odbcinst.ini and odbc.ini files under /etc.

1
2

sudo ln -s /etc/unixODBC/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /etc/unixODBC/odbc.ini /etc/odbc.ini

No more exceptions, but unfortunately, it keeps showing connection timeout, guessing that it is related to the database version of Informix.

1
2

Exception has occurred: OperationalError
('08001', '[08001] [unixODBC]It timed out to get data. (-1) (SQLDriverConnect)')

Table of Contents