🐍 Day 23: Phase 3 Capstone — CLI Expense Tracker with Database & Testing

1. Learning Objectives

By the end of Day 23, you will be able to:

Design a complete, modular Python application from scratch
Integrate OOP, SQLite, file I/O, exception handling, and command-line user input
Apply context managers (with statements) for database safety
Use decorators for logging and validation
Write comprehensive unit tests with pytest to ensure correctness
Structure a project with modules, packages, and clear separation of concerns

2. Concept Explanation — The Big Picture

Today’s project is an Expense Tracker – a console application that lets users record and analyse their daily spending.
This project synthesises all Phase 3 concepts (Days 16–22) and reinforces Phase 2 skills.

Core Features

Add an expense (amount, category, date, description)
List all expenses, with optional filtering by category or date range
Show summary statistics (total spent, average per category, etc.)
Edit or delete existing expenses
Export data to JSON for backup
All data persisted in an SQLite database

OOP Design

ExpenseManager    (business logic, CRUD operations)
    └── uses DBConnection (context manager for sqlite3)
Expense           (data class holding one expense row)

Project structure

expense_tracker/
├── main.py               # Entry point, menu loop
├── models/
│   ├── __init__.py
│   └── expense.py        # Expense dataclass
├── database.py           # Database setup, context manager, CRUD functions
├── cli.py                # User input / output functions
├── utils.py              # Validation helpers, decorators
└── tests/
    ├── __init__.py
    └── test_database.py  # Unit tests for database operations

3. Step‑by‑Step Building Guide

Step 1: The `Expense` Class (`models/expense.py`)

from dataclasses import dataclass
from datetime import date

@dataclass
class Expense:
    id: int = None
    amount: float = 0.0
    category: str = ""
    description: str = ""
    expense_date: date = None

💡 Using @dataclass (standard library) reduces boilerplate. We can easily serialise/deserialise to/from database rows.

Step 2: Database Context Manager & Schema (`database.py`)

import sqlite3
from contextlib import contextmanager
from models.expense import Expense

DB_NAME = "expenses.db"

@contextmanager
def get_db():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row   # rows can be accessed like dicts
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def init_db():
    with get_db() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS expenses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                amount REAL NOT NULL,
                category TEXT NOT NULL,
                description TEXT,
                expense_date TEXT NOT NULL
            )
        """)

Step 3: CRUD Functions (`database.py` – continued)

def add_expense(amount, category, description, expense_date):
    with get_db() as conn:
        conn.execute(
            "INSERT INTO expenses (amount, category, description, expense_date) VALUES (?, ?, ?, ?)",
            (amount, category, description, expense_date)
        )

def get_all_expenses():
    with get_db() as conn:
        rows = conn.execute("SELECT * FROM expenses ORDER BY expense_date DESC").fetchall()
        return [Expense(**row) for row in rows]

def get_expenses_by_category(category):
    with get_db() as conn:
        rows = conn.execute(
            "SELECT * FROM expenses WHERE category = ?", (category,)
        ).fetchall()
        return [Expense(**row) for row in rows]

def delete_expense(expense_id):
    with get_db() as conn:
        conn.execute("DELETE FROM expenses WHERE id = ?", (expense_id,))

Step 4: Validation with a Decorator (`utils.py`)

import functools
from datetime import datetime

def validate_amount(func):
    @functools.wraps(func)
    def wrapper(amount, *args, **kwargs):
        try:
            amount = float(amount)
        except ValueError:
            raise ValueError("Amount must be a number.")
        if amount <= 0:
            raise ValueError("Amount must be positive.")
        return func(amount, *args, **kwargs)
    return wrapper

Step 5: Command‑Line Interface (`cli.py`)

from database import add_expense, get_all_expenses, get_expenses_by_category, delete_expense
from utils import validate_amount

def menu():
    print("=== EXPENSE TRACKER ===")
    print("1. Add Expense")
    print("2. View All Expenses")
    print("3. Filter by Category")
    print("4. Delete Expense")
    print("5. Summary")
    print("6. Export to JSON")
    print("7. Quit")

@validate_amount
def prompt_add(amount):
    category = input("Category (e.g., Food, Transport): ").strip()
    desc = input("Description (optional): ").strip()
    date_str = input("Date (YYYY-MM-DD) or press Enter for today: ").strip()
    if not date_str:
        date_str = datetime.today().strftime("%Y-%m-%d")
    add_expense(amount, category, desc, date_str)
    print("Expense added.")

def view_all():
    expenses = get_all_expenses()
    for e in expenses:
        print(f"{e.id}: {e.expense_date} | {e.category:10} | ${e.amount:7.2f} | {e.description}")

Step 6: Main Loop (`main.py`)

from database import init_db
from cli import menu, prompt_add, view_all, ...

def main():
    init_db()
    while True:
        menu()
        choice = input("> ")
        # ... handle each choice with try/except for validation errors

4. Hands-On Exercises

Rather than separate exercises, today’s hands‑on work is to build the Expense Tracker incrementally, using the guide above. After each step, you can test that feature works.

Mini‑exercises embedded in the build:

Write the Expense dataclass and a small script that creates three different expenses and prints them.
Implement the init_db() and add_expense() functions. Manually insert a few rows using the Python shell.
Add a function get_expenses_by_month(year, month) that returns expenses for a given month.
Create a decorator log_query that prints every SQL query executed. Apply it to your CRUD functions.
Write a summary() function that prints total spent per category, and the overall total.

5. Applied Challenge Task 🏗️

Complete CLI Expense Tracker + Test Suite

Your final task is to finish the entire application and write a comprehensive pytest suite for it.

Requirements:

All features listed above must work.
Include input validation (positive amounts, valid dates, non‑empty category).
Add JSON export feature – a function that dumps all expenses to a timestamped JSON file.
Write unit tests for the database layer using an in‑memory SQLite database (as in Day 22).
Include tests that verify:
- add_expense stores data correctly
- duplicate IDs don’t occur
- get_expenses_by_category filters correctly
- delete_expense removes the row
- edge cases: adding expense with zero amount raises error, etc.

Stretch goals:

Add a budget feature: set a monthly budget per category and warn when exceeded.
Use argparse to allow command‑line options like --add, --list, --summary, bypassing the menu.
Add a decorator to time how long each database operation takes and print the slow ones.

6. Brief Review Summary

Concept	Application in Project
OOP	`Expense` dataclass models data
Context Managers	Database connections via `@contextmanager`
Decorators	`@validate_amount` for input validation
SQLite	Persistent storage, CRUD
Exception Handling	Ensures robustness, rollback on error
Testing	Unit tests using in‑memory DB and `pytest`
Generators (optional)	Exporting large datasets lazily

7. Phase 3 Complete! 🎉

You have now mastered:

Day	Topic
16	Iterators & Generators
17	Decorators
18	Context Managers
19	Working with APIs & JSON
20	Multithreading & Multiprocessing
21	Introduction to Databases (SQLite)
22	Software Testing with pytest
23	Phase 3 Capstone: CLI Expense Tracker

Next stop: Phase 4 – Professional & Expert Level (Days 24–30)

Day 24 kicks off with Code Optimization & Complexity Analysis — writing faster, leaner Python code.

🎯 Your Action Items for Day 23:

✅ Build the Expense Tracker step by step
✅ Write and run the test suite
✅ Experiment with adding a new feature (like a budget)
✅ Reflect on how the project ties together everything you've learned

Search This Blog

Python Adventures