Automated Book Return Reminder Service

Build a Python service that emails students before their library books are due.

Introduction

30 Second Summary

Every college library has students who forget to return books on time. The fines pile up, librarians send manual reminder emails one by one, and the whole process depends on someone remembering to check.

In this project, you will build a Python service that automatically checks a library database for books due soon and sends real email reminders to students before they're late.

What You'll Build

Run your service once and watch personalized reminder emails land in real inboxes within seconds, triggered entirely by your code reading loan dates from a MySQL database.

By the end of this project, you'll have:

A seeded database full of fake students and loans where you can trigger the service and watch real emails arrive in your inbox within seconds.
A configurable reminder window that lets you choose when reminders fire (1 day before, 2 days before, or both) by changing a single line.
A reminder log that tracks every notification sent, proving the system works and never double-sends to the same student.
A production-ready codebase you can point at your college's live SLiMS database by swapping one connection string.
Secret Mission: Add WhatsApp reminders via Twilio alongside the email notifications.

Are there any prerequisites?

You'll need a Gmail account with 2-Step Verification already enabled. The project uses Gmail to send real reminder emails, and 2-Step Verification is required to generate an App Password for your service.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your college's library system.

Install MySQL and Set Up the Project

Your reminder service needs to talk to two external systems: a MySQL database (where loan data lives) and a Gmail server (where reminders get sent). Right now, both are available on your machine but your code has no idea how to reach them.

In this step, you'll verify your tools are working, then write the configuration files that give your project the connection details it needs. Every other file in this project imports from these two files, so getting them right first saves you debugging later.

In this step, get ready to:

Verify MySQL and the Python connector are installed correctly.
Write the environment variables file with database and email credentials.
Create the config module that loads settings for the entire project.

Open the project and verify MySQL

Before writing any code, confirm that MySQL is running and that the Python connector can reach it.

Switch back to VS Code.
Click File in the top menu bar.
Click Open Folder.
Select the slims-reminder folder on your Desktop, then click Open.

You should see all six empty files listed in the Explorer sidebar on the left.

Open a terminal inside VS Code by pressing Ctrl+` (backtick key, top-left of your keyboard).
Verify MySQL is running by running this command:

mysql --version

You should see output similar to:

mysql  Ver 8.0.46 for Win64 on x86_64 (MySQL Community Server - GPL)

Not seeing a version number?

If you see 'mysql' is not recognized, MySQL's bin folder is not on your system PATH.

Open Environment Variables from the Windows search bar.
Under System variables, find Path, click Edit, and add C:\Program Files\MySQL\MySQL Server 8.0\bin.
Close and reopen your terminal, then try again.

help me fix the MySQL PATH issue on Windows

Write the environment variables file

Your project needs a MySQL password and Gmail credentials to function. Storing these in a .env file keeps them out of your code and out of version control.

In VS Code's Explorer sidebar, click on .env to open it.
Paste the following environment variables into the file:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your-mysql-password-here
DB_NAME=slims_reminder

SMTP_EMAIL=your-email@gmail.com
SMTP_PASSWORD=your-app-password-here

Replace your-mysql-password-here with the root password you set during MySQL installation.
Leave SMTP_EMAIL and SMTP_PASSWORD as placeholders for now. You'll fill these in when you set up Gmail in a later step.

What do these variables do?

DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME tell Python how to connect to your local MySQL server.
SMTP_EMAIL and SMTP_PASSWORD are for Gmail's mail server. Your code uses these to authenticate when sending reminder emails.

Save .env by pressing Ctrl+S.

Write the configuration module

Every file in this project needs access to the database and email settings. Instead of reading .env in every file, you'll write one config.py module that loads everything once and exports it as dictionaries.

In VS Code's Explorer sidebar, click on config.py to open it.
Paste the following configuration code:

import os
from dotenv import load_dotenv

load_dotenv()

DB_CONFIG = {
    "host": os.getenv("DB_HOST", "localhost"),
    "user": os.getenv("DB_USER", "root"),
    "password": os.getenv("DB_PASSWORD", ""),
    "database": os.getenv("DB_NAME", "slims_reminder"),
}

SMTP_CONFIG = {
    "email": os.getenv("SMTP_EMAIL", ""),
    "password": os.getenv("SMTP_PASSWORD", ""),
    "host": "smtp.gmail.com",
    "port": 587,
}

REMINDER_DAYS_BEFORE = [1, 2]

What does this code do?

load_dotenv() reads the .env file and loads each line as an environment variable into your Python process.
DB_CONFIG holds the four values needed to connect to MySQL. The second argument in each os.getenv() call is a fallback default if the variable is missing.
SMTP_CONFIG holds the Gmail connection details. The host smtp.gmail.com and port 587 are Gmail's official SMTP settings for TLS connections.
REMINDER_DAYS_BEFORE controls how many days before a due date the system sends a reminder. Currently set to 1 and 2 days.

Save config.py by pressing Ctrl+S.

Before you run the next command, what do you think will happen if config.py loaded your .env correctly?

Verify the configuration loads correctly by running this in your terminal:

python -c "from config import DB_CONFIG; print(DB_CONFIG['host'])"

You should see localhost printed. This confirms config.py loaded your .env values successfully.

Now verify the MySQL connector package is available by running:

python -c "import mysql.connector; print(mysql.connector.__version__)"

You should see a version number printed without errors. This proves Python can communicate with MySQL.

Seeing a ModuleNotFoundError?

If config is not found, make sure your terminal is inside the slims-reminder folder. Run cd ~/Desktop/slims-reminder and try again.
If mysql.connector is not found, the package may have installed to a different Python version. Run pip install mysql-connector-python==9.7.0 again and check the output for warnings.
If dotenv is not found, run pip install python-dotenv==1.1.0 and try the config import again.

help me fix my Python import errors for the SLiMS reminder project

✔️ Awesome, I've got everything!

Great. Double check you've saved both .env and config.py before moving on.

ⓧ I'd like to double check the full code

Here is the complete .env file:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your-mysql-password-here
DB_NAME=slims_reminder

SMTP_EMAIL=your-email@gmail.com
SMTP_PASSWORD=your-app-password-here

Here is the complete config.py file:

import os
from dotenv import load_dotenv

load_dotenv()

DB_CONFIG = {
    "host": os.getenv("DB_HOST", "localhost"),
    "user": os.getenv("DB_USER", "root"),
    "password": os.getenv("DB_PASSWORD", ""),
    "database": os.getenv("DB_NAME", "slims_reminder"),
}

SMTP_CONFIG = {
    "email": os.getenv("SMTP_EMAIL", ""),
    "password": os.getenv("SMTP_PASSWORD", ""),
    "host": "smtp.gmail.com",
    "port": 587,
}

REMINDER_DAYS_BEFORE = [1, 2]

Your environment is configured and both tools are proven working. Next up, you'll build the database schema that mirrors your college's SLiMS system and fill it with sample data to test against.

Create the SLiMS Database Schema with Sample Data

In the previous step you installed MySQL and set up the project skeleton with configuration files. Now your reminder service needs something to query.

Without a database full of loans and members, the reminder engine has nothing to check. In this step you will recreate the exact table structure your college's SLiMS system uses so the code works identically on the real system later.

In this step, get ready to:

Write a database module that creates the SLiMS tables in MySQL.
Seed the tables with sample students, books, and loans.
Verify the data appears in the MySQL command line.

Write the database connection module

The database.py file has two jobs: open a connection to MySQL and create all the tables your reminder service needs.

Open database.py in VS Code and paste the following code:

import mysql.connector
from config import DB_CONFIG


def get_connection():
    return mysql.connector.connect(**DB_CONFIG)


def create_tables():
    conn = mysql.connector.connect(
        host=DB_CONFIG["host"],
        user=DB_CONFIG["user"],
        password=DB_CONFIG["password"],
    )
    cursor = conn.cursor()

    cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{DB_CONFIG['database']}`")
    cursor.execute(f"USE `{DB_CONFIG['database']}`")

What does this code do?

get_connection() returns a ready-to-use MySQL connection using the credentials from your .env file.

create_tables() connects without specifying a database first, then creates the slims_reminder database if it does not exist. This way the script works on a fresh MySQL install with no manual setup.

Add the member table below the code you just pasted:

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `member` (
            `member_id` VARCHAR(20) NOT NULL,
            `member_name` VARCHAR(100) NOT NULL,
            `gender` INT(1) NOT NULL DEFAULT 0,
            `birth_date` DATE DEFAULT NULL,
            `member_type_id` INT(6) DEFAULT NULL,
            `member_address` VARCHAR(255) DEFAULT NULL,
            `member_email` VARCHAR(100) DEFAULT NULL,
            `member_phone` VARCHAR(50) DEFAULT NULL,
            `inst_name` VARCHAR(100) DEFAULT NULL,
            `register_date` DATE DEFAULT NULL,
            `expire_date` DATE NOT NULL,
            `input_date` DATE DEFAULT NULL,
            `last_update` DATE DEFAULT NULL,
            PRIMARY KEY (`member_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    """)

Why does the member table match SLiMS exactly?

The column names (member_id, member_name, member_email) are taken directly from the SLiMS 7 Cendana source schema.

By keeping the exact same structure, your reminder code will work on your college's live database without any changes to the query logic.

Add the biblio and item tables below the member table:

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `biblio` (
            `biblio_id` INT(11) NOT NULL AUTO_INCREMENT,
            `title` TEXT NOT NULL,
            `isbn_issn` VARCHAR(20) DEFAULT NULL,
            `publisher_id` INT(11) DEFAULT NULL,
            `publish_year` VARCHAR(20) DEFAULT NULL,
            `input_date` DATETIME DEFAULT NULL,
            `last_update` DATETIME DEFAULT NULL,
            PRIMARY KEY (`biblio_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `item` (
            `item_id` INT(11) NOT NULL AUTO_INCREMENT,
            `biblio_id` INT(11) DEFAULT NULL,
            `item_code` VARCHAR(20) DEFAULT NULL,
            `call_number` VARCHAR(50) DEFAULT NULL,
            `coll_type_id` INT(3) DEFAULT NULL,
            `input_date` DATETIME NOT NULL,
            `last_update` DATETIME DEFAULT NULL,
            PRIMARY KEY (`item_id`),
            UNIQUE KEY `item_code` (`item_code`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    """)

What are biblio and item?

biblio stores book metadata (title, ISBN). item represents a physical copy of a book with a unique item_code barcode.

One biblio record can have multiple item records. For example, the library might own three copies of the same textbook.

Add the loan table next:

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `loan` (
            `loan_id` INT(11) NOT NULL AUTO_INCREMENT,
            `item_code` VARCHAR(20) DEFAULT NULL,
            `member_id` VARCHAR(20) DEFAULT NULL,
            `loan_date` DATE NOT NULL,
            `due_date` DATE NOT NULL,
            `renewed` INT(11) NOT NULL DEFAULT 0,
            `loan_rules_id` INT(11) NOT NULL DEFAULT 0,
            `is_lent` INT(11) NOT NULL DEFAULT 0,
            `is_return` INT(11) NOT NULL DEFAULT 0,
            `return_date` DATE DEFAULT NULL,
            PRIMARY KEY (`loan_id`),
            KEY `item_code` (`item_code`),
            KEY `member_id` (`member_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    """)

What makes the loan table important?

This is the table your reminder service queries most. The due_date column tells you when a book must be returned.

The is_return column (0 or 1) indicates whether the book has been returned. Your query will filter for is_return = 0 to find books still out on loan.

Add the reminder_log table and the closing lines to finish the file:

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `reminder_log` (
            `log_id` INT(11) NOT NULL AUTO_INCREMENT,
            `member_id` VARCHAR(20) NOT NULL,
            `item_code` VARCHAR(20) NOT NULL,
            `due_date` DATE NOT NULL,
            `sent_at` DATETIME NOT NULL,
            `channel` VARCHAR(20) NOT NULL DEFAULT 'email',
            PRIMARY KEY (`log_id`),
            UNIQUE KEY `unique_reminder` (`member_id`, `item_code`, `due_date`, `channel`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    """)

    conn.commit()
    cursor.close()
    conn.close()
    print("All tables created successfully.")


if __name__ == "__main__":
    create_tables()

What is reminder_log for?

This table is not part of SLiMS itself. It tracks which reminders your service has already sent.

The UNIQUE KEY on (member_id, item_code, due_date, channel) prevents duplicate reminders for the same loan.

Save database.py.
Run the script to create the database and all five tables:

python database.py

You should see this output:

All tables created successfully.

Seeing Access denied or Can't connect?

Make sure the DB_PASSWORD value in your .env file matches the root password you set during MySQL installation.

If MySQL is not running, open the Windows **Services** app, find **MySQL80**, and click **Start**.

Help me troubleshoot my MySQL connection settings.

✔️ Awesome, I've got everything!

Great. Double check you have saved database.py and saw the success message in your terminal.

ⓧ I'd like to double check the full code

Your complete database.py should contain 5 table definitions inside the create_tables() function. Scroll through your file and confirm these are present, in order:

The imports and get_connection() function at the top.
create_tables() starting with database creation and USE statements.
The member table (14 columns).
The biblio table (7 columns) and item table (7 columns).
The loan table (10 columns) and reminder_log table (6 columns).
conn.commit(), cursor.close(), conn.close(), and the if __name__ block at the bottom.

Seed the database with sample students and loans

Your tables exist but they are empty. The seed script inserts sample students with email addresses, books, and loans with different due dates so you can test reminders for various scenarios.

Open seed_data.py in VS Code and paste the following code:

from datetime import date, timedelta
from database import create_tables, get_connection


def seed():
    create_tables()
    conn = get_connection()
    cursor = conn.cursor()

    today = date.today()

    members = [
        ("STU001", "Aaryan Hankare", "aaryan@example.com", "9876543210"),
        ("STU002", "Aniket Deshmukh", "aniket@example.com", "9876543211"),
        ("STU003", "Aftab Mulla", "aftab@example.com", "9876543212"),
        ("STU004", "Balu More", "balu@example.com", "9876543213"),
        ("STU005", "Priya Sharma", "priya@example.com", "9876543214"),
    ]

    for mid, name, email, phone in members:
        cursor.execute("""
            INSERT IGNORE INTO member (member_id, member_name, member_email,
            member_phone, gender, expire_date, input_date)
            VALUES (%s, %s, %s, %s, 1, %s, %s)
        """, (mid, name, email, phone, today + timedelta(days=365), today))

What does this code do?

The seed() function first calls create_tables() to ensure the schema exists, then inserts five sample students.

INSERT IGNORE means re-running the script will not duplicate data if rows already exist. Each student gets a unique ID like STU001 and an email address.

Add the books and items lists below the member insert loop:

    books = [
        (1, "Introduction to Python Programming", "978-0134444321"),
        (2, "Data Structures and Algorithms", "978-0262033848"),
        (3, "Database Management Systems", "978-0073523323"),
        (4, "Computer Networks", "978-0132126953"),
        (5, "Operating System Concepts", "978-1118063330"),
    ]

    for bid, title, isbn in books:
        cursor.execute("""
            INSERT IGNORE INTO biblio (biblio_id, title, isbn_issn, input_date)
            VALUES (%s, %s, %s, NOW())
        """, (bid, title, isbn))

    items = [
        (1, 1, "BK001"), (2, 2, "BK002"), (3, 3, "BK003"),
        (4, 4, "BK004"), (5, 5, "BK005"), (6, 1, "BK006"),
        (7, 2, "BK007"), (8, 3, "BK008"),
    ]

    for iid, bid, code in items:
        cursor.execute("""
            INSERT IGNORE INTO item (item_id, biblio_id, item_code, input_date)
            VALUES (%s, %s, %s, NOW())
        """, (iid, bid, code))

Why are there 8 items for 5 books?

Some books have multiple physical copies. Items BK006, BK007, and BK008 are second copies of the first three books, simulating a real library.

Add the loans list and the closing lines to finish the file:

    loans = [
        (1, "BK001", "STU001", today - timedelta(days=12), today + timedelta(days=1), 0),
        (2, "BK002", "STU001", today - timedelta(days=10), today + timedelta(days=2), 0),
        (3, "BK003", "STU002", today - timedelta(days=14), today + timedelta(days=1), 0),
        (4, "BK004", "STU003", today - timedelta(days=7), today + timedelta(days=5), 0),
        (5, "BK005", "STU004", today - timedelta(days=15), today - timedelta(days=1), 0),
        (6, "BK006", "STU005", today - timedelta(days=13), today + timedelta(days=2), 0),
        (7, "BK007", "STU002", today - timedelta(days=5), today + timedelta(days=7), 0),
        (8, "BK008", "STU003", today - timedelta(days=10), today + timedelta(days=1), 0),
    ]

    for lid, item, member, loan_dt, due_dt, is_ret in loans:
        cursor.execute("""
            INSERT IGNORE INTO loan (loan_id, item_code, member_id, loan_date,
            due_date, is_lent, is_return)
            VALUES (%s, %s, %s, %s, %s, 1, %s)
        """, (lid, item, member, loan_dt, due_dt, is_ret))

    conn.commit()
    cursor.close()
    conn.close()
    print(f"Seeded: {len(members)} members, {len(books)} books, {len(loans)} loans.")
    print(f"Loans due tomorrow: STU001 (BK001), STU002 (BK003), STU003 (BK008)")
    print(f"Loans due in 2 days: STU001 (BK002), STU005 (BK006)")


if __name__ == "__main__":
    seed()

What do the different due dates simulate?

The loans include a mix of scenarios your reminder service will encounter:

today + timedelta(days=1) means due tomorrow (should trigger a reminder).
today + timedelta(days=2) means due in 2 days (should also trigger).
today - timedelta(days=1) means already overdue (should NOT trigger a pre-due reminder).
today + timedelta(days=5) or more means not due soon (should NOT trigger).

Save seed_data.py.

Before you run this, what do you expect to happen to your MySQL database?

Run the seed script:

python seed_data.py

You should see output confirming the tables and data were created:

All tables created successfully.
Seeded: 5 members, 5 books, 8 loans.
Loans due tomorrow: STU001 (BK001), STU002 (BK003), STU003 (BK008)
Loans due in 2 days: STU001 (BK002), STU005 (BK006)

Seeing a ModuleNotFoundError?

If you see ModuleNotFoundError: No module named 'config', make sure you are running the command from inside the slims-reminder folder where config.py lives.

Help me figure out what went wrong with my seed script.

✔️ Awesome, I've got everything!

Great. Double check you have saved seed_data.py and saw the seeded confirmation output.

ⓧ I'd like to double check the full code

Your complete seed_data.py should contain these sections in order. Scroll through and confirm each is present:

Imports from datetime and database at the top.
The seed() function calling create_tables() first, then inserting members, books, items, and loans.
A members list with 5 tuples (STU001 through STU005).
A books list with 5 tuples and an items list with 8 tuples.
A loans list with 8 tuples using varying due dates.
conn.commit(), print statements, and if __name__ block at the bottom.

Verify the data in MySQL

The seed script printed a summary, but let's confirm the data is actually in MySQL by querying it directly.

Open a new terminal and connect to MySQL by running:

mysql -u root -p

Enter the root password you set during MySQL installation when prompted.
At the mysql> prompt, switch to your database and query the members:

USE slims_reminder;
SELECT member_name, member_email FROM member;

What do you expect to see in the results?

You should see a table with 5 rows showing each student's name and email address.

Type exit to close the MySQL client and return to your regular terminal.

Seeing 'Unknown database' error?

If MySQL does not recognize slims_reminder, the python seed_data.py command may not have completed successfully. Re-run it and check for error messages.

Help me verify that my seed data is in MySQL.

Your database is loaded with realistic test data. Next up, you will write the query engine that finds books due soon and identifies which students need a reminder.

Build the Reminder Query Engine

Your database is seeded with students and loans. Now you need the brain of the entire service: a single query that answers "which books are due soon and haven't been returned yet?"

If this query is wrong, reminders go to the wrong people or don't fire at all. In this step, you'll write a function that joins all four SLiMS tables and returns a clean list of due-soon loans ready for the email system.

In this step, get ready to:

Write a SQL query that joins the loan, member, item, and biblio tables.
Build a function that returns due-soon loans as a list of dictionaries.
Run the query and confirm it finds the correct students and books.

Write the query function

The query needs to cross four tables at once: loan holds the due dates, member holds names and emails, item maps item codes to books, and biblio holds the book titles.

Open reminder.py in VS Code and add the following imports at the top of the file:

from datetime import date, timedelta, datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

from config import DB_CONFIG, SMTP_CONFIG, REMINDER_DAYS_BEFORE
from database import get_connection

Why import smtplib now?

You won't use the email modules in this step. You're importing them now so the file is ready for the next step when you add the email-sending logic. This avoids editing the top of the file later.

Below the imports, add the get_upcoming_due_loans() function that queries all four tables:

def get_upcoming_due_loans():
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    today = date.today()
    # Calculate due dates to check (e.g., 1 and 2 days from now)
    due_dates = [today + timedelta(days=d) for d in REMINDER_DAYS_BEFORE]
    # Build parameterized query joining all four SLiMS tables
    placeholders = ", ".join(["%s"] * len(due_dates))
    query = f"""
        SELECT
            l.loan_id,
            l.item_code,
            l.member_id,
            l.due_date,
            m.member_name,
            m.member_email,
            m.member_phone,
            b.title AS book_title
        FROM loan l
        JOIN member m ON l.member_id = m.member_id
        JOIN item i ON l.item_code = i.item_code
        JOIN biblio b ON i.biblio_id = b.biblio_id
        WHERE l.is_return = 0
        AND l.due_date IN ({placeholders})
        AND m.member_email IS NOT NULL
        AND m.member_email != ''
    """
    cursor.execute(query, due_dates)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

What does this query do?

The function calculates target due dates from your REMINDER_DAYS_BEFORE config (set to [1, 2] in config.py). It looks for loans due tomorrow and the day after.

The SQL joins four tables: loan provides due dates, member provides contact info, item links item codes to books, and biblio provides book titles.

The WHERE l.is_return = 0 clause filters out books that have already been returned. The dictionary=True cursor option returns each row as a dictionary with column names as keys instead of a plain tuple.

Add the test runner and verify

The function exists but you have no way to call it yet. A test block at the bottom of the file lets you run the query directly and see what it finds.

At the bottom of reminder.py, add the test runner block:


if __name__ == "__main__":
    loans = get_upcoming_due_loans()
    print(f"Found {len(loans)} loan(s) due soon:")
    for loan in loans:
        print(loan)

What does name == "main" do?

This block only runs when you execute the file directly with python reminder.py. When another file imports reminder.py later (like main.py will), this test block is skipped.

Save reminder.py.

Before you run this, think about what you expect to see. Your seed data has loans due tomorrow (STU001, STU002, STU003) and loans due in 2 days (STU001, STU005). How many total loans should the query find?

Run the query by executing this command in your terminal:

python reminder.py

You should see 5 loans printed: 3 due tomorrow (BK001, BK003, BK008) and 2 due in 2 days (BK002, BK006). Each loan shows the member's name, email, phone number, book title, and due date.

Seeing 0 loans or a connection error?

If you see Found 0 loan(s) due soon, your seed data may have expired. Run python seed_data.py again to re-seed with fresh dates relative to today.
If you see an access denied error, check that your DB_PASSWORD in .env matches the root password you set during MySQL installation.
If you see a "table doesn't exist" error, run python seed_data.py first to create the tables and insert data.

Still stuck? Ask for help.

✔️ Awesome, I've got everything!

Great. Double check you've saved reminder.py and that your output shows 5 loans with member names, emails, and book titles.

ⓧ I'd like to double check the full code

Scroll up and confirm your reminder.py has these three sections in order:

The imports block (7 lines starting with from datetime import date, timedelta, datetime).
The get_upcoming_due_loans() function with the SQL query that SELECTs from all four tables and filters by is_return = 0 and due_date IN (...).
The if __name__ == "__main__" block that calls the function and prints each loan.

Your query engine works. It correctly identifies which students have books due soon and pulls all the info needed to contact them. Next up, you'll wire this into real email delivery so those students actually receive a reminder in their inbox.

Send Real Email Reminders via Gmail

Your query engine can find every student with a book due soon. That data is useless if it stays locked in your terminal.

In this step, you will wire up Gmail SMTP so your service delivers real, personalized reminder emails to students. You will also add duplicate-prevention logic so a student never receives the same reminder twice.

In this step, get ready to:

Create a Gmail App Password for your Python script to authenticate with.
Write a function that composes and sends HTML reminder emails.
Add duplicate-prevention logic so the service never double-sends reminders.

Create a Gmail App Password

Python's smtplib connects to Gmail's SMTP server using your email and a special 16-character App Password. Your regular Gmail password will not work here because you have 2-Step Verification enabled.

Open your browser and go to https://myaccount.google.com.
Click Security in the left sidebar.
Under the Signing in to Google section, click App passwords.
You may be prompted to re-enter your Google password.
In the App name field, type SLiMS Reminder and click Create.
Google displays a 16-character password. Copy it immediately. You will not be able to see it again after closing this dialog.

What is an App Password?

An App Password is a 16-character code that lets a third-party app (like your Python script) access your Gmail without needing your main password or a browser-based sign-in flow. It only works when 2-Step Verification is turned on.

Switch back to VS Code and open your .env file.
Replace your-email@gmail.com with your actual Gmail address.
Replace your-app-password-here with the 16-character App Password you just copied (no spaces).
Save .env.

Don't see App passwords in your settings?

Make sure 2-Step Verification is fully enabled on your Google Account. The App passwords option only appears after 2-Step Verification is active.

If you are using a work or school Google Workspace account, your administrator may have disabled App passwords. Use a personal Gmail account instead.

help me find the App passwords option in my Google Account

Write the email sending function

Your reminder emails need to be formatted, personal, and informative. The send_reminder_email() function composes an HTML email with the student's name, book title, and due date, then sends it through Gmail's SMTP server.

In reminder.py, find the if __name__ == "__main__": block at the bottom of the file.
Add the following function directly above that if __name__ block. This is the first part of send_reminder_email() which builds the email message and HTML body:

def send_reminder_email(member_name, member_email, book_title, due_date):
    # Build the email message container
    msg = MIMEMultipart("alternative")
    msg["Subject"] = f"Library Reminder: '{book_title}' is due on {due_date}"
    msg["From"] = SMTP_CONFIG["email"]
    msg["To"] = member_email

    # Calculate how urgent the reminder is
    days_left = (due_date - date.today()).days
    urgency = "tomorrow" if days_left <= 1 else f"in {days_left} days"

    # HTML email template with book details
    html = f"""
    <html>
    <body style="font-family: Arial, sans-serif; padding: 20px;">
        <h2>Library Book Return Reminder</h2>
        <p>Dear <strong>{member_name}</strong>,</p>
        <p>This is a friendly reminder that the following book is due <strong>{urgency}</strong>:</p>
        <table style="border-collapse: collapse; margin: 15px 0;">
            <tr>
                <td style="padding: 8px; border: 1px solid #ddd;"><strong>Book Title</strong></td>
                <td style="padding: 8px; border: 1px solid #ddd;">{book_title}</td>
            </tr>
            <tr>
                <td style="padding: 8px; border: 1px solid #ddd;"><strong>Due Date</strong></td>
                <td style="padding: 8px; border: 1px solid #ddd;">{due_date.strftime('%d %B %Y')}</td>
            </tr>
        </table>
        <p>Please return the book on time to avoid fines.</p>
        <p>Thank you,<br>Library Automated Reminder System</p>
    </body>
    </html>
    """

Directly below the closing """ of the HTML string (still inside the same function), add the code that attaches the HTML and sends the email:

    # Attach HTML content and send via Gmail SMTP
    msg.attach(MIMEText(html, "html"))

    with smtplib.SMTP(SMTP_CONFIG["host"], SMTP_CONFIG["port"]) as server:
        server.starttls()
        server.login(SMTP_CONFIG["email"], SMTP_CONFIG["password"])
        server.sendmail(SMTP_CONFIG["email"], member_email, msg.as_string())

What does this function do?

The function creates a MIME email with an HTML body. It calculates whether the book is due "tomorrow" or "in 2 days" and inserts that into the template.

The smtplib.SMTP connection uses STARTTLS on port 587 to encrypt the connection before authenticating with your App Password. Once logged in, it sends the email and closes the connection automatically.

Save reminder.py.
Verify the file still runs without errors by running:

python reminder.py

You should see the same loan data printed as before. This confirms your new function has no syntax errors.

Seeing a SyntaxError?

Check that the html = f""" opening and the closing """ are both present and properly indented inside the function. Triple-quoted strings must open and close with exactly three double-quote characters.

help me fix a SyntaxError in my send_reminder_email function

Add duplicate prevention and run the reminder orchestrator

Sending the same reminder twice is embarrassing for your library system. You need two helper functions that check the reminder_log table before sending and record each successful send after delivery.

In reminder.py, add these two functions directly below get_upcoming_due_loans() and above send_reminder_email():

def already_sent(conn, member_id, item_code, due_date, channel="email"):
    # Check if we already sent this specific reminder
    cursor = conn.cursor()
    cursor.execute("""
        SELECT log_id FROM reminder_log
        WHERE member_id = %s AND item_code = %s AND due_date = %s AND channel = %s
    """, (member_id, item_code, due_date, channel))
    result = cursor.fetchone()
    cursor.close()
    return result is not None


def log_reminder(conn, member_id, item_code, due_date, channel="email"):
    # Record that we sent this reminder so we never double-send
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO reminder_log (member_id, item_code, due_date, sent_at, channel)
        VALUES (%s, %s, %s, %s, %s)
    """, (member_id, item_code, due_date, datetime.now(), channel))
    conn.commit()
    cursor.close()

What do these functions do?

The already_sent() function queries the reminder_log table for a matching record. If one exists, the reminder was already delivered and should be skipped.

The log_reminder() function inserts a new row after a successful send. The unique_reminder constraint on the table prevents accidental duplicates even if the code runs twice simultaneously.

Now you need the orchestrator function that ties everything together: find due loans, skip already-sent ones, send the rest, and log each delivery.

Replace the entire if __name__ == "__main__": block at the bottom of reminder.py with the following run_reminder_check() function and updated main block:

def run_reminder_check():
    # Print timestamp for each check run
    print(f"\n[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] Running reminder check...")
    loans = get_upcoming_due_loans()
    print(f"  Found {len(loans)} loan(s) due soon.")
    if not loans:
        print("  No reminders to send.")
        return
    conn = get_connection()
    sent_count = 0
    skipped_count = 0
    for loan in loans:
        if already_sent(conn, loan["member_id"], loan["item_code"], loan["due_date"]):
            skipped_count += 1
            continue
        try:
            send_reminder_email(
                loan["member_name"],
                loan["member_email"],
                loan["book_title"],
                loan["due_date"],
            )
            log_reminder(conn, loan["member_id"], loan["item_code"], loan["due_date"])
            sent_count += 1
            print(f"  Sent reminder to {loan['member_name']} ({loan['member_email']}) "
                  f"for '{loan['book_title']}' due {loan['due_date']}")
        except Exception as e:
            print(f"  FAILED to send to {loan['member_email']}: {e}")
    conn.close()
    print(f"  Done: {sent_count} sent, {skipped_count} skipped (already sent).")

if __name__ == "__main__":
    run_reminder_check()

What does run_reminder_check do?

This function is the brain of your reminder service. It fetches all loans due in 1-2 days, checks each one against the reminder_log table, sends an email only if no record exists, then logs the successful send.

The try/except block ensures that one failed email does not crash the entire batch. The function prints a summary of how many emails were sent and how many were skipped.

Save reminder.py.

Before you run this, what do you expect to happen? Think about how many loans are due in 1-2 days from your seed data and whether any reminders have been sent before.

Run the full reminder check:

python reminder.py

You should see output showing 5 loans found and 5 reminders sent (with 0 skipped). Each line names the student and book title.

Check your email inbox. You should have received personalized reminder emails with the book title and due date for each loan that was due soon.
Run python reminder.py a second time. This time you should see 5 loans found but 0 sent and 5 skipped. The duplicate prevention is working.

Seeing an authentication error?

If you see a message about "Username and Password not accepted", double-check that you pasted the 16-character App Password (not your regular Gmail password) into the SMTP_PASSWORD field in .env. Remove any spaces from the password.

Also confirm that the SMTP_EMAIL value in .env matches the Gmail account where you created the App Password.

help me fix Gmail SMTP authentication errors in my Python script

✔️ Awesome, I've got everything!

Great. Double check you have saved reminder.py and that running it a second time shows 0 sent and 5 skipped.

ⓧ I'd like to double check the full code

Here is the full reminder.py at this point. Scroll through and confirm each section matches yours:

Imports and get_upcoming_due_loans() (the query function from the previous step).
The already_sent() and log_reminder() helper functions.
The send_reminder_email() function with HTML template and SMTP send.
The run_reminder_check() orchestrator function.
The if __name__ == "__main__": block calling run_reminder_check().

Your reminder service now finds overdue-soon books, sends personalized emails, and never double-sends. Next up, you will automate the whole thing so it runs daily without you lifting a finger.

Automate with the Scheduler

Your reminder service works perfectly when you manually run it. The problem is that "manually" is the opposite of what a library system needs.

A real production service runs on its own every single day without anyone touching it. In this step, you'll use the schedule library to make your reminder check fire automatically at 08:00 every morning.

In this step, get ready to:

Schedule the reminder check to run automatically at 08:00 daily.
Run an immediate check on startup for instant verification.
Observe the live console output showing loans found, reminders sent, and duplicates skipped.

Write the scheduler entry point

Right now you have to type python reminder.py every single morning. The schedule library lets you define "run this function at this time" and then forget about it.

In VS Code, open main.py and add the following code:

import schedule
import time
from datetime import datetime

from reminder import run_reminder_check


def main():
    # Print a startup banner so you know the service is running
    print("=" * 50)
    print("  SLiMS Automated Reminder Service")
    print("=" * 50)
    print(f"  Started at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("  Schedule: Daily at 08:00")
    print("  Press Ctrl+C to stop")
    print("=" * 50)

    # Schedule the reminder check to run every day at 08:00
    schedule.every().day.at("08:00").do(run_reminder_check)

    # Run one check immediately so you can verify without waiting
    print("\n  Running initial check now...")
    run_reminder_check()

    # Keep the script alive, checking every 60 seconds for pending jobs
    while True:
        schedule.run_pending()
        time.sleep(60)


if __name__ == "__main__":
    main()

What does this code do?

schedule.every().day.at("08:00").do(run_reminder_check) tells the scheduler to call your reminder function once per day at 8 AM.
run_reminder_check() is called immediately on startup so you can verify the service works without waiting until tomorrow morning.
The while True loop keeps the script alive. Every 60 seconds it checks whether a scheduled job is due to run.
The startup banner gives you a quick visual confirmation that the service is running, what time it started, and how to stop it.

Why schedule instead of APScheduler?

The schedule library is zero-config and has no external dependencies. For a simple "run daily at X time" use case, it's the perfect fit.

APScheduler is a more powerful alternative, but it adds complexity (job stores, executors, event listeners) that you don't need here. When one line of code does the job, keep it simple.

Save main.py.

Run the automated service

Before you run this, think about what you expect to see. You already sent reminders in the previous step. Will the service send them again, or will the duplicate-prevention logic kick in?

Start the reminder service by running this command:

python main.py

You should see the startup banner appear, followed by the immediate check running. The output looks like this:

==================================================
  SLiMS Automated Reminder Service
==================================================
  Started at: 2026-08-03 14:30:00
  Schedule: Daily at 08:00
  Press Ctrl+C to stop
==================================================

  Running initial check now...

[2026-08-03 14:30:00] Running reminder check...
  Found 5 loan(s) due soon.
  Done: 0 sent, 5 skipped (already sent).

Why does it show 0 sent and 5 skipped?

You already sent reminders when you ran python reminder.py in the previous step. The reminder_log table recorded each one. Now the duplicate-prevention logic sees those records and skips them.

This proves the system is working correctly. No student gets the same reminder twice, even if the scheduler runs multiple times.

Let the service sit for about 30 seconds. Confirm it stays running without crashing.
Press Ctrl+C to stop the service.

Service crashes immediately?

If you see ModuleNotFoundError: No module named 'schedule', make sure you installed the package with pip install schedule earlier.
If you see a MySQL connection error, confirm your MySQL service is still running.
If you see ImportError for run_reminder_check, check that reminder.py is saved in the same folder as main.py.

Still stuck? Help me debug why my main.py crashes on startup

✔️ Awesome, I've got everything!

Great. Double check you've saved main.py and that your service ran successfully.

ⓧ I'd like to double check the full code

Here is the complete main.py file:

import schedule
import time
from datetime import datetime

from reminder import run_reminder_check


def main():
    print("=" * 50)
    print("  SLiMS Automated Reminder Service")
    print("=" * 50)
    print(f"  Started at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("  Schedule: Daily at 08:00")
    print("  Press Ctrl+C to stop")
    print("=" * 50)

    schedule.every().day.at("08:00").do(run_reminder_check)

    print("\n  Running initial check now...")
    run_reminder_check()

    while True:
        schedule.run_pending()
        time.sleep(60)


if __name__ == "__main__":
    main()

Secret mission

Add WhatsApp/SMS Reminders via Twilio

Email is great, but students ignore it. WhatsApp is where they actually live. In this secret mission, you'll add a second notification channel using Twilio's free WhatsApp Sandbox so your reminder service becomes truly impossible to miss.

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

MySQL Server 8.0.46 (running as a Windows service).
The slims_reminder MySQL database with all tables and sample data.
The slims-reminder project folder on your Desktop.
Python packages: mysql-connector-python, schedule, python-dotenv, twilio (if Secret Mission completed).
Gmail App Password created for SMTP access.
Twilio account and WhatsApp Sandbox (if Secret Mission completed).

Keep everything running

No action needed. Choose this if you plan to connect the service to your college's real SLiMS database or continue developing it.

MySQL continues running as a Windows service in the background.
Your Gmail App Password remains active for sending reminders.
Your project folder stays on your Desktop, ready to run with python main.py whenever you need it.

Pause - I'll come back to this later

Stop MySQL from running in the background to free up system memory. Your database and project files stay intact for when you return.

Stop the MySQL service by opening a terminal as Administrator and running:
When you want to resume, start the service again with:

net stop MySQL80

To restart MySQL when you come back:

net start MySQL80

Your database, project files, and App Password all remain intact.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

Drop the MySQL database by running this in your terminal:

mysql -u root -p -e "DROP DATABASE slims_reminder;"

Enter your MySQL root password when prompted. This removes all tables and data.
Uninstall MySQL Server from your system:

Press the Windows key to open search.
Type Add or remove programs and press Enter.
Find MySQL Server 8.0 in the list, click it, and select Uninstall.
Also find and uninstall MySQL Installer - Community if listed.

Delete the project folder by running:

cd ~/Desktop
rmdir /s /q slims-reminder

Uninstall the Python packages by running:

pip uninstall mysql-connector-python schedule python-dotenv twilio -y

Revoke your Gmail App Password:

Go to myaccount.google.com in your browser.
Select Security.
Under 2-Step Verification, find App passwords.
Delete the App Password you created for this project.

If you completed the Secret Mission, you can close your Twilio account from the Twilio Console under Account > Settings > Close Account.

Nice Work!

You just built a fully automated reminder service that reads a live library database and sends personalized notifications to students before their books are overdue. No manual intervention, no missed deadlines.

You've learned how to:

Connect a Python service to a real MySQL database using the SLiMS schema and write SQL queries that join multiple tables to find exactly the data you need.
Send real email reminders through Gmail SMTP with duplicate-prevention logic that guarantees students never get the same reminder twice.
Automate the entire flow with a scheduler that runs daily without manual intervention and logs every action to the console.
(Secret Mission) Added WhatsApp as a second notification channel via Twilio, making your reminder system multi-channel and harder for students to miss.

Ready to quiz yourself?

Automated Book Return Reminder Service

Introduction

30 Second Summary

What You'll Build

Are there any prerequisites?

Before We Start

Install MySQL and Set Up the Project

In this step, get ready to:

Open the project and verify MySQL

Not seeing a version number?

Write the environment variables file

What do these variables do?

Write the configuration module

What does this code do?

Seeing a ModuleNotFoundError?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Create the SLiMS Database Schema with Sample Data

In this step, get ready to:

Write the database connection module

What does this code do?

Why does the member table match SLiMS exactly?

What are biblio and item?

What makes the loan table important?

What is reminder_log for?

Seeing Access denied or Can't connect?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Seed the database with sample students and loans

What does this code do?

Why are there 8 items for 5 books?

What do the different due dates simulate?

Seeing a ModuleNotFoundError?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Verify the data in MySQL

Seeing 'Unknown database' error?

Build the Reminder Query Engine

In this step, get ready to:

Write the query function

Why import smtplib now?

What does this query do?

Add the test runner and verify

What does __name__ == "__main__" do?

Seeing 0 loans or a connection error?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Send Real Email Reminders via Gmail

In this step, get ready to:

Create a Gmail App Password

What is an App Password?

Don't see App passwords in your settings?

Write the email sending function

What does this function do?

Seeing a SyntaxError?

Add duplicate prevention and run the reminder orchestrator

What do these functions do?

What does run_reminder_check do?

Seeing an authentication error?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Automate with the Scheduler

In this step, get ready to:

Write the scheduler entry point

What does this code do?

Why schedule instead of APScheduler?

Run the automated service

Why does it show 0 sent and 5 skipped?

Service crashes immediately?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Secret mission

Add WhatsApp/SMS Reminders via Twilio

Clean Up Your Resources

Clean Up Your Resources

Keep everything running

Pause - I'll come back to this later

Delete - I don't want to use this again

Nice Work!

Nice Work!

What does name == "main" do?