[DB-SIG] Some obscurity with paramstyle

James Henstridge james at jamesh.id.au
Mon Jul 18 11:25:43 CEST 2011

On Mon, Jul 18, 2011 at 3:52 PM, M.-A. Lemburg <mal at egenix.com> wrote:
> Vernon Cole wrote:
>> A cursor and/or a
>> connection should probably be context managers, so that they will work in a
>> "with" statement.
> This is was discussed before and it should probably go into
> the DB API in some form.
> I'm no particular fan of hiding transactions in context
> managers, though. In practice this often causes problems, since
> you usually want to apply error handling logic in case of
> problems, other than simply issuing a .rollback().

For most of the applications I work with, the transaction handling has
been delegated to function decorators, or have it hidden in the
framework (e.g. Django's TransactionMiddleware).  If there are clean
up tasks that need to happen on transaction commit or roll back (e.g.
deleting a file on a failed transaction), then using a global
transaction manager like Zope's transaction module seems to be a good

For transaction retry or error reporting, I haven't seen much benefit
in leaving the transaction in an "open by broken" state over cleaning
up with a rollback.

> Another problem is that the connections used in a with
> statement will usually have already started a transaction
> before entering the context, so the .rollback() would
> remove more than just the things added in the with
> context.

None of the databases I've worked with fit the Python DB API's
"implicit begin" behaviour for transactions, so their adapters have
all needed to run a simple state machine to determine when it is
necessary to start a transaction.  So it would be pretty easy for them
to raise an error if an attempt was made to use the context manager
when a transaction was already in progress.

If all adapters that support transactions function like that, then I
think it would be sensible to require them to raise an error in that
case.  If they don't raise an error, you know that someone somewhere
is going to write code like the following:

    with transaction:
        # do stuff
        with transaction:
            # do more stuff

... and wonder why things break.

> There's also the problem of intuitive use: some users will
> likely expect the connection to also be closed when leaving
> the context - much like what happens with files.

If that is a concern, you could require a method call to get the
context manager rather than making the connection itself a context
manager.  For example:

    with connection.begin_transaction():

> The case for having cursors automatically closed by
> is not problematic

I don't think it makes sense to make cursors behave as context
managers for transaction handling.  Transactions are a connection
level concept affecting all the cursors for the connection, so this
would just confuse matters and lead to coding errors.


More information about the DB-SIG mailing list