CSV导入的SQLite事务 [英] SQLite transaction for CSV importing

问题描述

我是python的新手，一直在我的树莓派，得到一个脚本和运行导入百万传感器数据记录到sqlite。我想在交易中做到这一点，使过程更有效率。我试图将交易分成10k块，如下所示： Python CSV到SQLite

I'm very new to python and have been working on my raspberry pi to get a script up and running to import millions of sensor data records into sqlite. I want to do this in transactions to make the process more efficient. I am trying to break the transactions down into 10k chunks as done here: Python CSV to SQLite

到目前为止，我有

import csv, sqlite3, time

def chunks(data, rows=10000):
    for i in range (0, len(data), rows):
            yield data[i:i+rows]

if __name__ == "__main__":

    t = time.time()

con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS sensor;")
cur.execute("CREATE TABLE sensor(key INT, reading REAL);")

filename = 'dummy.csv'
reader = csv.reader(open(filename,"r"))
divdata = chunks(reader)

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

    for col1, col2 in chunk:
            cur.execute('INSERT INTO sensor (key, reading) VALUES (?, ?)', (col1, col2))

    con.execute('COMMIT')

我在python 3.2.3中得到以下错误：

I'm getting the following error in python 3.2.3:

Traceback (most recent call last):
File "/home/pi/test1.py", line 20, in <module>
for chunk in divdata:
File "/home/pi/test1.py", line 4, in chunks
for i in range (0, len(data), rows):
TypeError: object of type '_csv.reader' has no len()

显然在大块部分在某个地方混乱，因为一切（基本插入）工作正常没有块和事务。任何帮助赞赏。

I'm obviously messing up in the chunks part somewhere as everything (basic insertion) works fine without the chunks and transaction. Any help appreciated.

推荐答案

您的SQL看起来不错。我确实看到你的CSV阅读器的一个问题，虽然：它不支持 len（）在 chunks（）

Your SQL looks okay. I do see a problem with your CSV reader, though: it doesn't support len() the way you're using it in chunks().

您可以在数据循环中使用更典型的行，请使用其中一种技术在这个线程，如果你需要将文件分成块。

You could either use the more typical for row in data loop, or use one of the techniques described in this thread if you need to break the file into chunks.

这篇关于CSV导入的SQLite事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！