[egenix-users] No commit in connection context mgr

M.-A. Lemburg mal at egenix.com
Fri Nov 11 10:48:38 CET 2016

Hi Jan,

On 08.11.2016 17:09, Jan Murre wrote:
> Hi,
> I am using the following code with a MS SQL database and the Microsoft
> driver:
> -----------
> con = Connect(dsn, user=user,
>     password=password, clear_auto_commit=1)
> with con:
>     with con.cursor() as cursor:
>         cursor.callproc('proc_AddReservation_web', (12, None, 'nl-nl'))
>         result = []
>         while True:
>             result.append(cursor.fetchall())
>             if not cursor.nextset():
>                 break
> -----------
> Based on the documentation I would have expected a commit inside the
> context manager.

The connection con will get committed if the outer block
is left without exception (this is how connections work as
context managers).

The inner block will only close the cursor, not commit
the transaction (cursors as connection managers only
close the cursor).

> When I use a conn.commit() or even a cursor.close() in the inner with block
> (the cursor context manager), the transactions gets committed.
> Setting clear_auto_commit=0 results in the following exception:
> "mx.ODBC.Error.NotSupportedError: connection has AUTOCOMMIT turned on"
> So, it seems one of the context mgr. does try to commit the transaction.
> Furthermore, I want to add a try except block around the two context mgrs
> to catch/log/reraise exceptions.
> However, it seems that the context managers effectively swallow exceptions
> and do not reraise them on __exit__.

When using cursors and connections as context managers, they
both will return False in case of an error. This should result in
Python reraising the exception.

However, looking at the code, you may have uncovered a bug
here. We'll have a closer look and report back.

Thanks for reporting.

Best Regards,
Marc-Andre Lemburg

Professional Python Services directly from the Experts (#1, Nov 11 2016)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> Python Database Interfaces ...           http://products.egenix.com/
>>> Plone/Zope Database Interfaces ...           http://zope.egenix.com/

::: We implement business ideas - efficiently in both time and costs :::

   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 egenix-users mailing list