[DB-SIG] autocommit support in pep-249

M.-A. Lemburg mal at egenix.com
Thu Sep 15 12:52:43 CEST 2011

Vernon Cole wrote:
> (I missed replying to the group. --- buggy mental code path ...;)
> ---------- Forwarded message ----------
> From: Vernon Cole <vernondcole at gmail.com>
> Date: Thu, Sep 15, 2011 at 4:23 AM
> Subject: Re: [DB-SIG] autocommit support in pep-249
> To: Federico Di Gregorio <fog at dndg.it>
> On Wed, Sep 14, 2011 at 6:16 AM, Federico Di Gregorio <fog at dndg.it> wrote:
>> [snap]
>> Client code can set .autocommit (yes, I am +1 for the attribute) to true
>> by two different code paths: the "correct" one and the "buggy" one. The
>> correct code path is not a problem: as long as the programmer knows the
>> behaviour she can do the right thing:
>>  1. exception => make sure to commit() or rollback() before,
>>                  depending on the wanted outcome;
>>  2. commit    => rollback() if you don't want to commit changes,
>>                  do nothing in the other case;
>>  3. rollback  => as (2), reversed.
>> What I am interested in is the behaviour of the driver when the code
>> sets .autocommit to true following a buggy code path, i.e., when there
>> is a pending transaction and the code is unaware of it:
>>  1. exception => pending transaction is lost, user (and eventually
>>                  also the programmer) gets a stack trace: this is
>>                  good because the user is sure about the outcome (data
>>                  is lost) _and_ has information about the problem;
>>  2. commit    => pending transaction is commited but it is what the
>>                  code was really supposed to do? noboby will know until
>>                  someone peeks at the database and _then_ a possibly
>>                  difficult bug hunting session begins;
>>  3. rollback  => as (2), but data is lost.
>> Having commit or rollbacks happen because of a bug really scares me:
>> spurious data starts to popup into the database and finding the bugs
>> usually isn't a piece of cake. That's why I vote for the exception.
>> federico
>> It's those buggy code paths that we are discussing, and I am much more
> interested in doing the right thing on a good code path.  We're using Python
> here, not Pascal.  If I wanted to force all programmers to be perfect, I'ld
> require that all variables be declared.   What we are discussing here is
> what to do in a situation which a wise programmer will never get in to.  I
> don't want to waste a lot of time and a lot of code making sure that the
> results of poor programming are consistent between different database
> engines.  I would advocate that the results of this questionable practice
> should be explicitly left as "undefined", with the cautionary note that an
> exception MIGHT occur, and suggest that the application programmer should
> either commit or rollback any in-progress transactions before changing the
> value of autocommit.
>   That way no complicated parsing or state tracking is required.  My
> __setattr__ for .autocommit would simply pass the new value on to the ADO
> engine to do whatever it pleased with it -- and pass back any error which
> the engine might decide to return.
> I LIKE simple -- it usually works better.

I agree with Vernon: the DB-API cannot mandate an exception in
case of a pending transaction, since the module may very well not
be aware of such a pending transaction:

Some databases manage
transactions on the server side and it's also possible to run
COMMIT and ROLLBACK using cursor.execute() (even though that is

If you then tell the user that an exception will be raised in
case of a pending transaction, she will feel mistakenly secure
about doing connection.autocommit = True without an explicit
.commit() or .rollback().

I find the implicit .commit() of setting connection.autocommit = True
very intuitive. After all, you expect the connection to take care
of the .commit()s for you when doing so.

Likewise, setting the attribute to False, means that the programmer
has to take care of the .commit()s again.

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Sep 15 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2011-10-04: PyCon DE 2011, Leipzig, Germany                19 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611

More information about the DB-SIG mailing list