Quickstart: Rapid prototyping with the mssql-python driver for Python

In this quickstart, you use Streamlit to create a report quickly, so you can gather user feedback early. You use the mssql-python driver for Python to connect to your database and read the data loaded into your report.

The mssql-python driver 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.10 or later.

• If you don't already have Python, install the Python runtime and pip package manager from python.org.

• Don't want to use your own environment? Follow Container and local development to create a reproducible devcontainer or GitHub Codespaces environment.

• Visual Studio Code with the following extensions:

  • Python extension for Visual Studio Code

• Azure Command-Line Interface (CLI) for passwordless authentication on macOS and Linux.

• If you don't already have uv, follow the installation instructions.

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

Install operating system prerequisites

Install one-time operating system specific prerequisites. Windows users can skip this step. For full platform details, see Install mssql-python.

Alpine

apk add libtool krb5-libs krb5-dev libstdc++

Debian/Ubuntu

apt-get install -y libltdl7 libkrb5-3 libgssapi-krb5-2

RHEL

dnf install -y libtool-ltdl krb5-libs

SUSE

zypper install -y libltdl7 libkrb5-3 libgssapi-krb5-2

openSUSE

zypper install -y libltdl7

macOS

brew install openssl

Create a SQL database

Create or connect to a SQL database on one of the following platforms:

Azure SQL Database

Create a SQL database in minutes using the Azure portal

SQL database in Fabric

Load AdventureWorks sample data in your SQL database in Microsoft Fabric

Microsoft SQL Server

Install SQL Server from the Installation Wizard

AdventureWorks sample databases

SQL Server container

Create a SQL Server container in Docker

Create a SQL Server container with sqlcmd

Create a SQL Server container with the MSSQL extension for VS Code

Create the project and run the code

• Create a new project
• Add dependencies
• Launch Visual Studio Code
• Update pyproject.toml
• Update main.py
• Save the connection string
• Use uv run to execute the script

Create a new project

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

2. Create a new project with uv.

   uv init rapid-prototyping-qs
   cd rapid-prototyping-qs
   

Add dependencies

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

uv add mssql-python python-dotenv streamlit

Launch Visual Studio Code

In the same directory, run the following command.

code .

Update pyproject.toml

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

2. Update the description value.

   description = "A quick example of rapid prototyping using the mssql-python driver and Streamlit."
   

3. 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 rapid-prototyping-qs!")
   
   if __name__ == "__main__":
       main()
   

2. At the top of the file, add the following imports before 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
   import pandas as pd
   import streamlit as st
   

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

   def page_load() -> None:
       st.set_page_config(
           page_title="View Data",
           page_icon=":bar_chart:",
           layout="wide",
           initial_sidebar_state="expanded"
       )
   
       st.title("AdventureWorksLT Customer Order History")
   
       SQL_QUERY = """
       SELECT c.*
       FROM SalesLT.Customer AS c
       INNER JOIN SalesLT.SalesOrderHeader AS soh
           ON c.CustomerID = soh.CustomerID;
       """
   
       df = load_data(SQL_QUERY)
   
       event = st.dataframe(
           df,
           use_container_width=True,
           hide_index=True,
           on_select="rerun",
           selection_mode="single-row"
       )
   
       customer = event.selection.rows
   
       SPEND_QUERY_ALL = """
       SELECT
           soh.OrderDate,
           SUM(sod.OrderQty),
           SUM(sod.OrderQty * sod.UnitPrice) AS spend,
           pc.Name AS ProductCategory
       FROM SalesLT.SalesOrderDetail AS sod
       INNER JOIN SalesLT.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
       INNER JOIN SalesLT.Product AS p
           ON sod.ProductID = p.ProductID
       INNER JOIN SalesLT.ProductCategory AS pc
           ON p.ProductCategoryID = pc.ProductCategoryID
       GROUP BY soh.OrderDate, pc.Name
       ORDER BY soh.OrderDate, pc.Name;
       """
       SPEND_QUERY_CUSTOMER = """
       SELECT
           soh.OrderDate,
           SUM(sod.OrderQty),
           SUM(sod.OrderQty * sod.UnitPrice) AS spend,
           pc.Name AS ProductCategory
       FROM SalesLT.SalesOrderDetail AS sod
       INNER JOIN SalesLT.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
       INNER JOIN SalesLT.Product AS p
           ON sod.ProductID = p.ProductID
       INNER JOIN SalesLT.ProductCategory AS pc
           ON p.ProductCategoryID = pc.ProductCategoryID
       WHERE soh.CustomerID = ?
       GROUP BY soh.OrderDate, pc.Name
       ORDER BY soh.OrderDate, pc.Name;
       """
       if len(customer) == 0:
           spend_df = load_data(SPEND_QUERY_ALL)
       else:
           customer_id = int(df.loc[customer, 'CustomerID'].values[0])
           spend_df = load_data(SPEND_QUERY_CUSTOMER, params=(customer_id,))
   
       st.write("Here's a summary of spend by product category over time:")
       st.bar_chart(spend_df.set_index('ProductCategory')['spend'], use_container_width=True)
   
       if len(customer) > 0:
           st.write(f"Displaying orders for Customer ID: {df.loc[customer, 'CustomerID'].values[0]}")
           customer_id = int(df.loc[customer, 'CustomerID'].values[0])
           SQL_QUERY = """
           SELECT *
           FROM SalesLT.SalesOrderHeader AS soh
           WHERE soh.CustomerID = ?;
           """
           st.dataframe(load_data(SQL_QUERY, params=(customer_id,)), hide_index=True, use_container_width=True)
           SQL_QUERY = """
           SELECT sod.*
           FROM SalesLT.SalesOrderHeader AS soh
           INNER JOIN SalesLT.SalesOrderDetail AS sod
               ON soh.SalesOrderID = sod.SalesOrderID
           WHERE soh.CustomerID = ?;
           """
           st.dataframe(load_data(SQL_QUERY, params=(customer_id,)), hide_index=True, use_container_width=True)
   

4. Between the imports and def page_load() -> None:, add this code.

   _connection = None
   
   def get_connection() -> Connection:
       global _connection
       if not _connection:
           load_dotenv()
           _connection = connect(getenv("SQL_CONNECTION_STRING"))
       return _connection
   
   @st.cache_data
   def load_data(SQL_QUERY, params=None) -> pd.DataFrame:
       data = pd.read_sql_query(SQL_QUERY, get_connection(), params=params)
       return data
   

5. Find this code.

   def main():
       print("Hello from rapid-prototyping-qs!")
   

6. Replace it with this code.

   def main() -> None:
       page_load()
       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 the following example.

   # Python-generated files
   __pycache__/
   *.py[oc]
   build/
   dist/
   wheels/
   *.egg-info
   
   # Virtual environments
   .venv
   
   # Connection strings and secrets
   .env
   

2. Save and close .gitignore.

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

4. Within the .env file, add an entry for your connection string named SQL_CONNECTION_STRING. Replace the placeholders with your actual values.

   SQL_CONNECTION_STRING="Server=<server>;Database=<database>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive"
   

   The connection string depends on the type of SQL database you're connecting to. For Azure SQL Database or SQL database in Fabric, use the ODBC connection string from the connection strings tab, and adjust the authentication type for your scenario. For more information about connection string syntax, see Connection strings for mssql-python.

   Tip

   On macOS, both ActiveDirectoryInteractive and ActiveDirectoryDefault work for Microsoft Entra authentication. ActiveDirectoryInteractive prompts you to sign in every time you run the script. To avoid repeated sign-in prompts, sign in once with the Azure CLI az login command, and use ActiveDirectoryDefault. ActiveDirectoryDefault reuses the cached credential.

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 streamlit run main.py
   

   Your report opens in a new tab in your web browser.

2. Try your report to see how it works. If you change anything, save main.py and use the reload option in the upper right corner of the browser window.

3. To share your prototype, copy all files except for the .venv folder to the other machine. The .venv folder is recreated with the first run.

Related content

• Quickstart: Repeatable deployments with the mssql-python driver for Python
• Build connection strings programmatically
• Execute queries with mssql-python