[DB-SIG] autocommit support in pep-249

[Chris Clark]

Hi All,

I was discussing with someone today autocommit support and found an area 
of pep-249 where I think we could improve the wording (and the spec):

http://www.python.org/dev/peps/pep-0249/

.....

        .commit()
          
            Commit any pending transaction to the database. Note that
            if the database supports an auto-commit feature, this must
            be initially off. An interface method may be provided to
            turn it back on.

.....

It is the last sentence that I'm looking at:

 > An interface method may be provided to turn it back on.

Comments:

    * The "may" clearly marks this as an optional interface (and I'm
      happy about that).
    * It would be helpful to make clear what the interface method would
      be so module authors can implement this consistently
    * if autocommit can be turned on in a connection, there should have
      a way to turn it off again


Here are some examples of the "interface method" implemented out in the 
wild today

    * It looks like mxodbc handles this as a setconnectoption() method
      on the connection.
      http://www.egenix.com/products/python/mxODBCConnect/doc/mxodbc/
    * a number of other ODBC based drivers offer an option in the
      connect() constructor (only)
    * The mysql module <http://mysql-python.sourceforge.net/> implies it
      has an method on the connection object for this conn.autocommit(),
      but this does not appear to be available in the pep-249 compatible
      API <http://mysql-python.sourceforge.net/MySQLdb-1.2.2/>!


Obviously there is plenty of scope for different implementations. I 
think there is value in documenting a recommendation on what this 
interface should look like.

I like the API approach mxodbc has taken (it matches ODBC very well) but 
it doesn't feel Pythonic :-(

To get the ball rolling, here is an initial suggestion:

    * needs to take a boolean flag as a parameter to enable and also disable
    * needs to return the auto commit state so it can be queried

pep changes, marked between ** and **:

Connection Objects

    Connection Objects should respond to the following methods:
......

        .commit()
          
            Commit any pending transaction to the database. Note that
            if the database supports an auto-commit feature, this must
            be initially off. **See the option autocommit() method to change
            autocommit behavior.**
            
            Database modules that do not support transactions should
            implement this method with void functionality.

.....
**
    Connection Objects may optional implement and respond to the 
following methods:

    autocommit(on=None)
       returns True when autocommit is on, and False when autocommit is off.
       If the keyword parameter "on" is set to True, the connection will 
commit any open transactions (as if connection.commit() was issued) and 
all subsequent transactions will auto commit, the method returns True if 
successfully in autocommit mode.
       If the keyword parameter "on" is set to False, the connection 
will commit any open transactions (as if connection.commit() was issued) 
and all subsequent transactions will no longer auto commit, the method 
returns False if successfully out of autocommit mode..
**

The reason for the immediate commit is to try and avoid DBMS specific 
behaviors (read error conditions) with different vendors when an auto 
commit is requested with open transactions.

Comments?


Chris