Quickly bring Data into Teradata with Python

DWH Pro Admin

January 29, 2021

minutes reading time


We often need to load a text file without writing elaborate loading scripts. If we want it to be quick and easy, it is neither valuable to design TPT scripts nor to use ETL tools (Informatica, AB Initio, Datastage, etc.). Teradata with Python is often the most straightforward choice.

This article will show you how to load any CSV file into a Teradata database with just a few code lines. Nevertheless, this example is intended for smaller files since no fastload or multiload protocol is used.

Preparing the CSV test file

In our example, we create a test table with three columns of data type INTEGER and load the CSV file shown below:

1,2,3
4,5,6
7,8,9
10,11,12

The Python Script

We first load the CSV file into a Teradata volatile table and reread it immediately. Each row is returned as a list. Each column of a row can then be easily accessed via the list element:

I think it can’t get any simpler than shown in our example:

import csv
import teradatasql
with open ('test.csv', newline='') as f:
    with teradatasql.connect ('{"host":"192.168.0.215","user":"dbc","password":"dbc"}') as con:
        with con.cursor () as cur:
            cur.execute ("create volatile table TestTable (num1 integer, num2 integer, num3 integer) on commit preserve rows")
            cur.execute ("insert into TestTable (?, ?, ?)", [ row for row in csv.reader (f) ])
            cur.execute ("select num1, num2, num3 from TestTable order by 1,2,3")
            [ print (row) for row in cur.fetchall () ]
teradata with python
The above figure shows the same script being executed in Jupyter Notebook
  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >