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 for to_sql and not your raw DB-API connection. — Parfait
– Parfait, Commented Jan 8, 2018 at 16:32
Also, pypyodbc may not have an alchemy layer but pyodbc does. — Parfait
– Parfait, Commented Jan 8, 2018 at 16:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 8, 2018 at 16:29

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PineNuts0 Over a year ago

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 - stand with Ukraine Over a year ago

@PineNuts0, you may want to check this answer

Brijesh Rana · Accepted Answer · 2019-03-26 07:59:14Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related