9

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)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
AlexSB
  • 585
  • 1
  • 5
  • 15
  • How are you calling `to_sql`? Are you passing the SQLAlchemy engine as [the second argument](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html)? – unutbu Jul 26 '17 at 11:53
  • 2
    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 Jul 26 '17 at 11:54
  • It would be very helpful if you can post a short runnable example which demonstrates the problem. That way we can see exactly how everything is defined and see what line is generating what error message. – unutbu Jul 26 '17 at 15:57
  • @unutbu see the runable example on the edited question. Thanks! – AlexSB Jul 27 '17 at 08:08
  • Thanks for the runnable example. Using your definition of `engine`, above, what does `import pandas.io.sql as psql`, `pandas_sql = psql.pandasSQL_builder(engine, schema=None, flavor=None)`, `print(type(pandas_sql))` return? – unutbu Jul 27 '17 at 11:46
  • @unutbu here is the output: `` – AlexSB Jul 27 '17 at 13:44
  • Your code looks perfectly correct to me. Perhaps you might want to [open an issue here](https://github.com/pandas-dev/pandas/issues) to bring it to the developers attention. – unutbu Jul 27 '17 at 18:26
  • 1
    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 Jul 28 '17 at 09:08

3 Answers3

14

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)
Scratch'N'Purr
  • 9,959
  • 2
  • 35
  • 51
  • 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. – AlexSB Jul 26 '17 at 12:25
  • 1
    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. – Scratch'N'Purr Jul 26 '17 at 12:37
  • 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... – AlexSB Jul 26 '17 at 14:21
  • Hmm, I'm not too sure since your syntax looks right. But I did find an [old post](https://stackoverflow.com/questions/34302451/writing-to-postgresql-from-pandas-attributeerror-engine-object-has-no-attrib) 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. – Scratch'N'Purr Jul 26 '17 at 14:34
  • @Scratch'N'Purr Thanks. I was having the same issue, myeng.connect() worked for me. – Harshit Mehta Nov 29 '18 at 17:10
3

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)
CaiYongAn
  • 31
  • 3
1

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.

AlSub
  • 1,384
  • 1
  • 14
  • 33
Faller
  • 1,588
  • 3
  • 16
  • 27