Connection

Connection Class

The connect() function simplifies creating a connection to SQL Server by returning a Connection object. Internally, it initializes and configures connection handle, processes the provided connection string, and attempts to establish a session with the target database.

The Connection Class itself supports fundamental DB-API 2.0 features, you can create cursors to run SQL statements, control transactional behavior via methods like commit and rollback, and close the connection once you’re done. By default, autocommit mode is set to True, meaning all statements are committed immediately—if you need explicit transactional control, simply set setautocommit(False) and invoke commit or rollback as needed.

The connection string traditionally indicates the database server, the specific database to connect to, driver settings, and security details (e.g., Trusted Connection). You can include a port number in server port syntax. If only a server name is included, port 1433 is assumed.

Following are the methods and attributes exposed through Connection Class:

Connection Functions

connect()

Creates a new Connection object.

from mssql_python import connect

conn_str = "Server=<your_server_name>,<port>;Database=<your_db_name>;Trusted_Connection=yes;"
conn = connect(conn_str)

cursor()

Creates and returns a cursor object for executing SQL commands.

cursor = conn.cursor()
cursor.execute("SELECT * FROM T1")
rows = cursor.fetchall()

commit()

Commits the current transaction. Only necessary if autocommit is off.

conn.commit()

rollback()

Rolls back the current transaction. Only necessary if autocommit is off.

conn.rollback()

close()

Closes the connection, freeing any resources. No further operations can be performed afterward.

conn.close()

setencoding()

conn.setencoding(encoding=None, ctype=None)

Sets the text encoding for SQL statements and text parameters.

Parameters:

• encoding: Python encoding used to convert text to bytes before sending to the database. Default: "utf-16le" for Unicode text.
• ctype: C data type for text values:
  • mssql_python.SQL_CHAR
  • mssql_python.SQL_WCHAR (default for UTF-16 encodings)

Example:

# Use UTF-8 for all text communication
conn.setencoding(encoding='utf-8')

Defaults:

Type	Encoding	CType
str	utf-16le	SQL_WCHAR

setdecoding()

conn.setdecoding(sqltype, encoding=None, ctype=None)

Sets the text decoding when reading SQL_CHAR or SQL_WCHAR data from the database.

Parameters:

• sqltype: One of
  • mssql_python.SQL_CHAR
  • mssql_python.SQL_WCHAR
  • mssql_python.SQL_WMETADATA (for column name metadata)
• encoding: Python encoding to decode database text into Python str
• ctype: C data type for SQLGetData.

Example:

# Decode all CHAR/WCHAR data using UTF-8
conn.setdecoding(mssql_python.SQL_CHAR, encoding='utf-8')
conn.setdecoding(mssql_python.SQL_WCHAR, encoding='utf-8')

Defaults:

SQL Type	Encoding	CType
SQL_CHAR	utf-8	SQL_CHAR
SQL_WCHAR	utf-16le	SQL_WCHAR
SQL_WMETADATA	utf-16le	SQL_WCHAR

execute(sql, *args)

This function is not part of the Python DB API.

Creates a new Cursor object, executes the given SQL statement, and returns the cursor. This method is intended for convenient one-off queries where explicit cursor management is unnecessary.

Cursors created by execute are tracked internally and automatically cleaned up when they go out of scope, but for long-running applications or loops it is recommended to explicitly call cursor.close() to release resources promptly.

Example:

import mssql_python

conn = mssql_python.connect(connection_string)

# One-off query with automatic cleanup
row = conn.execute("SELECT name FROM users WHERE id = ?", 123).fetchone()
print("User name:", row[0])

# Explicit resource management for large result sets
cursor = conn.execute("SELECT * FROM large_table")
try:
    rows = cursor.fetchall()
    for row in rows:
        print(row)
finally:
    cursor.close()  # Explicitly close the cursor

Performance & Resource Management Notes

• Each call to execute creates a new cursor. For workloads involving many queries, this may add unnecessary overhead.
• Prefer execute for quick, single queries (like fetching one row).
• For batch operations or repeated queries in loops, use an explicitly managed cursor or batch_execute.

batch_execute(statements, params, reuse_cursor, auto_close)

This function is not part of the Python DB API.

Executes multiple SQL statements efficiently using a single cursor. It supports parameterized queries, optional cursor reuse, and automatic cursor cleanup.

This is useful for:

• Executing a sequence of statements without repeatedly creating/destroying cursors
• Batch inserts, updates, or deletes
• Combining multiple queries in a single operation

Arguments:

• statements (list[str]) – SQL statements to execute.
• params (list, optional) – Parameter sets corresponding to each statement. Must be the same length as statements.
• reuse_cursor (Cursor, optional) – Use an existing cursor instead of creating a new one.
• auto_close (bool) – Whether to automatically close the cursor after execution (only applies if a new cursor was created).

Returns:

• A tuple (results, cursor) where:
• results is a list of execution results (rowcount for non-SELECT, or fetched rows for queries returning data).
• cursor is the cursor used (can be reused if needed).

Example – multiple statements:

import mssql_python as mssql

conn = mssql.connect("connection_string")

# Execute multiple statements with parameters
results, _ = conn.batch_execute(
    [
        "INSERT INTO users VALUES (?, ?)",
        "UPDATE stats SET count = count + 1",
        "SELECT * FROM users"
    ],
    [
        (1, "user1"),   # Parameters for INSERT
        None,           # No parameters for UPDATE
        None            # No parameters for SELECT
    ]
)

print("Inserted rowcount:", results[0])  # e.g. 1
print("Updated rowcount:", results[1])  # e.g. 1
print("Users:", results[2])             # List of rows from SELECT

Example – cursor reuse:

# Reuse the same cursor for multiple batches
my_cursor = conn.cursor()
results, _ = conn.batch_execute(
    ["SELECT * FROM table1", "SELECT * FROM table2"],
    reuse_cursor=my_cursor
)

# Cursor remains open
my_cursor.execute("SELECT * FROM table3")

Performance & Resource Management Notes

• batch_execute is more efficient than calling execute multiple times, since it avoids creating a new cursor for each statement.
• Use reuse_cursor=True to keep the cursor alive across batches when executing multiple groups of queries.
• Use auto_close=True for fire-and-forget workloads where you don’t need to reuse the cursor.
• For heavy transactional workloads (e.g., inserting thousands of rows), consider combining batch_execute with parameterized queries for maximum efficiency.

Output Converters

These functions are not part of the official Python DB API. They allow you to customize how values returned from SQL Server are converted into Python objects.

When you fetch results from a query, values are usually returned in default Python types (e.g., str, int, bytes). With output converters, you can intercept values of specific SQL types and transform them into more useful Python representations.

⚠️ Warning: Registering an output converter will cause the supplied Python function to be executed on every matching database value. Do not register converters from untrusted sources, as this can result in arbitrary code execution and security vulnerabilities.

add_output_converter(sqltype, func)

Registers a converter function for a given SQL type. Whenever a column of this SQL type is read from the database, the function will be invoked to transform its raw value.

Parameters

• sqltype: The integer value representing the SQL type. You can use standard constants (e.g., SQL_VARCHAR, SQL_BINARY) or SQL Server–specific codes.
• func: A callable that accepts a single parameter (the raw database value).

If the value is NULL, the parameter will be None. Otherwise, the parameter will be a bytes object. The function must return the converted Python object.

Example

import mssql_python

def to_str(value: bytes):
    return value.decode('utf-8') if value is not None else None

conn = mssql_python.connect(conn_str)
conn.add_output_converter(mssql_python.SQL_VARCHAR, to_str)

cursor = conn.cursor()
cursor.execute("SELECT CAST('hello' AS VARCHAR(10))")
print(cursor.fetchone()[0])  # Output: 'hello'

get_output_converter(sqltype)

Returns the currently registered converter function for a given SQL type. If no converter is registered, returns None.

Example

prev_converter = conn.get_output_converter(mssql_python.SQL_VARCHAR)
print(prev_converter)  # <function to_str at 0x...>

# Temporarily replace with a new converter
conn.add_output_converter(mssql_python.SQL_VARCHAR, lambda v: v.upper().decode('utf-8'))

remove_output_converter(sqltype)

Unregisters a converter function for a specific SQL type. Subsequent reads for this type will use the default conversion.

Example

conn.remove_output_converter(mssql_python.SQL_VARCHAR)

cursor.execute("SELECT CAST('world' AS VARCHAR(10))")
print(cursor.fetchone()[0])  # Default behavior: returns b'world'

clear_output_converters()

Removes all registered output converters from the connection. Useful if you want to reset behavior and start fresh.

Example

conn.clear_output_converters()

# All converters removed → values returned in their default form
cursor.execute("SELECT CAST('reset' AS VARCHAR(10))")
print(cursor.fetchone()[0])  # b'reset'

Connection Attribute

Autocommit

The autocommit is a read_only attribute which determines whether SQL statements are committed to the database automatically or only when explicitly requested. By default, autocommit is set to True, meaning any changes made (such as INSERT, UPDATE, or DELETE commands) are immediately committed and cannot be rolled back.

The setautocommit() function enables or disables autocommit mode for the current connection.

Behavior:

• When autocommit is True, each DML statement (INSERT, UPDATE, DELETE) completes as soon as it is executed.
• When autocommit is False, all changes remain in a temporary state until commit is called. If an error occurs, you can roll back pending changes.
• You can switch autocommit mode at any time by calling setautocommit(True) or setautocommit(False).
• You can check the current mode via the autocommit property; it returns True if autocommit is enabled or False if disabled.

from mssql_python import connect

# By default, autocommit is True
conn = connect("Server=<your_server_name>,<port>;Database=<your_db_name>;Trusted_Connection=yes;")
# Prints True since autocommit is enabled
print("Autocommit:", conn.autocommit)

# Disable autocommit
conn.setautocommit(False)
cursor = conn.cursor()
cursor.execute("INSERT INTO T1 (col_1) VALUES (?)", "1234")
# Changes remain uncommitted until explicitly committed
conn.commit()

searchescape

This property is not part of the Python DB API. Returns the ODBC search pattern escape character as provided by SQLGetInfo(SQL_SEARCH_PATTERN_ESCAPE). The escape character is used to treat special pattern characters (such as % and _) literally when writing LIKE queries. Different drivers may use different escape characters (commonly \).

Example – escaping %:

import mssql_python

conn = mssql_python.connect(connection_string)
escape = conn.searchescape

cursor = conn.cursor()
cursor.execute("CREATE TABLE #test (id INT, text VARCHAR(50))")
cursor.execute("INSERT INTO #test VALUES (1, 'abc%def'), (2, 'abcdef')")

# Use ESCAPE to treat '%' literally
query = f"SELECT * FROM #test WHERE text LIKE 'abc{escape}%def' ESCAPE '{escape}'"
cursor.execute(query)
rows = cursor.fetchall()

print(rows)  # Only matches 'abc%def'

Notes

• The value is driver-specific and may not always be a string.
• Cached at the connection level for efficiency — repeated calls return the same value.
• Useful when querying text data that contains wildcard characters.

timeout

This property is not part of the Python DB API. Specifies the timeout (in seconds) for SQL query execution. The default is 0, which disables query timeouts. Unlike connection timeouts (set during connect()), this applies only to SQL queries run on cursors created from this connection. If a query exceeds the timeout, the driver will raise an OperationalError (usually with SQLSTATE HYT00 or HYT01).

Example – setting timeout:

import mssql_python

# Set timeout in connection constructor
conn = mssql_python.connect("connection_string", timeout=30)
print(conn.timeout)  # 30

# Change timeout dynamically
conn.timeout = 10

cursor = conn.cursor()
try:
    # Query that might run longer than 10 seconds
    cursor.execute("WAITFOR DELAY '00:00:15'")
    cursor.fetchall()
except mssql.OperationalError as e:
    print("Query timed out:", e)

Notes

• Setting timeout = 0 disables query timeouts (default).
• Affects all cursors created from the connection (cannot be set per cursor).
• Values must be non-negative integers:
• Raises ValueError if negative.
• Raises TypeError if non-integer.
• For connection timeouts, use the timeout argument in mssql_python.connect().

Context Manager Support

Connection objects now support the Python context manager syntax (with statement). Using with on a Connection object ensures:

• Automatic commit if everything succeeds.
• Automatic rollback if an exception occurs.
• The connection is always closed when leaving the block.

from mssql_python import connect

with connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    # If no exception → commit happens automatically
    # If exception → rollback happens automatically
# Connection is closed automatically here

This is roughly equivalent to:

conn= connect(connection_string)
try:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    if not conn.autocommit:
        conn.commit()
except:
    if not conn.autocommit:
        conn.rollback()
    raise
finally:
    conn.close()

How It Behaves

1. Entering the block

• A connection is opened and assigned to conn.
• Any operations inside the block run using this connection.

2. When the block exits:

• No Exception:
  • If autocommit = False, all transactions since the last commit are automatically committed.
  • If autocommit = True, no commit happens because every statement is already committed automatically.
• Exception Raised:
  • If autocommit = False, all uncommitted changes are rolled back automatically.
  • The exception propagates upward unless you handle it.

3. Connection Cleanup:

• The connection is always closed after the block exits — even if an error occurs.
• This prevents resource leaks (open connections that aren’t properly closed).

This makes transaction handling simpler and reduces boilerplate code.

Exception Support

The DB-API 2.0 standard defines a set of exception classes—such as Error, ProgrammingError, DatabaseError, OperationalError, and more—that are essential for robust database error handling.

You can now catch exceptions using a connection instance, Example:

try:
    conn.cursor().execute("…")
except conn.ProgrammingError:
    # handle programming error
    ...

All the standard DB-API 2.0 exceptions are exposed on the connection, including:

• Warning
• Error
• InterfaceError
• DatabaseError
• DataError
• OperationalError
• IntegrityError
• InternalError
• ProgrammingError
• NotSupportedError

Connection Pooling

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on. In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, mssql_python uses an optimization technique called connection pooling.

Now, mssql_python driver provides built-in support for connection pooling, which helps improve performance and scalability by reusing active database connections instead of creating a new connection for every request. This document describes how you can configure and use it effectively.

Default Behavior

Connection pooling is enabled by default when the first connection is created. It uses the following default configuration:

• max_size = 100
• idle_timeout = 600 seconds (10 minutes)

Manually Configuring or Disabling Pooling

You can explicitly enable or disable pooling using the pooling() API before any connections are created.

import mssql_python

# Enabling pooling here with a maximum of 50 connections
# and idle timeout of 300 seconds (5 minutes)
mssql_python.pooling(max_size=50, idle_timeout=300)

To disable pooling explicitly:

import mssql_python

mssql_python.pooling(enabled=False)

• If enabled=False is passed, no pooling will be used — connections will always be created and destroyed per use.
• Pooling must be enabled before establishing a connection. It has no effect if called after connections are already created.

Parameters

• max_size (int) – Maximum number of pooled connections per unique connection string. 
• idle_timeout (int) – Time (in seconds) after which idle connections are evicted from the pool. 
• enabled (bool) – Whether to enable or disable pooling. Defaults to True.

Benefits

• Faster performance for applications making frequent connections.
• Reduced load on the database server.
• Transparent and compatible with the standard DBAPI interface.

Note: Connection pooling in mssql_python works consistently across all SQL Server-based environments, including Azure SQL Database, Azure SQL Managed Instance, and SQL Server (on-premises or in virtual machines (VMs)). The pooling mechanism is entirely client-side and functions identically across these platforms. However, service-specific factors can influence pooling efficiency: Azure SQL Database enforces connection limits based on the selected service tier (e.g., Basic, Standard, Premium), while Azure SQL Managed Instance ties connection limits to the instance's allocated resources, such as vCores and memory. In contrast, SQL Server on VMs has no enforced limits beyond hardware and licensing constraints, offering the most flexibility