Error while insert dataframe to Sql-server DB using to_sql function in python

Question

I am trying to insert pandas dataframe df into SQL Server DB using dataframe.to_sql function. But i getting below error:

Source code:

import pyodbc
import sqlalchemy
import urllib

df  #sample dataframe
params = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQL_User;PWD=pass")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
engine.connect()
df.to_sql(name='[Tableau].[dbo].[Test table]',con=engine, index=False, 
if_exists='append')

Error:

File "C:\Users\Arvinth\sqlalchemy\engine\default.py", line 470, in do_execute cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'INTEGER'. (102) (SQLExecDirectW)") [SQL: '\nCREATE TABLE [[Tableau].[dbo].[Test table]] (\n\t[A] INTEGER NULL, \n\t[B] INTEGER NULL, \n\t[C] INTEGER NULL\n)\n\n']

Sample dataframe:

Can anyone please help solve the issue.

This is not necessary [Tableau].[dbo]. as you are connecting to Tableau and default schema is dbo. Simply pass table name: df.to_sql(name='Test table',con=engine, index=False, if_exists='append') — Parfait
– Parfait, Commented Aug 15, 2017 at 17:32

Parfait · Accepted Answer · 2017-08-15 18:12:28Z

6

As commented, you referenced the database name and dbo schema for table name arg in pandas' to_sql call for '[Tableau].[dbo].[Test table]' and bracketed the names, rendering an error in the sqlAlchemy engine's CREATE TABLE SQL call. Since the current connection is the referenced database and the default schema is dbo, both qualifiers in name are not needed: ~~`'[Tableau].[dbo].~~'

Hence, in df.to_sql, simply reference the table name without bracket escaping:

df.to_sql(name='Test table', con=engine, index=False, if_exists='append')

which will create the needed table in the default dbo schema of the connected Tableau database:

CREATE TABLE [Test table] (
    [A] INTEGER NULL,
    [B] INTEGER NULL, 
    [C] INTEGER NULL
);

answered Aug 15, 2017 at 18:12

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

Comments

tuomastik · Accepted Answer · 2017-09-29 07:44:37Z

-2

Maybe you can refer the table in to_sql():

df.to_sql('test_table',con=engine, index=False, if_exists='append')

edited Sep 29, 2017 at 7:44

tuomastik

4,9856 gold badges46 silver badges52 bronze badges

answered Sep 29, 2017 at 7:06

user8694153

1

1 Comment

rene Over a year ago

this is also mentioned in the accepted answer. Only add new answers if you have another solution to the same problem.

Collectives™ on Stack Overflow

Error while insert dataframe to Sql-server DB using to_sql function in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related