为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

以下是我需要帮助的代码。我必须在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是表名。


查看完整描述

3 回答

?
一只甜甜圈

如@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实例可以直接读取源文件时,才执行语句。对于源文件在远程客户端上的情况


查看完整回答
反对 回复 2019-11-14
?
慕沐林林

如对另一个答案的注释中所述,BULK INSERT仅当要导入的文件与SQL Server实例位于同一台计算机上或位于SQL Server实例可以读取的SMB / CIFS网络位置时,T-SQL 命令才起作用。因此,它可能不适用于源文件在远程客户端上的情况。


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

...在我的测试机器上执行大约需要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')

...将执行时间减少到刚超过1秒。


查看完整回答
反对 回复 2019-11-14
?
料青山看我应如是

是的,批量插入是将大文件加载到数据库中的正确路径。乍一看,我要说的是,花费这么长时间的原因是您提到要遍历文件中的每一行数据,这实际上意味着已消除了使用批量插入并将其像普通插入一样的好处。请记住,因为它的名称暗示它被用来插入数据块。我将删除循环,然后重试。


另外,我会仔细检查您的批量插入语法,因为它对我来说似乎不正确。检查pyodbc生成的sql,因为我感觉它可能仅在执行普通插入操作


或者,如果仍然很慢,我会尝试直接从sql使用批量插入,或者将整个文件加载到带有批量插入的临时表中,然后将相关的列插入正确的表中。或混合使用批量插入和bcp来获取插入的特定列或OPENROWSET。


查看完整回答
反对 回复 2019-11-14

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信