将 python (pandas) 数据帧写入 SQL 数据库错误 [英] Writing python (pandas) Data Frame to SQL Database Error

查看:71
本文介绍了将 python (pandas) 数据帧写入 SQL 数据库错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试将 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆