streamlit drilldown screen

app.py

import streamlit as st
import pandas as pd
import psycopg2

# Database configuration (replace with your credentials)
DB_CONFIG = {
    "host": "your-postgres-host",
    "database": "your-database-name",
    "user": "your-username",
    "password": "your-password",
    "port": "5432"
}

@st.cache_resource
def get_connection():
    return psycopg2.connect(**DB_CONFIG)

@st.cache_data(ttl=300)
def fetch_parents():
    conn = get_connection()
    query = "SELECT parent_id, parent_name, description FROM parents ORDER BY parent_name;"
    return pd.read_sql(query, conn)

@st.cache_data(ttl=300)
def fetch_children(parent_id):
    conn = get_connection()
    query = """
        SELECT child_id, child_name, details, created_at 
        FROM children 
        WHERE parent_id = %s
        ORDER BY created_at DESC;
    """
    return pd.read_sql(query, conn, params=(parent_id,))

st.title("PostgreSQL Table Drilldown")

if 'selected_parent' not in st.session_state:
    st.session_state.selected_parent = None

if st.session_state.selected_parent is None:
    st.header("Parent Table")
    parents_df = fetch_parents()
    selected = st.dataframe(parents_df, use_container_width=True)
    
    # Add a selectbox for drilldown
    parent_ids = parents_df['parent_id'].tolist()
    parent_names = parents_df['parent_name'].tolist()
    selected_parent = st.selectbox(
        "Select a parent to drilldown:",
        options=parent_ids,
        format_func=lambda x: parents_df[parents_df['parent_id'] == x]['parent_name'].iloc[0]
    )
    if st.button("Drilldown"):
        st.session_state.selected_parent = selected_parent
        st.rerun()
else:
    st.header("Child Table")
    children_df = fetch_children(st.session_state.selected_parent)
    st.dataframe(children_df, use_container_width=True)
    if st.button("← Back to Parent Table"):
        st.session_state.selected_parent = None
        st.rerun()