Inserting rows while fetching(from another table) in SQLite

I'm getting this error no matter what with python and sqlite.

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

The code looks like this:

    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)

Can't do a fetchall() because table and column size are big, and the mem开发者_运维问答ory is scarce.

What can be done without resorting to dirty tricks(as getting the rowids in memory, which would probably fit, and then selecting the rows one by one)?.

---

The problem is that you've left the connection in auto-commit mode. Wrap a single transaction around the whole lot so that a commit only happens after you've done all the updates, and it should all work fine.

---

Don't know if this count as "dirty tricks" too ;-)

My solution to this problem is to use SELECT... LIMIT clause, assumed you have primary key integer field 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

---

The problem is that there should be no more than a single active cursor for a connection.

The solution is to use a new connection for the updates.

Unfortunatelly I do not remember the exact place in docs where I read it, so I can not prove it.

UPD:

The following code works on my 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')

And returns the following as expected:

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

If I move the conn2.commit() into the for loop, I get the same error as you mention:

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

Thus, the solution is to commit once at the end instead of committing after each line.