Python:将熊猫Dataframe写入MSSQL——>数据库错误。

I have a pandas dataframe that has about 20k rows and 20 columns. I want to write it to a table in MSSQL.

我有一个熊猫dataframe，大约有20k行和20列。我想将它写入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:

我正在尝试用以下代码将我的熊猫数据aframe写到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上执行失败，其中type='table'和name=?;': '42S02'， ' [42S02] [Microsoft][ODBC sql Server Driver][sql Server] [sql Server]无效对象名'sqlite_master'。

1 个解决方案

#1

Modern Pandas versions expect SQLAlchemy engine as a connection, so use SQLAlchemy:

现代熊猫版本期望SQLAlchemy引擎作为连接，所以使用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:

从DataFrame.to_sql()文档:

con : SQLAlchemy engine or DBAPI2 connection (legacy mode)

缺点:SQLAlchemy引擎或DBAPI2连接(遗留模式)

Using SQLAlchemy makes it possible to use any DB supported by that library.

使用SQLAlchemy，可以使用该库支持的任何DB。

If a DBAPI2 object, only sqlite3 is supported.

如果DBAPI2对象，只支持sqlite3。

#1