Comment accélérer l'insertion en vrac vers le serveur MS SQL à partir de CSV en utilisant pyodbc

ci-dessous est mon code que je voudrais un peu d'aide. Je dois l'exécuter sur 1.300.000 lignes ce qui signifie qu'il prend jusqu'à 40 minutes pour insérer ~300 000 lignes.

je pense que l'insertion en vrac est la route à suivre pour l'accélérer? Ou est-ce parce que je répète sur les lignes via for data in reader: partie?

#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()

je sélectionne dynamiquement mes en-têtes de colonne (comme je voudrais créer le code le plus pythonique possible).

SpikeData123 est le nom de la table.

18
demandé sur Gord Thompson 2015-04-15 01:01:12

3 réponses

BULK INSERT sera presque certainement être beaucoup plus rapide que de lire le fichier source ligne par ligne et de faire un INSERT régulier pour chaque ligne. Cependant, BULK INSERT et BCP ont une limitation importante en ce qui concerne les fichiers CSV en ce qu'ils ne peuvent pas traiter les qualificatifs de texte (réf: ici). C'est-à-dire, si votre fichier CSV fait avoir des chaînes de texte qualifiées ...

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

... ensuite, vous pouvez L'insérer en vrac, mais si elle contient des qualificatifs de texte (parce que certaines valeurs textuelles contiennent des virgules) ...

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

... alors, BULK INSERT ne peut pas le manipuler. Néanmoins, il pourrait être plus rapide dans l'ensemble de pré-traiter un tel fichier CSV dans un fichier délimité par pipe ...

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

... ou d'un fichier délimité par des tabulations (où → représente le caractère de tabulation) ...

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

... et puis en VRAC INSÉRER ce fichier. Pour ce dernier (tab-delimited) fichier, le code D'insertion en vrac ressemblerait à quelque chose comme ceci:

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()

Note: Comme mentionné dans un commentaire, exécuter un BULK INSERT l'instruction n'est applicable que si L'instance du serveur SQL peut lire directement le fichier source. Pour les cas où le fichier source est sur un client distant, voir cette réponse.

22
répondu Gord Thompson 2017-11-01 14:32:16

comme noté dans un commentaire à une autre réponse, le T-SQL ne fonctionnera que si le fichier à importer est sur la même machine que L'instance du serveur SQL ou se trouve dans un emplacement réseau SMB/CIFS que L'instance du serveur SQL peut lire. Ainsi, il peut ne pas être applicable dans le cas où le fichier source est sur un client distant.

pyodbc 4.0.19 ajouté un Curseur#fast_executemany fonctionnalité qui peut être utile dans ce cas. fast_executemany est "off" par défaut, et le suivant le code d'essai ...

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')

... il a fallu environ 22 secondes pour l'exécuter sur ma machine d'essai. Ajouter simplement 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')

... réduction du temps d'exécution à un peu plus d'une seconde.

20
répondu Gord Thompson 2017-11-02 00:44:12

oui bulk insert est droit chemin d'accès pour le chargement de gros fichiers dans une DB. En un coup d'oeil, je dirais que la raison pour laquelle cela prend autant de temps est comme vous avez mentionné que vous êtes en boucle sur chaque ligne de données du fichier qui signifie effectivement supprimer les avantages de l'utilisation d'un insert en vrac et de le faire comme un insert normal. Rappelez-vous juste que, comme son nom l'implique qu'il est utilisé pour insérer des mandrins de données. Je voudrais enlever la boucle et essayez de nouveau.

je vérifierais aussi votre syntaxe pour insérer en vrac comme il ne semble pas correct pour moi. vérifiez le sql généré par pyodbc car j'ai le sentiment qu'il n'exécute qu'un insert normal

alternativement si elle est encore lente, j'essaierais d'utiliser bulk insert directement à partir de sql et soit charger le fichier entier dans une table de température avec bulk insert puis insérer la colonne correspondante dans les tables de droite. ou utilisez un mélange de bulk insert et bcp pour obtenir les colonnes spécifiques insérées ou OPENROWSET.

1
répondu Michael Moura 2015-04-15 00:08:06