sqlite savepoint problem [solved]

[Ryan Kelly]

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
ryan at rfk.id.au        |  http://www.rfk.id.au/ramblings/gpg/ for details

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
URL: <http://mail.python.org/pipermail/python-list/attachments/20100315/48b57baf/attachment.sig>