python teradata_使用Python连接Teradata

在Python中，有不同的方法可以连接到Teradata。下表并非详尽无遗。

sql炼金术from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base, DeferredReflection

from sqlalchemy.orm import scoped_session, sessionmaker

[...]

# Connect

engine = create_engine('teradata://' + user + ':' + password + '@' + host + ':22/' + database)

db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))

db_session.execute('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;') # To avoid locking tables when doing select on tables

db_session.commit()

Base = declarative_base(cls=DeferredReflection)

Base.query = db_session.query_property()

然后可以使用db_session进行查询。见SQLAlchemy Session API

Pyodbc

如果要使用Pyodbc，首先需要在计算机上安装Teradata驱动程序。在我的示例中，在安装Teradata驱动程序之后，/etc/odbcinst.ini中有以下条目[Teradata]

Driver=/opt/teradata/client/16.00/odbc_64/lib/tdata.so

APILevel=CORE

ConnectFunctions=YYY

DriverODBCVer=3.51

SQLLevel=1

然后我可以连接以下内容：import pyodbc

[...]

#Teradata Connection

connection= pyodbc.connect("driver={Teradata};dbcname=" + host + ";uid=" + user + ";pwd=" + pwd + ";charset=utf8;", autocommit=True)

connection.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')

connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')

connection.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-8')

connection.setencoding(encoding='utf-8')

cursor= n.cursor()

cursor.execute("Select 'Hello World'")

for row in cursor:

print (row)