[DB-SIG] Use of context managers with DB API 2

[Christoph Zwerschke]

Am 02.11.2012 01:15, schrieb Vernon Cole:
 > I've been scratching my head over this question, too.  Someone
 > (Christoph?) please craft for us a little sample program, as small as
 > possible, showing how this ought to look in practice.

The implementation would be like this:

class ConnectionWithContextManager(Connection):

     def __enter__(self):
         return self

     def __exit__(self, et, ev, tb):
         if et is None and ev is None and tb is None:
             self.commit()
         else:
             self.rollback()

Cursor objects should also have an __enter__ method that returns self, 
but their __exit__ method should just execute self.close(). You can also 
call contextlib.closing on a normal cursor to get that.

The usage would be like this:

     con = dbapi2.connect(...)
     with con:  # 1st transaction
         with con.cursor() as cur:
             cur.execute("insert into debit(amount) values (-100)")
             cur.execute("insert into credit(amount) values (100)")
     with con:  # 2nd transaction
         with con.cursor() as cur:
             cur.execute("insert into debit(amount) values (-200)")
             cur.execute("insert into credit(amount) values (200)")
     con.close()

SQLite hasn't implemented cursors as context managers, but it has added 
execute() as a shortcut method on the connection, so here you can do:

     con = sqlite3.connect(...)
     with con:  # 1st transaction
         con.execute("insert into debit(amount) values (-100)")
         con.execute("insert into credit(amount) values (100)")
     with con:  # 2nd transaction
         con.execute("insert into debit(amount) values (-200)")
         con.execute("insert into credit(amount) values (200)")
     con.close()

See also 
http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

What I find a bit ugly about this approach is that now the context 
managers of connections and cursors behave differently, and that code 
written using these context managers is not self-explanatory. If 
connections and cursors would just have closing context managers, and 
connections had an extra "transaction" member that is a context manager 
for handling the transaction, the code would be more readable and 
explicit which is better than implicit:

     with dbapi2.connect(...) as con:
         with con.transaction:
             with con.cursor() as cur:
                 cur.execute("insert into debit(amount) values (-100)")
                 cur.execute("insert into credit(amount) values (100)")
         with con.transaction:
             with con.cursor() as cur:
                 cur.execute("insert into debit(amount) values (-200)")
                 cur.execute("insert into credit(amount) values (200)")

Or, when using the shortcut methods:

     with dbapi2.connect(...) as con:
         with con.transaction:
             con.execute("insert into debit(amount) values (-100)")
             con.execute("insert into credit(amount) values (100)")
         with con.transaction:
             con.execute("insert into debit(amount) values (-200)")
             con.execute("insert into credit(amount) values (200)")

-- Christoph