Getting Started with mssql-python for Data Developers

Problem

Programmatic interaction with SQL Server or Azure SQL from a Python script is possible using a driver. A popular driver has been pyodbc that can be used standalone or with a SQLAlchemy wrapper. SQLAlchemy on its own is the Python SQL toolkit and Object Relational Mapper for developers. In the end of 2025 Microsoft released v1 of their own Python SQL driver called mssql-python. How do you get started using mssql-python for programmatic access to your SQL Server?

Solution

mssql-python is a Python driver for Microsoft SQL family of databases, supporting both SQL Server on-prem and various Azure SQL Server editions. It uses Direct Database Connectivity (DDBC) that enables direct connections to SQL Server without requiring an external driver manager like ODBC is required for pyodbc. mssql-python supports a full range of database operations, including connection management, query execution, and transaction handling. At the time of writing the latest version of mssql-python is 1.3. The driver is compatible with Python version 3.10 and up.

Key Takeaways

  • mssql-python is a Python driver for SQL Server (Download Now) that simplifies connections and operations without external dependencies like ODBC.
  • To get started, create a project directory, set up a Python virtual environment, and create necessary files for database connection.
  • Define a SQL client class and a query class in Python for better code organization and modularity.
  • The main application combines the client and query classes, handling query execution and output formatting using the rich library.
  • mssql-python offers features like transaction management and stored procedure calls, providing a robust solution for interacting with SQL Server.

Scenario

For this scenario I will use AdventureWorks2025 deployed on SQL Server 2025 express edition on a local machine. To get started with this or another SQL Server 2025 edition follow this link. To import one of the Microsoft sample databases check this document. Importantly, the mssql-python driver does not require any external dependencies on Windows machines. The driver installs everything it needs with a single pip install, saving time and effort.

Environment

Create a project directory and open it in VS Code. Create an empty file requirements.txt and paste the following:

attrs
mssql-python
uv
python-dotenv
rich

Hit Ctrl+SHfit+P and choose Python: Create environment

python create environment

Then choose venv

select .venv

Next choose the global Python interpreter. In my case I have 3.11.4 installed:

choose global python interpreter

Finally select the requirements file so dependencies are installed after the environment is created:

select requirements.txt

Environment variables

Create an .env file in the project folder with the following contents:

# SQL Server 2025
DB_SERVER=".\\SQLEXPRESS2025"
DB_USERNAME=""  
DB_PASSWORD=""
DB_DATABASE="AdventureWorks2025"

We can reuse this file at a later stage in case we need to connect to another instance of SQL Server whether on prem or elsewhere.

SQL Client Class

Let us create the first piece of code -the SQL client class in Python. Instantiating this class will give a connection object to use for running queries. Create a file sql_client.py with the following code:

--MSSQLTips.com Python
01: """
02: MSSQLTips.com General database connection configuration class
03: """
04: import os
05: from attrs import define, field, validators
06: import mssql_python
07: from mssql_python import Connection
08: from dotenv import load_dotenv
09: 
10: load_dotenv()
11: 
12: 
13: @define
14: class RDSConfigMSSQLPython:
15:     DB_SERVER: str = field(
16:         factory=lambda: os.getenv("DB_SERVER", "SQLEXPRESS2025"),
17:         validator=validators.instance_of(str)
18:     )
19:     DB_DATABASE: str = field(
20:         factory=lambda: os.getenv("DB_DATABASE", "AdventureWorks2025"),
21:         validator=validators.instance_of(str)
22:     )
23:     DB_USERNAME: str = field(
24:         factory=lambda: os.getenv("DB_USERNAME", ""),
25:         validator=validators.instance_of(str)
26:     )
27:     DB_PASSWORD: str = field(
28:         factory=lambda: os.getenv("DB_PASSWORD", ""),
29:         validator=validators.instance_of(str)
30:     )
31: 
32:     def get_connection(self) -> Connection:
33:         connection_str = ""
34:         if not self.DB_USERNAME or not self.DB_PASSWORD:
35:             # Windows Authentication
36:             connection_str = f"Server={self.DB_SERVER};\
37:                 Database={self.DB_DATABASE};\
38:                 Trusted_Connection=yes;\
39:                 TrustServerCertificate=yes;"
40:         else:
41:             # SQL Server Authentication
42:             connection_str = f"Server={self.DB_SERVER};\
43:                 Database={self.DB_DATABASE};\
44:                 UID={self.DB_USERNAME};\
45:                 PWD={self.DB_PASSWORD};\
46:                 TrustServerCertificate=yes;"
47: 
48:         return mssql_python.connect(connection_str)
49: 
50: 
51: if __name__ == "__main__":
52:     db_cfg_mssqlpython = RDSConfigMSSQLPython()
53:     engine = db_cfg_mssqlpython.get_connection()
54:     print(engine)

This is what the code does:

  • 04 – 08: define necessary imports, including mssql-python for referring to the Connection type
  • 13 – 30: class definition with the following attributes, which will be loaded from the .env file previously created:
    • DB_SERVER for server name
    • DB_DATABASE for database name
    • DB_USERNAME for user login
    • DB_PASSWORD for password
  • 32 – 48: define a single method for creating a connection object. Note the connect method requires the connection string. UID for username and PWD for password are omitted since we are using windows authentication in this case on local machine.
  • 51 – 54: extra code for so we can easily check the output.
sql client class

Running this module should display the Connection object, thereby showing we are OK to proceed:

mssql-python connection object

If you see this output, it also means you have authenticated successfully to your SQL Server. Other than SQL user name and password login and Windows authentication, mssql-python supports MS Entra ID and Service Principle authentication, enabling developers to securely integrate in production scenarios.

Query Class

Next, we want to define a query class. There are multiple reasons for putting queries in a class container instead of hardcoding them in the main script:

  • Easy reusability – the class can be called from any other part of the program
  • Modularity – more queries can be added at any time
  • Support for future query parametrization – the class can be modified with behavior that alters the query definitions based on parameters coming from elsewhere
  • Code brevity – you just need to import a class and reuse it.

Create a file query.py with the following code:

--MSSQLTips.com Python
01: from attrs import define
02: from typing import ClassVar
03: 
04: 
05: @define
06: class QueryContainer:
07:     TOP_5_COUNTRIES_BY_SALES: ClassVar[str] = """
08:         SELECT TOP 5
09:                CASE st.CountryRegionCode
10:                         WHEN 'US' THEN 'United States'
11:                         WHEN 'CA' THEN 'Canada'
12:                         WHEN 'GB' THEN 'United Kingdom'
13:                         ELSE st.CountryRegionCode
14:                 END AS Country
15:                 , SUM(soh.TotalDue) AS TotalSales
16:                 , CAST(
17:                         100.0 * SUM(soh.TotalDue)
18:                         / SUM(SUM(soh.TotalDue)) OVER ()
19:                         AS DECIMAL(5,2)
20:                 ) AS PercentageShare
21:            FROM Sales.SalesOrderHeader AS soh
22:            JOIN Sales.SalesTerritory AS st
23:              ON soh.TerritoryID = st.TerritoryID
24:           GROUP BY st.CountryRegionCode
25:           ORDER BY TotalSales DESC;
26:           """
27: 
28:     @classmethod
29:     def get_query(cls, key: str | None = None) -> dict[str, str]:
30:         """Retrieves a query or all queries.
31:         Args:
32:             key (str | None, optional): query name. Defaults to None.
33:         Raises:
34:             KeyError: error if the query does not exist.
35:         Returns:
36:             str | dict[str, str]: query definition or dictionary of all queries.
37:         """
38:         queries = {
39:             'Top 5 Sales by Country': cls.TOP_5_COUNTRIES_BY_SALES,
40:         }
41: 
42:         if key is None:
43:             return queries
44: 
45:         if key not in queries:
46:             raise KeyError(f"Query '{key}' not found. Available keys: {list(queries.keys())}")
47: 
48:         return {key: queries[key]}

This is what the class does:

  • 01 – 02: define necessary imports. We use ClassVar to specify class variables (as compared to instance variables). The contained queries will be accessible via a function or calling QueryContainer.<query_name>.
  • 07 – 26: query for fetching top five countries by total sales
  • 28 – 48: simple method for fetching a query. The query name (key) argument is optional; when not provided the function will return all queries defined in the queries dictionary.

Here is what the class looks like:

query container class

Main application

The last point is to put everything together in a single python module. Additionally, we will use the rich Python library to format the query output to the console. Create a file main.py with the following code:

--MSSQLTips.com Python
01: from sql_client import RDSConfigMSSQLPython
02: from query import QueryContainer
03: import mssql_python
04: from rich.console import Console
05: from rich.table import Table
06: 
07: mssql_python.setup_logging(output='stdout')
08: 
09: mssql_python_cfg = RDSConfigMSSQLPython()
10: conn = mssql_python_cfg.get_connection()
11: cursor = conn.cursor()
12: 
13: queries = QueryContainer.get_query()
14: 
15: console = Console()
16: 
17: table = Table(
18:     title="Top 5 Sales by Country",
19:     show_header=True,
20:     header_style="bold cyan"
21: )
22: 
23: table.add_column("Country", style="bold")
24: table.add_column("Total Sales", justify="center")
25: table.add_column("Share", justify="center")
26: 
27: 
28: for key, val in queries.items():
29:     cursor.execute(val)
30:     rows = cursor.fetchall()
31:     for country, total_sales, share in rows:
32:         table.add_row(
33:             country,
34:             f"${total_sales:,.2f}",
35:             f"{share:,.2f} %",
36:         )
37: 
38: console.print(table)
39: conn.close()

Let us break it down:

  • 01 – 05: define necessary imports. sql_client and query are our own custom modules developed earlier.
  • 07: set up logging from the mssql-python module for comprehensive debug logging for troubleshooting
  • 09: create an instance of the configuration class
  • 10: get a mssql-python.Connection
  • 11: create a cursor. The cursor will execute the target SQL statement Cursor’s execute() function. When a SELECT statement returns rows, one of the cursor’s fetch functions can be used to retrieve those records.
  • 13: get queries from our custom module containing the queries
  • 15: instantiate fa rich Console for output formatting
  • 17 – 21: Create a table for displaying the query output
  • 23, 24, 25: add three rows for each expected column from the result set
  • 28 – 36: loop over the queries, where the key is the query name and the value is the SQL query statement
    • 29: execute the query
    • 30: Get all rows using fetchall. Note: for larger result sets we can use fetchmany to retrieve n rows at a time.
    • 31: iterate over the elements of the list contain the result set tuple
    • 32-36: populate the final table.
  • 38: print the result table
  • 39: close the connection explicitly.

This is what the final program looks like:

main program using mssql-pytho and custom modules

Here is the result, where:

  • in blue we see diagnostics and debugging information
  • in green we see the result set visualized
query output

Create project

In the beginning we also specified the uv package in the requirements file. With uv we can create and manage python projects easily. So, to finalize the setup run uv init in the console:

python uv create project

Then run uv add -r requirements.txt. While we do have a requirements file we want the required packages to be correctly reflected as dependencies in the project file:

python uv add dependencies to roject

Now we have toml project definition file with clearly listed dependencies and python version requirement:

toml file overview

Other project files such as a README and .gitignore also appeared because of the uv init command. This setup will be helpful if moving or deploying the project to another machine or scaling it.

Final Thoughts

In this document, we demonstrated using the newly released mssql-python for reading data out of an SQL Server 2025 database. The package offers additional powerful capabilities such as calling stored procedures, managing transactions and others. Stay tuned for more examples on working with mssql-python.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *