Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 38 ยท Projects and Practice

Project: Expense Tracker with SQLite

Build a complete expense tracker in Streamlit: a SQLite database layer, an add-expense form, filters, monthly summaries, charts and CSV export, with tested logic.

This project puts the course together in one small app. You will build an expense tracker where you add spending through a form, store it in SQLite, filter by month and category, see totals and a chart, and export the data. It touches forms, session state, caching, SQL and charts, in about 120 lines.

The most important design choice is the structure. The app has two layers: a data layer (plain Python functions that talk to SQLite and return DataFrames) and a UI layer (Streamlit code that calls them). The data layer is tested here with real code. The UI layer is read-only on this site, because Streamlit does not run in the browser, but it is short because all the logic lives below it.

Plan

flowchart TD
    F["Form: date, amount,<br/>category, note"] -->|validate| D[("SQLite<br/>expenses table")]
    D -->|query with filters| T["Table + metrics"]
    D -->|group by month| C["Bar chart"]
    T --> X["CSV download"]

Writes go through validation; every read goes through one query function.

The features, in order:

  1. Create the table if it does not exist.
  2. Add an expense (validated).
  3. List expenses with month and category filters.
  4. Summaries: total, count, average, by category.
  5. Delete a mistaken entry.
  6. Download as CSV.

Step 1: the data layer

Put this in db.py. It takes a connection as an argument so tests can use an in-memory database, and every statement uses parameters.

Python db.py: schema, add, query, summarize

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The two bad entries are rejected with specific messages and the good ones are saved. Check how list_expenses builds the query: the WHERE parts are fixed strings, and only the values travel as parameters.

Step 2: monthly totals

A bar chart of spending per month answers "am I spending more lately?". The aggregation belongs in SQL or pandas, not in the UI. Add this to db.py and try it:

Python Monthly totals and summary numbers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

st.metric("Total", f"{s['total']:,.2f}", delta=f"{change:+.2f}") shows this as a card with a green or red arrow.

Step 3: the UI layer (read-only)

Now the Streamlit file app.py. Study it with the earlier lessons in mind: the connection is cached, the form clears itself and saves through the data layer, and every display function is a thin wrapper.

Python
import streamlit as st
from datetime import date
from db import CATEGORIES, add_expense, by_category, connect, delete_expense, list_expenses, monthly_totals

st.set_page_config(page_title="Expense tracker", page_icon="๐Ÿ’ธ", layout="wide")

@st.cache_resource
def get_conn():
    return connect("expenses.db")           # a file, so data survives restarts

conn = get_conn()
st.title("Expense tracker")

with st.sidebar:
    st.header("Add expense")
    with st.form("add", clear_on_submit=True):
        spent_on = st.date_input("Date", value=date.today())
        amount = st.number_input("Amount", min_value=0.0, step=0.5, format="%.2f")
        category = st.selectbox("Category", CATEGORIES)
        note = st.text_input("Note", max_chars=200)
        submitted = st.form_submit_button("Save")
    if submitted:
        errors = add_expense(conn, spent_on.isoformat(), amount, category, note)
        if errors:
            for message in errors:
                st.error(message)
        else:
            st.success("Saved.")

    st.header("Filter")
    month = st.text_input("Month (YYYY-MM)", placeholder="2026-03") or None
    chosen = st.selectbox("Category filter", ["All"] + CATEGORIES)
    category_filter = None if chosen == "All" else chosen

df = list_expenses(conn, month, category_filter)

total = df["amount"].sum() if not df.empty else 0
left, middle, right = st.columns(3)
left.metric("Total", f"{total:,.2f}")
middle.metric("Entries", len(df))
right.metric("Average", f"{df['amount'].mean():,.2f}" if not df.empty else "โ€“")

tab_list, tab_charts = st.tabs(["Entries", "Charts"])
with tab_list:
    st.dataframe(df, hide_index=True, use_container_width=True)
    if not df.empty:
        st.download_button("Download CSV", df.to_csv(index=False).encode(), "expenses.csv", "text/csv")
        to_delete = st.selectbox("Delete entry id", df["id"])
        if st.button("Delete"):
            delete_expense(conn, to_delete)
            st.rerun()
with tab_charts:
    st.subheader("By month")
    st.bar_chart(monthly_totals(conn), x="month", y="total")
    st.subheader("By category")
    st.bar_chart(by_category(conn, month), x="category", y="amount")

What to notice:

  • The form uses clear_on_submit=True, so the boxes empty after saving, and the validation messages come from the data layer, not from UI conditions.
  • Nothing in app.py writes SQL. If you later move from SQLite to PostgreSQL, only db.py changes.
  • The delete button calls st.rerun() so the table is redrawn without the removed row.
  • The database is a file: on a host with a temporary disk (see Deploying to Streamlit Community Cloud) it would be lost on restart, so for a deployed app swap SQLite for a hosted database.

Step 4: ideas to extend it

  1. Budgets. A budgets table with a monthly limit per category, and a progress bar (st.progress) that turns red over 100 percent.
  2. Edit in place. Replace the delete selectbox with st.data_editor and the row-level save from Editing and Saving Data.
  3. Import. An uploader that reads a bank CSV, maps columns and inserts valid rows (Files, CSV and Excel).
  4. Users. A password gate (Performance and Security Checklist) and a user column in the table, so each person sees only their rows.
  5. Recurring entries. A button that copies last month's rent.

Common mistakes in projects like this

  • Mixing SQL into UI code, which makes both hard to change and test.
  • Building the WHERE clause by pasting user text into SQL.
  • Storing dates as free text in many formats; ISO YYYY-MM-DD sorts and filters correctly.
  • Using floats for money in a serious application: store integer cents, or use Decimal. Floats are acceptable for a personal tracker if you round on display.
  • Forgetting that a hosted app's disk may not persist.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why separate the data layer (db.py) from the Streamlit UI?

    Plain functions that take a connection are easy to test, and the UI stays a thin layer on top.
  2. 2. How do filters like month and category get into the SQL safely?

    Values are passed as parameters so user input can never change the structure of the query.
  3. 3. What does clear_on_submit=True do on the form?

    The input boxes reset after submitting, so the user can enter the next expense right away.
  4. 4. Why is a SQLite file a weak choice for the database of an app on a platform with a temporary disk?

    If the filesystem is not persistent, the data disappears. Use a hosted database for deployed apps.

Summary

  • Build apps in two layers: a tested data layer and a thin Streamlit UI.
  • Validate on write, parameterize every query and keep dates in ISO format.
  • Forms with clear_on_submit, metrics, tabs, charts and a CSV download make a complete small app.
  • Cache the connection with cache_resource; call st.rerun() after changes that must show immediately.
  • Persist data outside the container when you deploy.

Next: Project: Machine Learning Prediction App