使用 Teradata 模块将 Python 与 Teradata 连接
问题描述
我在 Windows 7 上安装了 python 2.7.0 和 Teradata 模块.我无法从 python 连接和查询 TD.
pip install Teradata
现在我想在我的源代码中导入 teradata 模块并执行如下操作 -
- 向 teradata 发起查询并获取结果集.
- 检查是否已连接到 teradata.
请帮我编写代码，因为我是 Python 新手，我没有可用的信息来连接到 teradata.
解决方案有多种方法可以连接到 Teradata 并将表导出到 Pandas.这里有四个+:
使用 teradata 模块
# 可以通过 PIP 安装 teradata:pip install teradata# 要获取您的 odbc 驱动程序名称列表，您可以执行以下操作:teradata.tdodbc.drivers# 如果使用 method='rest'，则无需安装 teradata odbc 驱动程序.# 有关连接示例，请参见从 df 向 teradata 发送数据导入 teradata将熊猫导入为 pd主机，用户名，密码 = 'HOST'，'UID'，'PWD'#建立连接udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)使用 udaExec.connect(method="odbc",system=host, username=username,密码=密码，驱动程序=驱动程序名称")作为连接:查询 = 选择 * 从 DATABASEX.TABLENAMEX；"#读取查询到dfdf = pd.read_sql(查询，连接)# 用 df 做一些事情，例如print(df.head()) #查看前5行使用 TeradataSQL
<块引用>来自@ymzkala:此软件包不需要您安装 Teradata 驱动程序(此软件包除外).
# 安装 python -m pip install teradatasql导入 teradatasql使用 teradatasql.connect(host='host', user='username', password='password') 作为连接:df = pd.read_sql(查询，连接)使用pyodbc模块
导入pyodbc#可以通过PIP安装teradata:pip install pyodbc#要获取您的 odbc 驱动程序名称列表，您可以执行以下操作: pyodbc.drivers()#建立连接链接='驱动程序={驱动程序名称}；DBCNAME={主机名}；UID={uid}；PWD={pwd}'.format(驱动程序名=驱动程序名，主机名=主机名，uid=用户名，pwd=密码)使用 pyodbc.connect(link,autocommit=True) 作为连接:#读取查询到dfdf = pd.read_sql(查询，连接)使用 sqlalchemy 模块
#可以通过PIP安装sqlalchemy:pip install sqlalchemy-teradata#注意:不是 pip install sqlalchemy.如果你已经有 sqlalchemy，你仍然需要 sqlalchemy-teradata 来获取 teradata 方言从 sqlalchemy 导入 create_engine#建立连接链接 = 'teradata://{username}:{password}@{hostname}/?driver={DRIVERNAME}'.format(用户名=用户名，主机名=主机名，驱动程序名=驱动程序名)使用 create_engine(link) 作为连接:#读取查询到dfdf = pd.read_sql(查询，连接)还有第五种方式，使用 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 表中的列顺序一致.
导入 teradata将熊猫导入为 pd# HOST_IP 可以通过执行 *>>nslookup 视点* 或 *ping 视点* 找到udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)使用 udaExec.connect(method="rest",system="DBName", username="UserName",password="Password", host="HOST_IP_ADDRESS") 作为连接:data = [df.to_records(index=False) 中 x 的元组 (x)]connect.executemany("INSERT INTO DATABASE.TABLEWITH5COL 值(?,?,?,?,?)",data,batch=True)使用odbc"，您必须将数据分块为小于 1MB 的块，以避免[HY001][Teradata][ODBC Teradata Driver] 内存分配错误"错误:例如
导入 teradata将熊猫导入为 pd将 numpy 导入为 npudaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)使用 udaExec.connect(method="odbc",system="DBName", username="UserName",password="Password", driver="DriverName") 作为连接:#我们可以将我们的huge_df分成小块.例如.100 座教堂chunks_df = np.array_split(huge_df, 100)#将块导入 Teradata对于 i,_ in enumerate(chunks_df):data = [tuple(x) for x in chuncks_df[i].to_records(index=False)]connect.executemany("INSERT INTO DATABASE.TABLEWITH5COL 值(?,?,?,?,?)",data,batch=True)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
Now I want to import teradata module in my source code and perform operations like -
- Firing queries to teradata and get result set.
- Check if connection is made to 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.
解决方案There are a number of ways to connect to Teradata and export table to Pandas. Here are four+:
Using teradata module
# 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
Using TeradataSQL
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)
Using pyodbc module
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)
Using sqlalchemy Module
#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)
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).
Note: Updated 13-07-2018, using of context manager to ensure closing of sessions
Update: 31-10-2018: Using teradata to send data from df to 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)
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)
相关文章