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 (SqlPassword, ActiveDirectoryPassword, ActiveDirectoryIntegrated, ActiveDirectoryInteractive, ActiveDirectoryMsi, ActiveDirectoryServicePrincipal). The mssql-python driver uses the same connection string attributes as the ODBC driver. See Using Microsoft Entra ID with the ODBC Driver for more details on authentication.

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

EntraID authentication is now fully supported on MacOS and Linux but with certain limitations as mentioned in the table:

For more information on Entra ID please refer this document

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