提高sqlite3列更新速度

Question

我写了一段Python代码，这段代码会不断地从sqlite数据库里读取数据，对这些数据进行一些计算，然后把结果作为新列写回数据库。不过，更新数据库的那部分代码感觉越来越慢了。有没有什么更快的方法可以做到同样的事情呢？

下面是我代码中相关的部分。在这段代码里，我创建了两个新列，分别叫做column_I1_和column_I2_，每个列名后面会加上迭代的编号，比如column_I1_1和column_I2_1，然后把新的结果从相应的列表写入这些列。问题是，由于迭代次数很多，计算也很耗时，加上行数很多（这里的行数由num值定义，大约有11000行），所以这个过程太慢了……

有没有其他方法可以替代更新操作呢？因为我觉得更新的时候，每次添加新值都要复制那一列，这样就导致了速度变慢。我以前用Excel做这个，速度快多了，但因为Excel有256列的限制，所以我不得不换用数据库。

cur.execute("alter table C add column_I1_%d integer"%iter) #makes a new column indexed by iteration number
con.commit()
for e in range(0,num): # num is a given number as input
    cur.execute("UPDATE C SET column_I1_%d=? WHERE Id=%d"%(iter,e+1),(w[e],)) # w is a list containing some results
con.commit()
    #
cur.execute("alter table C add column_I2_%d integer"%iter)  
con.commit()
for f in range(num,(2*num)):         
    cur.execute("UPDATE C SET column_I2_%d=? WHERE Id=%d"%(iter,f-num+1),(w[f],))
con.commit()

.....

非常感谢你的评论。另外，我对Python还比较陌生，所以请多多包涵！ :)

Answer 1

你可以考虑一次性使用 executemany，而不是在循环中多次调用 execute。

比如，看看下面的代码，

for e in range(0,num): # num is a given number as input
    cur.execute("UPDATE C SET column_I1_%d=? WHERE Id=%d"%(iter,e+1),(w[e],))

它可以改成下面这样，

cur.executemany("UPDATE C SET column_I1_%d=? WHERE Id=?"%(iter), zip(w, range(num)))

Answer 2

为了加快对Id列的查找速度，可以在这个列上创建一个索引：

cur.execute("CREATE INDEX MyLittleIndex ON C(Id)");

另外，如果Id列中的值是唯一的，可以把这个列声明为主键（这样会自动创建一个索引）：

cur.execute("CREATE TABLE C(Id PRIMARY KEY)");

如果这些值是整数，把这个列声明为INTEGER PRIMARY KEY会更高效一些：

cur.execute("CREATE TABLE C(Id INTEGER PRIMARY KEY)");