Edit

Share via

Quickstart: Repeatable deployments with the mssql-python driver for Python

In this quickstart, you use uv to manage project dependencies and environments for Python script that connects to a database that you created and loaded with sample data. You use the mssql-python driver for Python to connect to your database and perform basic operations, like reading and writing data.

The mssql-python doesn't require any external dependencies on Windows machines. The driver installs everything that it needs with a single pip install, allowing you to use the latest version of the driver for new scripts without breaking other scripts that you don't have time to upgrade and test.

mssql-python documentation | mssql-python source code | Package (PyPi) | UV

Prerequisites

  • Python 3

    • If you don't already have Python, install the Python runtime and Python Package Index (PyPI) package manager from python.org.

    • Prefer to not use your own environment? Open as a devcontainer using GitHub Codespaces.

  • If you don't already have uv, install uv by following the instructions from https://docs.astral.sh/uv/getting-started/installation/.

  • A database on SQL Server, Azure SQL Database, or SQL database in Fabric with the AdventureWorks2022 sample schema and a valid connection string.

  • Install one-time operating system specific prerequisites.

    apk add libtool krb5-libs krb5-dev

Create a SQL database

This quickstart requires the AdventureWorks2022 Lightweight schema, on Microsoft SQL Server, SQL database in Fabric or Azure SQL Database.

Create the project and run the code

Create a new project

  1. Open a command prompt in your development directory. If you don't have one, create a new directory called python, scripts, etc. Avoid folders on your OneDrive, the synchronization can interfere with managing your virtual environment.

  2. Create a new project with uv.

    uv init mssql-python-repeatable-qs
cd mssql-python-repeatable-qs

Add dependencies

In the same directory, install the mssql-python, python-dotenv, and rich packages.

uv add mssql-python python-dotenv rich

Update pyproject.toml

  1. The pyproject.toml contains the metadata for your project. Open the file in your favorite editor.

  2. Review the contents of the file. It should be similar to this example. Note the Python version and dependency for mssql-python uses >= to define a minimum version. If you prefer an exact version, change the >= before the version number to ==. The resolved versions of each package are then stored in the uv.lock. The lockfile ensures that developers working on the project are using consistent package versions. It also ensures that the exact same set of package versions is used when distributing your package to end users. You shouldn't edit the uv.lock file.

    [project]
name = "mssql-python-repeatable-qs"
version = "0.1.0"
description = "Add your description here"
readme = "README.md"
requires-python = ">=3.11"
dependencies = [
    "mssql-python>=0.10.0",
    "python-dotenv>=1.1.1",
    "rich>=14.1.0",
]

  3. Update the description to be more descriptive.

    description = "Connects to a SQL database using mssql-python"

  4. Save and close the file.

Update main.py

  1. Open the file named main.py. It should be similar to this example.

    def main():
    print("Hello from mssql-python-repeatable-qs!")

if __name__ == "__main__":
    main()

  2. At the top of the file, add the following imports above the line with def main().

    Tip

    If Visual Studio Code is having trouble resolving packages, you need to update the interpreter to use the virtual environment.

    from os import getenv
from dotenv import load_dotenv
from mssql_python import connect, Connection, Cursor
from rich.console import Console
from rich.progress import Progress, SpinnerColumn, TextColumn
from rich.table import Table
from argparse import ArgumentParser
from time import sleep

  3. Between the imports and the line with def main(), add the following code.

    def get_results(sleep_time: int = 0) -> None:
 with Progress(
     SpinnerColumn(),
     TextColumn("[progress.description]{task.description}"),
     transient=True,
 ) as progress:
     task = progress.add_task(
         description="Connecting to SQL...")

     cursor = query_sql()

     # Simulate a slow connection for demo purposes
     sleep(sleep_time)

     progress.update(task, description="Formatting results...")

     table = Table(title="Orders by Customer")
     # https://rich.readthedocs.io/en/stable/appendix/colors.html
     table.add_column("Customer ID", style="bright_blue", justify="center")
     table.add_column("Company Name", style="bright_white", justify="left")
     table.add_column("Order Count", style="bold green", justify="right")

     records = cursor.fetchall()
     for r in records:
         table.add_row(f"{r.CustomerID}",
                       f"{r.CompanyName}", f"{r.OrderCount}")

     if cursor:
         cursor.close()

     # Simulate a slow connection for demo purposes
     sleep(sleep_time)

     progress.stop()

     Console().print(table)

  4. Between the imports and def get_results(sleep_time: int = 0) -> None:, add this code.

    _connection = None

def get_connection() -> Connection:
   global _connection
   if not _connection:
       load_dotenv()
       _connection = connect(getenv("SQL_CONNECTION_STRING"))  # type: ignore
   return _connection

def query_sql() -> Cursor:

   SQL_QUERY = """
     SELECT TOP 5
     c.CustomerID,
     c.CompanyName,
     COUNT(soh.SalesOrderID) AS OrderCount
     FROM
     SalesLT.Customer AS c
     LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh
     ON c.CustomerID = soh.CustomerID
     GROUP BY
     c.CustomerID,
     c.CompanyName
     ORDER BY
     OrderCount DESC;
   """

   conn = get_connection()
   cursor = conn.cursor()
   cursor.execute(SQL_QUERY)
   return cursor

  5. Find this code.

    def main():
    print("Hello from test!")

  6. Replace it with this code.

    def main() -> None:
   parser = ArgumentParser()
   parser.add_argument("--sleep-time", type=int, default=0,
                       help="Time to sleep in seconds to simulate slow connection")
   args = parser.parse_args()

   if args.sleep_time > 0:
       get_results(args.sleep_time)
   else:
       get_results()

   if _connection:
       _connection.close()

  7. Save and close main.py.

Save the connection string

  1. Open the .gitignore file and add an exclusion for .env files. Your file should be similar to this example. Be sure to save and close it when you're done.

    # Python-generated files
__pycache__/
*.py[oc]
build/
dist/
wheels/
*.egg-info

# Virtual environments
.venv

# Connection strings and secrets
.env

  2. In the current directory, create a new file named .env.

  3. Within the .env file, add an entry for your connection string named SQL_CONNECTION_STRING. Replace the example here with your actual connection string value.

    SQL_CONNECTION_STRING="Server=<server_name>;Database={<database_name>};Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive"

    Tip

    The connection string used here largely depends on the type of SQL database you're connecting to. If you're connecting to an Azure SQL Database or a SQL database in Fabric, use the ODBC connection string from the connection strings tab. You might need to adjust the authentication type depending on your scenario. For more information on connection strings and their syntax, see connection string syntax reference.

Use uv run to execute the script

  1. In the terminal window from before, or a new terminal window open to the same directory, run the following command.

     uv run main.py

  2. Now let's run it again but more slowly to be able to see both status updates.

     uv run main.py --sleep-time 5

    Here's the expected output when the script completes.

                             Orders by Customer
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ Customer ID ┃ Company Name                   ┃ Order Count ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│    29485    │ Professional Sales and Service │           1 │
│    29531    │ Remarkable Bike Store          │           1 │
│    29546    │ Bulk Discount Store            │           1 │
│    29568    │ Coalition Bike Company         │           1 │
│    29584    │ Futuristic Bikes               │           1 │
└─────────────┴────────────────────────────────┴─────────────┘

  3. To deploy your script to another machine, copy all files except for the .venv folder to the other machine. The virtual environment is recreated with the first run.

Next step

Visit the mssql-python driver GitHub repository for more examples, to contribute ideas or report issues.

mssql-python driver on GitHub