使用Teradata模块将Python与Teradata连接 [英] Connecting Python with Teradata using Teradata module
问题描述
我已经在Windows 7上安装了python 2.7.0和Teradata模块.我无法从python连接和查询TD.
I have installed python 2.7.0 and Teradata module on Windows 7. I am not able to connect and query TD from python.
pip install Teradata
现在,我想在源代码中导入teradata模块并执行--p这样的操作
Now I want to import teradata module in my source code and perform operations like -
- 对Teradata进行查询并获取结果集.
- 检查是否已与Teradata建立连接.
请帮助我编写与我刚接触Python相同的代码,并且没有可用的信息连接到Teradata.
Please help me writing code for the same as I am new to Python and there is no information available with me to connect to teradata.
推荐答案
有多种方法可以连接到Teradata并将表导出到Pandas.这是四个以上:
There are a number of ways to connect to Teradata and export table to Pandas. Here are four+:
# 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 example
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
使用TeradataSQL
from @ymzkala:此软件包不需要您安装Teradata驱动程序(此软件包除外).
from @ymzkala : This package doesn't require you to install Teradata drivers (other than this package).
# Installing python -m pip install teradatasql
import teradatasql
with 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 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)
使用 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 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模块.我喜欢使用此模块,因为它随MLOAD,FASTLOAD,BULKEXPORT等一起提供.对于初学者来说,唯一的问题是它的要求(例如C/C ++编译器,Teradata CLIv2和TPT API标头/lib文件).
There is a fifth way, using giraffez module. I enjoy using this module as it come with MLOAD, FASTLOAD, BULKEXPORT etc. The only issue for beginners is its requirements (e.g C/C++ compiler ,Teradata CLIv2 and TPT API headers/lib files).
注意:已于2018年7月13日更新,使用上下文管理器确保会话关闭
Note: Updated 13-07-2018, using of context manager to ensure closing of sessions
更新时间:2018年10月31日:使用teradata将数据从df发送到teradata
我们可以将数据从df发送到Teradata.避免'odbc'1 MB限制和odbc驱动程序依赖性,我们可以使用'rest'方法.我们需要主机ip_address,而不是驱动程序参数. 注意::df中的列顺序应与Teradata表中的列顺序匹配.
We can send data from df to Teradata. Avoiding 'odbc' 1 MB limit and odbc driver dependency, we can use 'rest' method. We need host ip_address, instead of driver argument. NB: The order of columns in df should match the order of columns in Teradata table.
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驱动程序]内存分配错误"错误:例如
Using 'odbc', you have to chunk your data to less than 1MB chunks to avoid "[HY001][Teradata][ODBC Teradata Driver] Memory allocation error" error: E.g.
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)
这篇关于使用Teradata模块将Python与Teradata连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!