pyodbc/sqlAchemy включить быстрое выполнение многих

Question

В ответ на мой вопрос, как ускорить обработку данных LOT в Python + Pandas + sqlAlchemy + MSSQL/T-SQL, я был любезно направлен на ускорение pandas.DataFrame.to_sql с помощью fast_executemany pyODBC @IljaEverilä.

Примечание: в целях тестирования я читаю / пишу только 10 тыс. Строк.

Я добавил прослушиватель событий и а) функция вызывается, но б) ясно, что executemany не установлено, так как 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://stackru.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")

Что я сделал не так? Я предполагаю, что параметр executemany в receive_before_cursor_execute не установлен, но если это ответ, я понятия не имею, как это исправить.

Программа установки pyodbc 4.0.23, sqlAchemy 1.2.6, Python 3.6.

Answer 1

Полученная вами ошибка вызвана изменениями, внесенными в версию Pandas 0.23.0, возвращенной в 0.23.1 и повторно введенной в 0.24.0, как описано здесь. Произведенное предложение VALUES содержит 100000 маркеров параметров, и кажется, что счет хранится в 16-разрядном целом числе со знаком, поэтому он переполняется, и вы получите забавный результат.

SQL содержит маркеры параметров -31072, но предоставлено 100000 параметров

Вы можете проверить сами:

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

Если вы хотите продолжать использовать Pandas как есть, вам придется рассчитать и предоставить подходящий chunksize значение, такое как 100, которое вы использовали, принимая во внимание как максимальный предел строки 1000 для предложения VALUES, так и максимальный предел параметра 2100 для хранимых процедур. Детали снова объяснены в связанном Q / A.

До изменения Панды всегда использовали executemany() при вставке данных. Более новые версии обнаруживают, поддерживает ли используемый диалект предложение VALUES в INSERT. Это обнаружение происходит в SQLTable.insert_statement() и не может контролироваться, что является позором, так как PyODBC исправил их executemany() производительность, учитывая правильный флаг включен.

Для того, чтобы заставить панд использовать 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

Обезьяны Pandas с включенным быстрым исполнением:

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