to_sql pandas 数据框导入 SQL 服务器错误：DatabaseError
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45326026/
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
to_sql pandas data frame into SQL server error: DatabaseError
提问by AlexSB
While trying to write a pandas' dataframe into sql-server, I get this error:
在尝试将pandas' 数据帧写入 时sql-server，出现此错误：
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")
DatabaseError: sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server] 无法准备语句。(8180)")
It seems pandasis looking into sqliteinstead of the real database.
它似乎pandas正在调查sqlite而不是真正的数据库。
It's not a connection problem since I can read from the sql-serverwith the same connection using pandas.read_sqlThe connection has been set using
这不是连接问题，因为我可以sql-server使用相同的连接读取pandas.read_sql连接已使用
sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
It's not a database permission problem either since I can write line by line using the same connection parameters as:
这也不是数据库权限问题，因为我可以使用与以下相同的连接参数逐行编写：
cursor = conn.cursor()
cursor.execute('insert into test values (1, 'test', 10)')
conn.commit()
I could just write a loop to instert line by line but I would like to know why to_sqlisn't working for me, and I am affraid it won't be as efficient.
我可以写一个循环来逐行插入，但我想知道为什么to_sql对我不起作用，而且我担心它不会那么有效。
Environment:
Python: 2.7
Pandas: 0.20.1
sqlalchemy: 1.1.12
环境：：
Python2.7
Pandas：0.20.1
sqlalchemy：1.1.12
Thanks in advance.
提前致谢。
runnable example:
可运行示例：
import pandas as pd
from sqlalchemy import create_engine
import urllib
params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=
<servername>;DATABASE=<databasename>;UID=<username>;PWD=<password>")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
test = pd.DataFrame({'col1':1, 'col2':'test', 'col3':10}, index=[0])
conn=engine.connect().connection
test.to_sql("dbo.test", con=conn, if_exists="append", index=False)
回答by Scratch'N'Purr
According to the to_sqldoc, the conparameter is either an SQLAchemy engine or the legacy DBAPI2 connection (sqlite3). Because you are passing the connection object rather than the SQLAlchemy engine object as the parameter, pandas is inferring that you're passing a DBAPI2 connection, or a SQLite3 connection since its the only one supported. To remedy this, just do:
根据to_sqldoc，该con参数是 SQLAchemy 引擎或旧的 DBAPI2 连接 (sqlite3)。因为您传递的是连接对象而不是 SQLAlchemy 引擎对象作为参数，pandas 推断您传递的是 DBAPI2 连接或 SQLite3 连接，因为它是唯一受支持的连接。要解决此问题，只需执行以下操作：
myeng = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
# Code to create your df
...
# Now write to DB
df.to_sql('table', myeng, index=False)
回答by Faller
So I ran into this same thing. I tried looking through the code, couldn't figure out why it wasn't working but it looks like it gets stuck on this call.
所以我遇到了同样的事情。我尝试查看代码，无法弄清楚为什么它不起作用，但看起来它卡在了这个调用上。
pd.io.sql._is_sqlalchemy_connectable(engine)
I found that if I run this first it returns True, but as soon as I run it after running df.to_sql() it returns False. Right now I'm running it before I do the df.to_sql() and it actually works.
我发现如果我先运行它，它会返回 True，但是一旦我在运行 df.to_sql() 之后运行它，它就会返回 False。现在我在执行 df.to_sql() 之前运行它并且它确实有效。
Hope this helps.
希望这可以帮助。
回答by CaiYongAn
try this. good to connect MS SQL server(SQL Authentication) and update data
尝试这个。很好的连接 MS SQL 服务器（SQL 身份验证）和更新数据
from sqlalchemy import create_engine
params = urllib.parse.quote_plus(
'DRIVER={ODBC Driver 13 for SQL Server};'+
'SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
#df: pandas.dataframe; mTableName:table name in MS SQL
#warning: discard old table if exists
df.to_sql(mTableName, con=engine, if_exists='replace', index=False)
