Storing a dataframe with sqlalchemy, pyodbc: SQL syntax error

Question

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

Toby Speight · Accepted Answer · 2018-10-03 14:07:50Z

1

Here's a two part answer:

Install sqlalchemy-teradata.

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

edited Oct 3, 2018 at 14:07

Toby Speight

32.4k58 gold badges83 silver badges118 bronze badges

answered Oct 3, 2018 at 13:47

Marc S

972 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Storing a dataframe with sqlalchemy, pyodbc: SQL syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related