Insert rows when retrieving (from another table) in SQLite

Question

I get 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 is as follows:

    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)

It is not possible to do fetchall () because the size of the table and column is large and memory is small.

What can be done without resorting to dirty tricks (like getting rows in memory that would probably do, and then selecting rows one by one)?

Answer 1

The problem is that you left the connection in auto-lock mode. Wrap a single transaction around the entire batch so that the commit occurs only after you have made all the updates, and everything should work fine.

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

, .

.

, , , .

UPD

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

, , , .