for - python sql server

Cómo acelerar la inserción masiva a MS SQL Server desde CSV usando pyodbc

Question

A continuación se muestra mi código con el que me gustaría recibir ayuda. Tengo que ejecutarlo en más de 1,300,000 filas, lo que significa que toma hasta 40 minutos insertar ~ 300,000 filas.

Me imagino que la inserción masiva es la ruta a seguir para acelerarlo. ¿O es porque estoy iterando sobre las filas a través for data in reader: porción?

#Opens the prepped csv file
with open (os.path.join(newpath,outfile), ''r'') as f:
    #hooks csv reader to file
    reader = csv.reader(f)
    #pulls out the columns (which match the SQL table)
    columns = next(reader)
    #trims any extra spaces
    columns = [x.strip('' '') for x in columns]
    #starts SQL statement
    query = ''bulk insert into SpikeData123({0}) values ({1})''
    #puts column names in SQL query ''query''
    query = query.format('',''.join(columns), '',''.join(''?'' * len(columns)))

    print ''Query is: %s'' % query
    #starts curser from cnxn (which works)
    cursor = cnxn.cursor()
    #uploads everything by row
    for data in reader:
        cursor.execute(query, data)
        cursor.commit()

Estoy eligiendo dinámicamente mis encabezados de columna a propósito (ya que me gustaría crear el código más pitónico posible).

SpikeData123 es el nombre de la tabla.

Answer 1

Actualización: Como se señaló en el comentario de @SimonLang, BULK INSERT en SQL Server 2017 y posterior aparentemente admite calificadores de texto en archivos CSV (ref: here ).

BULK INSERT seguramente será mucho más rápido que leer el archivo fuente fila por fila y hacer un INSERT regular para cada fila. Sin embargo, tanto BULK INSERT como BCP tienen una limitación significativa con respecto a los archivos CSV en que no pueden manejar calificadores de texto (ref: here ). Es decir, si su archivo CSV no tiene cadenas de texto calificadas ...

1,Gord Thompson,2015-04-15
2,Bob Loblaw,2015-04-07

... entonces puede INSERTAR A GRANEL, pero si contiene calificadores de texto (porque algunos valores de texto contienen comas) ...

1,"Thompson, Gord",2015-04-15
2,"Loblaw, Bob",2015-04-07

... entonces BULK INSERT no puede manejarlo. Aún así, podría ser más rápido en general preprocesar un archivo CSV en un archivo delimitado por tuberías ...

1|Thompson, Gord|2015-04-15
2|Loblaw, Bob|2015-04-07

... o un archivo delimitado por tabulaciones (donde → representa el carácter de tabulación) ...

1→Thompson, Gord→2015-04-15
2→Loblaw, Bob→2015-04-07

... y luego INSERTAR A GRANEL ese archivo. Para el último archivo (delimitado por tabulaciones), el código BULK INSERT se vería así:

import pypyodbc
conn_str = "DSN=myDb_SQLEXPRESS;"
cnxn = pypyodbc.connect(conn_str)
crsr = cnxn.cursor()
sql = """
BULK INSERT myDb.dbo.SpikeData123
FROM ''C://__tmp//biTest.txt'' WITH (
    FIELDTERMINATOR=''//t'',
    ROWTERMINATOR=''//n''
    );
"""
crsr.execute(sql)
cnxn.commit()
crsr.close()
cnxn.close()

Nota: Como se menciona en un comentario, la ejecución de una BULK INSERT solo es aplicable si la instancia de SQL Server puede leer directamente el archivo fuente. Para los casos en que el archivo de origen está en un cliente remoto, consulte esta respuesta .

Answer 2

Como se señaló en un comentario a otra respuesta, el comando T-SQL BULK INSERT solo funcionará si el archivo a importar está en la misma máquina que la instancia de SQL Server o está en una ubicación de red SMB / CIFS que la instancia de SQL Server puede leer. Por lo tanto, puede no ser aplicable en el caso en que el archivo fuente esté en un cliente remoto.

pyodbc 4.0.19 agregó una función Cursor#fast_executemany que puede ser útil en ese caso. fast_executemany está "desactivado" de forma predeterminada, y el siguiente código de prueba ...

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f''txt{i:06d}'',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f''{time.time() - t0:.1f} seconds'')

... tardó aproximadamente 22 segundos en ejecutarse en mi máquina de prueba. Simplemente agregando crsr.fast_executemany = True ...

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

crsr.fast_executemany = True  # new in pyodbc 4.0.19

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f''txt{i:06d}'',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f''{time.time() - t0:.1f} seconds'')

... redujo el tiempo de ejecución a poco más de 1 segundo.

Answer 3

sí, la inserción masiva es la ruta correcta para cargar archivos grandes en una base de datos. De un vistazo, diría que la razón por la que lleva tanto tiempo es que usted mencionó que está recorriendo cada fila de datos del archivo, lo que significa que está eliminando los beneficios de usar un inserto masivo y hacerlo como un inserto normal. Solo recuerde que su nombre implica que se usa para insertar datos. Quitaría el bucle y volvería a intentarlo.

También verificaría dos veces su sintaxis para la inserción masiva, ya que no me parece correcta. compruebo el sql generado por pyodbc ya que tengo la sensación de que solo podría estar ejecutando una inserción normal

Alternativamente, si aún es lento, intentaría usar la inserción masiva directamente desde sql y cargaría todo el archivo en una tabla temporal con inserción masiva y luego insertaría la columna relevante en las tablas correctas. o use una combinación de inserción masiva y bcp para insertar las columnas específicas o OPENROWSET.