Skip to content

python-sql-driver-mssql-python-rapid-prototyping-quickstart.md

Latest commit

 

History

History
316 lines (217 loc) · 12.2 KB

python-sql-driver-mssql-python-rapid-prototyping-quickstart.md

File metadata and controls

316 lines (217 loc) · 12.2 KB
title Quickstart: Python SQL Driver - mssql-python Rapid Prototyping with the Python Driver for SQL Server
description This quickstart describes creating prototypes reports quickly using mssql-python.
author dlevy-msft-sql
ms.author dlevy
ms.reviewer vanto, randolphwest
ms.date 12/29/2025
ms.service sql
ms.subservice connectivity
ms.topic quickstart-sdk
ms.custom
sfi-ropc-nochange
ignite-2025

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

In this quickstart, you use Streamlit to quickly create a report, allowing you to quickly gather user feedback to ensure you're on the right track. 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

Create a SQL database

This quickstart requires the [!INCLUDE sssampledbnormal-md] Lightweight schema, on Microsoft SQL Server, SQL database in Fabric or Azure SQL Database.

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

AdventureWorks sample databases

Create a new local copy of a database in a container with sqlcmd

Create a new SQL Server container using the MSSQL extension for Visual Studio Code

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 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. The pyproject.toml contains the metadata for your project. Open the file in your favorite editor.

  2. Update the description to be more descriptive.

    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-protyping-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
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] c inner join SalesLT.SalesOrderHeader 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 sod inner join SalesLT.SalesOrderHeader soh on sod.salesorderid = soh.salesorderid inner join SalesLT.Product p on sod.productid = p.productid inner join SalesLT.ProductCategory 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 sod inner join SalesLT.SalesOrderHeader soh on sod.salesorderid = soh.salesorderid inner join SalesLT.Product p on sod.productid = p.productid inner join SalesLT.ProductCategory 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] 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] soh INNER JOIN SalesLT.SalesOrderDetail sod on soh.SalesOrderId = sod.SalesOrderId WHERE 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-protyping-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 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

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 via the Azure CLI by running az login, then use ActiveDirectoryDefault, which reuses the cached credential.

  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

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

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

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

Next step

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

[!div class="nextstepaction"] mssql-python driver on GitHub