Theconnect() 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 likecommit androllback, and close the connection once you’re done. By default,autocommit mode is set toTrue, meaning all statements are committed immediately—if you need explicit transactional control, simply setsetautocommit(False) and invokecommit orrollback 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).

Following are the methods and attributes exposed throughConnection Class:

Creates a new Connection object.

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

Creates and returns a cursor object for executing SQL commands.

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

Commits the current transaction. Only necessary ifautocommit is off.

conn.commit()

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

conn.rollback()

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

conn.close()

Theautocommit 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.

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

Behavior:

• Whenautocommit is True, each DML statement (INSERT, UPDATE, DELETE) completes as soon as it is executed.
• Whenautocommit 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 callingsetautocommit(True) orsetautocommit(False).
• You can check the current mode via the autocommit property; it returns True if autocommit is enabled or False if disabled.

frommssql_pythonimportconnect# By default, autocommit is Trueconn=connect("Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;")# Prints True since autocommit is enabledprint("Autocommit:",conn.autocommit)# Disable autocommitconn.setautocommit(False)cursor=conn.cursor()cursor.execute("INSERT INTO T1 (col_1) VALUES (?)","1234")# Changes remain uncommitted until explicitly committedconn.commit()

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 calledconnection pooling.

Now,mssql_python driver provides built-in support forconnection 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 canconfigure and use it effectively.

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

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

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

importmssql_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)

Todisable pooling explicitly:

importmssql_pythonmssql_python.pooling(enabled=False)

• Ifenabled=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.

• 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.

• 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