Df to sql to Teradata in python

Question

I'm trying to load a csv file into a Teradata table with the df.to_sql method. So far with Teradata python modules i was able to connect, but i can't manage to load my csv file.

Here is my code :

import teradata
import pandas as pd
global udaExec
global session
global host
global username
global password

def Connexion_Teradata(usernames,passwords):

   host= 'FTGPRDTD'
   udaExec = teradata.UdaExec (appName="TEST", version="1.0", logConsole=False)
   session=udaExec.connect(method="odbc",system=host, username=usernames,password=passwords, driver="Teradata")
   print('connection ok')

   df = pd.read_csv(r'C:/Users/c92434/Desktop/Load.csv')
   print('chargement df ok')
   df.to_sql(name = 'DB_FTG_SRS_DATALAB.mdc_load', con = session, if_exists="replace", index ="False" )
   print ('done')


Connexion_Teradata ("******","****")

When I play my script all I got is:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': (3707, "[42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like '(' between the 'type' keyword and '='. ")

What can I do?

I don't kow how to fix it, but the error message seems to indicate that the Select was written against a SQLite database ... — dnoeth, Aug 23 '19 at 09:36
Yes, to_sql assumes SQLite by default. Instead of passing the UdaExec session directly you will need to use sqlalchemy-teradata to pass an engine (or connection) object that uses the "teradata" dialect. Or use teradatasqlalchemy and the teradatasql dialect. — Fred, Aug 23 '19 at 15:20
So i installed teradatasqlalchemy but i cannot manage to create an engine connecting to teradata. Here is my code : from sqlalchemy import create_engine user = '****' pasw = '*****' host = 'FTGPRDTD' DATABASE = 'DB_FTG_SRS_DATALAB' # connect td_engine = create_engine('teradata://'+ user +':' + pasw + '@'+ host + ':22/' + DATABASE) — Kair0, Aug 26 '19 at 11:32

score 0 · Answer 1 · answered Sep 02 '20 at 17:06

Please try this:

from teradataml.dataframe.copy_to import copy_to_sql
from sqlalchemy import create_engine
import pandas as pd

sqlalchemy_engine  = create_engine('teradatasql://'+ user + ':' + passwd + '@'+host)
td_context = create_context(tdsqlengine = sqlalchemy_engine)

df = pd.read_csv(r'/Users/abc/test.csv')

Use copy_to_sql() function to create a table in Vantage based on a teradataml DataFrame or a pandas DataFrame.

copy_to_sql(df, 'testtable', if_exists='replace')

Df to sql to Teradata in python

1 Answers1