A Streamlit app can display database records only after Python can reach the database. `st.connection` establishes that connection. For a PostgreSQL-backed app, SQLAlchemy and the PostgreSQL adapter `psycopg2` work behind the scenes; install them with `pip install sqlalchemy` and `pip install psycopg2-binary`. section 8.2.5
Give the connection a name and specify `type="sql"`: `st.connection("haikuconn", type="sql")`. Configure the matching `[connections.haikuconn]` entry in `.streamlit/secrets.toml` with a PostgreSQL URL containing the username, password, server address, port, and database name. Keep those credentials out of the Python code. section 8.2.5
Listing 8.1 The Database class
import streamlit as st
from sqlalchemy import text
class Database:
def __init__(self):
self.conn = st.connection("haikuconn", type="sql")
def execute_query(self, query, params={}, write=False):
with self.conn.session as session:
try:
result = session.execute(text(query), params)
if write:
session.commit()
return result.fetchall()
except Exception:
if write:
session.rollback()
raise
A reusable `Database` class holds the connection so features that need database access can call `execute_query(query, params)` instead of repeating connection setup. It executes SQL through a session, commits writes, and rolls back a failed write before raising the error again. section 8.2.5
Show database rows in an app
Create `Database()`, call `database.execute_query('SELECT * FROM haikus')`, and pass the returned rows to `st.write`. This is a quick visible check that the app can read its PostgreSQL table. section 8.2.5
A working local connection is not the whole deployment plan: a production app also needs a separately provisioned database server. section 8.7
