to_sql pandas data frame into SQL server error: DatabaseError

Question

While trying to write a pandas' dataframe into sql-server, I get this error:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

It seems pandas is looking into sqlite instead of the real database.

It's not a connection problem since I can read from the sql-server with the same connection using pandas.read_sql The connection has been set using

sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

It's not a database permission problem either since I can write line by line using the same connection parameters as:

cursor = conn.cursor()
cursor.execute('insert into test values (1, 'test', 10)')
conn.commit()

I could just write a loop to instert line by line but I would like to know why to_sql isn't working for me, and I am affraid it won't be as efficient.

Environment: Python: 2.7 Pandas: 0.20.1 sqlalchemy: 1.1.12

Thanks in advance.

runnable example:

import pandas as pd
from sqlalchemy import create_engine
import urllib

params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=
<servername>;DATABASE=<databasename>;UID=<username>;PWD=<password>")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

test = pd.DataFrame({'col1':1, 'col2':'test', 'col3':10}, index=[0])
conn=engine.connect().connection
test.to_sql("dbo.test", con=conn, if_exists="append", index=False)

Answer 1

According to the to_sql doc, the con parameter is either an SQLAchemy engine or the legacy DBAPI2 connection (sqlite3). Because you are passing the connection object rather than the SQLAlchemy engine object as the parameter, pandas is inferring that you're passing a DBAPI2 connection, or a SQLite3 connection since its the only one supported. To remedy this, just do:

myeng = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

# Code to create your df
...

# Now write to DB
df.to_sql('table', myeng, index=False)