python连接teradata_使用Teradata模块将Python与Teradata连接

有许多方法可以连接到Teradata并将表导出到Pandas.这是三个：

# You can install teradata via PIP: pip install teradata

# to get a list of your odbc drivers names, you could do: teradata.tdodbc.drivers

import teradata

import pandas as pd

host,username,password = 'HOST','UID', 'PWD'

#Make a connection

udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)

with udaExec.connect(method="odbc",system=host, username=username,

password=password, driver="DRIVERNAME") as connect:

query = "SELECT * FROM DATABASEX.TABLENAMEX;"

#Reading query to df

df = pd.read_sql(query,connect)

# do something with df,e.g.

print(df.head()) #to see the first 5 rows

import pyodbc

#You can install teradata via PIP: pip install pyodbc

#to get a list of your odbc drivers names, you could do: pyodbc.drivers()

#Make a connection

link = 'DRIVER={DRIVERNAME};DBCNAME={hostname};UID={uid};PWD={pwd}'.format(

DRIVERNAME=DRIVERNAME,hostname=hostname,

uid=username, pwd=password)

with pyodbc.connect(link,autocommit=True) as connect:

#Reading query to df

df = pd.read_sql(query,connect)

#You can install sqlalchemy via PIP: pip install sqlalchemy-teradata

#Note: It is not pip install sqlalchemy. If you already have sqlalchemy, you still need sqlalchemy-teradata to get teradata dialects

from sqlalchemy import create_engine

#Make a connection

link = 'teradata://{username}:{password}@{hostname}/?driver={DRIVERNAME}'.format(

username=username,hostname=hostname,DRIVERNAME=DRIVERNAME)

with create_engine(link) as connect:

#Reading query to df

df = pd.read_sql(query,connect)

还有第四种方法,使用giraffez module.我喜欢使用这个模块,因为它附带了MLOAD,FASTLOAD,BULKEXPORT等.初学者的唯一问题是它的要求(例如C/C++编译器,Teradata CLIv2和TPT API头文件/ lib文件) .

注意：更新了13-07-2018,使用上下文管理器确保关闭会话

更新：31-10-2018：使用teradata将数据从df发送到teradata

我们可以将数据从df发送到Teradata.避免’odbc’1 MB限制以及odbc驱动依赖,我们可以使用’rest’方法.我们需要主机ip_address,而不是驱动程序参数.注意：df中列的顺序应与Teradata表中的列顺序相匹配.

import teradata

import pandas as pd

# HOST_IP can be found by executing *>>nslookup viewpoint* or *ping viewpoint*

udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)

with udaExec.connect(method="rest",system="DBName", username="UserName",

password="Password", host="HOST_IP_ADDRESS") as connect:

data = [tuple(x) for x in df.to_records(index=False)]

connect.executemany("INSERT INTO DATABASE.TABLEWITH5COL")

values(?,?,?,?,?)",data,batch=True)

使用’odbc’,你必须将你的数据块大小小于1MB块以避免“[HY001] [Teradata] [ODBC Teradata Driver]内存分配错误”错误：例如.

import teradata

import pandas as pd

import numpy as np

udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)

with udaExec.connect(method="odbc",system="DBName", username="UserName",

password="Password", driver="DriverName") as connect:

#We can divide our huge_df to small chuncks. E.g. 100 churchs

chunks_df = np.array_split(huge_df, 100)

#Import chuncks to Teradata

for i,_ in enumerate(chunks_df):

data = [tuple(x) for x in chuncks_df[i].to_records(index=False)]

connect.executemany("INSERT INTO DATABASE.TABLEWITH5COL")

values(?,?,?,?,?)",data,batch=True)