Manage connections with mssql-python

Most applications follow a simple pattern: open a connection, run queries, close the connection. The following sections cover opening and closing connections, using context managers, configuring autocommit, and working with connection attributes.

Open a connection

Use the connect() function to establish a connection. Pass a connection string with your server, database, and authentication details:

import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;"
    "Authentication=ActiveDirectoryDefault;Encrypt=yes"
)

The connect() function accepts:

• A connection string as the first positional argument or connection_str keyword.
• Individual keywords that the driver merges into the connection string.
• Other options like autocommit, timeout, and attrs_before.

You can mix both approaches. Keywords override values in the connection string, which is useful when you store a base connection string in configuration and override settings like timeout per call:

# Base connection string from config, with per-call overrides
conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;"
    "Authentication=ActiveDirectoryDefault;Encrypt=yes",
    timeout=30,
    autocommit=True
)

Close a connection

Always close connections when done to return them to the connection pool and release server resources. Unclosed connections hold server-side memory and can eventually exhaust the connection pool, causing new connection attempts to block or fail.

conn = mssql_python.connect(connection_string)
try:
    # Use the connection
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
finally:
    conn.close()

Once closed, the connection can't be used:

conn.close()
print(conn.closed)  # True

# This raises an error
cursor = conn.cursor()  # InterfaceError: Cannot create cursor on closed connection

Calling close() multiple times is safe (idempotent):

conn.close()
conn.close()  # No error

Context managers

Use the with statement to manage connections in most applications. It guarantees that the driver closes the connection when the block exits, even if an exception occurs. This approach eliminates the risk of leaked connections from forgotten close() calls:

with mssql_python.connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
    cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
    conn.commit()  # Must commit explicitly when autocommit=False
# Connection automatically closed

The context manager closes the connection on exit. It does not automatically commit or roll back transactions:

• Always: Calls close() on exit, whether or not an exception occurred.
• close() behavior: If autocommit=False, any uncommitted changes are rolled back when the connection closes.
• You must call conn.commit() explicitly to persist changes.

This design follows PEP 249 behavior and prevents accidental partial commits. If your code raises an exception before reaching commit(), the in-progress transaction is safely rolled back:

# Equivalent manual code:
conn = mssql_python.connect(connection_string)
try:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
    cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
    conn.commit()  # Must commit explicitly
finally:
    conn.close()  # Rolls back uncommitted changes if autocommit=False

Autocommit mode

By default, autocommit=False, which means each statement runs inside an implicit transaction. You must call conn.commit() to persist changes or conn.rollback() to discard them. Implicit transactions are the safest choice for data modifications because it lets you group multiple statements into a single atomic operation.

Enable autocommit when you want each statement to commit immediately. Autocommit is useful for DDL operations (CREATE TABLE, ALTER INDEX), read-only workloads, or administrative scripts where transaction grouping isn't needed:

conn = mssql_python.connect(connection_string)
print(conn.autocommit)  # False

cursor = conn.cursor()
cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
conn.commit()  # Required to persist changes

Enable autocommit for each statement to commit immediately. Use autocommit=True at connection time, or toggle it after connecting with setautocommit() or direct property assignment:

# At connection time
conn = mssql_python.connect(connection_string, autocommit=True)

# Or after connection (both forms work)
conn.setautocommit(True)
conn.autocommit = True
print(conn.autocommit)  # True

# Now changes are committed automatically
cursor = conn.cursor()
cursor.execute("SELECT TOP 1 Name FROM Production.Product")
print(cursor.fetchone().Name)
# No commit() needed

Connection timeout

The driver has two independent timeouts. Setting one doesn't affect the other.

Setting	What it bounds	Default
timeout argument to connect()	The authentication attempt, including the network connection. Sets SQL_ATTR_LOGIN_TIMEOUT.	0, which uses the driver default
Connection.timeout property	Each statement that the connection runs.	0, which disables the query timeout

# Bound the authentication attempt (in seconds)
conn = mssql_python.connect(connection_string, timeout=30)

# Bound each statement that this connection runs
conn.timeout = 60

If you set SQL_ATTR_LOGIN_TIMEOUT in attrs_before, that value takes precedence over the timeout argument.

Note

Microsoft Entra ID token acquisition happens before the driver connects, so the authentication timeout doesn't bound it.

If the target is Azure SQL Database serverless with auto-pause enabled, use at least 60. An auto-paused database resumes on the first connection attempt, and a shorter timeout expires before the resume completes. The attempt can also fail with error 40613 while the database resumes, so the application must retry. For more information, see Auto-pause and auto-resume.

Connection attributes

Use set_attr() to modify connection behavior at runtime. Connection attributes control low-level driver settings like access mode, transaction isolation, and packet size. Most applications don't need to change these attributes, but they're useful for specific scenarios:

• Read-only mode: Prevents accidental writes in reporting queries.
• Transaction isolation: Controls how concurrent transactions interact (use SERIALIZABLE for strict consistency, READ_COMMITTED for general use).
• Packet size: Tune for high-latency or high-throughput networks.

import mssql_python

conn = mssql_python.connect(connection_string)

# Set read-only mode
conn.set_attr(mssql_python.SQL_ATTR_ACCESS_MODE, mssql_python.SQL_MODE_READ_ONLY)

# Set transaction isolation level
conn.set_attr(mssql_python.SQL_ATTR_TXN_ISOLATION, mssql_python.SQL_TXN_SERIALIZABLE)

Available attributes:

Constant	Description
SQL_ATTR_CONNECTION_TIMEOUT	Connection timeout in seconds.
SQL_ATTR_LOGIN_TIMEOUT	Sign-in timeout in seconds.
SQL_ATTR_PACKET_SIZE	Network packet size.
SQL_ATTR_ACCESS_MODE	Read-only or read-write mode.
SQL_ATTR_TXN_ISOLATION	Transaction isolation level.
SQL_ATTR_CURRENT_CATALOG	Current database name.

Preconnection attributes

Some attributes must be set before the driver establishes the connection (for example, sign-in timeout). Pass them through attrs_before:

conn = mssql_python.connect(
    connection_string,
    attrs_before={
        mssql_python.SQL_ATTR_LOGIN_TIMEOUT: 30,
        mssql_python.SQL_ATTR_CONNECTION_TIMEOUT: 60,
    }
)

Get connection information

Use getinfo() to retrieve driver and server metadata for logging, diagnostics, or adapting behavior based on server capabilities:

conn = mssql_python.connect(connection_string)

# Server information
print(f"Server name: {conn.getinfo(mssql_python.SQL_SERVER_NAME)}")
print(f"Database name: {conn.getinfo(mssql_python.SQL_DATABASE_NAME)}")

# Driver information
print(f"Driver name: {conn.getinfo(mssql_python.SQL_DRIVER_NAME)}")
print(f"Driver version: {conn.getinfo(mssql_python.SQL_DRIVER_VER)}")

Get a list of available info constants:

constants = mssql_python.get_info_constants()
for name, value in constants.items():
    print(f"{name}: {value}")

Search escape character

The searchescape property returns the character used to escape wildcards (% and _) in LIKE patterns. Use it to safely search for literal wildcard characters in user input:

escape = conn.searchescape

# Use in queries with wildcard characters
cursor.execute(
    f"SELECT Name FROM Production.Product WHERE Name LIKE '%{escape}%%' ESCAPE '{escape}'"
)
# Matches names containing literal '%' character

Encoding and decoding

The driver always sends Transact-SQL (T-SQL) statements and str parameters to the server as UTF-16LE and binds text parameters as SQL_C_WCHAR. You can't change the outbound encoding. The setencoding() method records the values you request but doesn't change what the driver sends:

• Calling setencoding() with no arguments restores the defaults.
• A valid request that differs from UTF-16LE with SQL_WCHAR, such as encoding='utf-8' or ctype=mssql_python.SQL_CHAR, emits UserWarning and has no effect. A request that explicitly names UTF-16LE with SQL_WCHAR doesn't warn. The getencoding() method still reports the requested values.

import warnings

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    conn.setencoding(encoding="utf-8")
print(caught[0].category.__name__)  # UserWarning

conn.setencoding()  # Restore the defaults
print(conn.getencoding())  # {'encoding': 'utf-16le', 'ctype': -8}

Configure decoding for text that the server returns. By default, the driver requests char and varchar data as UTF-16LE with SQL_WCHAR, so values decode correctly regardless of the column collation and its code page. Most applications don't need to change the decoding settings. Calling setdecoding() with only the SQL type restores the default:

# Decode char and varchar data as UTF-8 instead of the default
conn.setdecoding(mssql_python.SQL_CHAR, encoding='utf-8')

# Restore the default
conn.setdecoding(mssql_python.SQL_CHAR)

# Get current decoding settings
settings = conn.getdecoding(mssql_python.SQL_CHAR)
print(settings)  # {'encoding': 'utf-16le', 'ctype': -8}

Default encodings:

Direction	SQL type	Default encoding	Change with
Outbound (str)	SQL_WCHAR	utf-16le	Can't be changed
Inbound	SQL_CHAR	utf-16le	setdecoding()
Inbound	SQL_WCHAR	utf-16le	setdecoding()
Inbound	SQL_WMETADATA	utf-16le	setdecoding()

Best practices

• Use context managers (with blocks) for all connections in application code. They guarantee cleanup even when exceptions occur.
• Use connection pooling for better performance (enabled by default). See Connection pooling.
• Set appropriate timeouts for your network environment. A 30-second timeout suits most cloud deployments; increase it for cross-region or VPN connections. Use at least 60 for Azure SQL Database serverless with auto-pause enabled, because an auto-paused database resumes on the first connection attempt.
• Set MultiSubnetFailover=yes in the connection string when the target is Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, an availability group listener, or a failover cluster instance. It's safe on single-IP targets, so leave it on for all Microsoft SQL family TCP endpoints.
• Use autocommit=False (the default) for data modification scenarios where you need transactional atomicity.
• Use autocommit=True for DDL operations, read-only queries, and admin scripts.
• Don't share connections across threads. The driver's thread safety level is 1 (threads can share the module but not connections).

Related content

• Connection strings for mssql-python
• Connection pooling with mssql-python
• Transaction management with mssql-python
• Manage cursors and result sets