Python Tutorial / Final Project: Build an SQLite Expense Manager

Python Tutorial

The final project accepts an expense’s date, category and amount, stores it in SQLite and lists records or category totals. Explicit initialization, validation, transactions and command handling connect the course’s core techniques.

Define the application’s scope and data rules

Amounts are nonnegative integers in whole KRW units, dates use YYYY-MM-DD and categories must not be blank. Banking integration, payments and multi-user authentication are outside this example. Each add call creates one new expense, so repeating the command adds another record.

Save ledger.py in a fresh practice folder. Python 3.12 or later and the standard library are sufficient. --db chooses a separate database path; the default ledger.db is relative to the working directory. Do not point the exercise at business or unrelated practice data.

For the relevant APIs and platform requirements, see Python: sqlite3.

Storage rules and automated tests build on the rules demonstrated in Store Data with sqlite3 and Parameterized Queries.

Save the complete program

import argparse
import sqlite3
from datetime import date
from pathlib import Path

MAX_AMOUNT = 1_000_000_000_000

def amount_value(text):
    try:
        value = int(text)
    except ValueError as error:
        raise argparse.ArgumentTypeError("expected an integer") from error
    if not 0 <= value <= MAX_AMOUNT:
        raise argparse.ArgumentTypeError("amount must be between 0 and 1000000000000")
    return value

def date_value(text):
    try:
        parsed = date.fromisoformat(text)
    except ValueError as error:
        raise argparse.ArgumentTypeError("invalid date") from error
    if parsed.isoformat() != text:
        raise argparse.ArgumentTypeError("use YYYY-MM-DD")
    return text

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--db", type=Path, default=Path("ledger.db"))
    commands = parser.add_subparsers(dest="command", required=True)
    commands.add_parser("init")
    add = commands.add_parser("add")
    add.add_argument("day", type=date_value)
    add.add_argument("category")
    add.add_argument("amount", type=amount_value)
    commands.add_parser("list")
    commands.add_parser("summary")
    args = parser.parse_args()
    if args.command != "init" and not args.db.is_file():
        parser.error("database not initialized")
    if args.command == "add" and not args.category.strip():
        parser.error("category is required")
    con = sqlite3.connect(args.db, autocommit=False)
    try:
        if args.command == "init":
            with con:
                con.execute("CREATE TABLE IF NOT EXISTS expenses (id INTEGER PRIMARY KEY, day TEXT NOT NULL, category TEXT NOT NULL CHECK(length(trim(category))>0), amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
            print("initialized")
        elif args.command == "add":
            with con:
                con.execute("INSERT INTO expenses(day,category,amount) VALUES(?,?,?)", (args.day, args.category.strip(), args.amount))
            print("added")
        elif args.command == "list":
            for row in con.execute("SELECT id,day,category,amount FROM expenses ORDER BY day,id"):
                print(*row)
        else:
            totals = {}
            for category, amount in con.execute("SELECT category,amount FROM expenses"):
                totals[category] = totals.get(category, 0) + amount
            for category, total in sorted(totals.items()):
                print(category, total)
    finally:
        con.close()

if __name__ == "__main__":
    main()

Initialize, enter records and query them

python ledger.py init
python ledger.py add 2026-10-17 food 3500
python ledger.py add 2026-10-17 food 8000
python ledger.py add 2026-10-17 transport 2500
python ledger.py list
python ledger.py summary

Initialization prints initialized and each successful add prints added. list displays id, date, category and amount, ordered by date and id. The final summary should be:

food 11500
transport 2500

init does not erase an existing table. To repeat from an empty state, use a new folder or a new --db path. When using --db, put this global option before the subcommand, for example python ledger.py --db other-ledger.db init. Do not delete an unknown database merely to reset the example.

Test rejection and durable storage

Try 2026-02-30 and confirm rejection before a record is stored.
Try a negative amount and a blank category, then confirm the list has not grown.
Restart the program and run list to verify persistence.
Use a new database and reproduce the same three records and totals.

Value parameters separate input from SQL syntax; database constraints reinforce amount and category rules. On failure, the connection context rolls back and finally closes it. Schema migrations, backup/restore and concurrent-user behavior need additional design.

Separate responsibilities as the project grows

One complete file makes this introductory program easy to inspect. A larger tool can separate argument parsing, validation, database access and aggregation into modules while tests preserve its behavior. Retain the main guard so that import alone does not create a database or prompt a user.

For real personal expenses, choose file permissions, backup procedures and sharing locations deliberately. Completion means checking invalid-input rejection and rerun semantics as well as correct totals. The testing guide provides the structure for repeatable checks.

The flow is argument parsing → date/amount validation → database existence check → transactional write or query → output. amount_value enforces integers from 0 through 1,000,000,000,000; date_value validates a real YYYY-MM-DD; main dispatches commands and manages the connection lifetime. summary accumulates Python integers instead of SQL SUM to avoid SQLite aggregate overflow. Rows are streamed, while the totals dictionary grows with the number of categories. Existing CHECK constraints do not update automatically: use a fresh database. The integration test below checks valid storage, date/negative/upper-bound rejection, listing and totals in a temporary database.

Save the complete example as check_ledger.py and run python check_ledger.py in the same practice directory. First place ledger.py in that directory too.

import subprocess
import sys
import tempfile
from pathlib import Path
program = Path("ledger.py").resolve()
with tempfile.TemporaryDirectory() as folder:
    db = Path(folder) / "test.db"
    def run(*args):
        return subprocess.run([sys.executable, str(program), "--db", str(db), *args], capture_output=True, text=True)
    assert run("init").returncode == 0
    assert run("add", "2026-10-17", "food", "8000").returncode == 0
    assert run("add", "2026-02-30", "food", "8000").returncode != 0
    assert run("add", "2026-10-17", "food", "-1").returncode != 0
    assert run("add", "2026-10-17", "food", "1000000000001").returncode != 0
    result = run("summary")
    assert result.returncode == 0 and result.stdout.strip() == "food 8000"
    assert len(run("list").stdout.splitlines()) == 1
print("integration checks passed")

Expected output:

integration checks passed

Official behavior and conditions: SQLite: sum and total

Python Tutorial