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