pyodbc / sqlAchemy permite ejecutar rápidamente muchos

En respuesta a mi pregunta ¿Cómo acelerar el flujo de datos MUCHO en Python + Pandas + sqlAlchemy + MSSQL / T-SQL? Fui dirigido amablemente a Speeding up pandas.DataFrame.to_sql con fast_executemany of pyODBC by @ IljaEverilä.

NB Para propósitos de prueba, solo estoy leyendo / escribiendo 10k filas.

Agregué el detector de eventos y a) se llama a la función pero b) claramente ejecutemany no se configura ya que el IF falla y cursor.fast_executemay no se configura.

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) 

Naturalmente no hay cambio de velocidad.

El código en mi pregunta original no ha cambiado en el to_sql

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

porque intenté, por ejemplo, establecer chunksize = None y recibir el mensaje de error (que había encontrado anteriormente)

(pyodbc.ProgrammingError) (‘El SQL contiene -31072 marcadores de parámetros, pero se suministraron 100000 parámetros’, ‘HY000’)

¿Qué he hecho mal? Supongo que el parámetro de ejecución de receive_before_cursor_execute no está establecido, pero si esa es la respuesta, no tengo idea de cómo solucionarlo.

La configuración es pyodbc 4.0.23, sqlAchemy 1.2.6, Python 3.6.something

El error que recibió se debe a cambios introducidos en Pandas versión 0.23.0, revertidos en 0.23.1 y reintroducidos en 0.24.0, como se explica aquí . La cláusula VALUES producida contiene 100.000 marcadores de parámetros y parece que el recuento se almacena en un entero de 16 bits con signo, por lo que se desborda y se obtiene lo divertido

El SQL contiene -31072 marcadores de parámetros, pero se suministraron 100000 parámetros

Puedes comprobarlo por ti mismo:

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

Si desea seguir utilizando Pandas tal como está, tendrá que calcular y proporcionar un valor de chunksize adecuado, como los 100 que estaba usando, teniendo en cuenta tanto el límite de fila máximo de 1.000 para la cláusula VALUES como el límite de parámetro máximo de 2.100 por procedimientos almacenados. Los detalles se explican de nuevo en el enlace Q / A.

Antes del cambio, Pandas solía usar siempre executemany() al insertar datos. Las versiones más recientes detectan si el dialecto en uso admite la cláusula VALUES en INSERT. Esta detección se produce en SQLTable.insert_statement() y no se puede controlar, lo cual es una pena porque PyODBC corrigió su rendimiento de ejecución ( ya que está habilitada la executemany() la derecha) .

Para forzar a Pandas a usar executemany() con PyODBC de nuevo, SQLTable tiene que ser superpuesto :

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

Esto será terriblemente lento, si el indicador Cursor.fast_executemany no está establecido, así que recuerde establecer el controlador de eventos adecuado.

Aquí hay una comparación de rendimiento simple, utilizando el siguiente dataframe:

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

Pandillas De Vainilla 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 parcheadas con ejecutables rápidos habilitados:

 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