[DB-SIG] autocommit support in pep-249

[Daniele Varrazzo]

On Mon, Sep 12, 2011 at 11:20 PM, Chris Clark <Chris.Clark at ingres.com> wrote:

> 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):

Agreed, it would have been long due. The quick survey I did when we
introduced the session in psycopg (last May) has been:

- MySQLdb: conn.autocommit()
- cx_Oracle: conn.autocommit (attribute, not function)
- pyodbc: conn.autocommit (attribute)
- sqlite3: conn.isolation_level = None
- KInterbaseDB: not supported

(to give context, in psycopg you had to use
conn.set_isolation_level(0) before version 2.4.2, when we introduced
better ways to manipulate the session - see
<http://archives.postgresql.org/psycopg/2011-05/msg00026.php> for the
discussion. Psycopg now uses an autocommit attribute - see
<http://initd.org/psycopg/docs/connection.html#connection.autocommit>).


> 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

[thus]

>   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..

Because you need a getter and a setter, I feel the right solution is
to use a property. A function that behaves as a getter when called
with no parameter and as a setter if it has one is not something
widely used in pythonland. A more natural solution, but inferior,
would be using two functions (of which choosing the name is a
bikeshedding bloodbath). Properties are just the right tools for this
task, and appear to be the most used solution in popular drivers.

The behaviour of setting autocommit when a transaction is already open
is also to be seen: there are three basic option:

- have a commit
- have a rollback
- have an error

I feel the implicit commit is the most dangerous option. An implicit
rollback may be more acceptable (it's the same behaviour of closing
the connection with a transaction in progress): I proposed it for
psycopg but, in the discussion, raising an exception proved the most
popular option.

-- Daniele