[DB-SIG] DB API extension suggestion

[Anthony Tuininga]

On 6/21/07, Carsten Haese <carsten at uniqsys.com> wrote:
> On Thu, 2007-06-21 at 10:41 -0600, Anthony Tuininga wrote:
> > I have been recently playing with context managers and the new "with"
> > statement added in Python 2.5. I would like to suggest the addition of
> > the following methods  to connections:
> >
> > def __enter__(self):
> >     return self
> >
> > def __exit__(self, excType, excValue, excTraceback):
> >     if excType is None and excValue is None and excTraceback is None:
> >         self.commit()
> >     else:
> >         self.rollback()
> >
> > This allows the following code:
> >
> > from __future__ import with_statement
> >
> > connection = .....
> > with connection:
> >     cursor = connection.cursor()
> >     cursor.execute("update SomeTable set SomeColumn = "SomeValue")
> >     cursor.execute("delete from SomeOtherTable where SomeOtherColumn = 5)
> >
> >
> > rather than
> >
> > try:
> >     cursor = connection.cursor()
> >     cursor.execute("update SomeTable set SomeColumn = "SomeValue")
> >     cursor.execute("delete from SomeOtherTable where SomeOtherColumn = 5)
> >     connection.commit()
> > except:
> >     connection.rollback()
> >
> > I've implemented this in cx_Oracle and it appears to work quite
> > nicely. Thoughts?
>
> It's a good idea, but there is a conflicting use case "in the wild" for
> having a connection behave as a context manager. InformixDB allows you
> to use connections and cursors as context managers that close
> themselves, analogous to what file objects do:
>
> with api.connect(dbname) as conn:
>    with conn.cursor() as cur:
>       cur.execute(stmt)

That's interesting but is it of much use? Connections and cursors
close themselves when they go out of scope. I rarely use
cursor.close() or connection.close() for that reason.

> To disambiguate these use cases and to improve code-readability, I think
> a context manager that encapsulates a transaction should have a name
> that makes this clear. Something like this:
>
> with conn.transaction():
>    do_stuff()
>
> I think we'd also have to discuss the semantics of how this with-managed
> transaction coexists with the transaction that is already active on the
> connection. Should that transaction be committed, rolled back, or
> continued? If the engine underneath supports nested transactions, should
> the with-statement's transaction enter a nested transaction?
>
> Maybe the transaction() function should grow a parameter for specifying
> this.

Good question on the nested transaction stuff. That might prove useful
-- but its also considerably more complicated than what I was
suggesting.... :-)

> Regards,
>
> --
> Carsten Haese
> http://informixdb.sourceforge.net
>
>
>