将 python (pandas) 数据帧写入 SQL 数据库错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26055556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing python (pandas) Data Frame to SQL Database Error
提问by Shivendra
I am trying to put a python data frame to a MS SQL DB and I am getting the following error
我正在尝试将 python 数据框放入 MS SQL DB 中，但出现以下错误
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';
采纳答案by joris
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:
在 pandas 0.14 之前从未支持 SQL 服务器（只有 mysql 和 sqlite 支持，默认为 sqlite。因此会出现错误），但从 pandas 0.14 开始支持将数据帧写入 MS SQL 服务器。
但是要使用它，您必须使用sqlalchemy引擎（请参阅文档）而不是 pyobdc 连接对象。例如：
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
请参阅熊猫文档：http: //pandas.pydata.org/pandas-docs/stable/io.html#sql-queries
