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)

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

Solution 2

try this. good to connect MS SQL server(SQL Authentication) and update data

from sqlalchemy import create_engine
params = urllib.parse.quote_plus(
'DRIVER={ODBC Driver 13 for SQL Server};'+
'SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

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

#df: pandas.dataframe; mTableName:table name in MS SQL
#warning: discard old table if exists
df.to_sql(mTableName, con=engine, if_exists='replace', index=False)

Solution 3

So I ran into this same thing. I tried looking through the code, couldn't figure out why it wasn't working but it looks like it gets stuck on this call.

pd.io.sql._is_sqlalchemy_connectable(engine)

I found that if I run this first it returns True, but as soon as I run it after running df.to_sql() it returns False. Right now I'm running it before I do the df.to_sql() and it actually works.

Hope this helps.

Comments

[AlexSB]

Hey! No I'm not... I just run df.to_sql("dbo.test", con=conn, if_exists="append") and pandas automatically searchs for sqlite_master; I don't know why! Where conn=engine.connect().connection

[AlexSB]

Finally, I coded the loop to upload the table line by line and the error there was more clear than using pandas. Basically there was an error on the variables types... 'pandas.to_sql` works if I convert all pandas' data frame to string and upload it to a sql varchar table. I've been working with python for 2 weeks so I cannot give further information about the format error yet. Both @unutbu @Scratch'N'Purr thank you for your help.

[AlexSB]

Thank for the answer. In fact, this is the first thing I tried but I got the following error: AttributeError: 'Engine' object has no attribute 'cursor'. I would appreciate any workaround on this.

[Scratch'N'Purr]

Hmm, how about trying conn = myeng.connect(), and then passing that as the connection object? I don't have any trouble passing myeng as the argument, but then I'm using ODBC Driver 13 for SQL Server as my ODBC driver, so it could be nuances between drivers.

[AlexSB]

Same result... I also tried engine.raw_connection() which I read in another post and I got the same result. In addition, I tried changing the driver to [SQL Server] and [ODBC Driver 11 for SQL Server] with no luck either...

[Scratch'N'Purr]

Hmm, I'm not too sure since your syntax looks right. But I did find an old post that had a similar issue with a different SQL flavor. The comments there might serve as something useful. If all else fails, I would probably create a clean environment and install the necessary packages in there and try it again, because what you have attempted should be working.

[Harshit Mehta]

@Scratch'N'Purr Thanks. I was having the same issue, myeng.connect() worked for me.