SQLite with Python in 2026: Build a Real Expense Tracker (Step-by-Step)

Stop reading theory. Build a real database-powered app with Python and SQLite — start to finish.

If you've spent the last hour searching "SQLite Python tutorial," you've probably noticed something frustrating: most guides teach you commands in isolation. CREATE TABLE here, INSERT INTO there — and at the end, you still don't know how to actually use SQLite for anything real.

That's exactly why I'm writing this differently. In the next 40 minutes, we're going to build a working personal expense tracker — a small but complete Python application that stores your spending, queries it intelligently, and protects against the most common mistakes I see beginners make. By the end, you'll have something you can actually run on your own machine, not just a pile of disconnected snippets.

I'm going to skip the textbook fluff and focus on what matters in 2026: the modern way to use the sqlite3 module that ships with Python today, parameterized queries from line one (not as an afterthought), proper error handling, and the small habits that separate a hobby script from a reliable program.

I'm Mostafa Amaan, and on Valley4Techs I write practical tech and programming guides built around real projects. Open your terminal — let's get to work.

Why SQLite (and Why Python's Built-In Module Is All You Need)

Before we write any code, let's get one thing straight. SQLite is not a "lite" or "stripped-down" database — it powers your phone's contacts app, your browser's history, most desktop apps you use daily, and a surprising number of production web services. It's the most deployed database engine in the world, and it does something most databases can't: it lives entirely inside a single file you can copy, email, or back up.

For our expense tracker, SQLite is perfect because:

• No server, no setup. No Docker container to spin up, no port to manage. Just a file.
• It ships with Python. The sqlite3 module has been part of the standard library since Python 2.5. You don't install anything.
• It scales further than you think. SQLite handles databases up to 281 TB and millions of rows comfortably. For personal projects and many production cases, you simply don't need anything else.
• It teaches real SQL. The skills you build here transfer 1:1 to PostgreSQL or MySQL when you eventually need them.

If you're still deciding between database engines for a future project, take a look at our MySQL vs PostgreSQL vs SQLite comparison — it'll save you from picking the wrong tool.

Setting Up Your Environment (5 Minutes)

Here's the entire setup process. Open your terminal and verify Python is installed:

python --version
# Expected output: Python 3.13.x or newer

In 2026, Python 3.13 is the stable baseline and 3.14 is the current release. Anything newer than 3.10 will work fine for this tutorial. If you're on an older version, update it — the sqlite3 module has improved significantly in recent releases.

Now let's confirm SQLite itself is available. In Python's interactive shell:

import sqlite3
print(sqlite3.sqlite_version)   # The SQLite engine version
print(sqlite3.version_info)     # The Python binding version

That's it for setup. No pip install needed. Create a folder called expense_tracker and a file inside it called tracker.py — that's where everything we write today will live.

The Project: A Personal Expense Tracker

Let me describe what we're building so the code makes sense as we go. Our expense tracker will:

• Store every expense with an amount, category, description, and date.
• Let you add expenses quickly from the terminal.
• Show you totals by category (where is your money actually going?).
• Filter expenses by date range — useful for end-of-month reviews.
• Update or delete expenses if you make a mistake.

By the end, you'll have the foundation for a real personal-finance tool you can extend with charts, exports, or even a simple web interface later. Here's the database schema we'll use:

Step 1: Connect to the Database the Modern Way

Let's start with the most-skipped detail in beginner tutorials: how to open and close a database connection safely. The lazy way looks like this:

conn = sqlite3.connect("expenses.db")
cur = conn.cursor()
# ... do stuff ...
conn.commit()
conn.close()

The problem? If anything between connect() and close() raises an exception, the connection stays open, the database file may stay locked, and your changes might not commit. I've debugged this exact issue more times than I want to admit. The fix is the with statement, which guarantees commit-or-rollback on exit:

import sqlite3
from contextlib import contextmanager

DB_PATH = "expenses.db"

@contextmanager
def get_connection():
    """Yields a SQLite connection that auto-commits on success, rolls back on error."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row   # Access columns by name, not index
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

Two things worth highlighting here. First, conn.row_factory = sqlite3.Row is a small line with a big impact — it lets you access columns by name (row["amount"]) instead of by numeric index. Your future self will thank you when you have ten columns and can't remember which is which.

Second, the rollback in the except block is what makes this safe. If an insert fails halfway through a batch, you won't end up with a half-written database. That's the kind of small reliability win that adds up.

Step 2: Create the Table

Now we'll add a function that creates our schema if it doesn't already exist. The IF NOT EXISTS clause is important — it means we can call this function every time the app starts without errors, and the table is only created the first time:

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS expenses (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    amount      REAL    NOT NULL CHECK (amount > 0),
    category    TEXT    NOT NULL,
    description TEXT,
    spent_on    TEXT    NOT NULL DEFAULT (date('now'))
);

CREATE INDEX IF NOT EXISTS idx_expenses_date ON expenses (spent_on);
CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses (category);
"""

def init_db():
    with get_connection() as conn:
        conn.executescript(SCHEMA_SQL)
    print("Database ready.")

Three details that beginner tutorials almost always miss:

• CHECK (amount > 0) — a constraint that rejects nonsense data at the database level. You can never accidentally insert a negative expense, even if your Python code has a bug.
• DEFAULT (date('now')) — if you don't supply a date, SQLite stamps today's date automatically. Less work for the caller.
• The two indexes — without them, queries that filter by date or category have to scan the entire table. With them, lookups stay fast even after thousands of expenses. Indexes are the single biggest performance lever in any database.

Step 3: Insert Data Safely (Parameterized Queries from Day One)

Here's the most important rule of writing SQL in any language: never build queries with string formatting or f-strings. Ever. The wrong way looks like this:

# SQL injection waiting to happen
cur.execute(f"INSERT INTO expenses (category) VALUES ('{user_input}')")

If user_input contains '); DROP TABLE expenses;--, you've just deleted your data. This isn't theoretical — it's the most common security flaw on the web. The right way is to use parameter placeholders and let SQLite handle escaping:

def add_expense(amount, category, description=None, spent_on=None):
    """Insert a new expense. Returns the new row's ID."""
    sql = """
        INSERT INTO expenses (amount, category, description, spent_on)
        VALUES (?, ?, ?, COALESCE(?, date('now')))
    """
    with get_connection() as conn:
        cur = conn.execute(sql, (amount, category, description, spent_on))
        return cur.lastrowid

# Example: Add a few expenses
add_expense(12.50, "Food",      "Lunch at the deli")
add_expense(45.00, "Transport", "Train ticket")
add_expense(89.99, "Bills",     "Electricity")

The ? placeholders are the safe way. Each one is replaced with the corresponding tuple value, fully escaped. This isn't just a security thing — it's also faster, because SQLite can cache the compiled query plan and reuse it for multiple inserts.

Inserting Many Rows at Once

If you're importing a list of expenses (say, from a CSV), use executemany. It's dramatically faster than a Python loop because it sends one prepared statement and lets SQLite handle the iteration:

def add_many(rows):
    """Insert many expenses at once. `rows` is a list of (amount, category, description, spent_on) tuples."""
    sql = """
        INSERT INTO expenses (amount, category, description, spent_on)
        VALUES (?, ?, ?, COALESCE(?, date('now')))
    """
    with get_connection() as conn:
        conn.executemany(sql, rows)

# Insert 100 rows in a single transaction — much faster than 100 add_expense() calls
batch = [
    (5.50,  "Food",      "Coffee",        "2026-04-01"),
    (22.00, "Transport", "Uber",          "2026-04-02"),
    (15.30, "Food",      "Groceries",     None),
    # ... 97 more rows
]
add_many(batch)

Step 4: Read and Filter Your Data

Now the rewarding part — actually getting answers from your data. We'll write three query functions: one to list recent expenses, one to summarize spending by category, and one to filter by date range.

def list_recent(limit=10):
    """Return the most recent expenses as a list of Row objects."""
    sql = """
        SELECT id, amount, category, description, spent_on
        FROM expenses
        ORDER BY spent_on DESC, id DESC
        LIMIT ?
    """
    with get_connection() as conn:
        return conn.execute(sql, (limit,)).fetchall()


def total_by_category():
    """Return [(category, total_amount, count), ...] sorted by spend, biggest first."""
    sql = """
        SELECT category,
               ROUND(SUM(amount), 2) AS total,
               COUNT(*)              AS num_transactions
        FROM expenses
        GROUP BY category
        ORDER BY total DESC
    """
    with get_connection() as conn:
        return conn.execute(sql).fetchall()


def expenses_between(start_date, end_date):
    """Return all expenses within an inclusive date range (ISO strings 'YYYY-MM-DD')."""
    sql = """
        SELECT * FROM expenses
        WHERE spent_on BETWEEN ? AND ?
        ORDER BY spent_on
    """
    with get_connection() as conn:
        return conn.execute(sql, (start_date, end_date)).fetchall()

Putting it to use is satisfyingly clean:

print("Top spending categories this month:")
for row in total_by_category():
    print(f"  {row['category']:<12} ${row['total']:>8.2f}  ({row['num_transactions']} transactions)")

# Sample output:
#   Bills        $  340.50  (4 transactions)
#   Food         $  187.30  (22 transactions)
#   Transport    $   95.00  (8 transactions)

Notice how row['category'] works thanks to the row_factory we set up in Step 1. If you forgot that, you'd be stuck with row[0], row[1], and so on — readable code thrown out the window.

Step 5: Update and Delete Records (Carefully)

Updates and deletes are where beginners do the most damage. The single rule that will save you a hundred headaches: always include a WHERE clause. Without one, an UPDATE rewrites every row in the table, and a DELETE wipes everything. SQLite won't ask for confirmation.

def update_amount(expense_id, new_amount):
    """Update the amount of a single expense. Returns True if a row was updated."""
    sql = "UPDATE expenses SET amount = ? WHERE id = ?"
    with get_connection() as conn:
        cur = conn.execute(sql, (new_amount, expense_id))
        return cur.rowcount > 0


def delete_expense(expense_id):
    """Delete an expense by ID. Returns True if it actually deleted something."""
    sql = "DELETE FROM expenses WHERE id = ?"
    with get_connection() as conn:
        cur = conn.execute(sql, (expense_id,))
        return cur.rowcount > 0


# Usage with safety check
if not update_amount(7, 14.75):
    print("No expense with id=7 was found. Nothing changed.")

The cur.rowcount check is something you'll want in nearly every update or delete function. It tells you whether the operation actually affected anything. Without it, your code can silently "succeed" while having done nothing — a bug that's incredibly painful to track down later.

5 Mistakes I See Beginners Make Constantly

These aren't theoretical concerns — they're the issues I keep running into when reviewing other people's code. Internalize these and you'll skip the painful learning curve most developers go through.

1. "Database is locked" errors. Almost always caused by leaving connections open or forgetting to commit. The with get_connection() pattern from Step 1 fixes 95% of these. The other 5% come from running multiple processes against the same database — switch on WAL mode (PRAGMA journal_mode=WAL;) and the problem usually disappears.
2. Storing money as REAL (floating point). I just did this in our tracker because it keeps the example simple, but for any serious accounting work, store cents as INTEGER instead (1250 instead of 12.50). Floating-point math has rounding errors that compound over many transactions — a classic accounting nightmare.
3. Treating dates as random text. Always store dates in ISO 8601 format (YYYY-MM-DD) so SQLite's date functions work and string comparisons sort correctly. "April 5, 2026" might be readable, but it sorts alphabetically — which means May comes before March.
4. Skipping indexes until "later." A table with 10,000 rows and no index on the column you filter by will be slow on a \$3,000 laptop. The same query with an index runs in under a millisecond on a Raspberry Pi. Add indexes upfront for any column you'll filter or join on.
5. Not using transactions for related operations. If you're inserting an expense and updating a running total in another table, do it inside a single with get_connection() block. That way, either both succeed or neither does — your data stays consistent. This is the whole point of transactions.

Performance Tips That Actually Matter

SQLite is fast by default, but there are a few flags that turn it from "fast enough" to "almost unfair." These are the settings I add to nearly every project after the first hour:

def configure(conn):
    """Apply sensible performance pragmas to a connection."""
    conn.execute("PRAGMA journal_mode = WAL")        # Concurrent reads + better crash safety
    conn.execute("PRAGMA synchronous = NORMAL")      # Faster writes, still safe in WAL mode
    conn.execute("PRAGMA foreign_keys = ON")         # Enforce foreign keys (off by default!)
    conn.execute("PRAGMA cache_size = -64000")       # Use 64 MB of cache (negative = KB)
    conn.execute("PRAGMA temp_store = MEMORY")       # Temp tables stay in RAM

Add a call to configure(conn) inside the get_connection() helper from Step 1, right after opening the connection. The biggest single win is WAL mode — it lets multiple readers run while a writer is active, which alone solves most "database is locked" complaints.

Putting It All Together: The Full Tracker Script

Here's everything we built, condensed into one file you can save as tracker.py and run today. It's about 80 lines — small enough to read in a single sitting, useful enough to actually track your spending:

import sqlite3
from contextlib import contextmanager

DB_PATH = "expenses.db"

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS expenses (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    amount      REAL    NOT NULL CHECK (amount > 0),
    category    TEXT    NOT NULL,
    description TEXT,
    spent_on    TEXT    NOT NULL DEFAULT (date('now'))
);
CREATE INDEX IF NOT EXISTS idx_expenses_date ON expenses (spent_on);
CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses (category);
"""

@contextmanager
def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def init_db():
    with get_connection() as conn:
        conn.executescript(SCHEMA_SQL)

def add_expense(amount, category, description=None, spent_on=None):
    sql = """INSERT INTO expenses (amount, category, description, spent_on)
             VALUES (?, ?, ?, COALESCE(?, date('now')))"""
    with get_connection() as conn:
        return conn.execute(sql, (amount, category, description, spent_on)).lastrowid

def list_recent(limit=10):
    sql = "SELECT * FROM expenses ORDER BY spent_on DESC, id DESC LIMIT ?"
    with get_connection() as conn:
        return conn.execute(sql, (limit,)).fetchall()

def total_by_category():
    sql = """SELECT category, ROUND(SUM(amount), 2) AS total, COUNT(*) AS num
             FROM expenses GROUP BY category ORDER BY total DESC"""
    with get_connection() as conn:
        return conn.execute(sql).fetchall()

def update_amount(expense_id, new_amount):
    sql = "UPDATE expenses SET amount = ? WHERE id = ?"
    with get_connection() as conn:
        return conn.execute(sql, (new_amount, expense_id)).rowcount > 0

def delete_expense(expense_id):
    sql = "DELETE FROM expenses WHERE id = ?"
    with get_connection() as conn:
        return conn.execute(sql, (expense_id,)).rowcount > 0


if __name__ == "__main__":
    init_db()
    add_expense(12.50, "Food",      "Lunch")
    add_expense(45.00, "Transport", "Train ticket")
    add_expense(89.99, "Bills",     "Electricity")

    print("\nRecent expenses:")
    for row in list_recent():
        print(f"  #{row['id']:>3}  {row['spent_on']}  {row['category']:<10}  ${row['amount']:>7.2f}  {row['description'] or ''}")

    print("\nTotals by category:")
    for row in total_by_category():
        print(f"  {row['category']:<12} ${row['total']:>8.2f}  ({row['num']} transactions)")

Run it with python tracker.py and you'll see your three sample expenses listed and summarized. The expenses.db file appears in the same folder — that's your entire database, portable and ready to back up.

Where to Take This Project Next

You now have a working foundation. The fun part is extending it. Here are five directions, ordered from easiest to most ambitious:

1. CLI menu. Wrap the functions in a simple while True loop with menu options for adding, listing, and deleting. Half an hour of work, and it's an actual app.
2. CSV import/export. Use Python's built-in csv module to load expenses from a bank statement export, or push your data out for spreadsheet analysis.
3. Monthly reports. Add a function that returns spending grouped by month, then plot it with matplotlib to visualize trends over time.
4. Web interface. Wrap the functions in a small Flask or FastAPI app — the SQL layer stays the same, you just add HTTP endpoints around it.
5. Multi-user version. Add a users table and a foreign key from expenses to it. This is where you'd want to switch on PRAGMA foreign_keys = ON and start thinking about authentication.

If you're considering a move to a server-based database for one of these extensions, our guide on MySQL vs PostgreSQL vs SQLite walks through exactly when each one makes sense. And for those eyeing the cloud, our overview of cloud databases on AWS, Azure, and Google Cloud will help you make sense of the options.

Final Thoughts

The biggest favor you can do yourself when learning databases is to stop reading and start running queries. SQLite is the friendliest place on Earth to do that — no server, no install, no configuration. Just import sqlite3 and you're a database away from real software.

The patterns I've shown here — context-managed connections, parameterized queries, indexes added upfront, proper rollback on errors — aren't "advanced" tricks. They're the bare minimum for code you don't regret six months later. Beginner tutorials skip them because they're a few lines longer. Production code uses them because the alternative is bugs at 2 AM.

Take the tracker, mess with it, break it, extend it. Every database concept you'll need later — joins, transactions, schema migrations, query optimization — is reachable from here. You're not learning "SQLite for beginners." You're learning SQL the way it's actually used.

If you found this practical, a great follow-up is our programming roadmap for beginners — it shows exactly where database skills fit in the bigger picture of becoming a developer.

Frequently Asked Questions

These are the questions readers and students ask me most often. If yours isn't here, leave it in the comments and I'll add it.