I'm a bit confused about Igors replies because you can very well do a select, step throu the results and even delete or update the table row that you are currently inspecting and procceed with stepping. As long as you use the same database connection for this.
I'm not sure what will happend if you for example delete a table row that would be the next row that sqlite3_step would see, but doing so to me sounds a bad approach anyhow. I do this directly using the C api, don't know why the python stuff is different here... Marcus > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
