Storing a dataframe with sqlalchemy, pyodbc: SQL syntax error

0
votes

I would to like to store a dataframe into a Teradata database using the command pandas.to_sql, but get a SQL syntax error. Error appears to come from the built-in method, I don't know how to deal with it.

My code:

import pandas as pd
import datetime as dt
import sqlalchemy, pyodbc

todays_date = dt.datetime.now().date()
index = pd.date_range(todays_date-dt.timedelta(10), periods=10, freq='D')
columns = ['A','B', 'C']
df_ = pd.DataFrame(index=index, columns=columns)
df_ = df_.fillna(0)

engine = sqlalchemy.create_engine("mssql+pyodbc://" + user + ":" + passwd + "@" +dsnname)
df_.to_sql(name= 'TableTest', con = engine, if_exists='replace')

And the error I get:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and ')'. (-3706) (SQLExecDirectW)") [SQL: 'SELECT schema_name()']

1
pythonsqlalchemyteradatapyodbc

1 Answers

1
votes

Here's a two part answer:

  1. Install sqlalchemy-teradata.

  2. Create the engine and the table as follows:

    engine = sqlalchemy.create_engine("teradata://" + user + ":" + passwd + "@" +dsnname)
df.to_sql(name= 'TableTest', con = engine, index=False, schema='database_name', if_exists='replace')