Connecting to ODBC Databases on Windows from Python using Turbodbc

Turbodbc is a Python module to access databases using ODBC interface, primarily targeting data scientists, offering them efficient native connectivity to the databases. It offers built-in support for Numpy, a powerful package for scientific computing with Python for data scientists.  This is an open source project which can be found on Github, if you are interested in checking out the project.

This tutorial will help you get started on how you can connect to the databases using Progress DataDirect ODBC drivers.  For the tutorial, we will be walking you through on how you can connect to the SQL Server database from Python using Turbodbc. Let’s get started.

Following are the pre-requisites before you can start installing Turbodbc.

• OS: Windows 64-bit


• Python: 3.5 or 3.6, 64-bit


• Runtime: Microsoft Visual C++ 2015 Redistributable Update 3

 

To install, open your command prompt and install Turbodbc using pip by running the following command:

1. Download the SQL Server ODBC driver for Windows 64- bit from the Progress website.
2. Extract the downloaded zip file and run the setup.exe to install the SQLServer ODBC driver. It’s a simple setup and you should be done in few seconds.
3. Open the ODBC Administrator (64 bit) and On the ODBC administrator, choose Add -> Select DataDirect 7.X SQL Server Wire Protocol as your driver as shown below







4. After selecting DataDirect 7.1 SQL Server Wire Protocol as the driver, click on Finish button. You should now see a setup window as below. 






5. . Fill in your database server address in Hostname field, database name in the database field and by default the port number for SQL Server is 1433 which will be auto filled. You can change the post number if your database is using a different port. Click on Test Connect button and authenticate yourself to check if you are able to connect properly.

1. To connect to your database using the above configured ODBC driver in your ODBC administrator, open a python file and paste the following code


from turbodbc import connect
##create connection
connection = connect(dsn='DSN_NAME', uid='Your_database_username', pwd='Your_database_password')
cursor = connection.cursor()
##execute query
cursor.execute('SELECT TOP 10 * FROM <table_name>)
for row in cursor:
    print(row)






2. The above code is a simple snippet where I am connecting to the database through Progress DataDirect ODBC driver, executing a query and executing a select.
3. Save the Python file and run the program by opening command prompt and running the following command.


python <your-file-name>.py



4. If you have properly configured everything you will be see that data will be printed on the Command prompt.
5. Here is another code snippet which shows you how you can Insert data in to the database using Turbodbc.

We hope this tutorial helped you to connect to your SQL Server database using Progress DataDirect ODBC driver for SQL Server and Turbodbc from Python. Try a 15-day free evaluation of any of the Progress DataDirect ODBC drivers that are available for Relational, Big Data and SaaS data sources. Feel free to contact us if you need an extension for the trial.