如何使用pyodbc加快从CSV到MS SQL Server的批量插入

Question

以下是我需要帮助的代码。我必须运行1,300,000行，这意味着最多需要 40分钟 才能插入〜300,000行。

我认为批量插入是加快速度的途径吗？还是因为我要通过for data in reader:部分遍历行？

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

我有目的地动态地选择列标题（因为我想创建尽可能多的pythonic代码）。

SpikeData123是表名。

Answer 1

更新：如@SimonLang的注释中所述，BULK INSERT在SQL Server
2017及更高版本下，显然支持CSV文件中的文本限定符（请参阅：此处）。

BULK INSERT几乎肯定会 多 比阅读源文件一行一行地，做的每一行定期INSERT更快。但是，对于CSV文件，BULK
INSERT和BCP都存在很大的局限性，因为它们不能处理文本限定符（请参阅：此处）。也就是说，如果您的CSV文件中
没有 限定的文本字符串，则…

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

…那么您可以批量插入它，但是如果它包含文本限定符（因为某些文本值包含逗号）…

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

…那么BULK INSERT无法处理它。尽管如此，将这样的CSV文件预处理为管道分隔文件的总体速度可能会更快…

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

…或制表符分隔的文件（其中→代表制表符）…

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

…然后批量插入该文件。对于后者（制表符分隔）文件，BULK INSERT代码如下所示：

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

注意：如注释中所述，BULK INSERT仅当SQL
Server实例可以直接读取源文件时，才执行语句。对于源文件在远程客户端上的情况，请参见以下答案。