Connect Teradata using Python pyodbc – Steps and example

  • Post author:
  • Post last modified:February 12, 2020
  • Post category:General
  • Reading time:5 mins read

Teradata is one of the widely used MPP databases. Teradata provides many connectors such as its own Python connector Teradata, Teradatasql, etc. You can use those to interact with the Teradata database. There are many different options to connect to Teradata. Teradata provides support for JDBC and ODBC drivers. In this article, we will check method on connect Teradata using the Python pyodbc module and odbc driver with a working example.

Teradata ODBC Driver

Being a popular MPP database, Teradata database server comes with ODBC support.

Before attempting to connect Teradata database from either a Windows or Linux, you must download and install platform specific ODBC driver. Python program will expect you to install and configure odbc details. Without these details, Python won’t be able make connection.

Teradata ODBC Driver for Windows

To download an ODBC driver, go to the Teradata official download page and download appropriate latest version of the software.

Following link is the official repository.

Note that, you will not be able to use the Python pyodbc module without installing and configuring OBDC drivers on required system. This step is one of the pre-requisites to use pyodbc module.

Configure Teradata ODBC on Linux

Similarly, download and configure the Linux ODBC driver from following official repository.

Download appropriate tar file and configure odbc.ini file as per your system details.

Install Python pyodbc Module

Once you set up the ODBC driver on either Windows or Linux, the next step would be to install pyodbc module if you have not installed it already.

If you are using Anaconda Python distribution, it will come with pyodbc module.

Otherwise, use pip to install.

For example,

pip install pyodbc

Teradata ODBC Connection String

The Teradata odbc connection string is similar to other relational database connection string.

Following is the sample Teradata ODBC connection string:

DRIVER={DRIVERNAME};DBCNAME={hostname};UID={uid};PWD={pwd}

Note that, the Teradata driver name is ‘Teradata Database ODBC Driver 16.10’. It is depends on the version you installed.

Connect Teradata using Python pyodbc Example

If you have configured Teradata ODBC drivers properly, then you are good go and test it using the Python pyodbc module.

Following is the small Python program to test ODBC connection Python pyodbc module. This program return the current date of the system. 

import pyodbc
import pandas as pd

# pyodbc connection string
link = 'DRIVER={DRIVERNAME};DBCNAME={hostname};UID={uid};PWD={pwd}'.format(
                      DRIVERNAME='Teradata Database ODBC Driver 16.10',hostname='192.168.218.146',
                      uid='DBC', Database='DS_TBL_DB', pwd='DBC')

conn = pyodbc.connect(link)

# Define Cursor
cus=conn.cursor()

query = "select current_date;"

# Execute SQL statement to get current datetime and store result in cursor
cus.execute(query)

# Display the content of cursor
row = cus.fetchone()
print(row)

# Use Pandas to execute and retrieve results
df = pd.read_sql(query, conn)
print(df)

Following is the sample output of the program:

(datetime.date(2020, 2, 12), )

Hope this helps 🙂

Tags: , ,