Python CSV 转 SQLite

Question

我正在“转换”一个很大的CSV文件（大约1.6GB），并把CSV中的特定字段插入到SQLite数据库中。简单来说，我的代码大致是这样的：

import csv, sqlite3

conn = sqlite3.connect( "path/to/file.db" )
conn.text_factory = str  #bugger 8-bit bytestrings
cur = conn.cur()
cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

reader = csv.reader(open(filecsv.txt, "rb"))
for field1, field2, field3, field4, field5 in reader:
  cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

一切都按我预期的那样工作，除了... 处理的时间实在是太长了。我是不是写错代码了？有没有更好的方法可以提高性能，完成我需要的工作（就是把CSV中的几个字段简单地转换成SQLite表）？

**补充说明 -- 我尝试直接将CSV导入SQLite，正如建议的那样，但结果发现我的文件中有字段包含逗号（例如："My title, comma"）。这导致导入时出现错误。看起来这种情况太多了，手动编辑文件不太现实...

还有其他想法吗？？**

Answer 1

正如Chris和Sam所说，使用事务确实能大幅提升插入数据的性能。

我想推荐另一个选择，就是使用一套Python工具来处理CSV文件，叫做csvkit。

安装方法如下：

pip install csvkit

要解决你的问题

csvsql --db sqlite:///path/to/file.db --insert --table mytable filecsv.txt

Answer 2

可以直接导入CSV文件：

sqlite> .separator ","
sqlite> .import filecsv.txt mytable

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Answer 3

Chris说得对 - 使用事务；把数据分成小块，然后再存储。

“...除非已经在一个事务中，否则每个SQL语句都会为它启动一个新的事务。这是非常耗费资源的，因为每个语句都需要重新打开、写入和关闭日志文件。通过用BEGIN TRANSACTION;和END TRANSACTION;语句将一系列SQL语句包裹起来，可以避免这种情况。即使是那些不改变数据库的语句，这种加速效果也是可以得到的。” - 来源：http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

“...还有一个可以加速SQLite的小技巧：事务。每当你需要进行多次数据库写入时，把它们放在一个事务中。这样，写入操作就不会每次都去写（和锁定）文件，而是在事务完成时只写一次。” - 来源：SQLite的可扩展性如何？

import csv, sqlite3, time

def chunks(data, rows=10000):
    """ Divides the data into 10000 rows each """

    for i in xrange(0, len(data), rows):
        yield data[i:i+rows]


if __name__ == "__main__":

    t = time.time()

    conn = sqlite3.connect( "path/to/file.db" )
    conn.text_factory = str  #bugger 8-bit bytestrings
    cur = conn.cur()
    cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

    csvData = csv.reader(open(filecsv.txt, "rb"))

    divData = chunks(csvData) # divide into 10000 rows each

    for chunk in divData:
        cur.execute('BEGIN TRANSACTION')

        for field1, field2, field3, field4, field5 in chunk:
            cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

        cur.execute('COMMIT')

    print "\n Time Taken: %.3f sec" % (time.time()-t)