DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Understanding the Fan-Out/Fan-In API Integration Pattern
  • Apache Spark for the Impatient
  • Rails 6: Multiple DB Support
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Trending

  • Performance Optimization Techniques for Snowflake on AWS
  • The Modern Data Stack Is Overrated — Here’s What Works
  • Unlocking AI Coding Assistants Part 3: Generating Diagrams, Open API Specs, And Test Data
  • Accelerating AI Inference With TensorRT
  1. DZone
  2. Data Engineering
  3. Databases
  4. Tutorial: Connecting to ODBC Data Sources With Python and pyodbc

Tutorial: Connecting to ODBC Data Sources With Python and pyodbc

In this tutorial, learn how to easily install and use a DataDirect ODBC driver, Python, and pyodbc. Code samples are included.

By 
Saikrishna Teja Bobba user avatar
Saikrishna Teja Bobba
·
Aug. 15, 17 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
90.2K Views

Join the DZone community and get the full member experience.

Join For Free

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.

python

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.

PythonODBCTerminal3

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.

ImportPythonODBC

Output

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

PythonODBCTerminal1PythonODBCTerminal2

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

PythonOutput

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.

Python (language) Data (computing) Driver (software) Wire protocol Database application Connection (dance) 64-bit Microsoft SQL Server

Published at DZone with permission of Saikrishna Teja Bobba, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Understanding the Fan-Out/Fan-In API Integration Pattern
  • Apache Spark for the Impatient
  • Rails 6: Multiple DB Support
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: