MY-SQL

DATABASE MANAGEMENT
WITH PYTHON

About MySQL
MySQL is an open-source relational database management system (RDBMS). . A
relational database organizes data into one or more data tables in which data types
may be related to each other; these relations help structure the data. SQL is a
language programmers use to create, modify, and extract data from the relational
database, as well as control user access to the database.

SUDHARSAN J

XII – C2
 ABOUT THE PROJECT

To put it simply, this project is a menu driven program

in python which aims to create a user- friendly way of
manipulating databases in MySQL and extracting
information from them.
THE CODE:
import mysql.connector
mydb=mysql.connector.connect(user='root',password=‘Root',host='localhost',database='hotel')
mycursor=mydb.cursor()
def registercust():
L=[]
name=input("enter name:-")
L.append(name) addr=input("enter
address:")L.append(addr)
indate=input("enter check in date:")
L.append(indate)
outdate=input("enter check out date:")
L.append(outdate)
cust=(L)
sql="insert into custdata (name, addr, indate, outdate) values (%s,%s,%s,%s)"mycursor.execute(sql,cust)
mydb.commit()
def roomtypeview():
print("Do yoy want to see room type available : Enter 1 for yes :")ch=int(input("enter
your choice:"))
if ch==1:
sql="select * from roomtype"
mycursor.execute(sql)
rows=mycursor.fetchall()
for x in rows:
print(x)
def roomrent():
print ("We have the following rooms for you:-")
print ("1. type A --- >rs 1000 PN\-")
print ("2. type B ---- >rs 2000 PN\-")
print ("3. type C ------- >rs 3000 PN\-")
print ("4. type D ------- >rs 4000 PN\-")
x=int(input("Enter Your Choice Please->"))
n=int(input("For How Many Nights Did You Stay:"))
if(x==1):
print ("you have opted room type A")
 s=1000*n
elif (x==2):
print ("you have opted room type B")
s=2000*n
elif (x==3):
print ("you have opted room type C")
s=3000*n
elif (x==4):
print ("you have opted room type D")
s=4000*n
else:
print ("please choose a room") print
("your room rent is =",s,"\n")
def restaurentmenuview():
print("Do yoy want to see mebu available : Enter 1 for yes :")ch=int(input("enter your
choice:"))
if ch==1:
sql="select * from restaurent"
mycursor.execute(sql)
rows=mycursor.fetchall()
for x in rows:
print(x)
def orderitem():global s
print("Do yoy want to see mebu available : Enter 1 for yes :")ch=int(input("enter your
choice:"))
if ch==1:
sql="select * from restaurent"
mycursor.execute(sql)
rows=mycursor.fetchall()
for x in rows:
print(x)
print("do you want to purchase from above list:enter your choice:")
d=int(input("enter your choice:"))
if(d==1):
print("you have ordered tea")
a=int(input("enter quantity"))
s=10*a
print("your amount for tea is :",s,"\n") elif
(d==2):
print("you have ordered coffee")
a=int(input("enter quantity"))
 s=10*a
print("your amount for coffee is :",s,"\n")
elif(d==3):
print("you have ordered colddrink")
a=int(input("enter quantity")) s=20*a
print("your amount for colddrink is :",s,"\n")
elif(d==4):
print("you have ordered samosa")
a=int(input("enter quantity")) s=10*a
print("your amount fopr samosa is :",s,"\n")
elif(d==5):
print("you have ordered sandwich")
a=int(input("enter quantity")) s=50*a
print("your amount fopr sandwich is :",s,"\n")
elif(d==6):
print("you have ordered dhokla")
a=int(input("enter quantity")) s=30*a
print("your amount for dhokla is :",s,"\n")
elif(d==7):
print("you have ordered kachori")
a=int(input("enter quantity")) s=10*a
print("your amount for kachori is :",s,"\n")
elif(d==8):
print("you have ordered milk")
a=int(input("enter quantity"))
s=20*a
print("your amount for kachori is :",s,"\n")
elif(d==9):
print("you have ordered noodles")
a=int(input("enter quantity")) s=50*a
print("your amount for noodles is :",s,"\n")
elif(d==10):
print("you have ordered pasta")
a=int(input("enter quantity")) s=50*a
print("your amount for pasta is :",s,"\n")
else:
print("please enter your choice from the menu")def
laundarybill():
global z
print("Do yoy want to see rate for laundary : Enter 1 for yes :") ch=int(input("enter your
choice:"))
if ch==1:
sql="select * from laundary"
mycursor.execute(sql)
rows=mycursor.fetchall() for x
in rows:
print(x)
y=int(input("Enter Your number of clothes->"))
z=y*10
print("your laundary bill:",z,"\n")
return z
def lb():
print(z)
def res():
print(s) def
viewbill():
a=input("enter customer name:")print("customer
name :",a,"\n") print("laundarey bill:")
print(lb) print("restaurent
bill:")print(res)
while True:
print("\nMENU")
print("enter 1: To enter customer data")

print("enter 2 : To view roomtype") print("enter 3 :

for calculating room bill") print("enter 4 : for
viewing restaurent menu")print("enter 5 : for
restaurent bill") print("enter 6 :for laundary bill")
print("enter 7 : for complete bill")
print("enter 8 : for exit:")
ch = int(input("\nEnter the Choice: "))if
ch==1:
registercust() elif
ch==2:
 roomtypeview()
elif ch==3:
roomrent()elif
ch==4:
restaurentmenuview()elif
ch==5:
orderitem()elif
ch==6:
laundarybill()
elif ch==7:
viewbill()
elif ch==8:
quit()
else:
print("QPPS! Select a correct option")

EXPLANATION:
Importing the Library
import mysql.connector
This line imports the mysql.connector library, which is used to connect
and interact with a MySQL database from Python

Establishing the Connection

mydb = mysql.connector.connect(
user='root',
password='Root',
host='localhost',
database='hotel'
)
mycursor = mydb.cursor()

Here, mydb establishes a connection to the MySQL server, and mycursor is

used to execute SQL commands.
Register Customer Function
def registercust():
L = []
name = input("Enter name: ")
L.append(name)
addr = input("Enter address: ")
L.append(addr)
indate = input("Enter check-in date: ")
L.append(indate)
outdate = input("Enter check-out date: ")
L.append(outdate)
cust = tuple(L)
sql = "INSERT INTO custdata (name, addr, indate, outdate) VALUES
(%s,%s,%s,%s)"
mycursor.execute(sql, cust)
mydb.commit()

This function collects customer details through user input, stores them in a
list L, converts the list to a tuple cust, and inserts the data into the cust data table.

Room Type View Function

def roomtypeview():
print("Do you want to see room type available? Enter 1 for yes:")
ch = int(input("Enter your choice: "))
if ch == 1:
sql = "SELECT * FROM roomtype"
mycursor.execute(sql)
rows = mycursor.fetchall()
for x in rows:
print(x)

This function displays available room types if the user chooses to see them.
Room Rent Calculation
def roomrent():
print("We have the following rooms for you:")
print("1. Type A - Rs 1000 per night")
print("2. Type B - Rs 2000 per night")
print("3. Type C - Rs 3000 per night")
print("4. Type D - Rs 4000 per night")

x = int(input("Enter your choice: "))

n = int(input("For how many nights did you stay? "))

if x == 1:
s = 1000 * n
elif x == 2:
s = 2000 * n
elif x == 3:
s = 3000 * n
elif x == 4:
s = 4000 * n
else:
print("Please choose a valid room type")
return
print("Your room rent is Rs", s)

This function calculates the room rent based on the user's choice and the number
of nights stayed.
Restaurant Menu View
def restaurentmenuview():
print("Do you want to see the menu available? Enter 1 for yes:")
ch = int(input("Enter your choice: "))
if ch == 1:
sql = "SELECT * FROM restaurent"
mycursor.execute(sql)
rows = mycursor.fetchall()
for x in rows:
print(x)

This function displays the restaurant menu if the user chooses to see it.

Order Items from Restaurant

def orderitem():
global s
print("Do you want to see the menu available? Enter 1 for yes:")
ch = int(input("Enter your choice: "))
if ch == 1:
sql = "SELECT * FROM restaurent"
mycursor.execute(sql)
rows = mycursor.fetchall()
for x in rows:
print(x)

print("Do you want to purchase from the above list? Enter your choice:")
d = int(input("Enter your choice: "))

if d == 1:
a = int(input("Enter quantity: "))
s = 10 * a
print("Your amount for tea is Rs", s)
elif d == 2:
a = int(input("Enter quantity: "))
s = 10 * a
print("Your amount for coffee is Rs", s)
else:
print("Please enter your choice from the menu")

This function lets the user order items from the restaurant menu and calculates the
total amount based on the quantity.
Laundry Bill Calculation
def laundarybill():
global z
print("Do you want to see the rate for laundry? Enter 1 for yes:")
ch = int(input("Enter your choice: "))
if ch == 1:
sql = "SELECT * FROM laundary"
mycursor.execute(sql)
rows = mycursor.fetchall()
for x in rows:
print(x)

y = int(input("Enter your number of clothes: "))

z = y * 10
print("Your laundry bill is Rs", z)
return z

This function calculates the laundry bill based on the number of clothes

Main Menu Loop

while True:
print("\nMENU")
print("1: To enter customer data")
print("2: To view room types")
print("3: For calculating room bill")
print("4: For viewing restaurant menu")
print("5: For restaurant bill")
print("6: For laundry bill")
print("7: For complete bill")
print("8: To exit")

ch = int(input("Enter your choice: "))

if ch == 1:
registercust()
elif ch == 2:
roomtypeview()
elif ch == 3:
roomrent()
elif ch == 4:
restaurentmenuview()
elif ch == 5:
orderitem()
elif ch == 6:
laundarybill()
elif ch == 7:
viewbill()
elif ch == 8:
quit()
else:
print("Oops! Select a correct option")

This loop provides a menu to interact with the various functions of the hotel
management system

OUTPUT:
THANK
YOU