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.
You can install Streamlit in a python environment with
pip install streamlit
and verify that it installed correctly with
steamlit hello, which will open a sample app in your web browser.
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 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 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:
Updated 7 months ago