使用Teradata模块将Python与Teradata连接

I have installed python 2.7.0 and Teradata module on Windows 7. i am not able to connect and quyey TD from python.

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

Pip install Teradata

Now i want to import teradata module using Python and perform operations like -

现在我想使用Python导入teradata模块并执行如下操作:

1. firing queries to teradata and get result set.

向teradata发送查询并获取结果集。

2. check if connection is made to teradata. 3.etc..

检查是否与teradata建立了连接。 3.etc ..

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.

请帮我编写代码,因为我是Python的新手,并且没有可用的信息可以连接到teradata。

2 个解决方案

#1

There are a number of ways to connect to Teradata and export table to Pandas. Here are three:

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

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

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 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=%s;UID=%s;PWD=%s'%(host, username, 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 +'@'+host+'/'+'?driver=DRIVERNAME'
with create_engine(link) as connect:

    #Reading query to df
    df = pd.read_sql(query,connect)

There is a fourth 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).

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

Note: Updated 13-07-2018, using of context manager to ensure closing of sessions

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

#2

Download the Teradata Python module and python pyodbc.pyd from internet. Install using cmd install setup.py.

从互联网下载Teradata Python模块和python pyodbc.pyd。使用cmd install setup.py进行安装。

Here is the sample script for connecting to teradata and extracting data:

以下是连接teradata和提取数据的示例脚本:

import teradata
import pyodbc
import sys



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

session = udaExec.connect(method="odbc", dsn="prod32",
        username="PRODRUN", password="PRODRUN");

i = 0
REJECTED = 'R';

f = file("output.txt","w");sys.stdout=f

cursor =  session.cursor();

ff_remaining = 0;

cnt = cursor.execute("SELECT  SEQ_NO,FRQFBKDC,PNR_RELOC FROM ttemp.ffremaining ORDER BY 1,2,3 ").rowcount;
rows = cursor.execute("SELECT  SEQ_NO,FRQFBKDC,PNR_RELOC FROM ttemp.ffremaining ORDER BY 1,2,3 ").fetchall();


for i in range(cnt):
    ff_remaining = cursor.execute("select count(*) as coun from  ttemp.ffretroq_paxoff where seq_no=? and status <> ?",(rows[i].seq_no,REJECTED)).fetchall();
    print ff_remaining[0].coun, rows[i].seq_no, REJECTED;