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! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users