Inserting rows while fetching(from another table) in SQLite

Question

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 memory 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)?.

Answer 1

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

Answer 2

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.

Answer 3

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.