Python PyTd teradata Query Into Pandas DataFrame

Question

I'm using the PyTd teradata module to query data from Teradata and want to read it into a Pandas DataFrame

import teradata
import pandas as pd

# teradata connection
udaExec = teradata.UdaExec(appName="Example", version="1.0",
                           logConsole=False)
session = udaExec.connect(method="odbc", system="", username="", password="")

# Create empty dataframe with column names
query = session.execute("SELECT TOP 1 * FROM table")
cols = [str(d[0]) for d in query.description]
df = pd.DataFrame(columns=cols)

# Read data into dataframe
for row in session.execute("SELECT * FROM table"):
    print type(row)
    df.append(row)

row is of teradata.util.Row class and can't be appended to the dataframe. I tried converting it to a list but the format gets messed up.

How can I read my data into a dataframe from Teradata using the teradata module? I'm not able to use the pyodbc module for this.

Is there a better way to create the empty dataframe with column names matching those in the database?

Prayson W. Daniel · Accepted Answer · 2018-02-16 05:25:54Z

5

You can use pandas.read_sql :)

import teradata
import pandas as pd

# teradata connection
udaExec = teradata.UdaExec(appName="Example", version="1.0",
                           logConsole=False)
with udaExec.connect(method="odbc", system="", username="", password="") as session:


    query ="SELECT * FROM table"

    df = pd.read_sql(query,session)

Using ‘with’ will ensure close of session after the query. I hope that helped :)

edited Feb 16, 2018 at 5:25

answered Oct 2, 2017 at 10:15

Prayson W. Daniel

15.8k6 gold badges57 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shingav · Accepted Answer · 2016-11-14 06:40:42Z

I know its a little late. But putting a note nevertheless.

There are a few questions here.

How can I read my data into a dataframe from Teradata using the teradata module?

At the end of the day, a teradata.util.Row is simply a list. So a simple list operation should help you get things out of Row.

','.join(str(item) for item in row)

kinda thing.

Pushing that into a pandas dataframe should be a list to df conversion exercise.

I'm not able to use the pyodbc module for this.

I used teradata's python module to do a LDAP auth. All worked fine. Didn't have this requirement. Sorry.

Is there a better way to create the empty dataframe with column names matching those in the database?

I assume, given a table name, you can query to figure it schema (table names) >> convert that to a list and create your pandas df?

navinkb · Accepted Answer · 2017-06-13 16:50:53Z

0

I know this is very late.

You can use read_sql() from pandas module. It returns pandas dataframe.

Here is the reference: http://pandas.pydata.org/pandas-docs/version/0.20/generated/pandas.read_sql.html

answered Jun 13, 2017 at 16:50

navinkb

1363 bronze badges

Collectives™ on Stack Overflow

Python PyTd teradata Query Into Pandas DataFrame

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related