On Fri, 2010-03-12 at 09:35 +0100, Laszlo Nagy wrote: > > No it doesn't. The problem is that using a connection as a context > > manager doesn't do what you think. > > > > It does *not* start a new transaction on __enter__ and commit it on > > __exit__. As far as I can tell it does nothing on __enter__ and calls > > con.commit() or con.rollback() on exit. With isolation_level=None, > > these are no-ops. > > > Thank you Ryan! You are abolutely right, and thank you for reading the > source. Now everything works as I imagined.
No problemo - isolation_level has given me my fair share of headaches in the past, so I couldn't resist the opportunity to understand it a little better. > The way the context manager and isolation_level works looks very very > strange to me. Here is a demonstration: > > import sqlite3 > def getconn(): > conn = sqlite3.connect(':memory:') > conn.isolation_level = None > return conn > def main(): > with getconn() as conn: > conn.execute("create table a ( i integer ) ") > try: > conn.execute("insert into a values (1)") > with conn: > conn.execute("insert into a values (2)") > raise Exception > except: > print "There was an error" > for row in conn.execute("select * from a"): > print row > main() > > > Output: > > There was an error > (1,) > (2,) > > > Looks like the context manager did not roll back anything. Yes, because there were no transactions created so there was nothing to roll back. > If I remove > isolation_level=None then I get this: > > There was an error > > E.g. the context manager rolled back something that was executed outside > the context. Yes, because the transactions created by the default isolation level do not nest, so the rollback happens at outermost scope. > I cannot argue with the implementation - it is that way. > But this is not what I would expect. I believe I'm not alone with this. That's at least two of us :-) > Just for clarity, we should put a comment at the end of the > documentation here: > > http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions > > I would add at least these things: > > #1. By using isolation_level = None, connection objects (used as a > context manager) WON'T automatically commit or rollback transactions. > #2. Using any isolation level, connection objects WON'T automatically > begin a transaction. > #3. Possibly, include your connection manager class code, to show how to > do it "the expected" way. > > Also one should clarify in the documentation, what isolation_level does. > Looks like setting isolation_level to None is not really an "auto commit > mode". It is not even part of sqlite itself. It is part of the python > extension. I think of it as almost the opposite - you have to set isolation_level=None to get the unadulterated behaviour of the underlying sqlite library. I'm sure the devs would appreciate a documentation patch (submission details at http://python.org/dev/patches/). I'm also pretty confident that I won't have time to do one up anytime soon :-) Good luck with your project! Ryan -- Ryan Kelly http://www.rfk.id.au | This message is digitally signed. Please visit r...@rfk.id.au | http://www.rfk.id.au/ramblings/gpg/ for details
signature.asc
Description: This is a digitally signed message part
-- http://mail.python.org/mailman/listinfo/python-list