04.4 Project: a library database

01 | YOUR PROJECT BRIEF

Build a local library system that records members, physical book copies and loan events. Users can add members and copies, issue books, record returns, view data and produce an overdue report.

We choose the library option for this worked project. A sports-results adaptation is suggested at the end. This is a local learning application, not an authenticated public service.

02 | DEFINE SUCCESS BEFORE CODING

• Members and physical copies have stable IDs.
• Every copy has a unique barcode; duplicate titles are allowed.
• A loan references existing member and book records.
• A copy can have at most one active loan, but many historical loans.
• Returning a copy changes its loan record without deleting history.
• Input values are bound with placeholders; dates are validated in the application.
• An overdue report includes only unreturned loans whose due date is earlier than the chosen report date.
• Records persist when the program closes and reopens.

Due today is not overdue under this definition. State a different rule explicitly if your library needs one.

03 | UNDERSTAND THE MODEL

Members → Loans and Books → Loans are one-to-many. Two copies of a title have different barcodes and IDs. A repeated borrower–book pair is allowed across history because loan_id identifies each event.

Availability is derived from active loans instead of duplicated in an available flag that could disagree with history.

04 | ENFORCE ONE ACTIVE LOAN

CREATE UNIQUE INDEX IF NOT EXISTS one_active_loan_per_book
ON Loans(book_id) WHERE returned_on IS NULL;

This SQLite partial unique index applies uniqueness only to rows meeting its WHERE condition. Returned history is outside that condition, so it does not block later borrowing.

A unique rule on book_id across all Loans would prevent historical repeat loans. A composite borrower–book key would also prevent the same person borrowing the same copy twice.

The index is an extension beyond basic table constraints. It enforces the active-loan rule in the database, so the program does not rely only on a prior “is it free?” lookup.

05 | SET UP AND RUN

Create sql_library_project.py in your VS Code project folder and paste the complete program below. Click Run Python File. It creates library.db beside the script, starting empty.

Choose 1 to add two fictional members and 2 to add two book copies. Choose 3 to view the assigned IDs, then use those IDs when choosing 4 to issue a book. Use YYYY-MM-DD dates. Choose 0 to quit cleanly.

Run again and choose 3 to confirm persistence. The program uses CREATE TABLE IF NOT EXISTS to preserve existing records, not reset them. That clause does not migrate an older incompatible schema; use a fresh project folder for this first version.

06 | COMPLETE WORKING PROGRAM

import sqlite3
from datetime import date
from pathlib import Path

SCHEMA = """
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;
"""


def open_database(path):
    # Explicit SQL transaction control throughout this program.
    connection = sqlite3.connect(path, isolation_level=None)
    connection.execute("PRAGMA foreign_keys = ON")
    connection.executescript(SCHEMA)
    return connection


def required_text(prompt):
    value = input(prompt).strip()
    if not value:
        raise ValueError("Please enter a non-blank value.")
    return value


def read_id(prompt):
    value = int(input(prompt))
    if value < 1:
        raise ValueError("IDs must be positive integers.")
    return value


def read_date(prompt):
    value = input(prompt).strip()
    parsed = date.fromisoformat(value)
    if parsed.isoformat() != value:
        raise ValueError("Use YYYY-MM-DD.")
    return parsed


def issue_book(connection, member_id, book_id, borrowed, due):
    if due < borrowed:
        raise ValueError("Due date cannot be before the loan date.")
    connection.execute("BEGIN IMMEDIATE")
    try:
        cursor = connection.execute(
            "INSERT INTO Loans (member_id, book_id, borrowed_on, due_on) "
            "VALUES (?, ?, ?, ?)",
            (member_id, book_id, borrowed.isoformat(), due.isoformat()),
        )
        loan_id = cursor.lastrowid
        connection.execute("COMMIT")
        return loan_id
    except Exception:
        if connection.in_transaction:
            connection.execute("ROLLBACK")
        raise


def return_book(connection, loan_id, returned):
    connection.execute("BEGIN IMMEDIATE")
    try:
        row = connection.execute(
            "SELECT borrowed_on FROM Loans "
            "WHERE loan_id = ? AND returned_on IS NULL", (loan_id,)
        ).fetchone()
        if row is None:
            raise ValueError("No active loan with that ID.")
        if returned < date.fromisoformat(row[0]):
            raise ValueError("Return date cannot be before the loan date.")
        connection.execute(
            "UPDATE Loans SET returned_on = ? "
            "WHERE loan_id = ? AND returned_on IS NULL",
            (returned.isoformat(), loan_id),
        )
        connection.execute("COMMIT")
    except Exception:
        if connection.in_transaction:
            connection.execute("ROLLBACK")
        raise


def print_rows(connection, sql, values=()):
    cursor = connection.execute(sql, values)
    print(" | ".join(column[0] for column in cursor.description))
    rows = cursor.fetchall()
    if not rows:
        print("No matching records.")
    for row in rows:
        print(row)


def list_data(connection):
    print("\nMEMBERS")
    print_rows(connection, "SELECT * FROM Members ORDER BY member_id")
    print("\nBOOKS: one row per physical copy")
    print_rows(connection, "SELECT * FROM Books ORDER BY book_id")
    print("\nLOAN HISTORY")
    print_rows(connection, "SELECT * FROM Loans ORDER BY loan_id")


def overdue_report(connection, as_of):
    # Fixed SQL written by us; only the comparison date is supplied as data.
    print_rows(
        connection,
        "SELECT l.loan_id, m.member_name, b.barcode, b.title, l.due_on "
        "FROM Loans AS l "
        "INNER JOIN Members AS m ON l.member_id = m.member_id "
        "INNER JOIN Books AS b ON l.book_id = b.book_id "
        "WHERE l.returned_on IS NULL AND l.due_on < ? "
        "ORDER BY l.due_on, l.loan_id",
        (as_of.isoformat(),),
    )


def main():
    path = Path(__file__).with_name("library.db")
    connection = open_database(path)
    print("Database:", path)
    print("Starts empty. Add members and physical books before issuing a loan.")
    try:
        while True:
            print("\n1 Add member | 2 Add book | 3 List data")
            print("4 Issue book | 5 Return book | 6 Overdue report | 0 Quit")
            choice = input("Choose: ").strip()
            try:
                if choice == "0":
                    break
                elif choice == "1":
                    name = required_text("Member name: ")
                    cursor = connection.execute(
                        "INSERT INTO Members (member_name) VALUES (?)", (name,)
                    )
                    print("New member ID:", cursor.lastrowid)
                elif choice == "2":
                    barcode = required_text("Unique barcode: ")
                    title = required_text("Book title: ")
                    cursor = connection.execute(
                        "INSERT INTO Books (barcode, title) VALUES (?, ?)",
                        (barcode, title),
                    )
                    print("New book ID:", cursor.lastrowid)
                elif choice == "3":
                    list_data(connection)
                elif choice == "4":
                    member_id = read_id("Member ID: ")
                    book_id = read_id("Physical book ID: ")
                    borrowed = read_date("Loan date (YYYY-MM-DD): ")
                    due = read_date("Due date (YYYY-MM-DD): ")
                    loan_id = issue_book(connection, member_id, book_id, borrowed, due)
                    print("New loan ID:", loan_id)
                elif choice == "5":
                    loan_id = read_id("Loan ID: ")
                    returned = read_date("Return date (YYYY-MM-DD): ")
                    return_book(connection, loan_id, returned)
                    print("Return recorded; history retained.")
                elif choice == "6":
                    as_of = read_date("Report date (YYYY-MM-DD): ")
                    overdue_report(connection, as_of)
                else:
                    print("Choose a listed option.")
            except ValueError as error:
                print("Input problem:", error)
            except sqlite3.IntegrityError as error:
                print("Database rule rejected the change:", error)
            except sqlite3.Error as error:
                print("Database problem:", error)
    finally:
        connection.close()


if __name__ == "__main__":
    main()

07 | TRACE AN ISSUE AND RETURN

issue_book checks date order, begins a write transaction and inserts a parameterised loan. Foreign keys reject unknown members or copies, and the partial index rejects a copy already on loan. Failure rolls back the active transaction.

return_book finds an active loan, validates the return date against its borrowing date, updates that event and commits. A repeated return is rejected because there is no longer an active loan with that ID.

The broad exception handler inside these functions rolls back and re-raises; it does not silently hide errors. The menu catches expected input and database problems to provide a readable message.

08 | READ THE OVERDUE REPORT

SELECT l.loan_id, m.member_name, b.barcode, b.title, l.due_on
FROM Loans AS l
INNER JOIN Members AS m ON l.member_id = m.member_id
INNER JOIN Books AS b ON l.book_id = b.book_id
WHERE l.returned_on IS NULL AND l.due_on < ?
ORDER BY l.due_on, l.loan_id;

The placeholder receives the chosen report date. Dates stored through the application use a consistent validated ISO date representation, so lexical comparison works for this supported format.

The joins produce readable names and titles. Filtering removes returned loans and dates not yet overdue. A unique loan-ID tie-breaker makes ordering stable.

09 | TEST THE SYSTEM

Also test an apostrophe in a member name, the due-today boundary and a failed operation followed by another valid action. Ensure a rejected transaction does not leave the connection stuck in an active transaction.

10 | KNOW THE LIMITS

There are no accounts, roles, notifications, fines or reservation queue. Validating names as non-blank does not prove identity. The application validates dates, while the database stores them as text; another program writing directly could bypass those application checks.

File access and backups are not managed by the menu. Multi-user deployment needs a separate design for access, privacy, operational reliability and concurrency. This program demonstrates a bounded local workflow.

11 | EXTEND OR ADAPT

Add a member-search feature using a bound LIKE pattern, a count of active loans per member including zero, or an available-copies report. Specify the expected output before coding.

For a sports adaptation, model Teams, Matches and Results. Decide whether a result row means one team’s participation in a match or a complete match score. Define keys and uniqueness accordingly; do not mechanically rename library tables without changing the rules.

Submit the schema, working program, sample data and a test record. Explain one constraint, one join, one transaction and one permission limitation.

KEEP LEARNING

Previous page 04 Changing data and building projects: challenges

References: Python SQLite parameters and transactions · SQLite transactions · Partial indexes