Tengo un marco de datos con aproximadamente 155 000 filas y 12 columnas. Si lo exporto a csv con dataframe.to_csv, el resultado es un archivo de 11 MB (que se produce instantáneamente).

Sin embargo, si exporto a Microsoft SQL Server con el método to_sql, ¡tardo entre 5 y 6 minutos! Ninguna columna es texto: solo int, float, bool y fechas. He visto casos en los que los controladores ODBC configuran nvarchar(max) y esto ralentiza la transferencia de datos, pero no puede ser el caso aquí.

¿Alguna sugerencia sobre cómo acelerar el proceso de exportación? Tomar 6 minutos para exportar 11 MB de datos hace que la conexión ODBC sea prácticamente inutilizable.

¡Gracias!

mi código es:

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select
ServerName = "myserver"
Database = "mydatabase"
TableName = "mytable"

engine = create_engine('mssql+pyodbc://' + ServerName + "https://stackoverflow.com/" + Database)
conn = engine.connect()

metadata = MetaData(conn)

my_data_frame.to_sql(TableName,engine)

Recientemente tuve el mismo problema y tengo ganas de agregar una respuesta a esto para otros.
to_sql parece enviar un INSERT consulta por cada fila, lo que lo hace realmente lento. Pero desde 0.24.0 hay un method parámetro en pandas.to_sql() donde puede definir su propia función de inserción o simplemente usar method='multi' para decirle a los pandas que pasen varias filas en una sola consulta INSERT, lo que lo hace mucho más rápido.

Tenga en cuenta que su base de datos puede tener un límite de parámetros. En ese caso, también debe definir un tamaño de fragmento.

Así que la solución simplemente debería verse así:

my_data_frame.to_sql(TableName, engine, chunksize=<yourParameterLimit>, method='multi')

Si no conoce el límite de parámetros de su base de datos, inténtelo sin el parámetro de tamaño de fragmento. Se ejecutará o le dará un error indicándole su límite.

los DataFrame.to_sql El método genera declaraciones de inserción en su conector ODBC que luego es tratado por el conector ODBC como inserciones regulares.

Cuando esto es lento, no es culpa de los pandas.

Guardando la salida del DataFrame.to_sql método a un archivo, luego reproducir ese archivo a través de un conector ODBC tomará la misma cantidad de tiempo.

La forma correcta de importar datos de forma masiva a una base de datos es generar un archivo csv y luego usar un comando de carga, que en el sabor de MS de las bases de datos SQL se llama BULK INSERT

Por ejemplo:

BULK INSERT mydatabase.myschema.mytable
FROM 'mydatadump.csv';

La referencia de sintaxis es la siguiente:

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )] 

Puedes usar esto: lo que lo hace más rápido es el method parámetro de pandas to_sql. Espero que esta ayuda ayude.

El resultado de esto en mi experiencia fue de tiempo infinito a 8 segundos.

df = pd.read_csv('test.csv')

conn = create_engine(<connection_string>)

start_time = time.time()
df.to_sql('table_name', conn, method='multi',index=False, if_exists="replace")
print("--- %s seconds ---" % (time.time() - start_time))

Puedes usar d6tstack que tiene pandas rápidos a la funcionalidad SQL porque utiliza comandos de importación de base de datos nativos. Es compatible con MS SQL, Postgres y MYSQL

uri_psql="postgresql+psycopg2://usr:pwd@localhost/db"
d6tstack.utils.pd_to_psql(df, uri_psql, 'table')
uri_mssql="mssql+pymssql://usr:pwd@localhost/db"
d6tstack.utils.pd_to_mssql(df, uri_mssql, 'table', 'schema') # experimental

También es útil para importar múltiples CSV con cambios en el esquema de datos y/o preprocesar con pandas antes de escribir en la base de datos; consulte más abajo en cuaderno de ejemplos

d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv'), 
    apply_after_read=apply_fun).to_psql_combine(uri_psql, 'table')

Por que es pandas.DataFrame.to_sql ¿lento?

Al cargar datos de pandas a Microsoft SQL Server, la mayor parte del tiempo se dedica realmente a la conversión de pandas a los objetos de Python a la representación que necesita el controlador ODBC de MS SQL. Una de las razones pandas es mucho más rápido para el análisis que el código básico de Python es que funciona en matrices nativas ajustadas de enteros/flotantes/… que no tienen la misma sobrecarga que sus respectivas contrapartes de Python. los to_sql El método en realidad está convirtiendo todas estas columnas magras en muchos objetos individuales de Python y, por lo tanto, no recibe el tratamiento de rendimiento habitual como el otro pandas las operaciones tienen.

Usar turbodbc.Cursor.insertmanycolumns para acelerar esto

Dado un pandas.DataFramepuedes usar turbodbc y pyarrow para insertar los datos con menos gastos generales de conversión que con la conversión a objetos de Python.

import pyarrow as pa
import turbodbc

cursor = …  # cursor to a MS SQL connection initiated with turbodbc
df = …  # the pd.DataFrame to be inserted

# Convert the pandas.DataFrame to a pyarrow.Table, most of the columns
# will be zero-copy and thus this is quite fast.
table = pa.Table.from_pandas(table)

# Insert into the database
cursor.executemanycolumns("INSERT INTO my_table VALUES (?, ?, ?)",
                           table)

¿Por qué es esto más rápido?

En lugar de la conversión de pd.DataFrame -> colección de objetos de Python -> estructuras de datos ODBC, estamos haciendo una ruta de conversión pd.DataFrame -> pyarrow.Table -> Estructura ODBC. Esto es más eficaz debido a:

• La mayoría de las columnas de un pandas.DataFrame se puede convertir en columnas de la pyarrow.Table sin copiar. Las columnas de la tabla harán referencia a la misma memoria. Por lo tanto, no se realiza ninguna conversión real.
• La conversión se realiza completamente en código nativo con tipos nativos. Esto significa que en ningún momento se produce la sobrecarga de los objetos de Python, siempre y cuando no tengamos object columnas mecanografiadas.

Me estaba quedando sin tiempo y memoria (más de 18 GB asignados para un DataFrame cargado desde 120 MB CSV) con esta línea:

df.to_sql('my_table', engine, if_exists="replace", method='multi', dtype={"text_field": db.String(64), "text_field2": db.String(128), "intfield1": db.Integer(), "intfield2": db.Integer(), "floatfield": db.Float()})

Aquí está el código que me ayudó a importar y rastrear el progreso de las inserciones al mismo tiempo:

import sqlalchemy as db
engine = db.create_engine('mysql://user:password@localhost:3306/database_name', echo=False)
connection = engine.connect()
metadata = db.MetaData()

my_table = db.Table('my_table', metadata,
              db.Column('text_field', db.String(64), index=True),
              db.Column('text_field2', db.String(128), index=True),
              db.Column('intfield1', db.Integer()),
              db.Column('intfield2', db.Integer()),
              db.Column('floatfield', db.Float())
             )
metadata.create_all(engine)
kw_dict = df.reset_index().sort_values(by="intfield2", ascending=False).to_dict(orient="records")

batch_size=10000
for batch_start in range(0, len(kw_dict), batch_size):
    print("Inserting {}-{}".format(batch_start, batch_start + batch_size))
    connection.execute(my_table.insert(), kw_dict[batch_start:batch_start + batch_size])

Con SQLAlchemy>=1.3al crear engine objeto, conjunto fast_executemany=True. Referencia