在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()操作。

在不使用卑劣手段（例如在内存中获取rowids，这可能适合，然后逐个选择行）的情况下，有什么可以做的呢？

- Samuel

Answer 1

问题在于您将连接保持在自动提交模式下。在整个操作过程中包装一个单独的事务，这样只有在完成所有更新后才进行提交，那么一切都应该正常工作。

- Donal Fellows

---

连接没有处于自动提交模式，我已经尝试显式设置隔离级别，但问题依然存在。在处理结束时进行单个提交不是一个选项，因为在saveBooks函数内部可能会发生“部分回滚”，只需要回滚某些更改而不是整个操作（而且很可能，导致我无法提交的原因也会阻止我“回滚”）。 - Samuel

@Samuel：你可以尝试使用SAVEPOINT，它是一种类似于半路检查点的东西...但我要提醒的是，我从未尝试过它们，所以我只知道我读到的一点点信息。 - Donal Fellows

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

- number5

Answer 3

问 题在于一个连接中不应该有超过一个活动的光标。

解决方案是使用新的连接进行更新。

不幸的是，我不记得在文档中读到了确切的位置，所以我无法证明它。

更新：

以下代码在我的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() 放进 for 循环中，我会得到你提到的相同错误。

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

因此，解决方案是在最后一次提交而不是每行提交一次。

- newtover

---

我也尝试过那个，但是出现了“数据库被锁定”等错误。无论如何，我采用了hackish的解决方案并继续前进。 - Samuel