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 Final Project: Build an SQLite Expense Manager documentation.

Save the complete program

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

def amount_value(text):
    value = int(text)
    if value < 0:
        raise argparse.ArgumentTypeError("amount must be nonnegative")
    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>=0))")
            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:
            for category, total in con.execute("SELECT category,SUM(amount) FROM expenses GROUP BY category ORDER BY category"):
                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.

Python Tutorial