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'.")

Solution 1

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.

Solution 2

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

Comments

[Parfait]

You need to use SQLAlchemy connection for to_sql and not your raw DB-API connection.

[Parfait]

Also, pypyodbc may not have an alchemy layer but pyodbc does.

[PineNuts0]

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?

[MaxU - stop genocide of UA]

@PineNuts0, you may want to check this answer