Using Streamlit with Microsoft Fabric

Connect to SQL endpoint using pyodbc and pandas

4 min readJan 22, 2024

In this blog, I will show an example Streamlit application that connects to Fabric SQL endpoint using pyodbc and executes some queries with pandas.

> For a quick look at the code, go straight to the GitHub repository.

Prerequisites

You’ll need the following:

Create a Service Principal on Azure.
Create a Fabric workspace and a Fabric lakehouse if you don’t have any. You can create both using the Fabric API too.
Give (Viewer) access to the Service Principal within the Fabric workspace.
ODBC driver, Python and Streamlit on your computer.

Here’s an example of how to create a Fabric lakehouse through the API. Refer to the notebook for instructions on creating a workspace/lakehouse.

import requests

url = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/items"

headers = {
    "Authorization": "Bearer " + mssparkutils.credentials.getToken("pbi"),
    "Content-Type": "application/json"
}

body = payload

response = requests.post(url, headers=headers, json=body)
print(response.text)

Note: Service Principal authentication with Fabric APIs are not yet supported. So user authentication is required to create workspace/lakehouse.

Creating a lakehouse creates a SQL analytics endpoint (plus a default semantic model), which points to the lakehouse Delta table storage. Once you create a Delta table in the lakehouse, it’s immediately available for querying using the SQL analytics endpoint. To learn more, see Data Warehouse documentation: SQL analytics endpoint.

Preparing the data

For demo purposes, I’m using the Wide World Importers (WWI) dataset. Import this notebook into your Fabric workspace and run the code in the notebook to create managed Delta tables in the Fabric lakehouse. Once all cells are executed, you will see tables created in the Lakehouse Explorer.

Connecting to the SQL endpoint

As mentioned above, when you create a Fabric lakehouse, a default Power BI semantic model and SQL endpoint are created. The lakehouse SQL endpoint is accessible through a Tabular Data Stream (TDS) using the SQL connection string in various ways, such as JDBC, ODBC or dbt, to just name a few. Two types of authentication are supported here: user identities (UPN) and service principals (SPN). In this example, we’ll use ODBC + pyodbc + pandas in Streamlit, employing SPN authentication.

To run the example, follow these steps:

Clone the repository: go to GitHub and clone the repository.
Configure the variables: jump to app.py and set up variables for service_principal_id, service_principal_password, server_name, and database_name. Also, specify the path for the ODBC driver. Keep in mind that these variables should be treated as secrets.

import streamlit as st
import pandas as pd
import pyodbc

# Service principal
service_principal_id = "<client_id>@something.contoso.com" # important to include the fully qualified domain
service_principal_password = "<client_secret>"

# SQL analytics endpoint 
server_name = "<server_name>"

# Lakehouse name
database_name = "<lakehouse_name>"

# Define the ODBC connection string
conn_str = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={server_name};"
    f"DATABASE={database_name};"
    f"UID={service_principal_id};"
    f"PWD={service_principal_password};"
    f"Authentication=ActiveDirectoryServicePrincipal"
)

# Establish the connection
conn = pyodbc.connect(conn_str)

# Test query
table_name = f"{database_name}.[dbo].[wwi_dimension_customer]"
query = f"SELECT COUNT(*) FROM {table_name}"
df = pd.read_sql(query, conn)
st.write(df.head())

Install required dependencies: install Python packages (libraries) with pip. This will install pyodbc and pandas.

pip install -r requirements.txt

Run Streamlit app: navigate to your fabric-streamlit/app folder within your cloned repository and run the Streamlit app.

streamlit run app.py --server.port 8081

Running queries

After setting up the connection, you can execute any SQL query and directly retrieve the results into pandas. In the example below, I run a few queries against the WWI dataset.

For any suggestions or questions, feel free to reach out :)

Note: this is a basic Streamlit example utilizing the Fabric SQL endpoint. This blog post doesn’t delve into query performance, creating sophisticated Streamlit apps, or constructing an ORM layer on top of the SQL endpoint.

References: