使用Teradata模块将Python与Teradata连接

使用Teradata模块将Python与Teradata连接

有多种方法可以连接到Teradata并将表导出到Pandas。这是四个+：

使用Teradata模块

# You can install teradata via PIP: pip install teradata# to get a list of your odbc drivers names, you could do: teradata.tdodbc.drivers# You don’t need to install teradata odbc driver if using method='rest'.     # See sending data from df to teradata for connection exampleimport teradataimport pandas as pdhost,username,password = 'HOST','UID', 'PWD'#Make a connectionudaExec = 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

使用TeradataSQL

from @ymzkala：此软件包不需要您安装Teradata驱动程序（此软件包除外）。

# Installing python -m pip install teradatasqlimport teradatasqlwith teradatasql.connect(host='host', user='username', password='password') as connect:    df = pd.read_sql(query, connect)

使用pyodbc模块

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 connectionlink = '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)

使用sqlalchemy模块

 #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 dialectsfrom sqlalchemy import create_engine#Make a connectionlink = '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模块。我喜欢使用此模块，因为它随MLOAD，FASTLOAD，BULKEXPORT等一起提供。对于初学者来说，唯一的问题是其要求（例如C
/ C ++编译器，Teradata CLIv2和TPT API标头/ lib文件）。

注意：已于2018年7月13日更新，使用上下文管理器确保会话关闭

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

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

import teradataimport 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驱动程序]内存分配错误”错误：例如

import teradataimport pandas as pdimport numpy as npudaExec = 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)