将 python (pandas) 数据帧写入 SQL 数据库错误 [英] Writing python (pandas) Data Frame to SQL Database Error
问题描述
我正在尝试将 python 数据框放入 MS SQL DB,但出现以下错误
I am trying to put a python data frame to a MS SQL DB and I am getting the following error
功能
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)
错误
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';
推荐答案
在 pandas 0.14 之前从未支持 SQL 服务器(只有 mysql 和 sqlite 支持,默认为 sqlite.因此出现错误),但从 pandas 0.14 开始支持将数据帧写入 MS SQL 服务器.
但是要使用它,您必须使用 sqlalchemy 引擎(请参阅 docs) 而不是 pyobdc 连接对象.例如:
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)
查看熊猫文档:http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries
这篇关于将 python (pandas) 数据帧写入 SQL 数据库错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!