CS22311 – DATABASE MANAGEMENT SYSTEM LABORATORY
Ex No:
12
HOTEL RESERVATION SYSTEM
DATE:
AIM:
To Develop Hotel reservation System by connecting database with python front end tools
OBJECTIVE:
The goal of this project is to develop a Hotel Management System that allows hotel staff to efficiently
manage customer details, including adding, updating, searching, deleting, and displaying customer
information. The system features an intuitive graphical user interface (GUI) built with Tkinter in Python
and utilizes a MySQL database to store customer records.
FRONT END:
Python With Tkinter
BACKEND:
MySql Database
MODULES USED:
Adding Customer Details: Staff can input and save customer data, including customer ID, name, room
number, check-in/check-out dates, contact information, and address.
Searching Customers: Users can search for customers by room number to find specific reservations.
Updating Customer Records: Existing customer data can be modified, such as changing the room
number or contact details.
Deleting Customer Records: Staff can remove customer records from the system.
Displaying Customer Information: A table view displays all customer records stored in the database,
allowing easy viewing and management.
Database Interaction: All operations (adding, updating, deleting) interact with a MySQL database to
ensure persistent storage of customer data.
PROGRAM:
import tkinter as tk
from tkinter import ttk, messagebox
import mysql.connector
from mysql.connector import Error
# List to store customer details
customer_list = []
RegNO : 2127230501158 PAGE NO:
def
connect_to_db():
try:
connection =
mysql.connector.connect( host="loc
alhost",
user="root",
password="Tanish@2006",
database="hotel"
)
if connection.is_connected():
return connection
except Error as e:
messagebox.showerror("Database Error", f"Error connecting to MySQL: {e}")
return None
# Function to save customer data to the database
def save_customer():
customer = {
"Customer ID":
entry_customer_id.get(), "Name":
entry_name.get(),
"Room No": entry_room_no.get(),
"Check-In": entry_check_in.get(),
"Check-Out": entry_check_out.get(),
"Contact": entry_contact.get(),
"Address": entry_address.get(),
}
try:
connection = connect_to_db()
if connection:
cursor = connection.cursor()
sql = """INSERT INTO hmt (customer_id, name, room_no, check_in, check_out, contact, address)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
cursor.execute(sql, (customer["Customer ID"], customer["Name"], customer["Room
No"], customer["Check-In"], customer["Check-Out"], customer["Contact"],
customer["Address"]))
connection.commit()
messagebox.showinfo("Success", "Customer details saved successfully")
# Update table to show new data
update_table()
except Error as e:
messagebox.showerror("Database Error", f"Failed to insert data into MySQL table: {e}")
finally:
if
connection.is_connected()
: cursor.close()
connection.close()
clear_entries()
def clear_entries():
entry_customer_id.delete(0, tk.END)
entry_name.delete(0, tk.END)
entry_room_no.delete(0, tk.END)
entry_check_in.delete(0, tk.END)
entry_check_out.delete(0, tk.END)
RegNO : 2127230501158 PAGE NO:
entry_contact.delete(0, tk.END)
RegNO : 2127230501158 PAGE NO:
entry_address.delete(0, tk.END)
# Function to update the table with customer data from the database
def update_table():
hotel_table.delete(*hotel_table.get_children())
customer_list.clear()
try:
connection = connect_to_db()
if connection:
cursor = connection.cursor()
cursor.execute("SELECT customer_id, name, room_no, check_in, check_out, contact, address FROM hmt")
records = cursor.fetchall()
for record in records:
customer = {
"Customer ID": record[0],
"Name": record[1],
"Room No": record[2],
"Check-In": record[3],
"Check-Out": record[4],
"Contact": record[5],
"Address": record[6],
}
customer_list.append(customer)
hotel_table.insert('', 'end', values=(
customer["Customer ID"], customer["Name"], customer["Room No"],
customer["Check-In"], customer["Check-Out"], customer["Contact"], customer["Address"]
))
except Error as e:
messagebox.showerror("Database Error", f"Failed to retrieve data from MySQL table:
{e}") finally:
if connection.is_connected():
cursor.close()
connection.close()
# Function to search customer data by Room No
def search_customer():
search_term = search_entry.get().lower()
hotel_table.delete(*hotel_table.get_children())
found_results =
False try:
connection = connect_to_db()
if connection:
cursor = connection.cursor()
cursor.execute("SELECT customer_id, name, room_no, check_in, check_out, contact, address FROM hmt")
records = cursor.fetchall()
for record in records:
customer = {
"Customer ID": record[0],
"Name": record[1],
"Room No": record[2],
"Check-In": record[3],
"Check-Out": record[4],
"Contact": record[5],
"Address": record[6],
RegNO : 2127230501158 PAGE NO:
}
if str(customer["Room No"]).lower() == search_term:
hotel_table.insert('', 'end', values=(
customer["Customer ID"], customer["Name"], customer["Room No"],
customer["Check-In"], customer["Check-Out"], customer["Contact"], customer["Address"]
))
found_results = True
except Error as e:
messagebox.showerror("Database Error", f"Failed to retrieve data from MySQL table:
{e}") finally:
if connection.is_connected():
cursor.close()
connection.close()
if not found_results:
messagebox.showinfo("Search Results", "No matching room number found.")
# Function to delete customer data from the database
def delete_customer():
selected_item =
hotel_table.selection() if not
selected_item:
messagebox.showwarning("Delete Error", "Please select a customer to delete.")
return
customer_id = hotel_table.item(selected_item, "values")[0]
confirm = messagebox.askyesno("Confirm Delete", f"Are you sure you want to delete customer ID
{customer_id}?")
if confirm:
try:
connection = connect_to_db()
if connection:
cursor = connection.cursor()
sql = "DELETE FROM hmt WHERE customer_id = %s"
cursor.execute(sql, (customer_id,))
connection.commit()
messagebox.showinfo("Success", "Customer details deleted successfully")
update_table()
except Error as e:
messagebox.showerror("Database Error", f"Failed to delete data from MySQL table: {e}")
finally:
if
connection.is_connected()
: cursor.close()
connection.close()
# Function to update customer data in the database
def update_customer():
selected_item =
hotel_table.selection() if not
selected_item:
messagebox.showwarning("Update Error", "Please select a customer to update.")
return
customer_id = hotel_table.item(selected_item, "values")[0] # Get selected customer ID
updated_customer = {
RegNO : 2127230501158 PAGE NO:
"Name": entry_name.get(),
"Room No":
entry_room_no.get(), "Check-In":
entry_check_in.get(),
RegNO : 2127230501158 PAGE NO:
"Check-Out": entry_check_out.get(),
"Contact": entry_contact.get(),
"Address": entry_address.get(),
}
confirm = messagebox.askyesno("Confirm Update", f"Are you sure you want to update customer ID
{customer_id}?")
if confirm:
try:
connection = connect_to_db()
if connection:
cursor = connection.cursor()
sql = """UPDATE hmt SET name = %s, room_no = %s, check_in = %s, check_out = %s, contact = %s,
address = %s
WHERE customer_id = %s"""
cursor.execute(sql, (updated_customer["Name"], updated_customer["Room
No"], updated_customer["Check-In"], updated_customer["Check-
Out"], updated_customer["Contact"],
updated_customer["Address"], customer_id)) # Update by
customer_id
connection.commit()
messagebox.showinfo("Success", "Customer details updated successfully")
# Update table to show updated data
update_table()
clear_entries()
except Error as e:
messagebox.showerror("Database Error", f"Failed to update data in MySQL table: {e}")
finally:
if
connection.is_connected()
: cursor.close()
connection.close()
# Function to display selected customer details in input fields
def display_selected_customer(event):
selected_item =
hotel_table.selection() if
selected_item:
customer_data = hotel_table.item(selected_item, "values")
entry_customer_id.delete(0, tk.END)
entry_customer_id.insert(0, customer_data[0])
entry_name.delete(0, tk.END)
entry_name.insert(0, customer_data[1])
entry_room_no.delete(0, tk.END)
entry_room_no.insert(0, customer_data[2])
entry_check_in.delete(0, tk.END)
entry_check_in.insert(0, customer_data[3])
entry_check_out.delete(0, tk.END)
entry_check_out.insert(0, customer_data[4])
entry_contact.delete(0, tk.END)
entry_contact.insert(0, customer_data[5])
entry_address.delete(0, tk.END)
entry_address.insert(0, customer_data[6])
# Main application window
root = tk.Tk()
root.title("HOTEL MANAGEMENT SYSTEM")
# Color scheme
RegNO : 2127230501158 PAGE NO:
bg_color = "#2F4F4F"
frame_color = "#A9A9A9"
RegNO : 2127230501158 PAGE NO:
# Title
title_label = tk.Label(root, text="HOTEL MANAGEMENT", font=("Arial", 24), bd=10, relief=tk.GROOVE,
bg=frame_color, fg="white")
title_label.pack(side=tk.TOP, fill=tk.X, pady=10)
# Customer Input Frame
input_frame = tk.Frame(root, bd=10, relief=tk.GROOVE, padx=10, pady=10, bg=frame_color)
input_frame.pack(side=tk.TOP, fill=tk.X)
# Customer Details Entries
tk.Label(input_frame, text="Customer ID", font=("Arial", 12), bg=frame_color).grid(row=0, column=0, padx=10,
pady=5)
entry_customer_id = tk.Entry(input_frame, font=("Arial", 12))
entry_customer_id.grid(row=0, column=1, padx=10, pady=5)
tk.Label(input_frame, text="Name", font=("Arial", 12), bg=frame_color).grid(row=0, column=2, padx=10, pady=5)
entry_name = tk.Entry(input_frame, font=("Arial", 12))
entry_name.grid(row=0, column=3, padx=10, pady=5)
tk.Label(input_frame, text="Room No", font=("Arial", 12), bg=frame_color).grid(row=1, column=0, padx=10,
pady=5)
entry_room_no = tk.Entry(input_frame, font=("Arial", 12))
entry_room_no.grid(row=1, column=1, padx=10, pady=5)
tk.Label(input_frame, text="Check-In", font=("Arial", 12), bg=frame_color).grid(row=1, column=2, padx=10,
pady=5)
entry_check_in = tk.Entry(input_frame, font=("Arial", 12))
entry_check_in.grid(row=1, column=3, padx=10, pady=5)
tk.Label(input_frame, text="Check-Out", font=("Arial", 12), bg=frame_color).grid(row=2, column=0, padx=10,
pady=5)
entry_check_out = tk.Entry(input_frame, font=("Arial", 12))
entry_check_out.grid(row=2, column=1, padx=10, pady=5)
tk.Label(input_frame, text="Contact", font=("Arial", 12), bg=frame_color).grid(row=2, column=2, padx=10, pady=5)
entry_contact = tk.Entry(input_frame, font=("Arial", 12))
entry_contact.grid(row=2, column=3, padx=10, pady=5)
tk.Label(input_frame, text="Address", font=("Arial", 12), bg=frame_color).grid(row=3, column=0, padx=10, pady=5)
entry_address = tk.Entry(input_frame, font=("Arial", 12))
entry_address.grid(row=3, column=1, padx=10, pady=5)
# Buttons
tk.Button(input_frame, text="Save", font=("Arial", 12), command=save_customer, bg="darkgreen",
fg="white").grid(row=4, column=0, padx=10, pady=10)
tk.Button(input_frame, text="Clear", font=("Arial", 12), command=clear_entries, bg="darkred",
fg="white").grid(row=4, column=1, padx=10, pady=10)
tk.Button(input_frame, text="Delete", font=("Arial", 12), command=delete_customer, bg="darkorange",
fg="white").grid(row=4, column=2, padx=10, pady=10)
tk.Button(input_frame, text="Update", font=("Arial", 12), command=update_customer, bg="darkblue",
fg="white").grid(row=4, column=3, padx=10, pady=10)
# Search Frame
search_frame = tk.Frame(root, bd=10, relief=tk.GROOVE, padx=10, pady=10, bg=frame_color)
search_frame.pack(side=tk.TOP, fill=tk.X)
tk.Label(search_frame, text="Search Room_no ", font=("Arial", 12), bg=frame_color).grid(row=0, column=0,
padx=10, pady=5)
search_entry = tk.Entry(search_frame, font=("Arial", 12))
search_entry.grid(row=0, column=1, padx=10, pady=5)
RegNO : 2127230501158 PAGE NO:
tk.Button(search_frame, text="Search", font=("Arial", 12), command=search_customer, bg="blue",
fg="white").grid(row=0, column=2, padx=10, pady=5)
tk.Button(search_frame, text="Show All", font=("Arial", 12), command=update_table, bg="orange",
fg="white").grid(row=0, column=3, padx=10, pady=5)
# Table to display customer records
table_frame = tk.Frame(root, bd=10, relief=tk.GROOVE, padx=10, pady=10, bg=frame_color)
table_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=True)
columns = ("Customer ID", "Name", "Room No", "Check-In", "Check-Out", "Contact", "Address")
hotel_table = ttk.Treeview(table_frame, columns=columns, show='headings')
# Configure column headings
for col in columns:
hotel_table.heading(col, text=col)
hotel_table.pack(fill=tk.BOTH,
expand=True)
# Bind the display function to the selection event of the hotel_table
hotel_table.bind("<<TreeviewSelect>>", display_selected_customer)
# Populate the table initially
update_table()
# Start the GUI event loop
root.mainloop()
RegNO : 2127230501158 PAGE NO:
OUTPUT:
RESULT:
Thus the hotel reservation system was developed by connecting mysql database with python and
the project is executed and verified successfully
RegNO : 2127230501158 PAGE NO:
RegNO : 2127230501158 PAGE NO:
RegNO : 2127230501158 PAGE NO:
