Week 6 Code Examples: Databases & SQLite

Databases solve a problem you've already hit: data disappears when your program stops. This week you'll use Python's built-in sqlite3 module to read and write a real database — no extra installation needed.

How to use this page: Copy any example into a .py file and run it. SQLite creates a .db file automatically in the same directory. Each example builds on the one before it, so read them in order.

1. What Is a Database and Why Use One?

A database stores data in structured tables — like a spreadsheet. Each table has named columns (fields) and rows (records). Unlike a JSON file, a database can handle millions of records efficiently and lets you query specific subsets instantly.

Key concept — Persistence: Data in a Python list or dictionary vanishes when the program exits. Data in a database file survives indefinitely. Adding a database is how you turn a toy program into a real application.

Files vs. databases — when to use each

          # JSON file — good for:
#   - Small amounts of data (hundreds of records, not thousands)
#   - Simple structure (no relationships between data)
#   - Human-readable backup/config files
#   - Quick prototypes

# SQLite database — better when:
#   - You need to search / filter records efficiently
#   - Data has relationships (e.g. orders belong to customers)
#   - You need to update individual records without rewriting everything
#   - Data will grow large over time
        

2. Connecting to SQLite and Creating Tables

sqlite3.connect() opens a database file (or creates it if it doesn't exist). You then create a cursor to send SQL commands.

          import sqlite3

# Connect — creates "library.db" if it doesn't exist
conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# CREATE TABLE IF NOT EXISTS — safe to run every startup
cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        title   TEXT    NOT NULL,
        author  TEXT    NOT NULL,
        pages   INTEGER NOT NULL,
        genre   TEXT
    )
""")

conn.commit()   # save changes
conn.close()    # always close when done
print("Database ready.")
        

Always call conn.commit() after INSERT, UPDATE, or DELETE — otherwise your changes are not saved to disk. conn.close() should always be the last step.

Column types in SQLite

          # SQLite has four main column types:
#
#   INTEGER  — whole numbers (1, 42, -7)
#   REAL     — decimal numbers (3.14, 99.99)
#   TEXT     — strings ("Alice", "2026-03-10")
#   BLOB     — raw binary data (images, files)
#
# Common constraints:
#   PRIMARY KEY AUTOINCREMENT — unique ID, auto-increments
#   NOT NULL                  — value is required
#   UNIQUE                    — no two rows can have the same value
        

3. Inserting Rows — Always Use Parameterized Queries

Parameterized queries use ? placeholders instead of putting values directly into the SQL string. This is not optional — it's how you prevent SQL injection.

SQL injection — never do this: cursor.execute(f"INSERT INTO books (title) VALUES ('{title}')")
If title contains '); DROP TABLE books; --, you've just deleted your database. Always use ? placeholders.

Inserting one row

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# The tuple of values matches the ? placeholders positionally
cursor.execute(
    "INSERT INTO books (title, author, pages, genre) VALUES (?, ?, ?, ?)",
    ("The Martian", "Andy Weir", 369, "Sci-Fi")
)

conn.commit()

# cursor.lastrowid — the auto-generated ID of the row just inserted
print(f"Inserted with ID: {cursor.lastrowid}")

conn.close()
        

Inserting multiple rows at once

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

books = [
    ("Clean Code",           "Robert Martin",  431, "Programming"),
    ("The Pragmatic Programmer", "David Thomas", 352, "Programming"),
    ("Dune",                 "Frank Herbert",  412, "Sci-Fi"),
]

cursor.executemany(
    "INSERT INTO books (title, author, pages, genre) VALUES (?, ?, ?, ?)",
    books
)

conn.commit()
print(f"Inserted {cursor.rowcount} rows")
conn.close()
        

4. Querying Rows — SELECT

SELECT retrieves rows from a table. Use fetchall() to get every match, fetchone() to get only the first.

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# Select all rows
cursor.execute("SELECT * FROM books")
rows = cursor.fetchall()
for row in rows:
    book_id, title, author, pages, genre = row
    print(f"  [{book_id}] {title} by {author}")

conn.close()
        

Filtering with WHERE

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# All books in a specific genre
cursor.execute("SELECT * FROM books WHERE genre = ?", ("Sci-Fi",))
scifi_books = cursor.fetchall()
print(f"Sci-Fi books: {len(scifi_books)}")

# Books over 400 pages
cursor.execute("SELECT title, pages FROM books WHERE pages > ?", (400,))
long_books = cursor.fetchall()
for title, pages in long_books:
    print(f"  {title}: {pages} pages")

# Find one specific book by title
cursor.execute("SELECT * FROM books WHERE title = ?", ("Dune",))
book = cursor.fetchone()   # returns one tuple, or None if not found
if book:
    print(f"Found: {book[1]} (ID {book[0]})")

conn.close()
        

Sorting and limiting results

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# Sort by pages, longest first; show only top 3
cursor.execute("""
    SELECT title, pages
    FROM books
    ORDER BY pages DESC
    LIMIT 3
""")
top_three = cursor.fetchall()
for title, pages in top_three:
    print(f"  {title}: {pages} pages")

conn.close()
        

5. Updating and Deleting Rows

UPDATE changes existing rows. DELETE removes them. Both require a WHERE clause — otherwise you change or delete every row in the table.

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# UPDATE — change the genre of one book
cursor.execute(
    "UPDATE books SET genre = ? WHERE title = ?",
    ("Classic Sci-Fi", "Dune")
)
print(f"Rows updated: {cursor.rowcount}")

# UPDATE multiple columns at once
cursor.execute(
    "UPDATE books SET pages = ?, genre = ? WHERE title = ?",
    (370, "Sci-Fi Thriller", "The Martian")
)

# DELETE a specific row
cursor.execute("DELETE FROM books WHERE title = ?", ("Clean Code",))
print(f"Rows deleted: {cursor.rowcount}")

conn.commit()
conn.close()
        

6. Two Tables — Foreign Keys and Relationships

Real databases split related data across multiple tables and link them with foreign keys. This avoids storing the same information repeatedly and keeps data consistent.

Key concept: A foreign key in one table is a reference to the primary key in another. It's how you express "this row belongs to that row."

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

# First table: members
cursor.execute("""
    CREATE TABLE IF NOT EXISTS members (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT    NOT NULL
    )
""")

# Second table: checkouts — each row links a member to a book
cursor.execute("""
    CREATE TABLE IF NOT EXISTS checkouts (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        member_id   INTEGER NOT NULL,
        book_title  TEXT    NOT NULL,
        checked_out TEXT    NOT NULL,
        FOREIGN KEY (member_id) REFERENCES members(id)
    )
""")

conn.commit()

# Insert a member and a checkout
cursor.execute("INSERT INTO members (name) VALUES (?)", ("Alice",))
member_id = cursor.lastrowid

cursor.execute(
    "INSERT INTO checkouts (member_id, book_title, checked_out) VALUES (?, ?, ?)",
    (member_id, "Dune", "2026-03-10")
)

conn.commit()

# Query with a JOIN — combine rows from both tables
cursor.execute("""
    SELECT members.name, checkouts.book_title, checkouts.checked_out
    FROM checkouts
    JOIN members ON checkouts.member_id = members.id
""")
for name, book, date in cursor.fetchall():
    print(f"  {name} checked out '{book}' on {date}")

conn.close()
        

7. INSERT OR REPLACE — Saving Without Duplicates

When saving an object that might already exist in the database, INSERT OR REPLACE handles both cases: insert if new, replace if the primary key already exists.

          import sqlite3

conn   = sqlite3.connect("library.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS settings (
        key   TEXT PRIMARY KEY,
        value TEXT NOT NULL
    )
""")

# First run: inserts. Every run after: replaces.
cursor.execute(
    "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
    ("theme", "dark")
)
cursor.execute(
    "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
    ("font_size", "14")
)

conn.commit()

cursor.execute("SELECT * FROM settings")
for key, value in cursor.fetchall():
    print(f"  {key} = {value}")

conn.close()
        

8. Connecting OOP to a Database Layer

In a real application, your OOP classes handle business logic and a separate database class handles persistence. They talk to each other but each does only its own job.

Key concept — Separation of concerns: The Book class knows nothing about SQL. The BookDB class knows nothing about book behavior. When you need to swap the database out, you only change BookDB.

          import sqlite3

# ── Business logic class ──────────────────────────────────────────────────────
class Book:
    def __init__(self, title, author, pages):
        self._title  = title
        self._author = author
        self._pages  = pages

    def summary(self):
        return f"'{self._title}' by {self._author} ({self._pages} pages)"

    def __str__(self):
        return self.summary()


# ── Database layer class ──────────────────────────────────────────────────────
class BookDB:
    def __init__(self, filename):
        self._conn   = sqlite3.connect(filename)
        self._cursor = self._conn.cursor()
        self._create_table()

    def _create_table(self):
        self._cursor.execute("""
            CREATE TABLE IF NOT EXISTS books (
                id     INTEGER PRIMARY KEY AUTOINCREMENT,
                title  TEXT    NOT NULL,
                author TEXT    NOT NULL,
                pages  INTEGER NOT NULL
            )
        """)
        self._conn.commit()

    def save(self, book):
        self._cursor.execute(
            "INSERT INTO books (title, author, pages) VALUES (?, ?, ?)",
            (book._title, book._author, book._pages)
        )
        self._conn.commit()

    def load_all(self):
        self._cursor.execute("SELECT title, author, pages FROM books")
        return [Book(t, a, p) for t, a, p in self._cursor.fetchall()]

    def find_by_author(self, author):
        self._cursor.execute(
            "SELECT title, author, pages FROM books WHERE author = ?",
            (author,)
        )
        return [Book(t, a, p) for t, a, p in self._cursor.fetchall()]

    def close(self):
        self._conn.close()


# ── Using them together ───────────────────────────────────────────────────────
db = BookDB("catalog.db")

db.save(Book("The Martian",  "Andy Weir",     369))
db.save(Book("Project Hail Mary", "Andy Weir", 476))
db.save(Book("Dune",         "Frank Herbert", 412))

print("All books:")
for book in db.load_all():
    print(f"  {book}")

print("\nBooks by Andy Weir:")
for book in db.find_by_author("Andy Weir"):
    print(f"  {book}")

db.close()
        

Notice: load_all() returns a list of Book objects — not raw database tuples. The rest of the program works with Book objects and never sees any SQL. This clean separation is the pattern you'll apply in the assignment.