Preparing your learning space...
67% through Real-World Projects tutorials
In this project you will build a command-line hotel booking application. Guests are stored in the database, rooms are tracked with a price per night, and reservations record who stays in which room and for how long.
The interesting part of this project is date logic. A reservation is only valid if the room is free for the whole stay, so you will learn how to detect overlapping bookings with a single SQL query.
The system has three tables: guests, rooms, and reservations. A reservation links one guest to one room and stores the check-in and check-out dates. The status of a reservation moves through the lifecycle reserved to checked_in to completed, and a reservation can be cancelled instead.
The application is a text menu in the terminal. Staff can add guests and rooms, make reservations, check guests in and out, cancel bookings, list which rooms are free on any date, and see revenue by room type. Building it teaches you joins, DATEDIFF, subqueries, and how to guard a business rule (no double bookings) inside application code.
mysql-connector-python package.input()..sql file.YYYY-MM-DD dates, because reservations depend on them.Install the connector package with pip:
pip install mysql-connector-python
Create the database by loading the schema file once, before the first run:
mysql -u root -p < database.sql
The tutorial assumes your MySQL user is root. If you use another user, change the settings in db_connection.py.
hotel-booking/
├── database.sql
├── db_connection.py
├── main.py
├── requirements.txt
└── README.md
database.sql — creates the hotel_db database, the three tables, and inserts seed data.db_connection.py — contains the get_connection() helper that returns a MySQL connection.main.py — the menu loop and one function per business operation.requirements.txt — lists the Python package needed to run the project.README.md — short setup notes.Three tables. guests stores the people who stay. rooms stores the rooms, each with a number, type, nightly price, and current status. reservations links a guest to a room for a date range and tracks the lifecycle status.
A hotel has two kinds of state. Rooms have a current state (available or occupied right now), and reservations are the history of who was in which room and when. Keeping those separate is important. A room can be occupied today because of a reservation made last week, so you cannot tell from the room alone what is booked tomorrow.
Create the database.sql file with the schema below.
CREATE DATABASE IF NOT EXISTS hotel_db;
USE hotel_db;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS guests;
CREATE TABLE guests (
guest_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15) NOT NULL
);
CREATE TABLE rooms (
room_id INT AUTO_INCREMENT PRIMARY KEY,
room_no VARCHAR(10) UNIQUE NOT NULL,
room_type ENUM('standard', 'deluxe', 'suite') NOT NULL,
price DECIMAL(10,2) NOT NULL,
status ENUM('available', 'occupied') NOT NULL DEFAULT 'available'
);
CREATE TABLE reservations (
reservation_id INT AUTO_INCREMENT PRIMARY KEY,
guest_id INT NOT NULL,
room_id INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
status ENUM('reserved', 'checked_in', 'completed', 'cancelled') NOT NULL DEFAULT 'reserved',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (guest_id) REFERENCES guests(guest_id) ON DELETE CASCADE,
FOREIGN KEY (room_id) REFERENCES rooms(room_id) ON DELETE CASCADE
);
The DROP TABLE statements run in dependency order: reservations first because it references the other two, then rooms and guests. The room_no column is UNIQUE so two rooms never share a number. The ENUM on status restricts the reservation lifecycle to four valid values, which keeps typos like complet out of the data. Check-in and check-out are DATE, not DATETIME, because a hotel cares about days, not clock times. Both foreign keys use ON DELETE CASCADE, so removing a guest also removes their reservations.
Starting rows: six guests, six rooms across the three types, and a mix of reservations with different statuses.
Fresh data lets you test overlap checks, check-in, check-out, and the revenue report without typing everything by hand. The reservations are spread across several months so the reports show real numbers on the first run.
Append these INSERT statements to database.sql.
INSERT INTO guests (first_name, last_name, email, phone) VALUES
('Meera', 'Nair', 'meera.nair@example.com', '9876500001'),
('Arjun', 'Mehta', 'arjun.mehta@example.com', '9876500002'),
('Kavya', 'Iyer', 'kavya.iyer@example.com', '9876500003'),
('Rahul', 'Verma', 'rahul.verma@example.com', '9876500004'),
('Ananya', 'Das', 'ananya.das@example.com', '9876500005'),
('Sameer', 'Joshi', 'sameer.joshi@example.com', '9876500006');
INSERT INTO rooms (room_no, room_type, price, status) VALUES
('101', 'standard', 1500.00, 'occupied'),
('102', 'standard', 1500.00, 'available'),
('201', 'deluxe', 3000.00, 'available'),
('202', 'deluxe', 3000.00, 'available'),
('301', 'suite', 6000.00, 'available'),
('302', 'suite', 6000.00, 'available');
INSERT INTO reservations (guest_id, room_id, check_in, check_out, status) VALUES
(1, 3, '2026-08-10', '2026-08-12', 'reserved'),
(2, 1, '2026-08-05', '2026-08-08', 'checked_in'),
(3, 5, '2026-08-15', '2026-08-20', 'reserved'),
(4, 2, '2026-08-01', '2026-08-03', 'completed'),
(5, 4, '2026-08-18', '2026-08-22', 'reserved'),
(6, 6, '2026-07-10', '2026-07-14', 'completed'),
(1, 2, '2026-06-05', '2026-06-08', 'completed'),
(3, 3, '2026-05-20', '2026-05-24', 'completed');
Room 101 is marked occupied because reservation number 2 (Arjun) is currently checked into it; the other five rooms are available. The dates are not random. Reservations 1, 2, 3, and 5 all sit inside August so that when you test the available-rooms screen, only some rooms come back free. The completed rows across May, June, and July give the revenue report data to sum.
A small module with one function, get_connection(), that returns a connection to the hotel_db database.
Every operation in main.py opens a connection. A helper keeps the host, user, and password in one place, so changing your MySQL password later means editing one file instead of ten functions.
Create db_connection.py.
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="rootpassword",
database="hotel_db"
)
Replace rootpassword with your own MySQL password. The database argument selects hotel_db, so every query in the rest of the program runs against that database automatically. This file is imported by main.py with from db_connection import get_connection.
Two functions. add_guest() inserts a row into guests. add_room() inserts a row into rooms after checking that the room type is valid.
Reservations reference guests and rooms by foreign key. You cannot make a booking for a guest who was never added, so the application needs a way to create them first. These two functions are also the simplest examples of the insert-plus-commit pattern used everywhere in this project.
Add these functions to main.py.
import sys
from datetime import datetime
import mysql.connector
from db_connection import get_connection
def add_guest():
first_name = input("First name: ").strip()
last_name = input("Last name: ").strip()
email = input("Email: ").strip()
phone = input("Phone: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO guests (first_name, last_name, email, phone) VALUES (%s, %s, %s, %s)",
(first_name, last_name, email, phone)
)
conn.commit()
print(f"\nGuest added. Guest ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def add_room():
room_no = input("Room number: ").strip()
room_type = input("Room type (standard/deluxe/suite): ").strip().lower()
price = float(input("Price per night: ").strip())
if room_type not in ("standard", "deluxe", "suite"):
print("\nRoom type must be standard, deluxe, or suite.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO rooms (room_no, room_type, price) VALUES (%s, %s, %s)",
(room_no, room_type, price)
)
conn.commit()
print(f"\nRoom {room_no} added. Room ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
Every query uses %s placeholders and passes the values as a tuple. That is parameterized SQL, and it stops SQL injection: user input is always treated as data, never as part of the query. The cursor.lastrowid gives you the auto-generated ID of the row you just inserted, which is the quickest way to confirm the insert worked. The finally block closes the cursor and connection no matter what, so a failure never leaks a database connection.
make_reservation() checks the dates, finds the guest and room, verifies the room has no overlapping booking, and inserts a reservation with status reserved.
Double-booking a room is the classic hotel bug. If guest A stays from the 10th to the 12th, guest B cannot check in on the 11th. MySQL will not check this for you, so the application must reject any reservation whose date range overlaps an existing active booking.
Add three helpers plus the reservation function.
def get_guest(conn, cursor):
email = input("Guest email: ").strip()
cursor.execute(
"SELECT guest_id, first_name, last_name FROM guests WHERE email = %s",
(email,)
)
return email, cursor.fetchone()
def parse_dates():
check_in = input("Check-in date (YYYY-MM-DD): ").strip()
check_out = input("Check-out date (YYYY-MM-DD): ").strip()
try:
ci = datetime.strptime(check_in, "%Y-%m-%d").date()
co = datetime.strptime(check_out, "%Y-%m-%d").date()
except ValueError:
return None, None
return ci, co
def room_overlaps(cursor, room_id, check_in, check_out):
cursor.execute(
"""SELECT reservation_id FROM reservations
WHERE room_id = %s
AND status IN ('reserved', 'checked_in')
AND check_in < %s
AND check_out > %s""",
(room_id, check_out, check_in)
)
return cursor.fetchone() is not None
def make_reservation():
ci, co = parse_dates()
if ci is None or co is None:
print("\nDates must be in YYYY-MM-DD format.")
return
if co <= ci:
print("\nCheck-out must be after check-in.")
return
conn = get_connection()
cursor = conn.cursor()
try:
email, guest = get_guest(conn, cursor)
if guest is None:
print("\nNo guest found with that email.")
return
guest_id = guest[0]
room_no = input("Room number: ").strip()
cursor.execute("SELECT room_id FROM rooms WHERE room_no = %s", (room_no,))
room = cursor.fetchone()
if room is None:
print("\nRoom not found.")
return
room_id = room[0]
if room_overlaps(cursor, room_id, ci, co):
print("\nThat room is already booked for the selected dates.")
return
cursor.execute(
"INSERT INTO reservations (guest_id, room_id, check_in, check_out) VALUES (%s, %s, %s, %s)",
(guest_id, room_id, ci, co)
)
conn.commit()
print(f"\nReservation created. Reservation ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
The heart of this step is room_overlaps. A new stay from check_in to check_out clashes with an existing booking when the existing check_in is before the new check_out AND the existing check_out is after the new check_in. Written as existing.check_in < new.check_out AND existing.check_out > new.check_in, this single condition catches every kind of overlap. Only reserved and checked_in bookings count; completed and cancelled stays are history and should not block a room. parse_dates converts the typed text into real date objects, so invalid formats fail early with a friendly message instead of reaching MySQL.
check_in() moves a reservation from reserved to checked_in and marks the room occupied. check_out() computes the bill from the nights stayed, marks the reservation completed, and frees the room.
The check-in and check-out actions move a reservation through its lifecycle. Check-out also produces money, which is the reason the hotel exists. The bill is not stored as a separate amount; it is derived from the room price and the number of nights, which means the bill stays correct even if the price changes later.
Add the two functions.
def check_in():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"SELECT reservation_id, room_id FROM reservations WHERE reservation_id = %s AND status = 'reserved'",
(reservation_id,)
)
row = cursor.fetchone()
if row is None:
print("\nNo reserved reservation with that ID.")
return
reservation_id, room_id = row
cursor.execute(
"UPDATE reservations SET status = 'checked_in' WHERE reservation_id = %s",
(reservation_id,)
)
cursor.execute(
"UPDATE rooms SET status = 'occupied' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nGuest checked in. Reservation {reservation_id} is now checked_in.")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def check_out():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"""SELECT r.reservation_id, r.room_id, r.check_in, r.check_out, rm.price
FROM reservations r
JOIN rooms rm ON r.room_id = rm.room_id
WHERE r.reservation_id = %s AND r.status = 'checked_in'""",
(reservation_id,)
)
row = cursor.fetchone()
if row is None:
print("\nNo checked-in reservation with that ID.")
return
reservation_id, room_id, check_in_date, check_out_date, price = row
nights = (check_out_date - check_in_date).days
bill = nights * price
cursor.execute(
"UPDATE reservations SET status = 'completed' WHERE reservation_id = %s",
(reservation_id,)
)
cursor.execute(
"UPDATE rooms SET status = 'available' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nCheck-out complete. Nights: {nights}, Rate: {price:.2f}, Total bill: {bill:.2f}")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
The check_in query only matches reservations whose status is reserved, so you cannot check in the same guest twice. Two UPDATE statements run together: the reservation moves forward and the room becomes occupied. check_out uses a JOIN to bring the room's nightly price into the same result as the reservation dates. Subtracting the two date objects gives a timedelta, and .days turns it into an integer number of nights. Both functions catch ValueError as well as the database error, so typing letters where a reservation ID belongs prints a clean message instead of crashing.
cancel_reservation() marks an active reservation as cancelled and frees the room if it was occupied. list_available_rooms() shows every room that is free between two dates.
Guests cancel, and staff need to know what is still bookable. The cancel action must also release the room; otherwise a cancelled check-in would leave a room stuck as occupied forever. The available-rooms screen answers the most common front-desk question: "What do we have on those nights?"
Add the two functions.
def cancel_reservation():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"SELECT room_id, status FROM reservations WHERE reservation_id = %s",
(reservation_id,)
)
row = cursor.fetchone()
if row is None or row[1] not in ("reserved", "checked_in"):
print("\nNo active reservation with that ID.")
return
room_id, status = row
cursor.execute(
"UPDATE reservations SET status = 'cancelled' WHERE reservation_id = %s",
(reservation_id,)
)
if status == "checked_in":
cursor.execute(
"UPDATE rooms SET status = 'available' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nReservation {reservation_id} cancelled.")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def list_available_rooms():
ci, co = parse_dates()
if ci is None or co is None:
print("\nDates must be in YYYY-MM-DD format.")
return
if co <= ci:
print("\nCheck-out must be after check-in.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"""SELECT room_no, room_type, price FROM rooms
WHERE room_id NOT IN (
SELECT room_id FROM reservations
WHERE status IN ('reserved', 'checked_in')
AND check_in < %s AND check_out > %s
)
ORDER BY room_no""",
(co, ci)
)
rows = cursor.fetchall()
print(f"\nAvailable rooms between {ci} and {co}")
print("-" * 45)
for room_no, room_type, price in rows:
print(f"{room_no} | {room_type:8s} | {price:.2f}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
finally:
cursor.close()
conn.close()
The cancel function reads the reservation first and only continues if the status is still active. Cancelling a checked_in reservation runs a second UPDATE to set the room back to available; a reserved one never touched the room status, so no room update is needed. The available-rooms query turns the overlap rule upside down with NOT IN: instead of asking which rooms clash, it asks which rooms have no clashing reservation. The subquery uses the same overlap condition as room_overlaps, so the two screens can never disagree about what is free.
revenue_by_type() sums completed stays grouped by room type, and main() ties everything together with the menu loop.
The revenue report shows what the business actually made, and it demonstrates grouping, SUM, and DATEDIFF in one query. The menu is the user interface; without it the functions exist but nobody can reach them.
Add the final two functions.
def revenue_by_type():
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"""SELECT rm.room_type,
COUNT(*) AS bookings,
SUM(DATEDIFF(r.check_out, r.check_in) * rm.price) AS revenue
FROM reservations r
JOIN rooms rm ON r.room_id = rm.room_id
WHERE r.status = 'completed'
GROUP BY rm.room_type
ORDER BY revenue DESC"""
)
rows = cursor.fetchall()
print("\nRevenue by room type (completed stays)")
print("-" * 50)
for room_type, bookings, revenue in rows:
print(f"{room_type:8s} | bookings: {bookings} | revenue: {revenue:.2f}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
finally:
cursor.close()
conn.close()
def main():
while True:
print("\n===== HOTEL BOOKING SYSTEM =====")
print("1. Add guest")
print("2. Add room")
print("3. Make a reservation")
print("4. Check in")
print("5. Check out")
print("6. Cancel a reservation")
print("7. List rooms available on a date")
print("8. Revenue by room type")
print("9. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_guest()
elif choice == "2":
add_room()
elif choice == "3":
make_reservation()
elif choice == "4":
check_in()
elif choice == "5":
check_out()
elif choice == "6":
cancel_reservation()
elif choice == "7":
list_available_rooms()
elif choice == "8":
revenue_by_type()
elif choice == "9":
print("\nGoodbye!")
sys.exit(0)
else:
print("\nInvalid option. Try again.")
if __name__ == "__main__":
main()
The revenue query joins reservations to rooms, filters to completed stays only, and groups by room type. DATEDIFF(check_out, check_in) returns the number of nights, which is multiplied by the nightly price before SUM adds everything up. The if __name__ == "__main__": guard starts the menu only when the file runs directly. The loop prints the options, reads a choice, and calls the matching function; an unknown choice prints a message and loops again instead of crashing.
Here is every file in the project. Create these files in the hotel-booking folder and you can run the whole project.
CREATE DATABASE IF NOT EXISTS hotel_db;
USE hotel_db;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS guests;
CREATE TABLE guests (
guest_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15) NOT NULL
);
CREATE TABLE rooms (
room_id INT AUTO_INCREMENT PRIMARY KEY,
room_no VARCHAR(10) UNIQUE NOT NULL,
room_type ENUM('standard', 'deluxe', 'suite') NOT NULL,
price DECIMAL(10,2) NOT NULL,
status ENUM('available', 'occupied') NOT NULL DEFAULT 'available'
);
CREATE TABLE reservations (
reservation_id INT AUTO_INCREMENT PRIMARY KEY,
guest_id INT NOT NULL,
room_id INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
status ENUM('reserved', 'checked_in', 'completed', 'cancelled') NOT NULL DEFAULT 'reserved',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (guest_id) REFERENCES guests(guest_id) ON DELETE CASCADE,
FOREIGN KEY (room_id) REFERENCES rooms(room_id) ON DELETE CASCADE
);
INSERT INTO guests (first_name, last_name, email, phone) VALUES
('Meera', 'Nair', 'meera.nair@example.com', '9876500001'),
('Arjun', 'Mehta', 'arjun.mehta@example.com', '9876500002'),
('Kavya', 'Iyer', 'kavya.iyer@example.com', '9876500003'),
('Rahul', 'Verma', 'rahul.verma@example.com', '9876500004'),
('Ananya', 'Das', 'ananya.das@example.com', '9876500005'),
('Sameer', 'Joshi', 'sameer.joshi@example.com', '9876500006');
INSERT INTO rooms (room_no, room_type, price, status) VALUES
('101', 'standard', 1500.00, 'occupied'),
('102', 'standard', 1500.00, 'available'),
('201', 'deluxe', 3000.00, 'available'),
('202', 'deluxe', 3000.00, 'available'),
('301', 'suite', 6000.00, 'available'),
('302', 'suite', 6000.00, 'available');
INSERT INTO reservations (guest_id, room_id, check_in, check_out, status) VALUES
(1, 3, '2026-08-10', '2026-08-12', 'reserved'),
(2, 1, '2026-08-05', '2026-08-08', 'checked_in'),
(3, 5, '2026-08-15', '2026-08-20', 'reserved'),
(4, 2, '2026-08-01', '2026-08-03', 'completed'),
(5, 4, '2026-08-18', '2026-08-22', 'reserved'),
(6, 6, '2026-07-10', '2026-07-14', 'completed'),
(1, 2, '2026-06-05', '2026-06-08', 'completed'),
(3, 3, '2026-05-20', '2026-05-24', 'completed');
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="rootpassword",
database="hotel_db"
)
import sys
from datetime import datetime
import mysql.connector
from db_connection import get_connection
def add_guest():
first_name = input("First name: ").strip()
last_name = input("Last name: ").strip()
email = input("Email: ").strip()
phone = input("Phone: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO guests (first_name, last_name, email, phone) VALUES (%s, %s, %s, %s)",
(first_name, last_name, email, phone)
)
conn.commit()
print(f"\nGuest added. Guest ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def add_room():
room_no = input("Room number: ").strip()
room_type = input("Room type (standard/deluxe/suite): ").strip().lower()
price = float(input("Price per night: ").strip())
if room_type not in ("standard", "deluxe", "suite"):
print("\nRoom type must be standard, deluxe, or suite.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO rooms (room_no, room_type, price) VALUES (%s, %s, %s)",
(room_no, room_type, price)
)
conn.commit()
print(f"\nRoom {room_no} added. Room ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def get_guest(conn, cursor):
email = input("Guest email: ").strip()
cursor.execute(
"SELECT guest_id, first_name, last_name FROM guests WHERE email = %s",
(email,)
)
return email, cursor.fetchone()
def parse_dates():
check_in = input("Check-in date (YYYY-MM-DD): ").strip()
check_out = input("Check-out date (YYYY-MM-DD): ").strip()
try:
ci = datetime.strptime(check_in, "%Y-%m-%d").date()
co = datetime.strptime(check_out, "%Y-%m-%d").date()
except ValueError:
return None, None
return ci, co
def room_overlaps(cursor, room_id, check_in, check_out):
cursor.execute(
"""SELECT reservation_id FROM reservations
WHERE room_id = %s
AND status IN ('reserved', 'checked_in')
AND check_in < %s
AND check_out > %s""",
(room_id, check_out, check_in)
)
return cursor.fetchone() is not None
def make_reservation():
ci, co = parse_dates()
if ci is None or co is None:
print("\nDates must be in YYYY-MM-DD format.")
return
if co <= ci:
print("\nCheck-out must be after check-in.")
return
conn = get_connection()
cursor = conn.cursor()
try:
email, guest = get_guest(conn, cursor)
if guest is None:
print("\nNo guest found with that email.")
return
guest_id = guest[0]
room_no = input("Room number: ").strip()
cursor.execute("SELECT room_id FROM rooms WHERE room_no = %s", (room_no,))
room = cursor.fetchone()
if room is None:
print("\nRoom not found.")
return
room_id = room[0]
if room_overlaps(cursor, room_id, ci, co):
print("\nThat room is already booked for the selected dates.")
return
cursor.execute(
"INSERT INTO reservations (guest_id, room_id, check_in, check_out) VALUES (%s, %s, %s, %s)",
(guest_id, room_id, ci, co)
)
conn.commit()
print(f"\nReservation created. Reservation ID: {cursor.lastrowid}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def check_in():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"SELECT reservation_id, room_id FROM reservations WHERE reservation_id = %s AND status = 'reserved'",
(reservation_id,)
)
row = cursor.fetchone()
if row is None:
print("\nNo reserved reservation with that ID.")
return
reservation_id, room_id = row
cursor.execute(
"UPDATE reservations SET status = 'checked_in' WHERE reservation_id = %s",
(reservation_id,)
)
cursor.execute(
"UPDATE rooms SET status = 'occupied' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nGuest checked in. Reservation {reservation_id} is now checked_in.")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def check_out():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"""SELECT r.reservation_id, r.room_id, r.check_in, r.check_out, rm.price
FROM reservations r
JOIN rooms rm ON r.room_id = rm.room_id
WHERE r.reservation_id = %s AND r.status = 'checked_in'""",
(reservation_id,)
)
row = cursor.fetchone()
if row is None:
print("\nNo checked-in reservation with that ID.")
return
reservation_id, room_id, check_in_date, check_out_date, price = row
nights = (check_out_date - check_in_date).days
bill = nights * price
cursor.execute(
"UPDATE reservations SET status = 'completed' WHERE reservation_id = %s",
(reservation_id,)
)
cursor.execute(
"UPDATE rooms SET status = 'available' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nCheck-out complete. Nights: {nights}, Rate: {price:.2f}, Total bill: {bill:.2f}")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def cancel_reservation():
conn = get_connection()
cursor = conn.cursor()
try:
reservation_id = int(input("Reservation ID: ").strip())
cursor.execute(
"SELECT room_id, status FROM reservations WHERE reservation_id = %s",
(reservation_id,)
)
row = cursor.fetchone()
if row is None or row[1] not in ("reserved", "checked_in"):
print("\nNo active reservation with that ID.")
return
room_id, status = row
cursor.execute(
"UPDATE reservations SET status = 'cancelled' WHERE reservation_id = %s",
(reservation_id,)
)
if status == "checked_in":
cursor.execute(
"UPDATE rooms SET status = 'available' WHERE room_id = %s",
(room_id,)
)
conn.commit()
print(f"\nReservation {reservation_id} cancelled.")
except (mysql.connector.Error, ValueError) as err:
print(f"\nError: {err}")
conn.rollback()
finally:
cursor.close()
conn.close()
def list_available_rooms():
ci, co = parse_dates()
if ci is None or co is None:
print("\nDates must be in YYYY-MM-DD format.")
return
if co <= ci:
print("\nCheck-out must be after check-in.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"""SELECT room_no, room_type, price FROM rooms
WHERE room_id NOT IN (
SELECT room_id FROM reservations
WHERE status IN ('reserved', 'checked_in')
AND check_in < %s AND check_out > %s
)
ORDER BY room_no""",
(co, ci)
)
rows = cursor.fetchall()
print(f"\nAvailable rooms between {ci} and {co}")
print("-" * 45)
for room_no, room_type, price in rows:
print(f"{room_no} | {room_type:8s} | {price:.2f}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
finally:
cursor.close()
conn.close()
def revenue_by_type():
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"""SELECT rm.room_type,
COUNT(*) AS bookings,
SUM(DATEDIFF(r.check_out, r.check_in) * rm.price) AS revenue
FROM reservations r
JOIN rooms rm ON r.room_id = rm.room_id
WHERE r.status = 'completed'
GROUP BY rm.room_type
ORDER BY revenue DESC"""
)
rows = cursor.fetchall()
print("\nRevenue by room type (completed stays)")
print("-" * 50)
for room_type, bookings, revenue in rows:
print(f"{room_type:8s} | bookings: {bookings} | revenue: {revenue:.2f}")
except mysql.connector.Error as err:
print(f"\nError: {err}")
finally:
cursor.close()
conn.close()
def main():
while True:
print("\n===== HOTEL BOOKING SYSTEM =====")
print("1. Add guest")
print("2. Add room")
print("3. Make a reservation")
print("4. Check in")
print("5. Check out")
print("6. Cancel a reservation")
print("7. List rooms available on a date")
print("8. Revenue by room type")
print("9. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_guest()
elif choice == "2":
add_room()
elif choice == "3":
make_reservation()
elif choice == "4":
check_in()
elif choice == "5":
check_out()
elif choice == "6":
cancel_reservation()
elif choice == "7":
list_available_rooms()
elif choice == "8":
revenue_by_type()
elif choice == "9":
print("\nGoodbye!")
sys.exit(0)
else:
print("\nInvalid option. Try again.")
if __name__ == "__main__":
main()
mysql-connector-python
# Hotel Booking System Project
A command-line hotel booking system built with Python and MySQL.
## Setup
1. Install the dependency: `pip install -r requirements.txt`
2. Load the schema and seed data: `mysql -u root -p < database.sql`
3. Run the application: `python main.py`
## Files
- `database.sql` - database schema and seed data
- `db_connection.py` - MySQL connection helper
- `main.py` - the menu-driven application
Install the connector package:
pip install mysql-connector-python
Load the schema and seed data. Run this once before the first launch:
mysql -u root -p < database.sql
Start the application:
python main.py
Your first run should look like this:
===== HOTEL BOOKING SYSTEM =====
1. Add guest
2. Add room
3. Make a reservation
4. Check in
5. Check out
6. Cancel a reservation
7. List rooms available on a date
8. Revenue by room type
9. Exit
Choose an option: 7
Check-in date (YYYY-MM-DD): 2026-08-11
Check-out date (YYYY-MM-DD): 2026-08-13
Available rooms between 2026-08-11 and 2026-08-13
---------------------------------------------
101 | standard | 1500.00
102 | standard | 1500.00
202 | deluxe | 3000.00
301 | suite | 6000.00
302 | suite | 6000.00
Work through these scenarios after the project runs. Each one checks a specific business rule.
Input
Option: 3 (Make a reservation)
Check-in date (YYYY-MM-DD): 2026-08-11
Check-out date (YYYY-MM-DD): 2026-08-14
Guest email: meera.nair@example.com
Room number: 201
Expected Output
That room is already booked for the selected dates.
Explanation
Room 201 already has a reservation from 2026-08-10 to 2026-08-12. The new stay overlaps it, so the overlap condition existing.check_in < new.check_out AND existing.check_out > new.check_in is true and the insert is blocked. Nothing is written to the database.
Input
Option: 3 (Make a reservation)
Check-in date (YYYY-MM-DD): 2026-09-01
Check-out date (YYYY-MM-DD): 2026-09-03
Guest email: arjun.mehta@example.com
Room number: 301
Expected Output
Reservation created. Reservation ID: 9
Explanation
Room 301 has a booking from 2026-08-15 to 2026-08-20, but that stay ends long before September 1. The overlap check finds nothing, so a new reservation row is inserted with status reserved.
Input
Option: 5 (Check out)
Reservation ID: 2
Expected Output
Check-out complete. Nights: 3, Rate: 1500.00, Total bill: 4500.00
Explanation
Reservation 2 is Arjun in room 101, checked in from 2026-08-05 to 2026-08-08. That is three nights at 1500 per night. The JOIN brings the price into the query, DATEDIFF-style subtraction gives three nights, and the bill comes to 4500. The reservation becomes completed and room 101 returns to available.
Input
Option: 7 (List rooms available on a date)
Check-in date (YYYY-MM-DD): 2026-08-11
Check-out date (YYYY-MM-DD): 2026-08-13
Expected Output
Available rooms between 2026-08-11 and 2026-08-13
---------------------------------------------
101 | standard | 1500.00
102 | standard | 1500.00
202 | deluxe | 3000.00
301 | suite | 6000.00
302 | suite | 6000.00
Explanation
Only room 201 is excluded, because its reservation from the 10th to the 12th overlaps the requested window. Rooms 101, 102, 202, 301, and 302 have no clashing bookings, so the NOT IN subquery keeps them in the result.
ModuleNotFoundError: No module named 'mysql.connector'Reason: The mysql-connector-python package is not installed in the Python environment you are running.
Solution: Run pip install mysql-connector-python. If you use a virtual environment, activate it first, then install and run the program from inside that environment.
Reason: The UPDATE ran but conn.commit() was never called, so MySQL kept the old data. The connection closed and the change was discarded.
Solution: Call conn.commit() after every statement that changes data, and conn.rollback() in the except block. The cancel function here commits both the reservation update and the room update together.
1452 Cannot add or update a child row: a foreign key constraint failsReason: The reservation references a guest_id or room_id that does not exist. This usually happens when the guest or room was typed in but never actually inserted.
Solution: Create the guest and the room first, then make the reservation. The program looks up both before inserting, so this error mainly appears when you mix up an ID or reuse data from an older database.
1062 Duplicate entry '201' for key 'rooms.room_no'Reason: You tried to add a room whose number already exists in the table. room_no is UNIQUE.
Solution: Pick a different room number, or check the existing rooms first with the available-rooms screen. The same rule applies to guest emails, which are also UNIQUE.
ValueError: time data '11-08-2026' does not match format '%Y-%m-%d'Reason: parse_dates expects dates in YYYY-MM-DD format, but you typed the day first.
Solution: Always type dates with the year first, for example 2026-08-11. Non-zero-padded dates such as 2026-8-1 fail too; write 2026-08-01.
Access denied for user 'root'@'localhost' (using password: YES)Reason: The password in db_connection.py does not match your MySQL root password.
Solution: Update the password value in db_connection.py. Test the credentials in the MySQL client first with mysql -u root -p; whatever password works there is the one the file needs.
beds column to rooms and a guest-per-room limit checked at reservation time.ON DELETE CASCADE for reservations with a rule that prevents deleting guests who have stays on record.SUM(DATEDIFF(check_out, check_in)) grouped by room_type.Save your progress and earn XP for completing tutorials.
Technology
MySQL
Lesson group
Real-World Projects
Progress
67% complete