Python：将 Pandas 数据帧写入 MSSQL --> 数据库错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48154172/
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
Python: Write Pandas Dataframe to MSSQL --> Database Error
提问by PineNuts0
I have a pandas dataframe that has about 20k rows and 20 columns. I want to write it to a table in MSSQL.
我有一个大约有 20k 行和 20 列的 Pandas 数据框。我想将它写入 MSSQL 中的表。
I have the connection successfully established:
我已成功建立连接：
connection = pypyodbc.connect('Driver={SQL Server};'
'Server=XXX;'
'Database=line;'
'uid=XXX;'
'pwd=XXX')
cursor = connection.cursor()
I'm trying to write my pandas dataframe to the MSSQL server with the following code:
我正在尝试使用以下代码将我的 Pandas 数据帧写入 MSSQL 服务器：
df_EVENT5_16.to_sql('MODREPORT', connection, if_exists = 'replace')
But I get the following error:
但我收到以下错误：
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'.")
DatabaseError: sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 上的执行失败' sqlite_master'.")
回答by MaxU
Modern Pandas versions expect SQLAlchemy engineas a connection, so use SQLAlchemy:
现代 Pandas 版本期望SQLAlchemy engine作为连接，所以使用 SQLAlchemy：
from sqlalchemy import create_engine
con = create_engine('mssql+pyodbc://username:password@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0')
and then:
进而：
df_EVENT5_16.to_sql('MODREPORT', con, if_exists='replace')
from DataFrame.to_sql() docs:
con: SQLAlchemy engine or DBAPI2 connection (legacy mode)
Using SQLAlchemy makes it possible to use any DB supported by that library.
If a DBAPI2 object, only sqlite3 is supported.
con：SQLAlchemy 引擎或 DBAPI2 连接（传统模式）
使用 SQLAlchemy 可以使用该库支持的任何数据库。
如果是 DBAPI2 对象，则仅支持 sqlite3。
回答by Brijesh Rana
No need to use pyodbc to connect with MSSQL, SQL Alchemy will do that for you. And also we can insert the data-frame directly into the database without iterating the data-frame using to_sql() method. Here is the code that working fine for me -
无需使用 pyodbc 连接 MSSQL，SQL Alchemy 将为您完成。而且我们还可以将数据框直接插入到数据库中，而无需使用 to_sql() 方法迭代数据框。这是对我来说工作正常的代码 -
# To insert data frame into MS SQL database without iterate the data-frame
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select
from six.moves import urllib
params = urllib.parse.quote_plus("DRIVER={SQL
Server};SERVER=serverName;DATABASE=dbName;UID=UserName;PWD=password")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
engine.connect()
# suppose df is the data-frame that we want to insert in database
df.to_sql(name='table_name',con=engine, index=False, if_exists='append')
