Project 01 - Library Management System

Objective

Build a command-line Library Management System backed by SQLite using Python's built-in sqlite3 module. There is no ORM. Every query is written by hand. The system manages a catalog of books, a registry of library members, and a loan lifecycle - borrowing and returning - enforced with foreign keys, constraints, and explicit transactions.

This project tests your ability to design a schema that enforces data integrity at the database level, write safe parameterized SQL, and build a clean separation between your data access layer and your application logic.

What to Build

Entities

Book

Column	Type	Constraints
isbn	TEXT	PRIMARY KEY
title	TEXT	NOT NULL
author	TEXT	NOT NULL
quantity	INTEGER	NOT NULL, DEFAULT 1, CHECK (quantity >= 0)

Member

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
name	TEXT	NOT NULL
email	TEXT	NOT NULL, UNIQUE
joined_date	TEXT	NOT NULL (store as ISO-8601 string: YYYY-MM-DD)

Loan

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
book_isbn	TEXT	NOT NULL, FOREIGN KEY → books(isbn)
member_id	INTEGER	NOT NULL, FOREIGN KEY → members(id)
loan_date	TEXT	NOT NULL (ISO-8601)
due_date	TEXT	NOT NULL (ISO-8601, 14 days after loan_date)
return_date	TEXT	nullable - NULL means still on loan

Entity Relationship Diagram

Operations to Implement

Your system must support all of the following operations. They can be invoked from a CLI menu, command-line arguments, or a REPL-style loop - your choice.

Book Operations

Operation	Description
add_book(isbn, title, author, quantity)	Insert a new book. Raise a meaningful error if the ISBN already exists.
search_books(query)	Full-text search across title and author using LIKE. Return all matching books.
get_book(isbn)	Return a single book by ISBN, or None if not found.
update_quantity(isbn, delta)	Increase or decrease quantity. Never let it go below 0 (enforce at DB and app level).

Member Operations

Operation	Description
register_member(name, email)	Insert a new member. Raise a meaningful error if the email is already registered.
get_member(member_id)	Return a single member by ID, or None.
list_members()	Return all members ordered by name.

Loan Operations

Operation	Description
loan_book(isbn, member_id)	Check book availability, decrement quantity, insert a Loan record - all in one transaction.
return_book(loan_id)	Set return_date on the loan, increment book quantity - all in one transaction.
list_overdue_loans()	Return all loans where due_date < today and return_date IS NULL, joined with book title and member name.
get_member_loans(member_id)	Return all loans (past and current) for a given member.
get_active_loan(isbn, member_id)	Return the active loan for a specific book+member pair, or None.

Technical Requirements

1. Parameterized Queries Only

Every query that incorporates external data must use placeholders. This is non-negotiable.

# CORRECT
cursor.execute("SELECT * FROM books WHERE isbn = ?", (isbn,))

# WRONG - never do this
cursor.execute(f"SELECT * FROM books WHERE isbn = '{isbn}'")
cursor.execute("SELECT * FROM books WHERE isbn = '" + isbn + "'")

2. Row Factory

Configure conn.row_factory = sqlite3.Row on every connection. This allows column access by name:

book = cursor.fetchone()
print(book["title"])   # correct
print(book[1])         # acceptable but fragile - avoid

3. Foreign Key Enforcement

SQLite does not enforce foreign keys by default. You must enable it on every connection:

conn.execute("PRAGMA foreign_keys = ON")

4. Explicit Transactions for Multi-Step Operations

loan_book and return_book each touch two tables. Both must succeed or neither must apply. Use explicit transaction control:

with conn:          # commits on exit, rolls back on exception
    conn.execute("UPDATE books SET quantity = quantity - 1 WHERE isbn = ?", (isbn,))
    conn.execute("INSERT INTO loans ...")

Alternatively, use BEGIN / COMMIT / ROLLBACK explicitly if you need finer control.

5. Constraint Violation Handling

Catch sqlite3.IntegrityError in your DAL functions and re-raise as a domain-level exception that your CLI can catch and display without a traceback:

class DuplicateISBNError(Exception): ...
class DuplicateEmailError(Exception): ...
class BookNotAvailableError(Exception): ...

6. Connection Management

Do not create a new connection on every function call. Create one connection at application startup, configure it (row factory, foreign keys), and pass it through your DAL - or use a module-level singleton. Close it cleanly on exit.

7. Schema in a Separate File

Put all CREATE TABLE IF NOT EXISTS statements in db/schema.sql. Your application reads and executes this file at startup. This keeps your Python files free of DDL noise.

Acceptance Criteria

Your implementation passes when all of the following are true:

• Adding a book with a duplicate ISBN raises a DuplicateISBNError (not a raw sqlite3.IntegrityError).
• Registering a member with a duplicate email raises a DuplicateEmailError.
• loan_book decrements books.quantity by exactly 1.
• loan_book raises BookNotAvailableError when quantity == 0.
• loan_book is atomic: if the loan insert fails for any reason, the quantity decrement is rolled back.
• return_book increments books.quantity by exactly 1 and sets a non-null return_date.
• return_book is atomic: if the quantity increment fails, return_date is not set.
• list_overdue_loans returns only loans where due_date < today AND return_date IS NULL.
• list_overdue_loans results include the book title and member name (JOIN, not separate queries).
• search_books("tolkien") matches books by author Tolkien (case-insensitive).
• No SQL in cli.py or any service layer file - all SQL lives in the DAL.
• No f-string SQL anywhere in the codebase (grep -n "f\".*SELECT\|f\".*INSERT\|f\".*UPDATE\|f\".*DELETE" returns nothing).
• PRAGMA foreign_keys = ON is set before any query runs.
• The database file persists across application restarts (data is not lost on exit).
• Attempting to delete a book that has active loans raises a ForeignKeyError (caught and re-raised from sqlite3.IntegrityError).

Loan Flow

Hints

How do I enable row_factory so I can use column names?

Set it immediately after opening the connection, before any queries run:

import sqlite3

conn = sqlite3.connect("library.db")
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")

After this, every fetchone() and fetchall() result supports both index access (row[0]) and name access (row["title"]). You can also convert a Row to a plain dict with dict(row).

How do I load and execute a .sql schema file at startup?

def init_db(conn: sqlite3.Connection, schema_path: str) -> None:
    with open(schema_path, "r") as f:
        schema_sql = f.read()
    conn.executescript(schema_sql)
    conn.commit()

executescript runs multiple semicolon-separated statements in one call. Use CREATE TABLE IF NOT EXISTS in your schema so repeated startups are safe.

How do I use context managers for transactions?

Python's sqlite3.Connection supports the context manager protocol. Inside with conn:, a successful exit commits; an exception triggers a rollback:

def loan_book(conn, isbn, member_id):
    # Check availability before the transaction
    book = get_book(conn, isbn)
    if book is None:
        raise BookNotFoundError(isbn)
    if book["quantity"] <= 0:
        raise BookNotAvailableError(isbn)

    today = date.today().isoformat()
    due = (date.today() + timedelta(days=14)).isoformat()

    with conn:
        conn.execute(
            "UPDATE books SET quantity = quantity - 1 WHERE isbn = ?",
            (isbn,),
        )
        conn.execute(
            "INSERT INTO loans (book_isbn, member_id, loan_date, due_date) VALUES (?, ?, ?, ?)",
            (isbn, member_id, today, due),
        )

How do I distinguish which IntegrityError was raised?

Check the str(e) message or use the sqlite3.IntegrityError subtype. For UNIQUE violations, the message contains the word UNIQUE. For FOREIGN KEY violations, it contains FOREIGN KEY:

except sqlite3.IntegrityError as e:
    msg = str(e).upper()
    if "UNIQUE" in msg and "isbn" in str(e):
        raise DuplicateISBNError(f"Book with ISBN {isbn} already exists") from e
    if "UNIQUE" in msg and "email" in str(e):
        raise DuplicateEmailError(f"Email {email} already registered") from e
    if "FOREIGN KEY" in msg:
        raise ForeignKeyError("Cannot delete - related records exist") from e
    raise  # re-raise unknown integrity errors unchanged

How do I write the overdue loans query?

You need a JOIN across three tables and a date comparison:

def list_overdue_loans(conn, today: str) -> list:
    cursor = conn.execute(
        """
        SELECT
            l.id,
            l.loan_date,
            l.due_date,
            b.title  AS book_title,
            b.isbn,
            m.name   AS member_name,
            m.email  AS member_email
        FROM loans l
        JOIN books   b ON b.isbn = l.book_isbn
        JOIN members m ON m.id   = l.member_id
        WHERE l.due_date < ?
          AND l.return_date IS NULL
        ORDER BY l.due_date ASC
        """,
        (today,),
    )
    return cursor.fetchall()

Call it as list_overdue_loans(conn, date.today().isoformat()).

How should I structure the CLI menu?

A simple loop with numbered options works fine. Keep all user-facing display in cli.py:

def main():
    conn = get_connection("library.db")
    init_db(conn, "db/schema.sql")

    while True:
        print("\n=== Library System ===")
        print("1. Add book")
        print("2. Search books")
        print("3. Register member")
        print("4. Loan book")
        print("5. Return book")
        print("6. List overdue loans")
        print("0. Quit")

        choice = input("Choice: ").strip()
        if choice == "0":
            break
        elif choice == "1":
            handle_add_book(conn)
        # ... and so on

    conn.close()

Each handle_* function collects input, calls the appropriate DAL function, catches domain exceptions, and prints the result. No SQL here.

Extension Challenges

These are optional. Attempt them only after the core acceptance criteria all pass.

Extension A - Reservation System

Add a Reservation entity:

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
book_isbn	TEXT	FOREIGN KEY → books(isbn)
member_id	INTEGER	FOREIGN KEY → members(id)
reserved_at	TEXT	NOT NULL
status	TEXT	NOT NULL, CHECK (status IN ('pending', 'fulfilled', 'cancelled'))

Rules:

• A member can reserve a book that has quantity == 0.
• When a book is returned, if there is a pending reservation for it, automatically notify (print) the next member in line (ordered by reserved_at) and set their reservation to fulfilled rather than decrementing quantity again.
• A member cannot have more than one active reservation for the same book.

Extension B - Overdue Report CSV Export

Add a CLI option that exports the current overdue loans to a CSV file:

• Filename: overdue_report_YYYY-MM-DD.csv
• Columns: loan_id, book_isbn, book_title, member_name, member_email, loan_date, due_date, days_overdue
• days_overdue is computed in Python from (today - due_date).days
• Use Python's csv.DictWriter
• Print a summary line: Exported N overdue loans to overdue_report_2024-01-15.csv

Extension C - Loan History Statistics

Add a stats command that prints:

=== Library Statistics ===
Total books (unique titles):  142
Total copies:                 389
Active loans:                  23
Overdue loans:                  7
Total members:                 81
Most borrowed book: "The Pragmatic Programmer" (12 loans)
Member with most loans: [email protected] (8 loans)

All statistics must be computed with a single SQL query each - no Python-side aggregation loops.