Writing python (pandas) Data Frame to SQL Database Error
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
Shivendra
I have graduated in 2014 with a degree in Mathematics and Computing from IIT Kharagpur. I have 2 years of experience as Data Analyst with Robert Bosch, Bangalore and currently exploring new and challenging Data Sciences and Machine Learning opportunities.
Updated on July 05, 2022Comments
-
Shivendra almost 2 years
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';