Forum Discussion

chapperscobbler's avatar
chapperscobbler
New Member
1 year ago
Solved

Using Python + StreamLit to connect to a Fabric SQL database/lakehouse

Hi all,   Has anyone setup a Python Streamlit App, to run locally on their laptop, and connect to a MS Fabric database ?  Looking to do this as POC, as we may be looking to utilise Streamlit furthe...
Data Science
  • chapperscobbler's avatar
    chapperscobbler
    11 months ago

    Hi v-sdhruv  , yes thanks for your help, have now connected to Fabric via Streamlit.

anilgavhane's avatar
anilgavhane
Icon for Super User rankSuper User
11 months ago

chapperscobbler 

Yes, you can connect a Python + Streamlit app to a Microsoft Fabric SQL database using ODBC and pyodbc, but authentication must be handled via Azure AD with either a user identity or service principal. The key is configuring your connection string and token acquisition correctly.

Steps to Connect Streamlit to Fabric SQL Database

1. Prerequisites

  • Fabric workspace with SQL database or Lakehouse (with SQL endpoint enabled)
  • Azure AD tenant with access to the Fabric workspace
  • Python, Streamlit, and ODBC driver installed locally
  • Service Principal or user credentials with appropriate permissions

 

Authentication Options

Option A: User Identity (Interactive Login)

Use Azure Identity libraries to acquire a token interactively:

 

from azure.identity import InteractiveBrowserCredential import pyodbc credential = InteractiveBrowserCredential() token = credential.get_token("https://database.windows.net/.default").token conn_str = ( "Driver={ODBC Driver 18 for SQL Server};" "Server=tcp:.database.windows.net;" "Database=;" "Authentication=ActiveDirectoryAccessToken;" ) conn = pyodbc.connect(conn_str, attrs_before={1256: token})

 

Option B: Service Principal (Recommended for Production)

Use ClientSecretCredential to authenticate with a service principal:

 

from azure.identity import ClientSecretCredential import pyodbc tenant_id = "" client_id = "" client_secret = "" credential = ClientSecretCredential(tenant_id, client_id, client_secret) token = credential.get_token("https://database.windows.net/.default").token conn_str = ( "Driver={ODBC Driver 18 for SQL Server};" "Server=tcp:.database.windows.net;" "Database=;" "Authentication=ActiveDirectoryAccessToken;" ) conn = pyodbc.connect(conn_str, attrs_before={1256: token})

 

 

Streamlit Integration

Once connected, you can use Pandas to query and display data:

 

import pandas as pd import streamlit as st query = "SELECT TOP 10 * FROM SalesLT.Product" df = pd.read_sql(query, conn) st.dataframe(df)

 

 

Troubleshooting Tips

  • Ensure the ODBC Driver 18 for SQL Server is installed.
  • Your Fabric SQL endpoint must be accessible externally (check firewall settings).
  • If using service principal, it must have Viewer or higher access to the Fabric workspace.
  • Store secrets securely using .streamlit/secrets.toml or environment variables.