Jim Wilcoxson <pri...@gmail.com> wrote: > 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.
You can't do this with SQLite. Commit fails if there are outstanding statements. > 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. It is SQLite requirement that all statements be reset or finalized before a transaction can commit. SQLite doesn't automatically reset those statements - it fails the COMMIT operation instead. Apparently, Python binding is trying to be helpful and resetting all the statements for you. One way or the other, your design ain't gonna fly. > 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. None of that is the problem. Realize that your select already sees changes made on the same connection, even before they are committed. The issue has to do with locking. COMMIT needs to release all locks the connection holds - but it can't if you still have outstanding statements. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
