Practical - 23: Python MySql Connectivity for class 12 CS and IP

 

'''Practical No: 23: - Write a menu driven program to demonstrate add, display, update, delete and exit, performed on a Student table containing  (sid, sname, course) through python-MySql connectivity.'''

import mysql.connector

con=mysql.connector.connect(host="localhost",username="root",passwd="root")

mycursor=con.cursor()

mycursor.execute("create database if not exists spsharmag")

mycursor.execute("use spsharmag")

mycursor.execute("create table if not exists Student (sid int primary key, sname varchar(20), course varchar(20))")

c="y"

while(c=="y" or c=="Y"):

    print("1. Press 1 for add new Student: ")

    print("2. Press 2 for Show the details of Students: ")

    print("3. Press 3 for Update Student Details: ")

    print("4. Press 4 for Delete Student Details: ")

    print("5. Press 5 for Exit: ")

    choice=int(input("Enter Your Choice 1 or 2 or 3 or 4 or 5: "))

    if(choice==1):

        sid=int(input("Enter Student Id: "))

        sname=input("Enter Student Name: ")

        course=input("Enter Student Course: ")

        mycursor.execute("insert into Student values(%s,%s,%s)",(sid,sname,course))

        con.commit()

    elif(choice==2):

        mycursor.execute("select * from Student")

        mystudents=mycursor.fetchall()

        for x in mystudents:

            print(x)

    elif(choice==3):

        sid=int(input("Enter the Student id for update: "))

        sname=input("Enter Student New Name: ")

        course=input("Enter Student New Course: ")

        mycursor.execute("update Student set sname=%s,course=%s where sid=%s",(sname,course,sid))

        con.commit()

    elif(choice==4):

        cid=int(input("Enter the Student id for delete: "))

        mycursor.execute("delete from Student where sid=%s",(sid,))

        con.commit()

    elif(choice==5):

        break

    else:

        print("Wrong Choice")

    c=input("Press 'y' for continue and 'n' for exit: ")