Teradata and sqlachemy connection

Question

I wish to use sqlachemy with teradata dialect to push some csv into a table. So far I wrote this :

import pandas as pd
from sqlalchemy import create_engine

user = '******'
pasw = '******'
host = 'FTGPRDTD'
DATABASE = 'DB_FTG_SRS_DATALAB'

# connect
td_engine = create_engine('teradata://'+ user +':' + pasw + '@'+ DBCNAME + ':1025/')

print ('ok step one')
print(td_engine)
# execute sql
df = pd.read_csv(r'C:/Users/c92434/Desktop/Load.csv')
print('df chargé')

df.to_sql(name= 'mdc_load', con = td_engine, index=False, schema = DATABASE, 
if_exists='replace')
print ('ok step two')

This is the error message I get :

DatabaseError: (teradata.api.DatabaseError) (0, '[08001] [TPT][ODBC SQL Server Wire Protocol driver]Invalid Connection Data., [TPT][ODBC SQL Server Wire Protocol driver]Invalid attribute in connection string: DBCNAME.') (Background on this error at: http://sqlalche.me/e/4xp6)

What I can I do ?

Answer 1

Hopefully you've solved this by now, but I had success with this. Looking at what you provided, it looks like the host information you set is not being used in the connection string. My example includes the dtype parameter, which I use to define the data type for each column so they don't show up as CLOB.

database = "database_name"
table = "mdc_load"
user = "user"
password = "password"
host = 'FTGPRDTD:1025'

td_engine = create_engine(f'teradata://{user}:{password}@{host}/?database={database}&driver=Teradata&authentication=LDAP')
conn = td_engine.connect()
data.to_sql(name=table, con=conn, index=False, if_exists='replace', dtype=destType)
conn.close()

Answer 2

The "teradata" dialect (sqlalchemy-teradata module) relies on a Teradata ODBC driver being separately installed on the client platform. If you have multiple ODBC drivers installed that include the word Teradata in the name (for example, because you installed TPT with the Teradata-branded drivers for other database platforms), you may need to explicitly specify the one to be used by appending an optional parameter to your connection string, e.g.

td_engine = create_engine('teradata://'+ user +':' + pasw + '@'+ DBCNAME + ':1025/?driver=Teradata Database ODBC Driver 16.20')

Alternatively, you could use the "teradatasql" dialect (teradatasqlalchemy module) which does not require ODBC.