pyodbc / sqlAchemy启用快速执行许多

小编典典

pyodbc / sqlAchemy启用快速执行许多

python

为了回答我的问题，如何在Python + Pandas + sqlAlchemy + MSSQL /
T-SQL中加快处理大量数据的速度，我很高兴地通过@IljaEverilä使用pyODBC的fast_executemany加速了pandas.DataFrame.to_sql。

注意：出于测试目的，我仅读取/写入1万行。

我添加了事件侦听器，并且a）调用了函数，但b）显然由于IF失败并且没有设置cursor.fast_executemay而未设置executemany。

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)

因为按照示例，我尝试设置chunksize = None并收到错误消息（我之前遇到过）

（pyodbc.ProgrammingError）（’SQL包含-31072参数标记，但是提供了100000个参数’，’HY000’）

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

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

阅读 216

2021-01-20

共1个答案

小编典典

您收到的错误是由熊猫版本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子句的最大行数限制为1000和2,100用于存储过程。再次在链接的Q
/ A中详细说明。

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

为了迫使熊猫executemany()再次与PyODBC一起使用，SQLTable必须进行monkeypatched：

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

2021-01-20