Cursor does not commit when executemany is used within closing context manager

[urucoder]

Environment

• Python: 3.9.19
• pyodbc: 5.2.0
• OS: Ubuntu 22.04.1
• DB: SQL Server
• driver: ODBC Driver 17 for SQL Server

Issue

I'm trying to follow the cursor context manager documentation, where it explicit that context manager can be used like this

from contextlib import closing
with closing(cnxn.cursor()) as crsr:
    do_stuff

having autocommit=False as default it works great when using execute method for single inserts or updates, but it doesn't commit when using executemany, if the closing wrapper is removed and the raw cursor context manager is used, then it works great, but it stop committing once the closing manager is added.

Minimum code to reproduce:

import pyodbc

from contextlib import closing


def get_db_conn():
    server = "<set server>"
    dbname = "<set dbname>"
    conn_string = f"""Driver={{ODBC Driver 17 for SQL Server}};
        Server={server};Database={dbname};
        Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30"""
    return pyodbc.connect(conn_string)


# TODO: switch context manager to get it working
def insert_bulk(conn, values):
    with closing(conn.cursor()) as cur:
    # with conn.cursor() as cur:
        try:
            cur.fast_executemany = True
            cur.executemany(
                """
                INSERT INTO table (
                    col1, col2, col3, col4
                ) VALUES (?, ?, ?, ?)
                """, values
            )
        except pyodbc.DatabaseError as err:
            cur.rollback()
            raise
        
        finally:
            cur.fast_executemany = False
            # cur.close()


db_conn = get_db_conn()
values = [<set values>]
insert_bulk(db_conn, values)

When using the closing manager I need to manually run cur.commit() otherwise it doesn't commit, but this only happen with executemany, it works great for execute.

Additionally, in the code above, trying to close the cursor at the finally, fails with the error
pyodbc.ProgrammingError: Attempt to use a closed cursor.
but in the documentation it says the raw cursor context manager doesn't close the cursor

Please, let me know if I can help any other way. Thanks

[urucoder]

After further testing I'm seeing a related error, it also fails to autocommit for SQL UPDATE when using execute method, the only difference is that it allows to manually close the cursor at the finally clause.

I still see it working for SQL INSERT with the closing manager

[thecouchcoder]

I am seeing a similar bug.

Using a connection string with ;autocommit=True;

Once I add closing around my connection like so with closing(pyodbc.connect(connection_string, timeout=2)) as conn:, nothing is committed when using execute. After removing closing, data begins to be committed again.

[keitherskine]

@urucoder and @thecouchcoder , you might want to take a look at this Wiki article about database transactions:
https://github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management
It might help to clarify things.

In short though, database transactions happen at the Connection level, not the cursor level. And you must always commit your transactions (either explicitly with conn.commit() or with autocommit=True) or they will get rolled back when the Connection is closed.

[urucoder]

hi @keitherskine , maybe you can take a look to the documentation I linked in the bug description, given commiting the changes isn't required when using a context manager, please review the context manager documentation.

As you can see, commit() is called on the cursor's connection even if autocommit is False. Hence, the "context" is not so much the cursor. Rather, it's better to think of it as a database transaction that will be committed without explicitly calling commit(). Also, keep in mind the cursor is not explicitly closed when the context is exited.