Writing python (pandas) Data Frame to SQL Database Error

Question

I am trying to put a python data frame to a MS SQL DB and I am getting the following error

FUNCTION

def put_to_server(df):       # df is a pandas data frame
   server="KORNBSVM04\MSSQLSERVER2012"
   Driver="{SQL Server}"
   userid=''
   pswd=''
   cnxn = pyodbc.connect(driver=Driver, server=server, database="CIHOTLINE",uid=userid, pwd=pswd)
   cur=cnxn.cursor()
   df.to_sql(name='dbo.test',con=cnxn)

ERROR

 File "C:\Python27\lib\site-packages\pandas\core\generic.py", line 950, in to_sql
index_label=index_label)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 475, in to_sql
index_label=index_label)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1084, in to_sql
index_label=index_label)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 543, in __init__
if self.pd_sql.has_table(self.name):
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1094, in has_table
return len(self.execute(query).fetchall()) > 0
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1041, in execute
raise_with_traceback(ex)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1030, in execute
cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name='dbo.test';

What version of pandas are you using?

joris
– joris

2014-09-27 20:48:24 +00:00
Commented Sep 27, 2014 at 20:48 — joris
– joris, Commented Sep 27, 2014 at 20:48

joris · Accepted Answer · 2014-09-29 11:28:12Z

21

SQL server was never supported before pandas 0.14 (only mysql and sqlite were, with default of sqlite. Hence the error you get), but from pandas 0.14 it is supported to write dataframes to MS SQL server.
But to use this, you have to use an sqlalchemy engine (see docs) instead of a pyobdc connection object. Eg:

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
df.to_sql('test', engine)

See the pandas documentation on this: http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries

answered Sep 29, 2014 at 11:28

joris

140k37 gold badges257 silver badges207 bronze badges

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

4 Comments

Areza Over a year ago

I get "'Engine' object has no attribute 'cursor'"

CutePoison Over a year ago

I have the exact same issue; using and Engine object throws the "no cursor" error, and using raw_connection() throws the "sqlite master" error

joris Over a year ago

@CutePoison can you open an issue at github.com/pandas-dev/pandas/issues ?

CutePoison Over a year ago

I just figured it out; If I used "Driver={ODBC Driver 17 for SQL Server}" it works

Collectives™ on Stack Overflow

Writing python (pandas) Data Frame to SQL Database Error

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related