Python Source Code

import mysql.connector

import pandas as pd

# Connect to the MySQL database

db = mysql.connector.connect(

host="localhost",

user="your_username",

password="your_password",

database="hotel_management"

# Create a cursor object to interact with the database

cursor = db.cursor()

# Create the tables for rooms and reservations

create_rooms_table = """

CREATE TABLE IF NOT EXISTS rooms (

room_id INT AUTO_INCREMENT PRIMARY KEY,

room_number INT NOT NULL,

room_type VARCHAR(255),

price DECIMAL(10, 2)
)

"""

create_reservations_table = """

CREATE TABLE IF NOT EXISTS reservations (

reservation_id INT AUTO_INCREMENT PRIMARY KEY,

room_id INT,

guest_name VARCHAR(255),

check_in DATE,

check_out DATE,

FOREIGN KEY (room_id) REFERENCES rooms(room_id)

"""

cursor.execute(create_rooms_table)

cursor.execute(create_reservations_table)

# Function to add a room to the database

def add_room(room_number, room_type, price):

insert_query = "INSERT INTO rooms (room_number, room_type, price) VALUES (%s, %s, %s)"

cursor.execute(insert_query, (room_number, room_type, price))

db.commit()

print(f"Room {room_number} added successfully.")

# Function to make a reservation

insert_query = "INSERT INTO reservations (room_id, guest_name, check_in, check_out) VALUES (%s,
%s, %s, %s)"

cursor.execute(insert_query, (room_id, guest_name, check_in, check_out))

db.commit()

print(f"Reservation for Room {room_id} made successfully.")

# Function to display available rooms

def display_available_rooms():

select_query = "SELECT * FROM rooms WHERE room_id NOT IN (SELECT room_id FROM reservations
WHERE check_out >= CURDATE())"

cursor.execute(select_query)

rooms_data = cursor.fetchall()

if rooms_data:

rooms_df = pd.DataFrame(rooms_data, columns=["Room ID", "Room Number", "Room Type",

print(rooms_df)

else:

print("No available rooms.")

# Function to display reservations

def display_reservations():

select_query = "SELECT * FROM reservations"

cursor.execute(select_query)

reservations_data = cursor.fetchall()

if reservations_data:
 reservations_df = pd.DataFrame(reservations_data, columns=["Reservation ID", "Room ID", "Guest
Name", "Check-in", "Check-out"])

print(reservations_df)

else:

print("No reservations found.")

# Main menu

while True:

print("\nHotel Management System")

print("1. Add Room")

print("2. Make Reservation")

print("3. Display Available Rooms")

print("4. Display Reservations")

print("5. Exit")

choice = input("Enter your choice: ")

if choice == "1":

room_number = int(input("Enter room number: "))

room_type = input("Enter room type: ")

price = float(input("Enter price per night: "))

add_room(room_number, room_type, price)

elif choice == "2":

display_available_rooms()

room_id = int(input("Enter the room ID you want to reserve: "))

guest_name = input("Enter guest name: ")

check_out = input("Enter check-out date (YYYY-MM-DD): ")

make_reservation(room_id, guest_name, check_in, check_out)

elif choice == "3":

display_available_rooms()

elif choice == "4":

display_reservations()

elif choice == "5":

print("Exiting the system.")

break

else:

print("Invalid choice. Please try again.")

# Close the database connection

db.close()

----------------------------------Completed----------completed-------------completed--------------------------------------
 Output
Hotel management system
1. Add Room

2. Make Reservation

3. Display Available Rooms

4. Display Reservations

5. Exit

Enter your choice: 1

Enter room number: 101

Enter room type: Single

Enter price per night: 100.00

Room 101 added successfully.

Hotel Management System

2. Make Reservation

3. Display Available Rooms

4. Display Reservations
5. Exit

Enter your choice: 2

Available Rooms:

Room ID: 101, Room Number: 101, Room Type: Single, Price: 100.0

Enter the room ID you want to reserve: 101

Enter guest name: John Doe

Enter check-in date (YYYY-MM-DD): 2023-10-15

Enter check-out date (YYYY-MM-DD): 2023-10-20

Reservation for Room 101 made successfully.

Hotel Management System

2. Make Reservation

3. Display Available Rooms

4. Display Reservations

5. Exit

Enter your choice: 3

Available Rooms:
Room ID: 102, Room Number: 102, Room Type: Single, Price: 100.0

Room ID: 201, Room Number: 201, Room Type: Double, Price: 150.0

Room ID: 202, Room Number: 202, Room Type: Double, Price: 150.0

Hotel Management System

2. Make Reservation

3. Display Available Rooms

4. Display Reservations

5. Exit

Enter your choice: 4

Reservations:

Reservation ID: 1, Room ID: 1, Guest Name: John Doe, Check-in: 2023-10-15,
Check-out: 2023-10-20

Hotel Management System

2. Make Reservation

3. Display Available Rooms

4. Display Reservations

5. Exit
Enter your choice: 5

Exiting the system.

>----------------------------------------------------------------------------------------------------<