Tutorial: Connecting to ODBC Data Sources With Python and pyodbc

Python is a general-purpose interpreted, interactive, object-oriented, and high-level programming language. It was created by Guido van Rossum and first released in 1991.

Pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification. Using pyodbc, you can easily connect Python applications to data sources with an ODBC driver.

As with other application stacks connecting through the ODBC API, the application—in this case your Python code along with the pyodbc module—will use an ODBC driver manager and ODBC driver. The ODBC driver manager is platform-specific, while the ODBC driver is database-specific. The ODBC driver manager and driver will connect, typically over a network, to the database server.

For more information on pyodbc, see the Github Pyodbc Wiki. 

Once a connection has been established, your application can execute selects, inserts, or other ODBC operations supported by your driver and database.

Below, we provide a tutorial to show you how to install a DataDirect ODBC driver, Python, and pyodbc. We've also provided a code sample demonstrating a connection to Microsoft SQL Server using the Python ODBC interface via the Progress DataDirect Connect64 for ODBC SQL Server Wire Protocol driver.

While this tutorial uses a simple code sample to connect and demonstrate basic operations, we recommend that you fully test your ODBC driver with the complete set of operations you expect to use in your production application. A quick test connection can demonstrate that connectivity has been successfully established, but won't necessarily test all the operations and load used by a full application.

Our tutorial demonstrates a connection to SQL Server with pyodbc, but you can use the same steps to connect to any data source using a DataDirect ODBC driver. Instead of using a data source, the code can also be modified to pass a connection string to the pyodbc.connect() function for DSN-less connections.

First, download a free 15 day trial of DataDirect ODBC drivers. Then follow the tutorial below to connect to your database using Python and pyodbc.

Using DataDirect ODBC Drivers With Python and pyodbc

This tutorial shows Python 3.6.1 and pyodbc working with the 64-bit Progress DataDirect Connect64 for ODBC Oracle Wire Protocol driver. The same steps are applicable to other ODBC drivers available from Progress Software.

Install and Configure the Driver

1. Download and install a 15-day evaluation copy of the Progress DataDirect Connect64 for ODBC Oracle Wire Protocol driver.

2. Create an ODBC Data Source to connect to Oracle as per the Progress DataDirect Connect Series for ODBC User’s Guide, “Quick Start Connect” : “Configuring and Connecting on UNIX and Linux" : "Configuring a Data Source."

Refer to Progress DataDirect Connect Series for ODBC User’s Guide, "Configuring the Product on UNIX/Linux" : "Data Source Configuration" : "Configuration Through the System Information (odbc.ini) File" : "Sample Default odbc.ini File" to view the settings for the Oracle Wire Protocol driver data source in the sample default odbc.ini file.

3. Configure the environment as per the Progress DataDirect Connect Series for ODBC User’s Guide, "Quick Start Connect" : "Configuring and Connecting on UNIX and Linux" : "Environment Configuration"

4. Ensure that the drivers are installed properly by doing a test connect using any of the sample applications that are installed with the driver before proceeding to use the drivers with Python. Refer to Progress DataDirect Connect Series for ODBC Troubleshooting Guide : "Diagnostic Tools" : "The example Application" and Progress DataDirect Connect Series for ODBC Troubleshooting Guide : "Diagnostic Tools" : "The demoodbc Application" for more information on the example and demoodbc applications respectively.

5. For performance considerations, refer to the Progress DataDirect Connect Series for ODBC User’s Guide, "Drivers for 32-Bit and 64-Bit Platforms" : "The Oracle Wire Protocol Driver" : "Performance Considerations" for connection option settings that impact performance.

Install Python and pyodbc

1. Download Python 3.6.1.

2. Build and install Python 3.6.1 according to https://docs.python.org/3.6/using/unix.html#on-linux.

3. Download pyodbc.

4. Build and install pyodbc from source according to https://github.com/mkleehammer/pyodbc/wiki/Building-pyodbc-from-source.

Make sure you install the unixODBC-devel package before building and installing pyodbc.

Python Code Sample

Run the below code from the Python prompt or save the code to a .py file and then execute the file. The code sample has been attached to this tutorial as a plain text file.

Ensure that you have all the required environment variables set, as per step 3 in "Install and Configure the Driver" above.

The Python code sample demonstrates connecting to Oracle using the Python ODBC interface using the Progress DataDirect Connect64 for ODBC Oracle Wire Protocol driver.

The code uses a data source named “SQLS” from the odbc.ini file to connect and issue a query. It creates a table, inserts data using literal and parameterized statements and fetches the data.

Output

When running the sample code (above, and attached) at the Python terminal, the output will be the following:

When running the same sample code from a file, the output will be the following:

The code can be modified to pass a connection string instead of using a data source to the pyodbc.connect() function for DSN-less connections.

Conclusion

While this tutorial demonstrates Python and pyodbc working with the 64-bit Progress DataDirect Connect64 for ODBC Oracle Wire Protocol driver, the same steps are applicable to other ODBC drivers available from Progress Software.

Learn more and get started today with a free 15 day evaluation of Progress ODBC drivers, available for many different data sources.