I'm using the Python sqlite3 (pysqlite) bindings. I'd like to be able
to do a select, and while fetching those rows and inserting new ones,
periodically do a commit. With the Python bindings, an error occurs
because a commit resets all pending select statements. Here is an
example showing the error:
import sqlite3
con = sqlite3.connect('testdb')
con.execute('drop table t')
con.execute('create table t(f)')
con.execute('insert into t (f) values (1)')
con.execute('insert into t (f) values (2)')
con.execute('insert into t (f) values (3)')
print '1st select:'
cur = con.execute('select * from t')
for row in cur:
print row
print '2nd select:'
cur = con.execute('select * from t')
for row in cur:
print row
con.execute('update t set f=2 where f=1')
con.commit()
Running it shows:
1st select:
(1,)
(2,)
(3,)
2nd select:
(1,)
Traceback (most recent call last):
File "dbbug.py", line 17, in <module>
for row in cur:
sqlite3.InterfaceError: Cursor needed to be reset because of
commit/rollback and can no longer be fetched from.
I can understand that there is some potential weirdness here in the
case where rows are being deleted or rowid's are getting changed
inside a select, and I think deciding I want to see those updates in
the select results is an isolation level setting - not sure of the
details. Right now, I don't think it matters whether I see the old or
new values, because I'm mostly just adding rows inside the
transaction, updating some stuff where the old or new value would be
okay, and not doing any deletes.
So I have a couple of questions/observations:
1. Is the thing about resetting cursors an SQLite requirement, or
something the Python binding is doing to protect people from the
confusion of seeing the updated rows.
2. If I were deleting rows, I can see where that would be a problem:
what if some of the rows satisfying the query were deleted? Once the
commit happens, the rows are gone unless there is a delete/add with
the same rowid.
I'm thinking about removing this reset from pysqlite, but don't want
to trash my database either.
Thanks for any insight.
Jim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
