Writing a convenient and decent-looking wrapper for working with transactions is a favorite pastime for many developers. Transactions must be able to start, finish and rollback properly, and they can also be nested. Python is a multifaceted language (or, as speechwriters say, “Multi-paradigm”), giving us innumerable ways of doing it.

As a foundation, I usually use Python DB API (namely, psycopg2 module). The API itself is rather low-level and is crying for improvement. What can be done?

Initially, let’s just make an ordinary decorator for opening and closing a transaction:

def do_database_stuff():
    return db.fetchall('select * from employee')

here db represents a small helper class, which monitors the opening and closing of cursors, and also contains connection management code.

Our decorator is not quite cool. On the one hand, it does everything correctly and properly closes the transaction with any outcome; on the other hand, we need to write a separate function for every transaction. This is somewhat messy.

Lets try to write it down as a lambda expression:

transactional(db)(lambda:db.fetchall('select * from employee'))()

Certainly not the cleanest line of code in the Python world. Incidentally, it is easy to make mistakes and miss the last pair of brackets.

To make us all happy, Python 2.5 offers a special new syntax for executable blocks, allowing better flow control of block entrance/exit. Such blocks are very common in the management of system resources: files, sockets, semaphores, etc.

This is how it will look like:

from __future__ import with_statement
with db.transaction() as tx:
    def employees = db.fetchall('select * from employee')

This is just one of the possible options. We have named object, tx, specified in the with-expression, representing our transaction. We can later place some useful methods into tx.

How with-expression interacts with an associated object (in our case tx)? There are two agreements:

  1. obj.__enter__() is always invoked before entering with-block.
  2. Any way out of with-block always causes a special method obj.__exit__(type, value, traceback) method to be invoked, where all three parameters will be filled only when an error occurs (typically for Python, they represent type of exception, the exception object and the call stack, respectively).

In our case, everything is quite simple:

  • We make an __enter__ method to open the transaction and perform any connection management logic.
  • Our __exit__ callback will be closing the transaction. The simplest implementation would be to always execute always commit on succuss, and if the with-block threw an exception, always try to do rollback . In real life, of course, this is not enough – you have to keep track of what kind of exception were thrown.

Python also makes sure that the call to return inside with-block will just as well be “filtered” by __exit__ callback.

What are the disadvantages of managing transactions via with-blocks? First of all, with-statement is not a function in terms of Python. For example, we can not repeat the execution of the block in the __exit__ callback (in case of decorators this can easily be achieved, allowing to program any retry logic).