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';
Pandasql can work both on Pandas DataFrame and Series . The sqldf method is used to query the Dataframes and it requires 2 inputs: The SQL query string. globals() or locals() function.
Unlike SQL, Pandas has built-in functions that help when you don't even know what the data looks like. This is especially useful when the data is already in a file format (. csv, .
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With