Jingwen Zheng
  • Feb 12, 2019

Python: Connection to Teradata Database

This blog will talk about how to connect to Teradata database and extract data with teradata python module.

As a data scientist, we need to extract data inevitably. Before extracting data, we should firstly connect to database. In this blog, I’ll talk about how to connect to Teradata Database with following points:

  • Required packages
  • Connecting to Teradata database
  • Extracting data with SQL query

Required packages

import pandas as pd
import teradata

teradata module is a freely available, open source, library for the Python programming language, whose aim is to make it easy to script powerful interactions with Teradata Database. It adopts the philosophy of udaSQL, providing a DevOps focused SQL Execution Engine that allows developers to focus on their SQL and procedural logic without worrying about Operational requirements such as external configuration, query banding, and logging.

Connecting to Teradata database

udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0",
                           logConsole=False)
session = udaExec.connect(method="odbc",
                          USEREGIONALSETTINGS="N",
                          system="tdprod",
                          username="xxx",
                          password="xxx");

Let’s read this example line by line: The first line initializes the UdaExec framework that provides DevOps support features such as configuration and logging. We tell UdaExec the name and version of our application during initialization so that we can get feedback about our application in DBQL and Teradata Viewpoint as this information is included in the QueryBand of all database sessions created by our script. We also tell UdaExec not to log to the console (e.g. logConsole=False) so that our print statement is easier to read. The second line creates a connection to a Teradata system named “tdprod” using ODBC as the connection method, and specify USEREGIONALSETTINGS="N" to ensure that float values can be loaded and make decimal separator be ‘.’

Extracting data with SQL query

After creating the connection to Teradata, we can extract data by SQL query.

employee_req = ("SELECT DISTINCT employee_id, employee_name, "
                "employee_salary "
                "FROM SALARY")
employee_df = pd.read_sql(employee_req, session)

We can apply session in pandas.read_sql() in order to executing SQL query employee_req.

Reference

Search