HowTo: Connecting pyodbc to an Informix Database via OpenLink ODBC Connectors (Drivers)

Data Access Drivers & Connectors Tips, Tricks, and HowTos
, , , ,
1

Overview

This guide provides step-by-step instructions for establishing a Python pyodbc connection to an Informix database using ODBC Data Source Names (DSNs) that have been configured to use the OpenLink Enterprise or Lite Edition ODBC to JDBC Bridge Drivers.

Prerequisites

  • Working installations of the OpenLink ODBC to JDBC Bridge Drivers (both Enterprise and Lite Editions)
  • Working Python 3 environment

pyodbc and Pandas Installation

  • Install the necessary Python packages:

    sudo apt install python3-pyodbc
sudo apt install python3-pandas

Setting an Enterprise Edition ODBC to JDBC Driver Data Source Name (DSN)

This data source name will use the Generic ODBC Driver component for Linux that bound to the installed ODBC to JDBC Bridge agent which has been configure to use a JDBC URL to connect to the target remote Informix database.

  1. Create an ODBC DSN for the Informix database using the following configuration:

    [mt_inf11]
Driver = /opt/openlink/lib/oplodbcu.so
ServerType = Informix 11.x
Username = informix
Password = xxxxxxx
Database = stores_demo
Options = ol_informix1410
FetchBufferSize = 99
ReadOnly = no
DeferLongFetch = no
Host = {hostname}:{port}
MultipleActiveStmtsEmul = Y
Description = Informix 11 DSN Connection
WideAsUTF16 = Y

Direct pyodbc Connection

  1. Use the following Python script to establish a direct connection to the Informix database and fetch customer data:

    import pyodbc

cnxn = pyodbc.connect('DSN=mt_inf11;UID=informix;PWD=xxxxxxx')
cursor = cnxn.cursor()
cursor.execute("SELECT fname, lname FROM customer")
row = cursor.fetchone()

while row:
    print(row)
    row = cursor.fetchone()

  2. Run the script:

    python3 pyodbc-uda-inf.py

    Example output:

    ('Ludwig', 'Pauli')
('Carole', 'Sadler')
('Philip', 'Currie')
...

Pandas pyodbc Connection

  1. Use the following Python script to connect to the Informix database using pandas:

    import pyodbc
import pandas as pd

cnxn = pyodbc.connect("DSN=mt_inf11;UID=informix;PWD=xxxxxxx")
query = "SELECT fname, lname FROM customer"
df = pd.read_sql_query(query, cnxn)
print(df)

  2. Run the script:

    python3 pyodbc-pandas-uda-inf.py

    Example output:

        fname     lname
0  Ludwig     Pauli
1  Carole     Sadler
2  Philip     Currie

Setting a Lite Edition ODBC to JDBC Driver Data Source Name (DSN)

This data source name will use the ODBC to JDBC Driver component for Linux that bound to the installed JAVA Runtime via a JDBC URL for the target remote Informix database.

[jdbc_informix_lt]         
Driver          = /opt/openlink/lib/jdbc18_mt_lu.so
ServerType      = Jdbc 1.8 
Username        = informix
Password        = xxxxxxx       
Database        = com.informix.jdbc.IfxDriver
Options         = jdbc:informix-sqli://{informix-server-hostname}:{port}/stores_demo:INFORMIXSERVER=ol_informix1410
FetchBufferSize = 99    
ReadOnly        = no
DeferLongFetch  = no
JetFix          = no
Description     = Sample Jdbc 1.8 Lite Connection
WideAsUTF16     = Y

Test Connection

$ iodbctestw jdbc_informix_lt
iODBC Unicode Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1419.0723
Driver: 08.00.190906 OpenLink JDBC Lite Driver (JDK 1.8) (jdbc18_mt_lu.so)

SQL> select count(*) from orders

(count(*))       
-----------------
23               

 result set 1 returned 1 rows.

SQL>

And the pyodbc connection using the jdbc_informix_lt DSN also work:

$ cat pyodbc-uda-jdbc_inf_lt.py
import pyodbc 
cnxn = pyodbc.connect('DSN=jdbc_informix_lt;UID=informix;PWD=xxxxxxx')
cursor = cnxn.cursor()
cursor.execute("select fname, lname from customer")
row = cursor.fetchone() 
while row: 
    print (row)
    row = cursor.fetchone()
$

$ python3 pyodbc-uda-jdbc_inf_lt.py
('Ludwig         ', 'Pauli          ')
('Carole         ', 'Sadler         ')
('Philip         ', 'Currie         ')
...

('Eileen         ', 'Neelie         ')
('Kim            ', 'Satifer        ')
('Frank          ', 'Lessor         ')
$

Troubleshooting

  • Ensure that the ODBC driver and Informix database credentials are correctly configured.
  • Check for any errors in the script or the output messages to debug any connection issues.
  • If encountering warnings with pandas, consider using SQLAlchemy for better compatibility.

Related