Build a Personal Expense Tracker with Python and SQLite

Most budgeting apps want an account, a subscription and a link to your bank before they will show you anything. If the question you actually want answered is simpler — where did last month's money go? — Python and a single database file will get you there in an afternoon. What follows is a complete, working expense tracker. You add expenses from the terminal, each one gets a category, and one command prints the month's total plus a breakdown by category. Everything uses the standard library, so there is nothing to install and nothing to configure.

Why SQLite is the right storage for this

SQLite ships inside Python as the sqlite3 module. There is no server to start, no port to open, no password to pick. The entire database is one file called expenses.db, which you can copy to a USB stick, put in a synced folder or email to yourself.

The alternative approaches run out of road quickly. A CSV file works fine until you want totals per category, at which point you are writing parsing code that slowly turns into a bad database. JSON is pleasant to read but awkward to aggregate. SQLite gives you WHERE, GROUP BY and ORDER BY on day one, and the SQL you learn here works in Postgres and MySQL later.

One decision to make early: store money as integer cents, not as a float. Floating-point arithmetic produces totals like 43.21999999999999, and those errors compound once you sum a few hundred rows. Keep whole cents in the database, divide by 100 only when you print.

Step one: create the tables and seed some categories

Two tables are enough. One holds category names so you never mistype "groceries" three different ways; the other holds the expenses themselves and points at a category by id.

import sqlite3 conn = sqlite3.connect("expenses.db") conn.execute("PRAGMA foreign_keys = ON") with conn: conn.execute(""" CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE ) """) conn.execute(""" CREATE TABLE IF NOT EXISTS expenses ( id INTEGER PRIMARY KEY, amount_cents INTEGER NOT NULL CHECK (amount_cents > 0), spent_on TEXT NOT NULL, category_id INTEGER NOT NULL REFERENCES categories(id), note TEXT DEFAULT '' ) """)

The with conn block commits when it finishes and rolls back if anything raises, which saves you from half-written rows. The CHECK constraint refuses negative amounts before they reach your summary. Dates live in a TEXT column as ISO strings — 2025-03-14 — because that format sorts correctly as plain text and parses cleanly inside SQLite's strftime.

Seed a handful of categories once, at setup:

for name in ("Groceries", "Rent", "Transport", "Eating out", "Utilities", "Fun"): conn.execute("INSERT OR IGNORE INTO categories (name) VALUES (?)", (name,)) conn.commit()

Step two: add expenses without corrupting them

Resist the temptation to build a menu first. A single function that takes an amount, a date, a category and an optional note is easier to test and easier to reuse from a script later.

from datetime import date def add_expense(conn, amount, spent_on, category, note=""): cents = round(float(amount) * 100) if cents <= 0: raise ValueError("amount must be positive") date.fromisoformat(spent_on) # raises ValueError on a bad date with conn: conn.execute("INSERT OR IGNORE INTO categories (name) VALUES (?)", (category,)) cat_id = conn.execute( "SELECT id FROM categories WHERE name = ?", (category,) ).fetchone()[0] conn.execute( "INSERT INTO expenses (amount_cents, spent_on, category_id, note)" " VALUES (?, ?, ?, ?)", (cents, spent_on, cat_id, note), )

Notice the question marks. Those are parameter placeholders, and you should use them for every value you insert or filter on. Building SQL with f-strings breaks the moment a note contains an apostrophe — Maria's birthday will throw a syntax error — and it opens the door to SQL injection if the data ever comes from somewhere less trusted than your own keyboard. The INSERT OR IGNORE line quietly creates a new category the first time you use one, then does nothing on later runs because the name is unique.

The date check is worth keeping. date.fromisoformat raises on anything that is not a real ISO date, so "14/03/2025" fails loudly at entry time rather than quietly vanishing from next month's report.

Step three: print a monthly summary

This is where SQLite earns its place. One query groups the month by category and sorts the biggest line first.

def monthly_summary(conn, month): # month looks like "2025-03" rows = conn.execute(""" SELECT c.name, SUM(e.amount_cents) AS total FROM expenses e JOIN categories c ON c.id = e.category_id WHERE strftime('%Y-%m', e.spent_on) = ? GROUP BY c.name ORDER BY total DESC """, (month,)).fetchall() grand = sum(total for _, total in rows) for name, total in rows: share = total / grand * 100 if grand else 0 print(f"{name[:14]:9.2f} {share:5.1f}%") print(f"{'TOTAL':9.2f}")

The strftime call chops the stored date down to year and month so every expense in March matches 2025-03. The join swaps the category id for the name a human can read. The f-string column widths line everything up in a monospace terminal, and slicing the name to 14 characters stops one long category from pushing the numbers out of alignment.

Run it and you get something like this: Eating out 214.60 31.4%, Groceries 187.15 27.4%, Transport 92.00 13.5%, and a total at the bottom. Two seconds of reading, and you know which habit to look at.

A useful variation: loop over the last six months calling the same query, and print just the totals. Trends are easier to spot as six numbers in a column than as one month in isolation.

Step four: wrap it in a small command-line menu

A while True loop with three options — add an expense, print a summary, quit — is enough interface for a personal tool. Wrap the input handling in try/except ValueError so a mistyped amount prints a friendly message instead of a traceback.

If you would rather skip menus entirely, expose the functions as command-line arguments instead: python tracker.py add 12.50 2025-03-04 Groceries "milk and bread". That version is faster to use and trivial to call from a shell alias. Keep the whole file under about a hundred lines. A graphical interface is a different project, and building one now delays the part that actually helps you: knowing where the money went.

Worthwhile upgrades

• Export to CSV with the csv module when you want to chart a year in a spreadsheet.
• Budget limits — a small table of monthly caps per category, and a warning line when a summary exceeds one.
• Recurring expenses such as rent and subscriptions, inserted by a function that runs once a month.
• Tests against an in-memory database using sqlite3.connect(":memory:"), so experiments never touch your real file.
• A backup habit — copy expenses.db somewhere safe once a week; it is a single file, so this takes a second.

Make it a habit, not a weekend project

A tracker that is only half filled in gives confident wrong answers, which is worse than no tracker at all. Pick a fixed moment — Sunday evening, five minutes, receipts from the week — and enter everything at once. Keep the category list under about ten names; a list of thirty becomes a decision you avoid making, and avoided decisions end up in "Fun" or nowhere.

Print the summary on the first of each month and read it before you do anything else with the numbers. If one category looks wrong, the note column usually explains it.

If you only do one thing after finishing this project, make it the weekly entry habit. The code took an afternoon; the habit is what turns it into something worth keeping.

From here the schema ports almost unchanged to Postgres if you ever want a web front end, and the functions you wrote stay useful as the core of a larger tool. Start with the file on your disk, one month of data, and a single command that tells you the truth.

Photo: https://kaboompics.com/ / Pexels