PYMYSQL-3
import pymysql
# Connect to the database
conn = pymysql.connect(host='localhost',user='root',password='aaditya2007',)
# Create a cursor object
cur = conn.cursor()
#creating database
cur.execute('create database if not exists education;')
cur.execute('use education;')
#creating Table
cur.execute('create table if not exists university(u_id int(3),University varchar(100),Course
varchar (100),duration int(2),Fees int(5));')
# Insert 15 records into the table
cur.execute('''insert into university values
(431, 'Harvard University', 'Computer Science', 4, 50000),
(290, 'Stanford University', 'Data Science', 2, 40000),
(349, 'Massachusetts Institute of Technology', 'Electrical Engineering', 4, 55000),
(493, 'University of California, Berkeley', 'Business Administration', 2, 30000),
(534, 'Carnegie Mellon University', 'Artificial Intelligence', 3, 45000),
(632, 'University of Oxford', 'Law', 3, 35000),
(712, 'University of Cambridge', 'Mathematics', 3, 32000),
(804, 'National University of Singapore', 'Computer Engineering', 4, 25000),
(509, 'University of Toronto', 'Psychology', 3, 28000),
(910, 'University of Melbourne', 'Medicine', 4, 40000),
(611, 'ETH Zurich', 'Physics', 3, 22000),
(102, 'University of California, Los Angeles', 'Film Production', 2, 30000),
(135, 'University of Michigan', 'Economics', 3, 25000),
(234, 'Georgia Institute of Technology', 'Aerospace Engineering', 4, 35000),
(150, 'University of Illinois at Urbana-Champaign', 'Computer Information Systems', 4,
28000);''')
#Functions for 'university' table
def add_university():
import random
x=random.randint(100,999)
print("University id is ",x)
u_id=x
university=input("Enter name of the university : ")
course=input("Enter name of the course :")
duration=int(input("Enter duration of course : "))
fees=int((input("Enter fees of course : ")))
sol="insert into university
values('%d','%s','%s','%d','%d')"%(u_id,university,course,duration,fees)
cur.execute(sol)
conn.commit()
print('updated successfully')
g=cur.execute("select * from university;")
h=cur.fetchall()
for i in h:
print(i)
def update_name():
u_id=int(input("Enter university Id :"))
university=input("Enter updated university name : ")
sol="update university set university='%s' where u_id='%d' "%(university,u_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from university where u_id='%d' "%(u_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def update_course():
u_id=int(input("Enter university Id :"))
course=input("Enter updated course name : ")
sol="update university set course='%s' where u_id='%d' "%(course,u_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from university where u_id='%d' "%(u_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def update_duration():
u_id=int(input("Enter university Id :"))
duration=int(input("Enter updated duration : "))
sol="update university set duration='%d' where u_id='%d' "%(duration,u_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from university where u_id='%d' "%(u_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def update_fees():
u_id=int(input("Enter university Id :"))
fees=int(input("Enter updated fees : "))
sol="update university set fees='%d' where u_id='%d' "%(fees,u_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from university where u_id='%d' "%(u_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def delete_university():
u_id=int(input("Enter university id to be abandoned :"))
sol="delete from university where u_id='%d' "%(u_id)
cur.execute(sol)
conn.commit()
print(' Abandoned successfully')
def search_university():
u_id=int(input("Enter university id to search :"))
sol="select * from university where u_id='%d' "%(u_id)
cur.execute(sol)
h=cur.fetchall()
k=0
for i in h:
k=1
if k==1:
print("Yes this university id exists")
print(i)
if k==0:
print("No this university id doesn't exist")
#FUNCTIONS DEFINED TO UPDATE DATA OF UNIVERSITY
def update_data():
while True:
print("What do you want to update sir")
print("1.Update university name")
print("2.Update course ")
print("3.Update duration of course")
print("4.Update fees of course")
print("5.Get Back")
z=int(input("May I know your need SIR : "))
if z==1:
update_name()
elif z==2:
update_course()
elif z==3:
update_duration()
elif z==4:
update_fees()
elif z==5 :
print("Thank you coming sir")
break
else :
print("invalid choice sir , please try again")
continue
#USER INTERFACE
a=input("Enter your name : ")
print("Welcome",a,"to our program [UNIVERSITY]")
while True:
print("Kindly provide us your reason to visit by telling the serial number of following things ")
print("1.View the data of universities")
print("2.Add the data of universities")
print("3.Update the data of universities")
print("4.Search the data of universities")
print("5.Delete a data of universities")
print("6.Exit")
y=int(input("Enter serial number : "))
if y==1:
g=cur.execute("select * from university;")
h=cur.fetchall()
for i in h:
print(i)
elif y==2:
add_university()
elif y==3:
update_data()
elif y==4:
search_university()
elif y==5:
delete_university()
elif y==6:
print("Thank you for coming")
break
else:
print("Invalid choice")
continue
OUTPUT
Enter your name : peter
Welcome peter to our program [UNIVERSITY]
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 1
(431, 'Harvard University', 'Computer Science', 4, 50000)
(290, 'Stanford University', 'Data Science', 2, 40000)
(349, 'Massachusetts Institute of Technology', 'Electrical Engineering', 4, 55000)
(493, 'University of California, Berkeley', 'Business Administration', 2, 30000)
(534, 'Carnegie Mellon University', 'Artificial Intelligence', 3, 45000)
(632, 'University of Oxford', 'Law', 3, 35000)
(712, 'University of Cambridge', 'Mathematics', 3, 32000)
(804, 'National University of Singapore', 'Computer Engineering', 4, 25000)
(509, 'University of Toronto', 'Psychology', 3, 28000)
(910, 'University of Melbourne', 'Medicine', 4, 40000)
(611, 'ETH Zurich', 'Physics', 3, 22000)
(102, 'University of California, Los Angeles', 'Film Production', 2, 30000)
(135, 'University of Michigan', 'Economics', 3, 25000)
(234, 'Georgia Institute of Technology', 'Aerospace Engineering', 4, 35000)
(150, 'University of Illinois at Urbana-Champaign', 'Computer Information Systems', 4, 28000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 2
University id is 154
Enter name of the university : Delhi University
Enter name of the course :Electronics
Enter duration of course : 4
Enter fees of course : 50000
updated successfully
(431, 'Harvard University', 'Computer Science', 4, 50000)
(290, 'Stanford University', 'Data Science', 2, 40000)
(349, 'Massachusetts Institute of Technology', 'Electrical Engineering', 4, 55000)
(493, 'University of California, Berkeley', 'Business Administration', 2, 30000)
(534, 'Carnegie Mellon University', 'Artificial Intelligence', 3, 45000)
(632, 'University of Oxford', 'Law', 3, 35000)
(712, 'University of Cambridge', 'Mathematics', 3, 32000)
(804, 'National University of Singapore', 'Computer Engineering', 4, 25000)
(509, 'University of Toronto', 'Psychology', 3, 28000)
(910, 'University of Melbourne', 'Medicine', 4, 40000)
(611, 'ETH Zurich', 'Physics', 3, 22000)
(102, 'University of California, Los Angeles', 'Film Production', 2, 30000)
(135, 'University of Michigan', 'Economics', 3, 25000)
(234, 'Georgia Institute of Technology', 'Aerospace Engineering', 4, 35000)
(150, 'University of Illinois at Urbana-Champaign', 'Computer Information Systems', 4, 28000)
(154, 'Delhi University', 'Electronics', 4, 50000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 3
What do you want to update sir
1.Update university name
2.Update course
3.Update duration of course
4.Update fees of course
5.Get Back
May I know your need SIR : 1
Enter university Id :154
Enter updated university name : University of Delhi
updated successfully
(154, 'University of Delhi', 'Electronics', 4, 50000)
What do you want to update sir
1.Update university name
2.Update course
3.Update duration of course
4.Update fees of course
5.Get Back
May I know your need SIR : 2
Enter university Id :154
Enter updated course name : Electronics Engineering
updated successfully
(154, 'University of Delhi', 'Electronics Engineering', 4, 50000)
What do you want to update sir
1.Update university name
2.Update course
3.Update duration of course
4.Update fees of course
5.Get Back
May I know your need SIR : 3
Enter university Id :154
Enter updated duration : 3
updated successfully
(154, 'University of Delhi', 'Electronics Engineering', 3, 50000)
What do you want to update sir
1.Update university name
2.Update course
3.Update duration of course
4.Update fees of course
5.Get Back
May I know your need SIR : 4
Enter university Id :154
Enter updated fees : 40000
updated successfully
(154, 'University of Delhi', 'Electronics Engineering', 3, 40000)
What do you want to update sir
1.Update university name
2.Update course
3.Update duration of course
4.Update fees of course
5.Get Back
May I know your need SIR : 5
Thank you coming sir
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 4
Enter university id to search :154
Yes this university id exists
(154, 'University of Delhi', 'Electronics Engineering', 3, 40000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 5
Enter university id to be abandoned :154
Abandoned successfully
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of universities
2.Add the data of universities
3.Update the data of universities
4.Search the data of universities
5.Delete a data of universities
6.Exit
Enter serial number : 6
Thank you for coming
