to_sql pandas 数据框导入 SQL 服务器错误:DatabaseError [英] to_sql pandas data frame into SQL server error: DatabaseError

问题描述

在尝试将 pandas' 数据帧写入 sql-server 时，出现此错误:

<块引用>

数据库错误:sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server] 上的执行失败无效的对象名称 'sqlite_master'.(208) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]无法准备语句.(8180)")

看来 pandas 正在研究 sqlite 而不是真正的数据库.

这不是连接问题，因为我可以使用 pandas.read_sql 从具有相同连接的 sql-server 读取已使用

设置连接

sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

这也不是数据库权限问题，因为我可以使用与以下相同的连接参数逐行写入:

cursor = conn.cursor()cursor.execute('插入测试值(1, 'test', 10)')conn.commit()

我可以编写一个循环来逐行插入，但我想知道为什么 to_sql 对我不起作用，而且我担心它不会那么有效.

环境:Python: 2.7熊猫:0.20.1sqlalchemy: 1.1.12

提前致谢.

可运行示例:

将pandas导入为pd从 sqlalchemy 导入 create_engine导入 urllibparams = 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().connectiontest.to_sql("dbo.test", con=conn, if_exists="append", index=False)

解决方案

根据 to_sql doc，con 参数是 SQLAchemy 引擎或旧的 DBAPI2 连接 (sqlite3).因为您传递的是连接对象而不是 SQLAlchemy 引擎对象作为参数，pandas 推断您传递的是 DBAPI2 连接或 SQLite3 连接，因为它是唯一受支持的连接.要解决此问题，只需执行以下操作:

myeng = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)# 用于创建 df 的代码...# 现在写入数据库df.to_sql('table', myeng, index=False)

While trying to write a pandas' dataframe into sql-server, I get this error:

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)")

It seems pandas is looking into sqlite instead of the real database.

It's not a connection problem since I can read from the sql-server with the same connection using pandas.read_sql The connection has been set using

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_sql isn't working for me, and I am affraid it won't be as efficient.

Environment: Python: 2.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)

解决方案

According to the to_sql doc, the con parameter 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:

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)

这篇关于to_sql pandas 数据框导入 SQL 服务器错误:DatabaseError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！