使用 Teradata 模块将 Python 与 Teradata 连接 [英] Connecting Python with Teradata using Teradata module

问题描述

我在 Windows 7 上安装了 python 2.7.0 和 Teradata 模块.我无法从 python 连接和查询 TD.

pip install Teradata

现在我想在我的源代码中导入 teradata 模块并执行如下操作 -

1. 向 teradata 发起查询并获取结果集.
2. 检查是否已连接到 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 -

1. Firing queries to teradata and get result set.
2. 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)

这篇关于使用 Teradata 模块将 Python 与 Teradata 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！