If I use a context manager with a pyodbc cursor, will it rollback on an error?

Question

I tried reading the pyodbc source, but it is all C++ code (I'm not competent in C++). I need to know the behavior of a statement like:

 with connection.cursor() as cursor:
     cursor.execute(query_1) #inserts some stuff into table A
     cursor.execute(query_2) #inserts some stuff into table B, but throws an error

 with connection.cursor() as cursor2:
     cursor.execute(select_query_1) #selects from table A
     cursor.execute(select_query_2) #selects from table B

This is in the same connection where we haven't commited yet - I am curious about whether selecting from table A will give the new values inserted in the first cursor - or whether the error in query_2 causes the work of the first cursor to be rolled back for table A.

score 7 · Accepted Answer · answered Sep 09 '15 at 03:39

Looking at the source, and the ODBC Documentation the behavior depends, in part, on whether autocommit is enabled, or disabled.

The first call to cursor.execute() implicitly opens a new transaction, if autocommit is False. (See Note 1) Each subsequent call to execute() for this cursor uses the same transaction, unless commit() or rollback() are called.
When Python leaves the with block and calls __exit__:
- if autocommit is False, and there were no errors, the cursor automatically commits the transaction. See note 2 for the source of this operation.
- if autocommit is True, or if there is an error, the cursor exits without ending the transaction.
When the cursor is closed, either with cursor.close() or when __del__ is explicitly or implicitly called, pending statement results are also automatically deleted when the cursor's internal handle is freed. (Note 3)

If cursor.execute() fails, the transaction is still open, but not committed. It is up to you to commit, or rollback in this case.

All that said, you should still test the behavior in your target environment. Different ODBC data sources have different levels of transaction support.

Note 1: (source)

If the data source is in manual-commit mode (requiring explicit transaction initiation) and a transaction has not already been initiated, the driver initiates a transaction before it sends the SQL statement.

Note 2: (pyodbc/cursor.cpp @ 2151)

// If an error has occurred, `args` will be a tuple of 3 values.  
// Otherwise it will be a tuple of 3 `None`s.
I(PyTuple_Check(args));
if (cursor->cnxn->nAutoCommit == SQL_AUTOCOMMIT_OFF && PyTuple_GetItem(args, 0) == Py_None)
    ...
    ret = SQLEndTran(SQL_HANDLE_DBC, cursor->cnxn->hdbc, SQL_COMMIT);

Note 3: (source

When an application calls SQLFreeHandle to free a statement that has pending results, the pending results are deleted.

`>if autocommit is False` to me the logic seems inverted. Please review and correct if necessary — Pynchia, May 23 '23 at 14:04

score 5 · Answer 2 · answered Oct 30 '19 at 17:01

In my experience using a pyodbc connection (with a Microsoft Access Driver) is that:

Assuming autocommit is False (which seems to be the default)

Doesn't commit:

with pyodbc.connect(connectionString) as con:

    with con.cursor() as cursor:

        cursor.execute(query)
        raise Exception('failed')

Does commit:

with pyodbc.connect(connectionString) as con:

    with con.cursor() as cursor:

        cursor.execute(query)

    raise Exception('failed')

Doesn't commit:

with pyodbc.connect(connectionString) as con:

    cursor = con.cursor()
    cursor.execute(query)
    cursor.close()

    raise Exception('failed')

Does commit:

with pyodbc.connect(connectionString) as con:

    cursor = con.cursor()
    cursor.execute(query)
    cursor.close()

raise Exception('failed')

score 0 · Answer 3 · answered Sep 09 '15 at 15:56

pyodbc does not automatically handle transaction for you.

It means that select_query_1 will see records, inserted by query_1, even if query_2 failed. (I assume try/catch around first block of code so second one will be executed). However, some RDBMS, namely PostgreSQL, does not allow any other statement (except rollback) to be executed if one of previous statements in same transaction fail. In case of PostrgreSQL RDBMS (for example) and without autocommit, select_query_1 will just fail if query_2 failed.

If I use a context manager with a pyodbc cursor, will it rollback on an error?

3 Answers3