Day 26 — SQLite

What is it? #

SQLite is a full SQL database that lives in a single file. There is no server to install or start, and Python ships with a driver, so it takes zero setup.

You interact with it through SQL: CREATE TABLE to define structure, INSERT to add rows, SELECT to read, UPDATE and DELETE to change.

The single most important habit is parameterised queries. You pass values separately from the SQL text, which makes SQL injection impossible. Building SQL by string formatting is the most common serious security bug in beginner code.

Transactions group changes so they either all apply or none do. That is what stops a half-finished transfer leaving money missing.

Think of it like this #

A spreadsheet is fine until two people edit it, or until you need to find "all orders over ₹1000 placed last week" in a file with 200,000 rows. A database is a spreadsheet that enforces structure, answers questions quickly, and never leaves a row half-written.

Simple example #

You are building a small expense tracker. You need a table, a way to add expenses safely, a query with a filter and a sum, and an update that either fully succeeds or rolls back.

Code #

import sqlite3
from contextlib import closing

conn = sqlite3.connect("expenses.db")
conn.row_factory = sqlite3.Row          # rows behave like dictionaries

with closing(conn.cursor()) as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS expenses (
            id       INTEGER PRIMARY KEY AUTOINCREMENT,
            category TEXT    NOT NULL,
            amount   REAL    NOT NULL CHECK (amount >= 0),
            spent_on TEXT    NOT NULL
        )
    """)
    cur.execute("CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses(category)")

# Safe insert — values passed separately, never formatted into the SQL
rows = [
    ("food", 450.0, "2026-09-20"),
    ("travel", 1200.0, "2026-09-21"),
    ("food", 300.0, "2026-09-22"),
]
with conn:                               # commits, or rolls back on error
    conn.executemany(
        "INSERT INTO expenses (category, amount, spent_on) VALUES (?, ?, ?)",
        rows,
    )

# Query with a filter
cur = conn.execute(
    "SELECT category, SUM(amount) AS total FROM expenses "
    "WHERE spent_on >= ? GROUP BY category ORDER BY total DESC",
    ("2026-09-20",),
)
for row in cur:
    print(row["category"], row["total"])

# NEVER do this — a value like "'; DROP TABLE expenses; --" would execute
# conn.execute(f"SELECT * FROM expenses WHERE category = '{user_input}'")

# Transaction: both updates apply, or neither does
try:
    with conn:
        conn.execute("UPDATE expenses SET amount = amount - 100 WHERE id = 1")
        conn.execute("UPDATE expenses SET amount = amount + 100 WHERE id = 999999")
except sqlite3.Error as exc:
    print("Rolled back:", exc)

conn.close()

How it works #

sqlite3.connect("expenses.db") opens the file, creating it if needed. Everything lives in that one file, which you can copy, back up or delete.

conn.row_factory = sqlite3.Row makes rows accessible by column name, so row["category"] works instead of row[0]. Positional access breaks the moment someone reorders the SELECT.

The CREATE TABLE defines types and constraints. NOT NULL and CHECK (amount >= 0) push validation into the database, so bad data cannot get in even through another program.

The index on category makes filtering by category fast once the table grows. Indexes are covered properly in the System Design track.

The ? placeholders are the critical part. The driver sends the SQL and the values separately, so a value containing SQL syntax is treated as text, not as code. The commented-out f-string line is exactly how SQL injection happens.

with conn: opens a transaction. If the block finishes normally it commits; if an exception escapes, everything in the block is rolled back. That is why the failed second update leaves the first one undone rather than half-applying the transfer.

executemany runs the same statement for many rows in one round trip, which is much faster than a loop of individual inserts.

Real-world use #

SQLite is not a toy. It powers mobile apps, desktop software, browsers and plenty of small production services. It is a good fit whenever you have a single machine and modest write concurrency.

When you need many machines writing at once, you move to PostgreSQL or MySQL. Everything you learned here transfers — the SQL, the parameters, the transactions — only the connection setup changes.

In real projects most people use an ORM such as SQLAlchemy or Django's, which generates SQL for you. Knowing raw SQL still matters: when a query is slow, you have to read what the ORM produced and fix it.

The parameterised query habit is non-negotiable. SQL injection remains one of the most exploited vulnerabilities on the web, and the fix has been the same for thirty years.

Common mistakes #

• Building SQL with f-strings or concatenation. Always use ? placeholders.
• Forgetting to commit. Without with conn: or conn.commit(), your changes vanish.
• Reading rows positionally with row[0], which breaks when the SELECT changes.
• Running inserts one at a time in a loop when executemany would be far faster.
• Using SQLite for an application with many concurrent writers — it locks the whole database for writes.

Practice #

Build a small notes.db with a table of notes (id, title, body, created_on). Insert three rows using parameters, query notes whose title contains a word using a LIKE ? parameter, update one inside a transaction, and print all rows using column names.