Streamlit

Streamlit is an open-source Python app framework focused on building and sharing data apps. You can configure PostgreSQL (and thus bit.io) databases as data sources for use with Streamlit data apps.

Getting Started

You can install Streamlit in a python environment with pip:

pip install streamlit

and verify that it installed correctly with steamlit hello, which will open a sample app in your web browser.

Connect to bit.io Using Streamlit

We can set up a postgres config for Streamlit with our bit.io connection credentials in to use bit.io as a data source for our Streamlit apps. Create a .streamlit directory in your project's home directory, and then create a file called .streamlit/secrets.toml. This file will include your connection details. You can find the necessary connection details in your database's bit.io Connect Tab. The file should be formatted as follows:

# .streamlit/secrets.toml

[postgres]
host = "db.bit.io"
port = 5432
dbname = "bitdotio/nhtsa_ads_crashes" # database name
user = "<your_bitdotio_username>" # your username
password = "<your_bitdotio_password>" # your password (from the connect menu)

Using Streamlit with bit.io

Using the connection we configured above, we can create a simple Steamlit app to display the output of a query in a table. This app will connect to the database, execute the query, and display an interactive table with the query results.

import streamlit as st
import psycopg2
import pandas as pd

# initialize the connection
@st.experimental_singleton
def init_connection():
    return psycopg2.connect(**st.secrets["postgres"])

conn = init_connection()

# execute a query
@st.experimental_memo(ttl=600)
def run_query(query):
    with conn.cursor() as cur:
        cur.execute(query)
        colnames = [desc[0] for desc in cur.description]
        return colnames, cur.fetchall()

names, rows = run_query("SELECT * from ads_incident_report_data limit 10;")

# save as DataFrame
df = pd.DataFrame(rows, columns=names)

# write to app
df

You can preview the app in your browser with streamlit run bitdotio_streamlit_app.py in the directory where the file is located. This simple data app will look like this:

For further reading, check out the Streamlit docs on Connecting to PostgreSQL and then look into the Main Concepts of Streamlit for more details on how to build more complicated and useful data apps.