#Airline ticket reservation
import mysql.connector as mc
#Global Variables
myconnection=""
cursor=""
ursername=""
password=""
#Module to check MySQL connectivity
def MYSQLconnectioncheck():
global myconnection
global username
global password
username=input("\n Enter MySQL Server's Username: ")
password=input("\n Enter MySQL Server's Password: ")
myconnection=mc.connect(host="localhost",
user=username,password=password,database='airline_ticket_reservation')
if myconnection:
print("\n Congratulations! Your MySQL Connection has been established")
cursor=myconnection.cursor()
cursor.execute("Create database if not exists airline_ticket_reservation")
cursor.execute("Commit")
cursor.close()
return myconnection
else:
print('\n Error is establishing MySQL Connection, check username and/or
password!')
#TO DISPLAY TITLE OF PROJECT
def title():
print(""" IGPS
AIRLINE TICKET RESERVATION
_______________________________________________________________
Designed and maintained by
Herschell Mahendra- Class XII A
Azaan NV- Class XII A
""")
#Shows customer menu
def custmenu():
print()
print()
print("="*50)
print(" 0. Show all flights available")
print(" 1. Show Table Passenger")
print(" 2. Add new Passenger Detail")
print(" 3. Search flight by Flight Number")
print(" 4. Search flights by airport")
print(" 5. Show Food Menu")
print(" 6. Show luggage rates")
print(" 7. Show ALL Types of Seats and their Ticket Price ")
print(" 8. Show Type of Seats passenger has chosen and its Ticket Price ")
print(' 9. Billing')
print(" 10.Exit")
print("="*50)
print()
print()
#Displays Customer table
def create_customertable():
cursor=myconnection.cursor()
if myconnection:
c1=myconnection.cursor()
c1.execute('Select * from passenger')
a=c1.fetchall()
if a:
print('-'*76)
print('| name | address | Mobile_no |
homeairport |')
print('-'*76)
for i in a:
print("|",i[0],' '*(11-len(str(i[0]))),'|',i[1],' '*(14-
len(str(i[1]))),'|',i[2],' '*(16-len(str(i[2]))),'|',i[3],' '*(18-
len(str(i[3]))),'|')
print('-'*76)
#Function to add customer details
def add_customerdetails():
if myconnection:
c1=myconnection.cursor()
name=input("Enter Passenger Name: ")
address=input("Enter Passenger Address: ")
mobile=int(input("Enter passenger mobile no.: "))
homeairport=input('Enter nearest airport: ')
sql='insert into passenger (name, address, Mobile_no, homeairport) values
("{}","{}","{}","{}")'.format(name,address,mobile,homeairport)
c1.execute(sql)
c1.execute('Commit')
print("\nNew passenger profile created successfully")
c1.close()
else:
print('/nError establishing connection')
#To view all flights
def flightview():
cursor=myconnection.cursor()
if myconnection:
sql="Select * from flights"
cursor.execute(sql)
D=cursor.fetchall()
if D:
print('-'*113)
print("| s_no | AIRLINES_NAME | DEPARTURE | DESTINATION | FLIGHT_NO |
TIME_OF_DEPARTURE | TIME_OF_ARRIVAL | CHARGES |")
print('-'*113)
for i in D:
print("|",i[0],' '*(3-len(str(i[0]))),'|',i[1],' '*(12-
len(str(i[1]))),'|',i[2],' '*(10-len(str(i[2]))),'|',i[3],' '*(10-
len(str(i[3]))),'|',i[4],' '*(8-len(str(i[4]))),'|',i[5],' '*(14-
len(str(i[5]))),'|',i[6],' '*(17-len(str(i[6]))),'|',i[7],' '*(6-
len(str(i[7]))),'|')
print('-'*113)
#Function to search for flight by flight details
def flightnosearch():
cursor=myconnection.cursor()
if myconnection:
a=input('Enter Flight No.: ')
sql="Select FLIGHT_NO, AIRLINES_NAME, TIME_OF_DEPARTURE,TIME_OF_ARRIVAL,
CHARGES from flights where FLIGHT_NO='{}'".format(a)
cursor.execute(sql)
D=cursor.fetchall()
if D:
print('-'*113)
print("| FLIGHT_NO | AIRLINES_NAME |
TIME_OF_DEPARTURE | TIME_OF_ARRIVAL | CHARGES |")
print('-'*113)
for i in D:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(22-
len(str(i[1]))),'|',i[2],' '*(22-len(str(i[2]))),'|',i[3],' '*(20-
len(str(i[3]))),'|',i[4],' '*(12-len(str(i[4]))),'|')
print('-'*113)
else:
print("Record Not Found Try Again!")
return False
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
#MYSQL CONNECTION
def MYSQLconnection():
myconnection=mc.connect(host="localhost",
user=username,password=password,database='airline_ticket_reservation')
if myconnection:
cursor=myconnection.cursor()
return myconnection
else:
print('\n Error is establishing MySQL Connection, check username and/or
password!')
#Function to display flights by entering airport of departure
def flightplacesearch():
cursor=myconnection.cursor()
if myconnection:
a=input('Enter departure airport: ')
sql="Select FLIGHT_NO, AIRLINES_NAME, DESTINATION,
TIME_OF_DEPARTURE,TIME_OF_ARRIVAL, CHARGES from flights where
DEPARTURE='{}'".format(a)
cursor.execute(sql)
D=cursor.fetchall()
if D:
print('-'*135)
print("| FLIGHT_NO | AIRLINES_NAME | DESTINATION
| TIME_OF_DEPARTURE | TIME_OF_ARRIVAL | CHARGES |")
print('-'*135)
for i in D:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(22-
len(str(i[1]))),'|',i[2],' '*(18-len(str(i[2]))),'|',i[3],' '*(22-
len(str(i[3]))),'|',i[4],' '*(20-len(str(i[4]))),'|',i[5],' '*(12-
len(str(i[5]))),'|')
print('-'*135)
else:
print("Record Not Found Try Again!")
return False
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
#Function to display food table
def showfood():
cursor=myconnection.cursor()
if myconnection:
sql="Select * from food"
cursor.execute(sql)
a=cursor.fetchall()
if a:
print('-'*63)
print("| SERIAL_NO | FOOD | CHARGE
|")
print('-'*63)
for i in a:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(21-
len(str(i[1]))),'|',i[2],' '*(13-len(str(i[2]))),'|')
print('-'*63)
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
#Function to display luggage table
def show_luggage():
cursor=myconnection.cursor()
if myconnection:
sql='Select * from Luggage'
cursor.execute(sql)
a=cursor.fetchall()
if a:
print('-'*57)
print("| SERIAL_NO | WEIGHT | CHARGE |")
print('-'*57)
for i in a:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(15-
len(str(i[1]))),'|',i[2],' '*(13-len(str(i[2]))),'|')
print('-'*57)
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
#Shows seatrate for different classes
def show_seatrate():
cursor=myconnection.cursor()
if myconnection:
sql='Select * from seat_rate'
cursor.execute(sql)
a=cursor.fetchall()
if a:
print('-'*58)
print("| SERIAL_NO | CLASS | CHARGE |")
print('-'*58)
for i in a:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(16-
len(str(i[1]))),'|',i[2],' '*(13-len(str(i[2]))),'|')
print('-'*58)
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
#To know price of a seat in mentioned class
def seatratesearch():
cursor=myconnection.cursor()
if myconnection:
a=input('Enter desired class: ')
sql="Select Serial_no, Seat_class, Rate from seat_rate where
Seat_class='{}'".format(a)
cursor.execute(sql)
D=cursor.fetchall()
if D:
print('-'*58)
print("| SERIAL_NO | CLASS | CHARGE |")
print('-'*58)
for i in D:
print("|",i[0],' '*(16-len(str(i[0]))),'|',i[1],' '*(16-
len(str(i[1]))),'|',i[2],' '*(13-len(str(i[2]))),'|')
print('-'*58)
cursor.close()
else:
print('\nSomething Went Wrong, Please Try Again!')
return False
#Displays menu for staff login
def staffmenu():
print('1.Input new flight details')
print('2.Add new food item')
print('3.Change food rates')
print('4.Add new luggage configuration')
print('5.Change luggage rates')
print('6.Change class prices')
print('7. Exit')
#add flight details to the flight table
def addflight():
if myconnection:
cursor=myconnection.cursor()
from datetime import datetime
a=input('Airlines name: ')
b=input('Departure airport: ')
c=input('Arrival airport: ')
d=input('Flight no: ')
e=(input('time of departure: '))
f=(input("Time of arrival: "))
g=int(input('Enter rate: '))
sql="insert into flights values
('{}','{}','{}','{}','{}','{}','{}')".format(a,b,c,d,e,f,g)
cursor.execute(sql)
cursor.execute('Commit')
print('\nNew flight entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#Add food item to food table
def addfood():
if myconnection:
cursor=myconnection.cursor()
a=input('enter serial no.: ')
b=input("Enter food item: ")
c=int(input('Enter rate: '))
sql="Insert into food values('{}','{}','{}')".format(a,b,c)
cursor.execute(sql)
cursor.execute('Commit')
print('\nNew food item entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#To add luggage details to a table
def addluggage():
if myconnection:
cursor=myconnection.cursor()
a=input('enter serial no.: ')
b=input("Enter weight class: ")
c=int(input('Enter rate: '))
sql="Insert into Luggage values('{}','{}','{}')".format(a,b,c)
cursor.execute(sql)
cursor.execute('Commit')
print('\nNew weight class entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#To bring about changes in ther
def changefoodrate():
if myconnection:
cursor=myconnection.cursor()
a=input("Enter food item: ")
b=int(input('Enter new rate: '))
sql="update food set rate='{}' where food_type='{}'".format(a,b)
cursor.execute(sql)
cursor.execute('Commit')
print('\n Updated rate entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#To change class rate
def changeclassrate():
if myconnection:
cursor=myconnection.cursor()
a=input("Enter class: ")
b=int(input('Enter new rate: '))
sql="update seat_rate set rate='{}' where Seat_class='{}'".format(a,b)
cursor.execute(sql)
cursor.execute('Commit')
print('\n Updated rate entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#To change luggage rate
def changeluggagerate():
if myconnection:
cursor=myconnection.cursor()
a=input("Enter weight class: ")
b=int(input('Enter new rate: '))
sql="update Luggage set rate='{}' where Seat_class='{}'".format(a,b)
cursor.execute(sql)
cursor.execute('Commit')
print('\n Updated rate entered successfully')
cursor.close()
else:
print("\Error establishing connection")
#To check billing
def billing():
cursor=myconnection.cursor()
if myconnection:
flightview()
a=input('Enter flight number: ')
print()
showfood()
b=input("Enter food: ")
print()
show_seatrate()
c=input('Class type: ')
print()
show_luggage()
print()
d=int(input('Entire weight class: '))
g1=int(input("Enter no of adults: "))
h1=int(input('Enter no. of children: '))
sql="Select CHARGES from flights where FLIGHT_NO='{}'".format(a)
cursor.execute(sql)
x=cursor.fetchone()
for i in x:
w=i
sql="Select rate from food where food_type='{}'".format(b)
cursor.execute(sql)
y=cursor.fetchone()
for i in y:
x1=i
sql="Select rate from seat_rate where Seat_class='{}'".format(c)
cursor.execute(sql)
z=cursor.fetchone()
for i in z:
y1=i
sql="Select rate from Luggage where Weight_per_person='{}'".format(d)
cursor.execute(sql)
e=cursor.fetchone()
for i in e:
z1=i
print('Your total Bill comes to a grand total of Rs.',g1*(w+x1+y1+z1)+
(h1*(w+x1+y1+z1))/2)
#Main Program
title()
a= input("Enter username: ")
b= input("Enter password: ")
if a=="airlinehost" and b=="september11":
myconnection=MYSQLconnectioncheck()
if myconnection:
MYSQLconnection()
print('\n\n')
while True:
staffmenu()
choice=int(input('Enter Your choice: '))
if choice==1:
addflight()
elif choice==2:
addfood()
elif choice==3:
changefoodrate()
elif choice==4:
addluggage()
elif choice==5:
changeluggagerate()
elif choice==6:
changeclassrate()
elif choice==7:
print('Thank You!')
break
else:
print('\nError Establishing MYSQL Connection!')
else:
#
myconnection=MYSQLconnectioncheck()
if myconnection:
MYSQLconnection()
while True:
custmenu()
choice=int(input('Enter Your choice: '))
if choice==0:
flightview()
elif choice==1:
create_customertable()
elif choice==2:
add_customerdetails()
elif choice==3:
flightnosearch()
elif choice==4:
flightplacesearch()
elif choice==5:
showfood()
elif choice==6:
show_luggage()
elif choice==7:
show_seatrate()
elif choice==8:
seatratesearch()
elif choice==9:
billing()
elif choice==10:
print('Thank You!')
break
else:
print('\nError Establishing MYSQL Connection!')
