使用Python将大量数据批量插入SQLite

Question

我读到这个：Importing a CSV file into a sqlite3 database table using Python

似乎每个人都建议使用逐行读取，而不是使用bulk.import from SQLite。但是，如果有数百万行的数据，这将使插入速度非常慢。有没有其他办法来规避这个问题？

更新：我尝试了下面的代码逐行插入，但是速度没有我预期的好。还有什么可以改进的吗

for logFileName in allLogFilesName:
    logFile = codecs.open(logFileName, 'rb', encoding='utf-8')
    for logLine in logFile:
        logLineAsList = logLine.split('\t')
        output.execute('''INSERT INTO log VALUES(?, ?, ?, ?)''', logLineAsList)
    logFile.close()
connection.commit()
connection.close()

Answer 1

使用生成器表达式动态地将数据分成块，在事务中进行插入。这是sqlite optimization FAQ的一句话：

Unless already in a transaction, each SQL statement has a new transaction started for it. This is very expensive, since it requires reopening, writing to, and closing the journal file for each statement. This can be avoided by wrapping sequences of SQL statements with BEGIN TRANSACTION; and END TRANSACTION; statements. This speedup is also obtained for statements which don't alter the database.

Here's代码的外观。

此外，sqlite还可以import CSV files。

Answer 2

Sqlite可以做tens of thousands of inserts per second，只需确保在一个事务中完成所有这些操作，方法是将插入包围在BEGIN和COMMIT中。（executemany（）会自动执行此操作。）

一如既往，在你知道速度会成为问题之前不要优化。首先测试最简单的解决方案，只有在速度不可接受时才进行优化。

Answer 3

因为这是谷歌搜索的第一个结果，我想更新这个问题可能会更好。

从python sqlite docs中可以使用

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?,?)", persons)

我使用这种方法一次提交了超过50k行插入，而且速度非常快。