mssql-python provides a straightforward interface to create a new connection to a database by calling the connect() function, which returns an instance of the Connection Class. This Connection class manages all aspects of interacting with the database, including establishing a session with SQL Server, controlling transactions by committing or rolling back changes, and closing the connection when finished.

The connection string traditionally indicates the database server, the specific database to connect to, driver settings, and security details (e.g., Trusted Connection). mssql-python make it very simple to give the connection attributes to connect to the server and database.

Here is an example of the connection string that must be defined within the driver to connect to the SQL Server database:

# Using SQLPassword authentication
conn_str = Server=<your_server_name>;Database=<your_database_name>;UID=<your_user_id>;PWD=<your_password>;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes;Authentication=<SqlPassword>;Application Name=<your_application_name_optional>;

# Using EntraID authentication
conn_str = Server=<your_server_name>;Database=<your_database_name>;Uid=your_user_id@your_domain.com;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;Authentication=ActiveDirectoryInteractive;Application Name=<your_application_name_optional>;

In this release, it is recommended to use only the specified attributes as other attributes have not been fully tested:

• Server: Specifies the name or network address of the SQL Server instance to connect to.

• Database: Sets the name of the database to be used after the connection is established.

• UID: The SQL Server login account (User ID).

• PWD: The password for the SQL Server login account specified in the UID parameter.

• Trusted_Connection: When set to "yes", instructs the driver to use Windows Authentication for login validation.

• Encrypt: Determines whether data should be encrypted before sending it over the network. Possible values are "yes", "no", and "strict".

• TrustServerCertificate: When used with Encrypt, enables encryption using a self-signed server certificate without validation.

• Authentication: Sets the authentication mode to use when connecting to SQL Server (e.g., "SqlPassword", "ActiveDirectoryIntegrated").

• Application Name: The name of the application calling SQLDriverConnect.

• Connection Timeout: Specifies the duration (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

NOTE ON CONNECTION POOLING: Currently, the mssql-python driver does not support connection pooling attribute. As a result, a new database connection must be established for each request, which can introduce additional latency, especially in high-throughput applications or environments with frequent short-lived connections. This may impact overall performance and scalability, as establishing a new connection involves authentication, network overhead, and resource allocation on both the client and server sides. To mitigate this, users may consider implementing application-level pooling or leveraging external connection pooling mechanisms where applicable. However, connection pooling will be made available as an attribute to mssql-python drivr in subsequent releases.

Following are the methods and attributes exposed through Connection Class:

Creates a new Connection object.

from mssql_python import connect

conn_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()