AI 對話紀錄  March 19, 2025

【AG-03】Reporting with Streamlit

Q: To complete my basic technical offerings, I want to know is it possible to use the Python Streamlit framework to replace OpenOffice Calc as the reporting engine by connecting the MariaDB? I've created the report output with database Views so what Streamlit needs to do is just to format them properly and provide basic options on a web interface like the time period to show, the job role or task to drill down into. So yes, the report will be one a different system than the operation and CMS combo.

A: I’m glad you’re excited about rounding out your technical offerings! Your idea to use the Python Streamlit framework to replace OpenOffice Calc as a reporting engine, connected to MariaDB database views, is not only possible but also a fantastic fit for what you’re aiming to achieve. Since you’ve already created report output with database views, Streamlit can step in to format that data beautifully and provide a web interface with interactive options—like time periods, job roles, or tasks to drill down into—all without needing to overhaul your existing operational and CMS combo. Let’s break this down and see how it can work for you.

Is It Possible?

Yes, absolutely! Streamlit is an open-source Python framework designed to turn data scripts into interactive web apps with minimal effort. It’s perfect for replacing something like OpenOffice Calc (a desktop spreadsheet tool) with a modern, web-based reporting solution. Here’s why it fits your needs:

• MariaDB Connection: Streamlit can connect to MariaDB using Python libraries like mysql-connector-python or SQLAlchemy, pulling data from your views effortlessly.

• Prebuilt Views: Since your report output is already defined in database views, Streamlit doesn’t need to handle complex logic—it just queries those views and displays the results.

• Web Interface: It offers a simple way to add interactive controls (e.g., dropdowns for time periods, job roles, or tasks) and format data into tables, charts, or metrics—all accessible via a browser.

• Separate System: Streamlit can run on a different server from your operational and CMS combo, querying MariaDB remotely over a secure connection.

This is a big upgrade from OpenOffice Calc: no more manual exports, desktop-only access, or clunky formatting—your team gets a live, web-based dashboard instead.

How It Could Work

Step 1: Connect Streamlit to MariaDB

You’ll need to link Streamlit to your MariaDB database. Since your views are ready, this is straightforward.

• Install Dependencies: Add mysql-connector-python or SQLAlchemy to your Python environment.

• Basic Connection Code: Here’s a minimal example to connect and fetch data from a view:

  python

  import streamlit as st
  import mysql.connector
  import pandas as pd
  
  # Database connection (use secrets for security)
  conn = mysql.connector.connect(
      host="your-mariadb-host",
      user="your-username",
      password="your-password",
      database="your-database"
  )
  
  # Query a view
  query = "SELECT * FROM report_view"
  df = pd.read_sql(query, conn)
  conn.close()
  
  # Display in Streamlit
  st.dataframe(df)

• Secrets Management: Store credentials in a .streamlit/secrets.toml file (e.g., host = "x", user = "y", password = "z") to keep them secure.

Step 2: Format the Report Output

Your views likely output structured data (e.g., columns for dates, job roles, tasks, metrics). Streamlit can format this with:

• Tables: st.dataframe(df) for interactive, sortable tables.

• Charts: Use st.line_chart() or st.bar_chart() for trends, or integrate plotly/altair for fancier visuals.

• Metrics: Highlight key numbers with st.metric("Total Tasks", 150, delta=10).

Step 3: Add Interactive Options

Here’s where Streamlit shines—adding controls for time periods, job roles, or tasks to drill down:

• Time Period Filter:

  python

  time_period = st.selectbox("Select Time Period", ["Last 7 Days", "Last 30 Days", "Last Year"])
  if time_period == "Last 7 Days":
      query = "SELECT * FROM report_view WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)"
  elif time_period == "Last 30 Days":
      query = "SELECT * FROM report_view WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)"
  else:
      query = "SELECT * FROM report_view WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)"
  df = pd.read_sql(query, conn)
  st.dataframe(df)

• Job Role Drill-Down:

  python

  roles = pd.read_sql("SELECT DISTINCT job_role FROM report_view", conn)["job_role"].tolist()
  selected_role = st.selectbox("Filter by Job Role", ["All"] + roles)
  if selected_role != "All":
      query = f"SELECT * FROM report_view WHERE job_role = '{selected_role}'"
  else:
      query = "SELECT * FROM report_view"
  df = pd.read_sql(query, conn)
  st.dataframe(df)

• Task Drill-Down:

  python

  tasks = pd.read_sql("SELECT DISTINCT task FROM report_view", conn)["task"].tolist()
  selected_task = st.multiselect("Filter by Task", tasks)
  if selected_task:
      query = f"SELECT * FROM report_view WHERE task IN {tuple(selected_task)}"
  else:
      query = "SELECT * FROM report_view"
  df = pd.read_sql(query, conn)
  st.dataframe(df)

Step 4: Deploy on a Separate System

• Run Locally: Test with streamlit run app.py on your machine.

• Deploy: Host it on a separate server (e.g., a QNAP VM, AWS, or Streamlit Community Cloud):

  • Use a requirements.txt file: streamlit, mysql-connector-python, pandas.

  • Ensure the server can reach your MariaDB host (e.g., via VPN or public IP with firewall rules).

• Access: Share the URL (e.g., http://your-server:8501) with your team.

• • *

Why It Replaces OpenOffice Calc

• Web-Based: No desktop software needed—access from anywhere.

• Live Data: Pulls directly from MariaDB views, no manual imports.

• Interactivity: Filters and drill-downs beat static spreadsheets.

• Scalability: Handles larger datasets and multiple users better than Calc.

Your views do the heavy lifting, so Streamlit just needs to present the data and add those basic options—perfectly doable.

How It Fits Your Setup

Since this report will live on a different system from your operational AppGini + CMS combo:

• MariaDB as the Hub: Both systems can query the same MariaDB database (assuming network access).

• Separation: The Streamlit app won’t touch your operational or CMS logic—it’s a standalone reporting layer.

• User Perspective: Your team manages data in AppGini, and donors see the CMS site, while reports get their own sleek interface.

• • *

Quick Prototype

Here’s a full example to get you started:

python

import streamlit as st
import mysql.connector
import pandas as pd

# Connection (replace with your details or secrets.toml)
conn = mysql.connector.connect(
    host="your-mariadb-host",
    user="your-username",
    password="your-password",
    database="your-database"
)

# Sidebar filters
st.sidebar.header("Report Filters")
time_period = st.sidebar.selectbox("Time Period", ["Last 7 Days", "Last 30 Days", "All"])
role = st.sidebar.selectbox("Job Role", ["All"] + pd.read_sql("SELECT DISTINCT job_role FROM report_view", conn)["job_role"].tolist())
task = st.sidebar.multiselect("Tasks", pd.read_sql("SELECT DISTINCT task FROM report_view", conn)["task"].tolist())

# Build query
query = "SELECT * FROM report_view WHERE 1=1"
if time_period == "Last 7 Days":
    query += " AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)"
elif time_period == "Last 30 Days":
    query += " AND date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)"
if role != "All":
    query += f" AND job_role = '{role}'"
if task:
    query += f" AND task IN {tuple(task)}"

# Fetch and display
df = pd.read_sql(query, conn)
st.title("Chaplaincy Report")
st.dataframe(df)
st.bar_chart(df.groupby("job_role").size())  # Example chart
conn.close()

• Run: Save as app.py, then streamlit run app.py.

• Test: Add your MariaDB details and tweak the query to match your view’s columns.

• • *

Next Steps

• Try It: Set up this script with your MariaDB credentials and a sample view. See how it feels!

• Enhance: Add more visuals (e.g., st.plotly_chart) or metrics based on your report needs.

• Deploy: Move it to a server once you’re happy—let me know if you need hosting tips.

This totally completes your offerings: operational system (AppGini), CMS (Publii), and now reporting (Streamlit). What do you think—ready to give it a spin? How’s Tattoo coming along, by the way?

Sharing is sexy