Personal Expense Logger with SQLite
Build a Python command-line tool that records your expenses in a local SQLite database, the database that comes built into Python. You add expenses with commands like expenses add 12.50 food "lunch", list them, edit or delete them by id, and see monthly totals by category, which you can also export as a CSV file for a spreadsheet. It's a great first step into databases: you'll learn creating tables, inserting, updating and deleting rows, grouping with GROUP BY, safe queries with parameters, and organizing a command-line app with argparse subcommands.
- Estimate
- ~7.5h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
sqlite3 · argparse
Project roadmap
- 01
Create the database
~1hWrite db.py with a function that connects to expenses.db with sqlite3 and creates a transactions table if it doesn't exist: id (INTEGER PRIMARY KEY), date (TEXT, YYYY-MM-DD), amount_cents (INTEGER, to avoid rounding errors), category (TEXT) and description (TEXT). Add a CHECK that the amount is greater than 0. Done when: running it creates the database file with the table.
- 02
Add and list expenses
~1.5hUse argparse subcommands: add AMOUNT CATEGORY [DESCRIPTION] [--date] and list [--month YYYY-MM] [--category]. Convert the amount to cents, validate the date, and always use ? placeholders in queries, never string formatting. Print the list as an aligned table with the id, date, amount, category and description. Done when: added expenses show up in the list, and filters work.
- 03
Show monthly summaries
~1.5hAdd summary [--month YYYY-MM], which runs a query with GROUP BY on the month (substr(date, 1, 7)) and category, and prints each category's total and its share of the month's total, sorted by total, plus the month's grand total. Without --month, show every month. Done when: the summary totals match the listed expenses.
- 04
Edit and delete
~1hAdd edit ID with optional --amount, --category, --description and --date, updating only the given fields, and delete ID, which asks for confirmation (skipped with --yes). When the id doesn't exist, say so clearly and exit with code 1, checking the cursor's rowcount. Done when: editing and deleting work, and unknown ids give a clear message.
- 05
Export to CSV
~1hAdd export --month YYYY-MM --out FILE, which writes the monthly summary (category and total) and optionally all transactions with --all, using the csv module. Refuse to overwrite an existing file unless --force is given. Done when: the exported file opens correctly in a spreadsheet.
- 06
Test it
~1.5hWrite pytest tests using a temporary database file (the tmp_path fixture): adding with valid and invalid amounts and dates, listing with filters, summary totals, editing, deleting unknown ids, and export. Done when: pytest passes.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack