A Streamlit app needs a way to read and change PostgreSQL data through Python. SQLAlchemy is the database toolkit Streamlit uses behind `st.connection` to manage connections and execute queries. Install it with `pip install sqlalchemy`; PostgreSQL also needs the `psycopg2-binary` adapter. 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
The `Database` class keeps connection logic in one place. Its `execute_query` method runs SQL with parameters, returns fetched rows, commits writes, and rolls back failed writes. section 8.2.5
Read rows in the app
After configuring the named connection, call `database.execute_query('SELECT * FROM haikus')` and pass the results to `st.write` to display the table. section 8.2.5
