在SQLite中从另一个表获取时插入行

Question

我在使用Python和SQLite时总是遇到这个错误。

  File "addbooks.py", line 77, in saveBook
  conn.commit()
  sqlite3.OperationalError: cannot commit transaction - SQL statements in progress

代码大概是这样的：

    conn = sqlite3.connect(fname)
cread = conn.cursor()

cread.execute('''select book_text from table''')
while True:
    row = cread.fetchone()
    if row is None:
        break
    ....
    for entry in getEntries(doc):
        saveBook(entry, conn)

因为表和列的数据量很大，而内存又不够，所以无法使用fetchall()。

有没有什么办法可以解决这个问题，而不需要用一些不太好的方法（比如把行的ID放到内存中，这样可能能放下，然后再一行一行地选择）？

Answer 1

问题在于，每个连接只能有一个活动的光标。

解决办法是为更新操作使用一个新的连接。

不幸的是，我不记得在文档的哪个地方看到过这个，所以无法提供证明。

更新：

以下代码在我的Windows XP上可以正常运行：

import sqlite3
import os
conn1 = sqlite3.connect('test.db')
cursor1 = conn1.cursor()
conn2 = sqlite3.connect('test.db')
cursor2 = conn2.cursor()


cursor1.execute("CREATE TABLE my_table (a INT, b TEXT)")
cursor1.executemany("INSERT INTO my_table (a, b) VALUES (?, NULL);", zip(range(5)))
conn1.commit()

cursor1.execute("SELECT * FROM my_table")
for a, b in cursor1:
    cursor2.execute("UPDATE my_table SET b='updated' WHERE a = ?", (a, ))

conn2.commit()

print "results:"
print 10 * '-'
cursor1.execute("SELECT * FROM my_table")
for a, b in cursor1:
    print a, b
cursor1.close()
conn1.close()
cursor2.close()
conn2.close()
os.unlink('test.db')

并且返回了预期的结果：

results:
----------
0 updated
1 updated
2 updated
3 updated
4 updated

如果我把conn2.commit()放到循环里面，就会出现你提到的同样错误：

Traceback (most recent call last):
  File "concurent.py", line 16, in <module>
    conn2.commit()
sqlite3.OperationalError: database is locked

所以，解决方案是在最后一次提交，而不是在每一行后都提交。

Answer 2

我不知道这算不算“脏技巧” ;-)

我解决这个问题的方法是使用 SELECT... LIMIT 语句，假设你有一个主键的整数字段 id

current_id = 0
while True:    
    cread.execute('''select book_text from table where id > %s limit 2''' % current_id)
    results = cread.fetchall()
    if results is None:
        break;
    for row in results:
         ... (save book) ...
         current_id = row.id

Answer 3

问题在于你把连接设置成了自动提交模式。你需要把所有操作放在一个事务里，这样只有在你完成所有更新后才会提交，这样就能正常工作了。