Using DataDirect ODBC drivers 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.

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.

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.

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 unixODBC-devel package before building and installing pyodbc.

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.

 

When running the sample code (above, and attached) at the Python terminal, 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.

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.