python处理teradata数据库_使用Python连接到Teradata

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?

解决方案

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

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

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

Pyodbc

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)