Python: Write Pandas Dataframe to MSSQL --> Database Error

Question

I have a pandas dataframe that has about 20k rows and 20 columns. I want to write it to a table in MSSQL.

I have the connection successfully established:

connection = pypyodbc.connect('Driver={SQL Server};' 
                              'Server=XXX;' 
                              'Database=line;' 
                              'uid=XXX;' 
                              'pwd=XXX')

cursor = connection.cursor()

I'm trying to write my pandas dataframe to the MSSQL server with the following code:

df_EVENT5_16.to_sql('MODREPORT', connection, if_exists = 'replace')

But I get the following error:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'.")

You need to use [SQLAlchemy connection](http://docs.sqlalchemy.org/en/latest/dialects/mssql.html) for `to_sql` and not your raw DB-API connection. — Parfait, Jan 08 '18 at 16:32
Also, `pypyodbc` may not have an alchemy layer but `pyodbc` does. — Parfait, Jan 08 '18 at 16:33

score 12 · Answer 1 · edited Jun 20 '20 at 09:12

12

Modern Pandas versions expect SQLAlchemy engine as a connection, so use SQLAlchemy:

from sqlalchemy import create_engine

con = create_engine('mssql+pyodbc://username:password@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0')

and then:

df_EVENT5_16.to_sql('MODREPORT', con, if_exists='replace')

from DataFrame.to_sql() docs:

con : SQLAlchemy engine or DBAPI2 connection (legacy mode)

Using SQLAlchemy makes it possible to use any DB supported by that library.

If a DBAPI2 object, only sqlite3 is supported.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 08 '18 at 16:29

MaxU - stand with Ukraine

205,989
36
386
419

thank you .. do I need to pip install sql alchemy first? Also, after your 'mssql+pyodbc' statement ... is it: what is scott? what is tiger? what is mydsn? – PineNuts0 Jan 08 '18 at 16:33
@PineNuts0, you may want to check [this answer](https://stackoverflow.com/a/41729710/5741205) – MaxU - stand with Ukraine Jan 08 '18 at 16:37

Brijesh Rana · Answer 2 · 2019-03-26T07:59:14.830

No need to use pyodbc to connect with MSSQL, SQL Alchemy will do that for you. And also we can insert the data-frame directly into the database without iterating the data-frame using to_sql() method. Here is the code that working fine for me -

# To insert data frame into MS SQL database without iterate the data-frame
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select
from six.moves import urllib
params = urllib.parse.quote_plus("DRIVER={SQL 
Server};SERVER=serverName;DATABASE=dbName;UID=UserName;PWD=password")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) 
engine.connect() 
# suppose df is the data-frame that we want to insert in database
df.to_sql(name='table_name',con=engine, index=False, if_exists='append')

Python: Write Pandas Dataframe to MSSQL --> Database Error

2 Answers2