Connect Streamlit to Microsoft SQL Server

Introduction

This guide explains how to securely access a remote Microsoft SQL Server database from Streamlit Community Cloud. It uses the pyodbc library and Streamlit's Secrets management.

Create an SQL Server database

push_pin

Note

If you already have a remote database that you want to use, feel free to skip to the next step.

First, follow the Microsoft documentation to install SQL Server and the sqlcmd Utility. They have detailed installation guides on how to:

Once you have SQL Server installed, note down your SQL Server name, username, and password during setup.

Connect locally

If you are connecting locally, use sqlcmd to connect to your new local SQL Server instance.

In your terminal, run the following command:

sqlcmd -S localhost -U SA -P '<YourPassword>'

As you are connecting locally, the SQL Server name is localhost, the username is SA, and the password is the one you provided during the SA account setup.
You should see a sqlcmd command prompt 1>, if successful.
If you run into a connection failure, review Microsoft's connection troubleshooting recommendations for your OS (Linux & Windows).

star

Tip

When connecting remotely, the SQL Server name is the machine name or IP address. You might also need to open the SQL Server TCP port (default 1433) on your firewall.

Create a SQL Server database

By now, you have SQL Server running and have connected to it with sqlcmd! 🥳 Let's put it to use by creating a database containing a table with some example values.

From the sqlcmd command prompt, run the following Transact-SQL command to create a test database mydb:

CREATE DATABASE mydb
To execute the above command, type GO on a new line:

GO

Insert some data

Next create a new table, mytable, in the mydb database with three columns and two rows.

Switch to the new mydb database:

USE mydb
Create a new table with the following schema:

CREATE TABLE mytable (name varchar(80), pet varchar(80))
Insert some data into the table:

INSERT INTO mytable VALUES ('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bird')
Type GO to execute the above commands:

GO

To end your sqlcmd session, type QUIT on a new line.

Add username and password to your local app secrets

Your local Streamlit app will read secrets from a file .streamlit/secrets.toml in your app's root directory. Create this file if it doesn't exist yet and add the SQL Server name, database name, username, and password as shown below:

# .streamlit/secrets.toml

server = "localhost"
database = "mydb"
username = "SA"
password = "xxx"

priority_high

Important

When copying your app secrets to Streamlit Community Cloud, be sure to replace the values of server, database, username, and password with those of your remote SQL Server!

And add this file to .gitignore and don't commit it to your GitHub repo.

Copy your app secrets to Streamlit Community Cloud

As the secrets.toml file above is not committed to GitHub, you need to pass its content to your deployed app (on Streamlit Community Cloud) separately. Go to the app dashboard and in the app's dropdown menu, click on Edit Secrets. Copy the content of secrets.toml into the text area. More information is available at Secrets management.

Add pyodbc to your requirements file

To connect to SQL Server locally with Streamlit, you need to pip install pyodbc, in addition to the Microsoft ODBC driver you installed during the SQL Server installation.

On Streamlit Cloud, we have built-in support for SQL Server. On popular demand, we directly added SQL Server tools including the ODBC drivers and the executables sqlcmd and bcp to the container image for Cloud apps, so you don't need to install them.

All you need to do is add the pyodbc Python package to your requirements.txt file, and you're ready to go! 🎈

# requirements.txt
pyodbc==x.x.x

Replace x.x.x ☝️ with the version of pyodbc you want installed on Cloud.

push_pin

Note

At this time, Streamlit Community Cloud does not support Azure Active Directory authentication. We will update this tutorial when we add support for Azure Active Directory.

Write your Streamlit app

Copy the code below to your Streamlit app and run it. Make sure to adapt query to use the name of your table.

import streamlit as st
import pyodbc

# Initialize connection.
# Uses st.cache_resource to only run once.
@st.cache_resource
def init_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
        + st.secrets["server"]
        + ";DATABASE="
        + st.secrets["database"]
        + ";UID="
        + st.secrets["username"]
        + ";PWD="
        + st.secrets["password"]
    )

conn = init_connection()

# Perform query.
# Uses st.cache_data to only rerun when the query changes or after 10 min.
@st.cache_data(ttl=600)
def run_query(query):
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

rows = run_query("SELECT * from mytable;")

# Print results.
for row in rows:
    st.write(f"{row[0]} has a :{row[1]}:")

See st.cache_data above? Without it, Streamlit would run the query every time the app reruns (e.g. on a widget interaction). With st.cache_data, it only runs when the query changes or after 10 minutes (that's what ttl is for). Watch out: If your database updates more frequently, you should adapt ttl or remove caching so viewers always see the latest data. Learn more in Caching.

If everything worked out (and you used the example table we created above), your app should look like this:

Previous: Google Cloud Storage Next: MongoDB

forum

Still have questions?

Our forums are full of helpful information and Streamlit experts.