使用Python连接到Teradata

本文介绍了使用Python连接到Teradata的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试连接到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 ，则还需要安装软件包 SQLAlchemy-Teradata .连接方法如下:

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_sessionmit() 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)