3

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?

Pirate X
  • 3,023
  • 5
  • 33
  • 60
Ishwor Bhatta
  • 139
  • 1
  • 3
  • 13
  • Check out https://developer.teradata.com/tools/articles/teradata-sqlalchemy-introduction – a p Mar 10 '17 at 00:49

4 Answers4

3

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)
Alexis.Rolland
  • 5,724
  • 6
  • 50
  • 77
1

To connect to a teradata database, you need pyodbc, i also have problems with teradata dialect.

Example:

import pyodbc

user = 'user'

pasw = 'pass'

host = 'host'

connection = pyodbc.connect('DRIVER=Teradata;DBCNAME=' + host +';UID=' + user + ';PWD=' + pasw +';QUIETMODE=YES', autocommit=True,unicode_results=True)
Francisco de Larrañaga
  • 109
  • 1
  • 5
0

I am not sure why your are using sqlalchemy. But you could explore using Teradata module to connect to Teradata as explained in the other link: Connecting Python with Teradata using Teradata module

Community
  • 1
  • 1
Uma Senthil
  • 423
  • 2
  • 5
  • 18
  • 1
    I wanted to use the 'to_sql' feature of pandas' dataframe. Hence I used sqlalchemy. The problem was on index of the data frame. The issue is now solved when I use the following code; f3.to_sql(con=engine, name='sample', if_exists='replace', schema = 'schema_name', index = False) – Ishwor Bhatta Mar 10 '17 at 02:25
  • I'm getting the below error for teradata. Any ideas how to solve it? I'm using Anaconda. I can read from Teradata using Pyodbc. I can read/write from Oracle using sqlalchemy. I'm trying to write to Teradata, but no luck yet. NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:teradata – Ramsey Oct 30 '18 at 19:24
  • Do you have sqlalchemy module installed? – Uma Senthil Oct 30 '18 at 20:37
0

I met a similar problem in airflow, I used jars and jaydebeapi to connect teradata database and execute sql:

[root@myhost transfer]# cat test_conn.py 
import jaydebeapi
from contextlib import closing


jclassname='com.teradata.jdbc.TeraDriver'
jdbc_driver_loc = '/opt/spark-2.3.1/jars/terajdbc4-16.20.00.06.jar,/opt/spark-2.3.1/jars/tdgssconfig-16.20.00.06.jar'
jdbc_driver_name = 'com.teradata.jdbc.TeraDriver'
host='my_teradata.address'

url='jdbc:teradata://' + host + '/TMODE=TERA'
login="teradata_user_name"
psw="teradata_passwd"

sql = "SELECT COUNT(*) FROM  A_TERADATA_TABLE_NAME where month_key='202009'"


conn = jaydebeapi.connect(jclassname=jdbc_driver_name,
                                  url=url, 
                                  driver_args=[login, psw],
                                  jars=jdbc_driver_loc.split(","))

with closing(conn) as conn:
    with closing(conn.cursor()) as cur:
        cur.execute(sql)
        print(cur.fetchall())
   
[root@myhost transfer]# python test_conn.py
[(7734133,)]
[root@myhost transfer]#
kain
  • 214
  • 2
  • 4