#Menu driven code
import mysql.connector
db=mysql.connector.connect(host="localhost",
user="root",
password="root",
database="school")
con=db.cursor()
#TO SEARCH ANY RECORD
def search():
f=0
r=int(input("Enter roll no to be searched"))
query=("select * from personal where rno=%s"%r)
con.execute(query)
d=con.fetchone()
f=1
print(d)
if f!=1:
print("Record is not available")
#TO DELETE RECORD
def delete():
f=0
ch='y'
while True:
con.execute("select * from personal")
data=con.fetchall()
print()
print("Total number of rows:",con.rowcount)
print()
print("Data:")
for i in data:
print(i)
s=input("Choose the Roll no to be deleted")
try:
con.execute("delete from personal where rno=%s" %s)
db.commit()
except:
print("Data not updated")
db.rollback()
con.execute("select * from personal")
data=con.fetchall()
if data!=None:
for i in data:
if i[0]==s:
print("Remaining data is")
print(i,sep=" ")
print("Data deleted from database")
f=1
if f==0:
print("wrong input")
else:
print("Record is not available")
ch=input("Do you want to delete more record....y/n")
if ch=='N'or ch=='n':
break
#TO UPDATE ANY RECORD
def update():
f=0
r=int(input("Enter roll no to be updated"))
con.execute("select * from personal")
d=con.fetchall()
for i in d:
if i[0]==r:
n=input("Enter name to be changed")
c=input("Would you like to change location....y/n")
f=1
if f==0:
print("Roll no is not available in database ")
try:
if c=='y':
l=input("Enter city name")
query=("update personal set city='{}'where rno={}".format(l,r))
con.execute(query)
db.commit()
query=("update personal set name='{}'where rno={}".format(n,r))
con.execute(query)
db.commit()
print("Record updated")
print()
except:
print("Result not updated")
con.execute("select * from personal")
d=con.fetchall()
for i in d:
print(i)
#TO INSERT THE VALUE
def insert():
ch='y'
while True:
r=int(input("Enter roll no"))
n=input("Enter name")
c=int(input("Enter class"))
m=int(input("Enter marks"))
city=input("Enter city")
try:
query="insert into personal values(%s,%s,%s,%s,%s)"
val=(r,n,c,m,city)
con.execute(query,val)
db.commit()
except:
print("Data not updated")
db.rollback()
ch=input("Do you want to add more record....y/n")
if ch=='N' or ch=='n':
break
#TO DISPLAY DATA
def display():
query=("select * from personal")
con.execute(query)
data=con.fetchall()
for i in data:
if i!=None:
print(i[0],i[1],i[2],i[3],i[4],sep="::",end="\n")
else:
print("Record is not available")
#MAIN PROGRAM
def menu():
while True:
print()
print("Database Implementation")
print("To insert record")
print("To search")
print("To display")
print("To update")
print("To delete")
print()
c=int(input("press 1 to insert\npress 2 to display\npress 3 to search\npress 4 to
update\npress 5 to delete"))
if c==1:
insert()
elif c==2:
display()
elif c==3:
search()
elif c==4:
update()
elif c==5:
delete()
else:
print("Wrong choice")
menu()
