使用Python连接到Teradata [英] Connecting to Teradata using Python

问题描述

我正在尝试连接到Teradata服务器，并使用python将数据帧加载到表中.这是我的代码-

I am trying to connect to teradata server and load a dataframe into a table using python. Here is my code -

import sqlalchemy 

engine = sqlalchemy.create_engine("teradata://username:passwor@hostname:port/")

f3.to_sql(con=engine, name='sample', if_exists='replace', schema = 'schema_name')

但是我遇到了以下错误-

But I am getting the following error -

InterfaceError: (teradata.api.InterfaceError) ('DRIVER_NOT_FOUND', "No driver found for 'Teradata'.  Available drivers: SQL Server,SQL Server Native Client 11.0,ODBC Driver 13 for SQL Server")

有人可以帮助我弄清楚我的方法中有什么问题吗?

Can anybody help me to figure out whats wrong in my approach?

推荐答案

在Python中有多种连接Teradata的方法.以下列表并不详尽.

There's is different ways to connect to Teradata in Python. The following list is not exhaustive.

SQLAlchemy

如果您希望使用 SQLAlchemy ，则还需要安装软件包

If you wish to use SQLAlchemy, you will also need to install the package SQLAlchemy-Teradata. Here is how you can connect:

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会话API

Then you can use db_session to make queries. See SQLAlchemy Session API

Pyodbc

如果您想使用 Pyodbc 首先，您需要在计算机上安装Teradata驱动程序.我的示例，安装Teradata驱动程序后，我在/etc/odbcinst.ini

If you wish to use Pyodbc you will first need to install Teradata driver on your machine. Example on mine, after installing Teradata driver I have the following entry in /etc/odbcinst.ini

[Teradata]
Driver=/opt/teradata/client/16.00/odbc_64/lib/tdata.so
APILevel=CORE
ConnectFunctions=YYY
DriverODBCVer=3.51
SQLLevel=1

然后我可以连接以下内容:

Then I can connect with the following:

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)

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