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 old or new data would be one thing, but in this example, totally wrong data is returned. There's no explanation of why it was happening. On 6/30/09, Jim Wilcoxson <pri...@gmail.com> wrote: > 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 <rog...@rogerbinns.com> wrote: >> -----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 underlying >> implementation details in pysqlite and it doing all sorts of transaction >> related stuff behind the scenes, and so were telling you about SQLite >> behaviour in general. In summary the behaviour you see from pysqlite >> will not necessarily match the behaviour you would see if using the >> SQLite C api directly to do the same thing. >> >> It was issues like this that led me to create APSW in the first place >> because I wanted the exact SQLite semantics and not some random >> "standard" that tried to pretend all database apis behave in exactly the >> same way. I also document which SQLite apis are called from each Python >> api and the index lets you work in the reverse direction: >> >> http://apsw.googlecode.com/svn/publish/genindex.html#S >> >>> 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. >> >> Note that none of the Python apis do that by default and you have to >> call a method to grab all the results. The apis for other databases do >> do that by default since they use the network and grabbing result rows >> one at a time would be far slower than batching them. >> >>> For my particular app, I'm only adding new rows, so there wouldn't be >>> too many weird effects. >> >> There was a poster (I forget if it was this list or the pysqlite/APSW >> one) who managed to get an infinite loop. Effectively he was changing a >> rowid for each row found (an UPDATE was doing it - rowid wasn't >> explicitly mentioned in the query) while doing a select over all items. >> The constantly updated rowid effectively added the changed row to the >> end of the select results and so it carried on forever. >> >> Although the relevant information is in my APSW docs, I think I need to >> pull more of it together and make things clearer. >> >> Roger >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.9 (GNU/Linux) >> >> iEYEARECAAYFAkpKfdYACgkQmOOfHg372QTAjQCeL3zZCBh8Pgnh/GT95RH0JIY0 >> qGgAoKsqr66QwxKDtQNqe6W1jayU90/J >> =Xs+y >> -----END PGP SIGNATURE----- >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Software first. Software lasts! > -- Software first. Software lasts! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
