{"id":4361,"date":"2025-03-18T12:19:41","date_gmt":"2025-03-18T19:19:41","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=4361"},"modified":"2025-03-18T12:19:41","modified_gmt":"2025-03-18T19:19:41","slug":"build-a-streamlit-application-with-fabric-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/build-a-streamlit-application-with-fabric-sql-database\/","title":{"rendered":"Build a Streamlit application with Fabric SQL Database"},"content":{"rendered":"

Building an application allows you to operationalize the data you have in Microsoft Fabric<\/a>, such that it can be consumed by stakeholders in your organization to generate business insights. \u00a0Streamlit<\/a> is easy to use to build simple applications without needing to know front-end application frameworks like Flask, Django etc. It makes it easier to prototype and visualize your data in Microsoft Fabric with Streamlit framework.<\/p>\n

In this blog post we will show how to develop an app using Streamlit<\/a> and Fabric SQL database<\/a>. Consider a scenario that you are a data engineer in a retail company and want to keep track of your product inventory. With a Streamlit app, you can connect to your Fabric SQL database<\/a> where all the inventory data is stored and then create a really cool app that lets you see which products are running low, which ones are selling fast, and even predict future inventory needs.<\/p>\n

Prerequisites<\/strong><\/h2>\n
    \n
  1. You need an existing Fabric capacity. If you don’t have one,\u00a0start a Fabric trial<\/a>.<\/li>\n
  2. Make sure that you\u00a0Enable SQL database in Fabric using Admin Portal tenant settings<\/a>.<\/li>\n
  3. Install Visual Studio code<\/a>, Python runtime<\/a> and Python extension for VS Code<\/a> on our local machine.<\/li>\n<\/ol>\n

    Create a workspace<\/strong><\/h2>\n
      \n
    1. Sign in to\u00a0Microsoft Fabric <\/a>.<\/li>\n
    2. Select\u00a0Workspaces<\/strong>\u00a0>\u00a0New workspace<\/strong>.<\/li>\n
    3. Fill out the\u00a0Create a workspace.\u00a0<\/strong><\/li>\n
    4. In the Advanced<\/strong> section, choose\u00a0Fabric capacity<\/strong>\u00a0or\u00a0Trial<\/strong>\u00a0in the\u00a0License mode<\/strong>\u00a0section, or choose a premium capacity you have access to.<\/li>\n
    5. Select\u00a0Apply <\/strong>to complete creation of the workspace.<\/li>\n<\/ol>\n

      Create your first SQL database<\/strong><\/h2>\n
        \n
      1. Ensure you are in the Workspace you created earlier by selecting the\u00a0Workspaces<\/strong>\u00a0icon in the navigation bar, and then selecting the Workspace you created in the last step.<\/li>\n
      2. Create a Fabric SQL database by selecting the\u00a0+ New item<\/strong>.<\/li>\n
      3. In the\u00a0New Item | All Items<\/strong>\u00a0panel, scroll to the\u00a0Store Data<\/strong>\u00a0area and select\u00a0SQL database (preview)<\/strong>.<\/li>\n
      4. Open the SQL database in Fabric database you created in the last tutorial step. You can find it in the navigation bar of the Fabric portal, or by finding it in your Workspace for this tutorial.<\/li>\n
      5. Select the\u00a0Sample data<\/strong>\u00a0button. This takes a few moments to populate your tutorial database with the\u00a0SalesLT<\/em>\u00a0sample data.<\/li>\n
      6. Once the sample data is imported, SalesLT\u00a0schema and associated tables are added to the object explorer.<\/li>\n
      7. Select the settings->Connection string <\/strong>to get the ODBC connection string<\/strong> for the SQL Database.<\/li>\n<\/ol>\n

        Develop Streamlit app<\/h2>\n

        Use Visual Studio code<\/a> for developing the application.<\/p>\n

          \n
        1. Create a folder to work on this project. Open it in VS Code.<\/li>\n
        2. Create Python virtual environment<\/a> in your working directory.<\/li>\n
        3. Create requirements.txt<\/strong> file. Add the following libraries<\/li>\n<\/ol>\n
          streamlit\r\npandas\r\npyodbc<\/pre>\n
            \n
          1. Run the following command in VS Code terminal to setup all the requirements.,<\/li>\n<\/ol>\n
            pip install -r requirements.txt<\/pre>\n
              \n
            1. Create app.py<\/strong> file and copy this code into the file.<\/li>\n<\/ol>\n
              import streamlit as st\r\nimport pyodbc\r\nimport pandas as pd\r\nimport os\r\nimport struct\r\n\r\n# Database Connection Settings\r\n# ODBC Connection String\r\nCONN_STR = (\r\n  \u00a0 'REPLACE_WITH_ODBC_CONNECTION_STRING'\r\n)\r\n\r\n# Function to connect to Azure SQL\r\ndef connect_to_db():\r\n  \u00a0 try:\r\n  \u00a0 \u00a0 \u00a0 conn = pyodbc.connect(CONN_STR)\r\n\u00a0 \u00a0 \u00a0 \u00a0 return conn\r\n  \u00a0 except Exception as e:\r\n  \u00a0 \u00a0 \u00a0 st.error(f\"Database connection failed: {str(e)}\")\r\n  \u00a0 \u00a0 \u00a0 return None\r\n# Streamlit UI\r\n\r\nst.title(\"Fabric SQL Streamlit App\")\r\nif st.button(\"Fetch Data\"):\r\n  \u00a0 conn = connect_to_db()\r\n    st.header(\"Show all products and description\", divider=\"gray\")\r\nif conn:\r\n\u00a0 \u00a0 \u00a0 \u00a0 query = \"SELECT TOP (100) * FROM [SalesLT]. [vProductAndDescription]\"\r\n\u00a0 \u00a0 \u00a0 df = pd.read_sql(query, conn)\r\n\u00a0 \u00a0 \u00a0 st.write(df)\r\n  \u00a0 \u00a0 st.title(\"\ud83d\udcca Simple Bar Chart Example\")\r\n  \u00a0 \u00a0 getproductsbymodel= \"SELECT top 5 ProductModel, ProductCount=COUNT(*) \u00a0FROM [SalesLT].[vProductAndDescription] GROUP by ProductModel order by ProductCount DESC\"\r\n  \u00a0 \u00a0 chartdf = pd.read_sql(getproductsbymodel, conn)\r\n\r\n  \u00a0 \u00a0 # Bar Chart\r\n  \u00a0 \u00a0 st.subheader(\"Get Products inventory by Model:\")\r\n  \u00a0 \u00a0 st.bar_chart(chartdf.set_index('ProductModel'))\r\n  \u00a0 \u00a0 # Display Data\r\n  \u00a0 \u00a0 st.subheader(\"Data:\")\r\n  \u00a0 \u00a0 st.dataframe(chartdf)\r\n  \u00a0 \u00a0 # Close Connection\r\n  \u00a0 \u00a0 conn.close()\r\n  \u00a0 else:\r\n  \u00a0 \u00a0 st.error(\"Database connection failed.\")\r\n\r\n<\/pre>\n
              Test locally<\/h2>\n
              Open a terminal in VS code and run python -m streamlit run app.py<\/em> \u00a0. You will the similar output in the terminal window.<\/p>\n
              (.venv) C:\\streamlitappdemo>python -m streamlit run app.py\r\n\r\n\u00a0 You can now view your Streamlit app in your browser.\r\n\r\n\u00a0\u00a0Local URL: http:\/\/localhost:8501\r\n\r\n\u00a0 Network URL: http:\/\/10.12.110.104:8501<\/pre>\n
               <\/p>\n
              Select the local URL and run it in your browser. Select Fetch data and this will ask you to authenticate to valid your permissions to the database before fetching the results. Your app will show the data return<\/p>\n