SQLite with Python — the sqlite3 Module

• Connect to a SQLite file with sqlite3.connect.
• Open a cursor and run SQL with cur.execute(sql).
• Fetch results with fetchall / fetchone.
• Commit writes; use parameter placeholders (?) to prevent SQL injection.

SQLite stores a whole database in one file. No server, no setup, no password. Open VS Code, create school.db implicitly:

import sqlite3

con = sqlite3.connect("school.db")
print(con)
con.close()

That run creates school.db on disk (empty) — and gives you back a connection. Two more commands open a cursor and run SQL.

Create + insert

import sqlite3

con = sqlite3.connect("school.db")
cur = con.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id    INTEGER PRIMARY KEY,
        name  TEXT,
        score INTEGER
    )
""")

cur.execute("INSERT INTO students (name, score) VALUES (?, ?)",
            ("Aisyah", 88))

con.commit()      # writes are not saved until commit
con.close()

Read

con = sqlite3.connect("school.db")
cur = con.cursor()
cur.execute("SELECT id, name, score FROM students")
print(cur.fetchall())
con.close()

[(1, 'Aisyah', 88)]

Each row is a tuple. Use fetchone() for a single row, fetchall() for everything.

Parameters — the only safe way to plug values in

# ✗ Wrong — opens you to SQL injection
cur.execute(f"SELECT * FROM students WHERE name = '{user_input}'")

# ✓ Right
cur.execute("SELECT * FROM students WHERE name = ?", (user_input,))

The ? is a placeholder. The driver escapes the value for you — even if user_input is "Bobby'; DROP TABLE students;--" it's just a name to look up.

Many rows at once — executemany

rows = [("Wei Jie", 75), ("Suresh", 92), ("Mei", 80)]
cur.executemany("INSERT INTO students (name, score) VALUES (?, ?)", rows)
con.commit()

Connection as a context manager

The cleanest pattern — auto-commit on success, auto-rollback on exception:

with sqlite3.connect("school.db") as con:
    cur = con.cursor()
    cur.execute("UPDATE students SET score = score + 5 WHERE name = ?", ("Aisyah",))
# commit happens automatically when the block exits cleanly

Bonus — rows as dicts

con.row_factory = sqlite3.Row  # rows behave like dicts AND tuples
cur = con.cursor()
cur.execute("SELECT * FROM students LIMIT 1")
row = cur.fetchone()
print(row["name"], row["score"])

# csv_to_sqlite.py — load sales.csv into a sales table
import csv, sqlite3
from pathlib import Path

DB = Path("shop.db")
CSV = Path("sales.csv")

with sqlite3.connect(DB) as con:
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS sales")
    cur.execute("""
        CREATE TABLE sales (
            id       INTEGER PRIMARY KEY AUTOINCREMENT,
            date     TEXT,
            product  TEXT,
            quantity INTEGER,
            price    REAL
        )
    """)

    with CSV.open(newline="") as f:
        rows = [
            (r["date"], r["product"], int(r["quantity"]), float(r["price"]))
            for r in csv.DictReader(f)
        ]
    cur.executemany(
        "INSERT INTO sales (date, product, quantity, price) VALUES (?,?,?,?)",
        rows,
    )

    cur.execute("SELECT COUNT(*) FROM sales")
    print(f"loaded {cur.fetchone()[0]} rows into {DB}")

    cur.execute("""
        SELECT product, SUM(quantity*price) AS revenue
        FROM sales
        GROUP BY product
        ORDER BY revenue DESC
    """)
    print("\nrevenue by product:")
    for product, rev in cur.fetchall():
        print(f"  {product:<8} RM {rev:.2f}")

Sample output

loaded 6 rows into shop.db

revenue by product:
  nasi     RM 24.00
  milo     RM 18.00
  roti     RM 12.00

Read the diff

Three reusable habits: drop-and-recreate the table for clean reloads, executemany for bulk inserts, and writing the aggregate query in pure SQL instead of looping in Python. The CSV is now a queryable database — Lesson 23 turns this into a full CRUD app.

Five steps: connect, cursor, execute, fetch, commit. ? placeholders are mandatory; never paste user input into SQL strings. executemany is the fast path for bulk inserts. with sqlite3.connect(...) handles commit/rollback. Tomorrow we package these into a full CRUD program.