04 Changing data and building projects: challenges

Create sql_changes.py , paste this complete runner and click Run Python File. Change the SQL in changes for the operation and query for the inspection SELECT. Keep setup_sql unchanged. Every run starts fresh, so each example below is independent.

This runner uses SQLite through Python, with explicit SQL transaction control. Outside an explicit BEGIN, individual completed write statements commit automatically. For an atomic multi-statement change, use BEGIN and COMMIT, with error handling that rolls back. The transaction lesson explains this distinction.

import sqlite3

# Explicit SQL transaction control; fresh data on every run.
connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys = ON")
setup_sql = """

CREATE TABLE IF NOT EXISTS Members (
    member_id INTEGER PRIMARY KEY,
    member_name TEXT NOT NULL CHECK (length(trim(member_name)) > 0)
);
CREATE TABLE IF NOT EXISTS Books (
    book_id INTEGER PRIMARY KEY,
    barcode TEXT NOT NULL UNIQUE CHECK (length(trim(barcode)) > 0),
    title TEXT NOT NULL CHECK (length(trim(title)) > 0)
);
CREATE TABLE IF NOT EXISTS Loans (
    loan_id INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES Members(member_id),
    book_id INTEGER NOT NULL REFERENCES Books(book_id),
    borrowed_on TEXT NOT NULL,
    due_on TEXT NOT NULL,
    returned_on TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS one_active_loan_per_book
ON Loans(book_id) WHERE returned_on IS NULL;

INSERT INTO Members VALUES (1, 'Maya'), (2, 'Leo'), (3, 'Amina');
INSERT INTO Books VALUES (10, 'LIB-010', 'The Code Book'),
    (11, 'LIB-011', 'The Code Book'), (12, 'LIB-012', 'Python Adventures');
INSERT INTO Loans VALUES
    (100, 1, 10, '2026-10-01', '2026-10-15', NULL),
    (101, 2, 11, '2026-09-01', '2026-09-15', '2026-09-14');

"""
changes = """
-- Put this exercise's INSERT, UPDATE, DELETE or transaction here.
"""
query = """
SELECT * FROM Loans ORDER BY loan_id;
"""

try:
    connection.executescript(setup_sql)
    connection.executescript(changes)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    if connection.in_transaction:
        connection.execute("ROLLBACK")
    print("Database error:", error)
finally:
    connection.close()

Use executescript only for fixed teaching scripts like these, not text assembled from user input. Do not call executescript in the middle of a manually controlled transaction; the complete application later uses execute for its transaction statements.

Use an explicit column list.

INSERT INTO Members (member_id, member_name) VALUES (4, 'Noah');

Target the stable ID.

UPDATE Members SET member_name = 'Leo Martin' WHERE member_id = 2;

An event is retained and its status is derived from returned_on.

UPDATE Loans SET returned_on = '2026-10-12' WHERE loan_id = 100 AND returned_on IS NULL;

Check references, not just active status.

DELETE FROM Books WHERE book_id = 12;

Book 10 is referenced by loan 100. A returned loan can also keep its parent referenced. Do not delete loan history merely to force the parent deletion.

ROLLBACK applies before commit.

BEGIN;
UPDATE Members SET member_name = 'Trial';
ROLLBACK;

The whole-table target here is deliberate and confined to the fresh practice database.

The old active event must be closed before a new active event can pass the index.

BEGIN IMMEDIATE;
UPDATE Loans SET returned_on = '2026-10-12' WHERE loan_id = 100 AND returned_on IS NULL;
INSERT INTO Loans (loan_id, member_id, book_id, borrowed_on, due_on)
VALUES (102, 3, 10, '2026-10-12', '2026-10-26');
COMMIT;

In application code, inspect the update result and roll back on a failed next statement. Do not assume a statement error undoes the entire transaction automatically.

Use one ? and a one-item tuple.

wanted = input("Member name: ")
rows = connection.execute(
    "SELECT member_id, member_name FROM Members WHERE member_name = ?",
    (wanted,),
).fetchall()

Use this snippet with an open library connection. No input text is inserted into SQL syntax.

Map choices to fixed query strings.

queries = {
    'title': "SELECT book_id, title FROM Books ORDER BY title, book_id",
    'barcode': "SELECT book_id, title FROM Books ORDER BY barcode, book_id",
}
choice = input("Sort: ").strip()
if choice not in queries:
    raise ValueError("Unknown sort option")
rows = connection.execute(queries[choice]).fetchall()

Query structure and authority answer different questions.

Binding protects SQL structure, not permission. Enforce the user’s allowed operations in the trusted application and, where supported, database roles. For ordinary SQLite, also protect file access; a hidden delete button does not prevent another write path.

Exclude returned loans; due today is not overdue under our rule.

-- Read-only report; no change statements needed.

Only loan 100 is active and overdue. In an application, bind the report date instead of constructing SQL from input.

Uniqueness applies only where returned_on IS NULL.

INSERT INTO Loans (loan_id, member_id, book_id, borrowed_on, due_on)
VALUES (102, 3, 10, '2026-10-12', '2026-10-26');

On the original state this fails the partial unique index. On a fresh run, first update loan 100’s returned_on, then this insertion succeeds. Use a transaction for the intended complete handover.

Define the added feature’s output and use parameter binding for supplied values.

A suitable extension is a member’s loan history filtered by a bound member_id, joining book titles and ordering by borrowing date and loan ID. Record expected and actual results for normal, invalid and boundary inputs. Preserve old events and verify that rejected writes do not affect existing records.

SELECT l.loan_id, b.title, l.borrowed_on, l.returned_on
FROM Loans AS l
INNER JOIN Books AS b ON l.book_id = b.book_id
WHERE l.member_id = ?
ORDER BY l.borrowed_on, l.loan_id;