pyodbc/sqlAchemy 可实现快速执行许多

Question

在回答我的问题时 如何在Python + Pandas + sqlAlchemy + MSSQL/T-SQL 中加速数据整理 我被亲切地引导到加速熊猫。DataFrame.to_sql @IljaEverilä 的 pyODBC fast_executemany。

注意 出于测试目的，我只读取/写入 10k 行。

我添加了事件侦听器，并且 a( 调用了函数，但 b( 显然没有设置执行许多，因为 IF 失败并且没有设置cursor.fast_executemay。

def namedDbSqlAEngineCreate(dbName):
# Create an engine and switch to the named db
# returns the engine if successful and None if not
# 2018-08-23 added fast_executemany accoding to this https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc?rq=1
engineStr = 'mssql+pyodbc://@' + defaultDSN
engine = sqla.create_engine(engineStr, echo=False)
@event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
# print("FUNC call")
if executemany:
print('executemany')
cursor.fast_executemany = True
try:
engine.execute('USE ' +dbName)
return(engine)
except sqla.exc.SQLAlchemyError as ex:
if ex.orig.args[0] == '08004':
print('namedDbSqlAEngineCreate:Database %s does not exist' % dbName)
else:
print(ex.args[0])
return(None)

速度自然没有变化。

我原始问题中的代码在to_sql中保持不变

nasToFillDF.to_sql(name=tempTableName, con=engine.engine, if_exists='replace', chunksize=100, index=False)

因为我尝试根据示例设置块大小 = 无并收到错误消息(我之前遇到过(

(噗嗤。编程错误(('SQL 包含 -31072 参数标记， 但提供了 100000 个参数"、"HY000"(

我做错了什么？我想receive_before_cursor_execute的 executemany 参数没有设置，但如果这是答案，我不知道如何解决它。

安装程序是pyodbc 4.0.23，sqlAchemy 1.2.6，Python 3.6.something

Answer 1

您收到的错误是由 Pandas 版本 0.23.0 中引入的更改、在 0.23.1 中恢复以及在 0.24.0 中重新引入的更改引起的，如此处所述。生成的 VALUES 子句包含 100,000 个参数标记，并且计数似乎存储在有符号的 16 位整数中，因此它溢出，您会感到有趣

SQL 包含 -31072 个参数标记，但提供了 100000 个参数

您可以自己检查：

In [16]: 100000 % (2 ** 16) - 2 ** 16
Out[16]: -31072

如果你想继续按原样使用 Pandas，你必须计算并提供一个合适的chunksize值，例如你正在使用的 100，同时考虑到 VALUES 子句的最大行限制 1,000 和存储过程的最大参数限制 2,100。详细信息在链接的问答中再次解释。

在更改之前，熊猫在插入数据时总是使用executemany()。较新的版本检测正在使用的方言是否支持 INSERT 中的 VALUES 子句。此检测发生在SQLTable.insert_statement()并且无法控制，这是一种耻辱，因为 PyODBC 修复了他们的executemany()性能，给定了正确的标志。

为了迫使 Pandas 再次将executemany()与 PyODBC 一起使用SQLTable必须被猴子修补：

import pandas.io.sql
def insert_statement(self, data, conn):
return self.table.insert(), data
pandas.io.sql.SQLTable.insert_statement = insert_statement

如果未设置Cursor.fast_executemany标志，这将非常慢，因此请记住设置正确的事件处理程序。

下面是使用以下数据帧进行的简单性能比较：

In [12]: df = pd.DataFrame({f'X{i}': range(1000000) for i in range(9)})

香草熊猫 0.24.0：

In [14]: %time df.to_sql('foo', engine, chunksize=209)
CPU times: user 2min 9s, sys: 2.16 s, total: 2min 11s
Wall time: 2min 26s

猴子补丁熊猫启用了快速执行许多：

In [10]: %time df.to_sql('foo', engine, chunksize=500000)
CPU times: user 12.2 s, sys: 981 ms, total: 13.2 s
Wall time: 38 s