School Library Issue Register

# ---------------------------------------------------------------------------
# library.py
#
# Issue register for the LambdaLab Public School library, using Python with
# MySQL.
#
# Three tables carry the data: books, members, and the loans between them. A
# loan stores only the ids of a book and a member — the title and the name live
# once, in their own tables, so nothing can disagree with itself.
# ---------------------------------------------------------------------------

import mysql.connector                      # the Python-MySQL connection
from datetime import date, timedelta        # for due dates and fines

LOAN_DAYS = 14                 # how long a book may be kept
FINE_PER_DAY = 2.0             # charged for each day it is late

# The connection is opened once and used by every function below. Change the
# user and password to the ones on your own computer.
db = mysql.connector.connect(host="localhost", user="root",
                             passwd="school", database="lambdalab_library")

# A cursor is what actually carries an SQL statement to the server and brings
# the answer back.
cur = db.cursor()


def issue_book(book_id, member_id):
    """Give a book out, if a copy is free."""
    # %s is a placeholder. The value goes in the tuple that follows, and the
    # connector inserts it safely — never build SQL by joining strings.
    cur.execute("SELECT title, copies FROM books WHERE book_id = %s", (book_id,))
    row = cur.fetchone()                    # one row, or None if there is none

    if row is None:
        print("No such book.")
        return

    title, copies = row

    # Checked BEFORE anything is written, so the register can never show a copy
    # out that does not exist.
    if copies < 1:
        print("'{}' — all copies are out.".format(title))
        return

    due = date.today() + timedelta(days=LOAN_DAYS)     # date arithmetic

    cur.execute("INSERT INTO loans (book_id, member_id, issue_date, due_date)"
                " VALUES (%s, %s, %s, %s)", (book_id, member_id, date.today(), due))

    # The same action that records the loan reduces the count, so the shelf and
    # the database cannot drift apart.
    cur.execute("UPDATE books SET copies = copies - 1 WHERE book_id = %s", (book_id,))

    db.commit()                             # nothing is saved until this runs
    print("Issued '{}' to {}. Due on {}.".format(title, member_id, due))


def return_book(loan_id):
    """Take a book back, and charge a fine if it is late."""
    cur.execute("SELECT book_id, due_date FROM loans WHERE loan_id = %s", (loan_id,))
    book_id, due = cur.fetchone()

    # Subtracting two dates gives a timedelta; .days turns it into a number.
    late = (date.today() - due).days
    fine = late * FINE_PER_DAY if late > 0 else 0      # no fine if not late

    cur.execute("UPDATE loans SET return_date = %s, fine = %s WHERE loan_id = %s",
                (date.today(), fine, loan_id))
    cur.execute("UPDATE books SET copies = copies + 1 WHERE book_id = %s", (book_id,))

    db.commit()
    print("Returned. Fine: Rs {:.2f}".format(fine))


def overdue_list():
    """Every book still out past its due date."""
    # One query across all three tables. The JOINs bring the member's name and
    # the book's title alongside the loan, which holds only their ids.
    # return_date IS NULL means still out; NULL cannot be tested with = .
    cur.execute("SELECT l.loan_id, m.name, b.title, l.due_date"
                " FROM loans l JOIN members m ON l.member_id = m.member_id"
                " JOIN books b ON l.book_id = b.book_id"
                " WHERE l.return_date IS NULL AND l.due_date < %s"
                " ORDER BY l.due_date", (date.today(),))

    print("{:<8}{:<16}{:<26}{:<12}".format("LOAN", "MEMBER", "TITLE", "DUE"))

    for loan_id, name, title, due in cur.fetchall():   # every matching row
        print("{:<8}{:<16}{:<26}{:<12}".format(loan_id, name, title, str(due)))


# --- the program itself ----------------------------------------------------

issue_book(1, "M014")          # give Wings of Fire to member M014
overdue_list()                 # then show what is overdue today

db.close()                     # always close the connection when finished

CREATE DATABASE IF NOT EXISTS lambdalab_library;
USE lambdalab_library;

CREATE TABLE books (
    book_id   INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(60) NOT NULL,
    author    VARCHAR(40),
    copies    INT DEFAULT 1
);

CREATE TABLE members (
    member_id VARCHAR(6) PRIMARY KEY,
    name      VARCHAR(40) NOT NULL,
    class     VARCHAR(6)
);

CREATE TABLE loans (
    loan_id     INT PRIMARY KEY AUTO_INCREMENT,
    book_id     INT,
    member_id   VARCHAR(6),
    issue_date  DATE,
    due_date    DATE,
    return_date DATE DEFAULT NULL,
    fine        DECIMAL(6,2) DEFAULT NULL,
    FOREIGN KEY (book_id)   REFERENCES books(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

INSERT INTO books (title, author, copies) VALUES
    ('Wings of Fire', 'A P J Abdul Kalam', 3),
    ('The Jungle Book', 'R Kipling', 2);

INSERT INTO members VALUES
    ('M014', 'Ananya Sen', 'XII-A'),
    ('M021', 'Rohit Das', 'XI-B');

INSERT INTO loans (book_id, member_id, issue_date, due_date) VALUES
    (2, 'M021', '2026-08-01', '2026-08-15');

C:\LambdaLab\Projects\Library> python library.py
Issued 'Wings of Fire' to M014. Due on 2026-09-09.
LOAN    MEMBER          TITLE                     DUE         
1       Rohit Das       The Jungle Book           2026-08-15