Tech Monger

If you are connecting to the ODBC database then it is necessary to test connection before creating ODBC data source inisde ODBC.INI file. Below we will create simple python script which initiate ODBC connection with details provided inside ODBC.ini file and output if connection is working or failing.

---

Install Pyodbc with pip

pip install pyodbc

---

Pyodbc Caveats - Read Before Jumping to Code

• pyodbc by default will look for the ODBC.INI file inside /etc/ directory in linux i.e. /etc/odbc.ini.

• If you have it configured at some different location then please set value of environment variable $ODBC_HOME and $ODBC_INI accordingly inside .profile file of the user OR you can export it manually with before executing below python code.

• Pyodbc assumes that ODBC drivers are installed for the given data source and working correctly. Hence if your drivers are not working it will flag connection not working as well.

Example

Let say you want to test following Denodo ODBC Data Source configured inside odbc.ini file.

DENODO_VDP_CONNECTION=ODBC DRIVER FOR DENODO VDP

[DENODO_VDP_CONNECTION]
Driver=/path/to/odbc-drivers/denodo-vdp-odbcdriver-linux/lib/unixodbc_x32/denodoodbc.so
Description=ODBC DRIVER FOR DENODO VDP
DriverUnicodeType=1
Servername=denododb.example.com
Database=Denodo_DB_NAME
PortNumber=9999
Username=Denodo_DB_USERNAME
Password=Denodo_DB_PASS

Code : Python Script to Test above ODBC Connection

import pyodbc

try:
    dsn= "DENODO_VDP_CONNECTION"
    username = "Denodo_DB_NAME"
    password = "Denodo_DB_PASS"
    connect_string = "DSN={d};UID={u};PWD={p}".format(d=dsn, u=username, p=password)
    cnxn = pyodbc.connect(connect_string)
    print("{c} is working".format(c=connect_string))
    cnxn.close()
except pyodbc.Error as ex:
    print("{c} is not working".format(c=connect_string))

Note that in above code we have replaced string values for the variable dsn, username and password with the actual values present inside configured (odbc.ini) denodo data source.

Conclusion