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
[email protected] | 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
