Create python program to create,read,update and delete student details in MySQL database
import mysql.connector as p
con = p.connect(host="localhost",user="root",passwd="1234",database="useless")
if con.is_connected():
print("Connection Established")
cur = con.cursor()
while True:
print("Enter 1 to add new student")
print("Enter 2 to update student details")
print("Enter 3 to delete student")
print("Enter 4 to show student list")
print("Enter 5 to exit system")
ch = int(input())
if ch==1: #insert data into table
ids = int(input("Enter the student id:"))
name = input("Enter the student name:")
cls = int(input("Enter student class:"))
q = "insert into student(id,name,class) values({},'{}',{})".format(ids,name,cls)
cur.execute(q)
con.commit()
elif ch==2:
k = int(input("Enter 1 to change name\nEnter 2 to change class:\n"))
if k==1:
ids = int(input("Enter the student id:"))
name = input("Enter the new name:")
q = "update student set name='{}' where id={}".format(name,ids)
cur.execute(q)
con.commit()
elif k==2:
ids = int(input("Enter the student id:"))
cls = int(input("Enter student class:"))
q = "update student set class={} where id={}".format(cls,ids)
cur.execute(q)
con.commit()
else:
print("Wrong Option Selected")
elif ch==3:
ids = int(input("Enter the student id you want remove:"))
q = "delete from student where id={}".format(ids)
cur.execute(q)
con.commit()
elif ch==4:
k = int(input("Enter 1 to display entire list\nEnter 2 to search by starting alphabet:\n"))
if k==1:
print("The records are:")
q = "select * from student"
cur.execute(q)
data = cur.fetchall() #a list of tuples,where each tuple represents a row
for i in data:
print(i)
elif k==2:
t=input("Enter the letter:")
q = "select * from student where name like '{}%'".format(t)
cur.execute(q)
data = cur.fetchall()
if data==[]:
print("No names found")
else:
print("The names are:")
for i in data:
print(i)
else:
print("Wrong option selected")
else:
print("Thank You :D")
con.close()
break
else:
print("Access Denied")