How to connect to database with Python and iODBC under Linux?

Question

I would like to connect to a database with Python through iODBC under Linux (Ubuntu).

I installed packages iodbc and libiodbc2-dev. And I installed the driver for my database (a HFSQL Classic database from PcSoft (french solution)).

Then I can connect to my database with iodbctest (a C program from iodbc) with success.

Now I would like to connect with Python 3.

I tried pyodbc and pypyodbc modules, but they are linked to use unixODBC (libodbc.so), not iODBC (libiodbc.so).

How can I connect with Python to my database through iODBC under Linux (Ubuntu) ?

Answer 1

To connect to a database with Python using iODBC on Linux (Ubuntu), you can use the pyodbc module with a few extra steps to make it work with iODBC. Here's a step-by-step guide:

1. Install pyodbc using pip:

pip install pyodbc

2. Configure iODBC driver for your database: You mentioned that you have already installed the driver for your HFSQL Classic database from PcSoft. Make sure it is correctly configured in the iODBC configuration file (odbcinst.ini). You can typically find this file at /etc/odbcinst.ini.

3. Configure your data source in the iODBC configuration file (odbc.ini): Create or edit the odbc.ini file, typically located at /etc/odbc.ini, to define your data source. The data source is the connection information needed to connect to your database. Here's an example of how the odbc.ini might look like:

[MyDataSource]
Driver=TheDriverNameInOdbcInstIni
Database=YourDatabaseName
Server=YourDatabaseServer

Replace MyDataSource, TheDriverNameInOdbcInstIni, YourDatabaseName, and YourDatabaseServer with the appropriate values for your setup.

4. Set the ODBCINI environment variable: To make sure pyodbc uses the correct iODBC configuration files, you need to set the ODBCINI environment variable to point to your odbc.ini file. You can do this in your Python script or set it globally for your system. For example, in your Python script:

import os

os.environ['ODBCINI'] = '/path/to/your/odbc.ini'

Replace /path/to/your/odbc.ini with the actual path to your odbc.ini file.

5. Use pyodbc to connect to your database: Now you should be able to use pyodbc to connect to your HFSQL Classic database using iODBC. Here's a basic example:

import pyodbc

connection_string = 'DSN=MyDataSource;UID=YourUsername;PWD=YourPassword'
conn = pyodbc.connect(connection_string)

# Now you can use the 'conn' object to execute SQL queries and interact with the database.

Replace MyDataSource, YourUsername, and YourPassword with the appropriate values for your database connection.

With these steps, you should be able to connect to your HFSQL Classic database from Python using iODBC on Linux (Ubuntu) with the pyodbc module.