Search code examples
python-3.xinformix

Cannot connect to Informix DB using python

I have tried many ways to connect to IBM Informix DB using python code (pyodbc, IfxPy, pymssql, ibm_db, ibm_db_sa) but so far I have no luck and I am getting the below-mentioned errors:

1

import pyodbc
from sqlalchemy import create_engine

import ibm_db, ibm_db_dbi

conn_str='database=database_name;hostname=host;port=16827;protocol=tcp/ip;uid=username;PWD=password'
ibm_db_conn = ibm_db.connect(conn_str,'','')
conn = ibm_db_dbi.Connection(ibm_db_conn)

cur = conn.cursor()
cur.execute("select id, name from employee")
rows=cur.fetchall()
for row in rows:
    print(row)

Error:

----> 4 ibm_db_conn = ibm_db.connect(conn_str,'','')
      5 conn = ibm_db_dbi.Connection(ibm_db_conn)
      6 

 SQLCODE=-1013on: [IBM][CLI Driver] SQL1013N  The database alias name or database name "" could not be found.  SQLSTATE=42705

2

CONNECTION_STRING = 'DRIVER={%s};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;HOST=%s' % (DRIVER, SERVER, DATABASE, USERNAME, PASSWORD, HOST)
con = pyodbc.connect(CONNECTION_STRING, autocommit=False)
cur = con.cursor()
cur.execute("select * from employee")
rows=cur.fetchall()
for row in rows:
    print(row)

Error:

----> 3 con = pyodbc.connect(constr, autocommit=False)
      4 cur = conn.cursor()
      5 cur.execute("select * from employee")

OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [08001] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

3

engine = create_engine("ibm_db_sa://informix:informix@host:16827/database_name")

for row in engine.connect().execute('select * from employee'):
    print(row)

Error:

---> 32 from sqlalchemy.connectors.zxJDBC import ZxJDBCConnector
     33 from .base import _SelectLastRowIDMixin, DB2Dialect, DB2ExecutionContext, DB2Compiler
     34 from . import reflection as ibm_reflection

ModuleNotFoundError: No module named 'sqlalchemy.connectors.zxJDBC'

4

# Using SQLAlchemy 
import pyodbc
engine1 = create_engine("pyodbc+ibm_db://informix:informix@host:16827/database_name")

for row in engine1.connect().execute('select * from employee'):
    print(row)

Error:

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:pyodbc.ibm_db

5

import IfxPy
ConStr = "SERVER=lo_informix1410;DATABASE=database_name;HOST=host;SERVICE=16827;uid=username;PWD=password;"
conn=IfxPy.connect(ConStr,"","")

Error:

----> 1 import IfxPy
      2 ConStr = "SERVER=lo_informix1410;DATABASE=database_name;HOST=host;SERVICE=16827;uid=username;PWD=password;"
      3 conn=IfxPy.connect(ConStr,"","")

ImportError: DLL load failed while importing IfxPy: The specified module could not be found.

Can someone please suggest how to connect to IBM Informix DB for running queries using python both in Windows & Linux environments?

Thanks

Solution

  • This shows how to build IfxPy from scratch

    informix@irk:/tmp$ mkdir test

informix@irk:/tmp$ cd test

informix@irk:/tmp/test$ python3 -V      

Python 3.10.1

informix@irk:/tmp/test$ export INFORMIXDIR=/opt/informix/IBM/4.50.FC5

informix@irk:/tmp/test$ export CSDK_HOME=$INFORMIXDIR

informix@irk:/tmp/test$ export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/cli

informix@irk:/tmp/test$ git clone https://github.com/OpenInformix/IfxPy.git
Cloning into 'IfxPy'...
remote: Enumerating objects: 2189, done.
...
...
Resolving deltas: 100% (1623/1623), done.

informix@irk:/tmp/test$ cd IfxPy/IfxPy

informix@irk:/tmp/test/IfxPy/IfxPy$ python3 setup.py bdist_wheel  --plat-name manylinux1_x86_64
Detected 64-bit Python
Smart Triggers are enabled.
running bdist_wheel
running build
running build_py
creating build
creating build/lib.linux-x86_64-3.10
copying IfxPyDbi.py -> build/lib.linux-x86_64-3.10
running build_ext
creating build/temp.linux-x86_64-3.10
gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall -fPIC -DHAVE_SMARTTRIGGER -I/tmp/test/IfxPy/IfxPy -I/tmp/test/IfxPy/IfxPy/Include -I/opt/informix/IBM/4.50.FC5/incl/cli -I/usr/local/include/python3.10 -c ifxpyc.c -o build/temp.linux-x86_64-3.10/ifxpyc.o
ifxpyc.c: In function 'estrndup':
ifxpyc.c:158:13: warning: comparison between signed and unsigned integer expressions [-Wsign-compare]
....
....
running install
running install_lib
creating build/bdist.linux-x86_64
creating build/bdist.linux-x86_64/wheel
running install_egg_info
running egg_info
creating IfxPy.egg-info
writing manifest file 'IfxPy.egg-info/SOURCES.txt'
warning: no files found matching '*.pyd'
writing manifest file 'IfxPy.egg-info/SOURCES.txt'
Copying IfxPy.egg-info to build/bdist.linux-x86_64/wheel/IfxPy-3.0.5-py3.10.egg-info
running install_scripts
informix@irk:/tmp/test/IfxPy/IfxPy$ 

informix@irk:/tmp/test/IfxPy/IfxPy$ sudo pip3 install ./dist/IfxPy-3.0.5-cp310-cp310-manylinux1_x86_64.whl --force-reinstall
Processing ./dist/IfxPy-3.0.5-cp310-cp310-manylinux1_x86_64.whl
Installing collected packages: IfxPy
  Attempting uninstall: IfxPy
    Found existing installation: IfxPy 3.0.5
    Uninstalling IfxPy-3.0.5:
      Successfully uninstalled IfxPy-3.0.5
Successfully installed IfxPy-3.0.5
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
WARNING: You are using pip version 21.2.4; however, version 22.0.3 is available.
You should consider upgrading via the '/usr/local/bin/python3.10 -m pip install --upgrade pip' command.
informix@irk:/tmp/test/IfxPy/IfxPy$ 


informix@irk:/tmp/test/IfxPy/IfxPy$ cd /tmp

informix@irk:/tmp$ cat test.py 
import IfxPy

ConStr="Driver={IBM INFORMIX ODBC DRIVER};SERVER=ol_informix1410_1;DATABASE=sysmaster;host=irk;service=13808;protocol=onsoctcp"

conn=IfxPy.connect(ConStr,"informix","whocares")
if conn:
      stmt = IfxPy.exec_immediate(conn, "SELECT tabname FROM systables")
      res = IfxPy.fetch_tuple(stmt)
      rows = res[0]
      print (rows)

informix@irk:/tmp$ python3 test.py 
systables

    If I remove the cli directory from LD_LIBRARY_PATH I get the same error complaining about not finding libthcli.so

    informix@irk:/tmp$ export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql
informix@irk:/tmp$ python3 test.py 
Traceback (most recent call last):
  File "/tmp/test.py", line 1, in <module>
    import IfxPy
ImportError: libthcli.so: cannot open shared object file: No such file or directory
informix@irk:/tmp$ 

informix@irk:/tmp$ export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/cli
informix@irk:/tmp$ 
informix@irk:/tmp$ python3 test.py 
systables
informix@irk:/tmp$ 
informix@irk:/tmp$

    Can you get a strace and check where is search for the library? Something like:

    informix@irk:/tmp$ strace -o trace.txt python3 test.py 
systables
informix@irk:/tmp$ grep libthcli.so trace.txt 
openat(AT_FDCWD, "/opt/informix/IBM/4.50.FC5/lib/libthcli.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/opt/informix/IBM/4.50.FC5/lib/esql/libthcli.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/opt/informix/IBM/4.50.FC5/lib/cli/libthcli.so", O_RDONLY|O_CLOEXEC) = 3
informix@irk:/tmp$

    Another test you can try is adding the lib directories to the system path

    informix@irk:/tmp$ cat /etc/ld.so.conf.d/ifx.conf 
/opt/informix/IBM/4.50.FC5/lib
/opt/informix/IBM/4.50.FC5/lib/esql
/opt/informix/IBM/4.50.FC5/lib/cli

informix@irk:/tmp$ sudo ldconfig

informix@irk:/tmp$ unset LD_LIBRARY_PATH

informix@irk:/tmp$ python3 test.py 
systables

informix@irk:/tmp$