Re: [sqlite] Python sqlite binding: commit inside select loop

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jim Wilcoxson wrote: > I did some checking on the pysqlite mailing list. Apparently the > change to reset all cursors before commit is recent, and the reason is > that pysqlite or sqlite itself would return bogus rows. It is pysqlite returning the

Re: [sqlite] Python sqlite binding: commit inside select loop

I did some checking on the pysqlite mailing list. Apparently the change to reset all cursors before commit is recent, and the reason is that pysqlite or sqlite itself would return bogus rows. There is an example here: http://article.gmane.org/gmane.comp.python.db.pysqlite.user/2217 Returning

Re: [sqlite] Python sqlite binding: commit inside select loop

Thanks for the comments and explanations everyone - much appreciated. It seems there are a few alternatives I can check out. Jim On 6/30/09, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Jim Wilcoxson wrote: >> I guess I am a bit confused. Igor

Re: [sqlite] Python sqlite binding: commit inside select loop

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jim Wilcoxson wrote: > I guess I am a bit confused. Igor says it's not possible, but Roger > says my example works. For the exact code you were running the error message came from pysqlite and not from SQLite. Other posters are not aware of the

Re: [sqlite] Python sqlite binding: commit inside select loop

I guess I am a bit confused. Igor says it's not possible, but Roger says my example works. One other point of confusion is that sometimes an interface layer will grab all or a bunch of the rows after a select, even though it may hand them to upper layers one row at a time. For example, with

Re: [sqlite] Python sqlite binding: commit inside select loop

thank you very much Richard! very interesting to get some info about these rather internal issues. This gives us a good background to review some potential dangerous loops. Your hint with the temp table is extremely useful. Best wishes Marcus Grimm > > On Jun 30, 2009, at 2:34 PM, Marcus Grimm

Re: [sqlite] Python sqlite binding: commit inside select loop

> Marcus Grimm wrote: >> what is possible I guess would be to start a transaction >> inside the loop, do something, and commit and procced >> with stepping > > No, this is not possible either. By starting the select, you also start > an implicit transaction. SQLite

Re: [sqlite] Python sqlite binding: commit inside select loop

On Jun 30, 2009, at 2:34 PM, Marcus Grimm wrote: > > 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. The official policy is that if you modify a table (via INSERT,

Re: [sqlite] Python sqlite binding: commit inside select loop

Marcus Grimm wrote: > what is possible I guess would be to start a transaction > inside the loop, do something, and commit and procced > with stepping No, this is not possible either. By starting the select, you also start an implicit transaction. SQLite doesn't support

Re: [sqlite] Python sqlite binding: commit inside select loop

> Marcus Grimm wrote: >> 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. > > ... but you cannot COMMIT your

Re: [sqlite] Python sqlite binding: commit inside select loop

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jim Wilcoxson wrote: > With the Python bindings, an error occurs > because a commit resets all pending select statements. Note that there are some constraints about how SQLite works detailed by Igor, but in this particular case you are being stymied

Re: [sqlite] Python sqlite binding: commit inside select loop

Marcus Grimm wrote: > 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. ... but you cannot COMMIT your changes

Re: [sqlite] Python sqlite binding: commit inside select loop

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

Re: [sqlite] Python sqlite binding: commit inside select loop

Jim Wilcoxson 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. >