Python 如何使用 pyodbc 加快从 CSV 批量插入到 MS SQL Server 的速度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29638136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to speed up bulk insert to MS SQL Server from CSV using pyodbc
提问by TangoAlee
Below is my code that I'd like some help with. I am having to run it over 1,300,000 rows meaning it takes up to 40 minutesto insert ~300,000 rows.
以下是我需要帮助的代码。我必须运行超过 1,300,000 行，这意味着插入约 300,000 行最多需要40 分钟。
I figure bulk insert is the route to go to speed it up?
Or is it because I'm iterating over the rows via for data in reader:portion?
我认为批量插入是加快速度的途径？还是因为我通过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()
I am dynamically picking my column headers on purpose (as I would like to create the most pythonic code possible).
我故意动态选择我的列标题（因为我想创建尽可能多的 Pythonic 代码）。
SpikeData123 is the table name.
SpikeData123 是表名。
采纳答案by Gord Thompson
Update: As noted in the comment from @SimonLang, BULK INSERTunder SQL Server 2017 and later apparently does support text qualifiers in CSV files (ref: here).
更新：如@SimonLang 的评论中所述，BULK INSERT在 SQL Server 2017 及更高版本下，显然支持 CSV 文件中的文本限定符（参考：此处）。
BULK INSERT will almost certainly be muchfaster than reading the source file row-by-row and doing a regular INSERT for each row. However, both BULK INSERT and BCP have a significant limitation regarding CSV files in that they cannot handle text qualifiers (ref: here). That is, if your CSV file does nothave qualified text strings in it ...
BULK INSERT几乎肯定会多比阅读源文件一行一行地，做的每一行定期INSERT更快。但是，BULK INSERT 和 BCP 都对 CSV 文件有很大的限制，因为它们无法处理文本限定符（参考：此处）。也就是说，如果您的 CSV 文件中没有限定的文本字符串......
1,Gord Thompson,2015-04-15
2,Bob Loblaw,2015-04-07
... then you can BULK INSERT it, but if it contains text qualifiers (because some text values contains commas) ...
...然后您可以批量插入它，但如果它包含文本限定符（因为某些文本值包含逗号）...
1,"Thompson, Gord",2015-04-15
2,"Loblaw, Bob",2015-04-07
... then BULK INSERT cannot handle it. Still, it might be faster overall to pre-process such a CSV file into a pipe-delimited file ...
...然后 BULK INSERT 无法处理它。尽管如此，将这样的 CSV 文件预处理为以管道分隔的文件总体上可能会更快......
1|Thompson, Gord|2015-04-15
2|Loblaw, Bob|2015-04-07
... or a tab-delimited file (where →represents the tab character) ...
... 或制表符分隔的文件（其中→表示制表符）...
1→Thompson, Gord→2015-04-15
2→Loblaw, Bob→2015-04-07
... and then BULK INSERT that file. For the latter (tab-delimited) file the BULK INSERT code would look something like this:
...然后批量插入该文件。对于后者（制表符分隔）文件，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()
Note: As mentioned in a comment, executing a BULK INSERTstatement is only applicable if the SQL Server instance can directly read the source file. For cases where the source file is on a remote client, see this answer.
注意：如注释中所述，执行BULK INSERT语句仅适用于 SQL Server 实例可以直接读取源文件的情况。对于源文件位于远程客户端的情况，请参阅此答案。
回答by Michael Moura
yes bulk insert is right path for loading large files into a DB. At a glance I would say that the reason it takes so long is as you mentioned you are looping over each row of data from the file which effectively means are removing the benefits of using a bulk insert and making it like a normal insert. Just remember that as it's name implies that it is used to insert chucks of data. I would remove loop and try again.
是的，批量插入是将大文件加载到数据库的正确路径。乍一看，我会说它需要这么长时间的原因是，正如您提到的，您正在循环文件中的每一行数据，这实际上意味着消除了使用批量插入的好处并使其像普通插入一样。请记住，顾名思义，它用于插入数据。我会删除循环并重试。
Also I'd double check your syntax for bulk insert as it doesn't look correct to me. check the sql that is generated by pyodbc as I have a feeling that it might only be executing a normal insert
另外，我会仔细检查您的批量插入语法，因为它对我来说看起来不正确。检查 pyodbc 生成的 sql，因为我觉得它可能只是在执行正常的插入
Alternatively if it is still slow I would try using bulk insert directly from sql and either load the whole file into a temp table with bulk insert then insert the relevant column into the right tables. or use a mix of bulk insert and bcp to get the specific columns inserted or OPENROWSET.
或者，如果它仍然很慢，我会尝试直接从 sql 使用批量插入，然后将整个文件加载到带有批量插入的临时表中，然后将相关列插入到正确的表中。或者混合使用批量插入和 bcp 来插入特定的列或 OPENROWSET。
回答by Gord Thompson
As noted in a comment to another answer, the T-SQL BULK INSERTcommand will only work if the file to be imported is on the same machine as the SQL Server instance or is in an SMB/CIFS network location that the SQL Server instance can read. Thus it may not be applicable in the case where the source file is on a remote client.
如对另一个答案的评论所述，T-SQLBULK INSERT命令仅在要导入的文件与 SQL Server 实例位于同一台计算机上或位于 SQL Server 实例可以读取的 SMB/CIFS 网络位置时才有效。因此，它可能不适用于源文件位于远程客户端的情况。
pyodbc 4.0.19 added a Cursor#fast_executemanyfeature which may be helpful in that case. fast_executemanyis "off" by default, and the following test code ...
pyodbc 4.0.19 添加了Cursor#fast_executemany功能，在这种情况下可能会有所帮助。fast_executemany默认情况下为“关闭”，以下测试代码...
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')
... took approximately 22 seconds to execute on my test machine. Simply adding crsr.fast_executemany = True...
...在我的测试机器上执行大约需要 22 秒。只需添加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')
... reduced the execution time to just over 1 second.
...将执行时间减少到 1 秒多一点。
