python连接teradata数据库,Python PyTd teradata查询到Pandas DataFrame中

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?

解决方案

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 :)