CODEWITHSUNDEEP
pythonsql-serverpandassql-insertpandas-to-sql
Arvinth Kumar
Arvinth Kumar

Reputation: 1014

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

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:

    A  B  C
 0  0  0  0
 1  1  1  1
 2  2  2  2
 3  3  3  3
 4  4  4  4
 5  5  5  5
 6  6  6  6
 7  7  7  7

Can anyone please help solve the issue.

Upvotes: 5

Views: 5237

Answers (2)

user8694153
user8694153

Reputation: 1

Maybe you can refer the table in to_sql():

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

Upvotes: -2

Parfait
Parfait

Reputation: 107567

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

Upvotes: 6

Related Questions