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

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 2

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