Connection Context Manager and Commits

[benmatwil]

Describe the bug

When using a context manager as described in the docs:
https://github.com/microsoft/mssql-python/wiki/Connection#context-manager
there does not seem to be any commit on exit. Are the docs incorrect?

Also do bulkcopies completely ignore the current connection object and create a new one?

To reproduce

import mssql_python

CONN_STR = ...

# Create the table inside a context manager block — no explicit commit
with mssql_python.connect(CONN_STR) as conn:
    conn.execute("CREATE TABLE dbo.no_commit_test (id INT)")
    print("Tried creating table inside context manager")

# Open a new connection and check whether the table exists
with mssql_python.connect(CONN_STR) as conn:
    with conn.cursor() as cursor:
        cursor.tables(table="no_commit_test", schema="dbo", tableType="TABLE")
        exists = cursor.fetchone() is not None

print(f"Table exists after context manager exit: {exists}")
# Expected output: False — the CREATE TABLE was rolled back

Expected behavior

The process described by the docs

Further technical details

Python version: 3.10
SQL Server version: SQL Server 2025
Operating system: Windows/Docker

[github-actions]

Hi @benmatwil, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[bewithgaurav]

Hey @benmatwil, thanks for reporting this in detail.
You're right, this is a bug.

The __exit__ implementation today just calls close(), which always rolls back uncommitted changes when autocommit=False.
There's no commit path in the context manager at all, so clean exits and exception exits both end up rolling back.

The wiki and the docstrings all describe commit-on-success / rollback-on-exception. , and that's what the implementation should be doing.
We'll fix __exit__ to match.

On bulkcopy and connections: Yes, bulkcopy() opens a separate internal PyCoreConnection under the hood.
This is a known limitation of the design and has been discussed across & tracked in #492.

[benmatwil]

Ah thanks for looking!

Given the bulkcopy does not, does the arrow functionality share a connection with python or it's own? When the new underlying connection is created, is it with the same parameters? I'm guessing it does it's own commiting too for bulkcopy?
Is the plan to being these connections together at some point?

[bewithgaurav]

@benmatwil - you're correct, its with same params (we're fixing some bugs on that area e.g. #626)
yes, we do have a plan and there's some development undergoing to facilitate that.