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

 '''Practical No: 22: - Write a menu driven program to demonstrate add, display, update, delete and exit, performed on a table club containing (cid, cname, city) 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 club (cid int primary key, cname varchar(20),city varchar(20))")

c="y"

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

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

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

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

    print("4. Press 4 for Delete club 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):

        cid=int(input("Enter club Id: "))

        cname=input("Enter club Name: ")

        city=input("Enter club city: ")

        mycursor.execute("insert into club values(%s,%s,%s)",(cid,cname,city))

        con.commit()

    elif(choice==2):

        mycursor.execute("select * from club")

        myclubs=mycursor.fetchall()

        for x in myclubs:

            print(x)

    elif(choice==3):

        cid=int(input("Enter the club id for update: "))

        cname=input("Enter club New Name: ")

        city=input("Enter club New city: ")

        mycursor.execute("update club set cname=%s,city=%s where cid=%s",(cname,city,cid))

        con.commit()

    elif(choice==4):

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

        mycursor.execute("delete from club where mid=%s",(cid,))

        con.commit()

    elif(choice==5):

        break

    else:

        print("Wrong Choice")

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