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

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

'''Practical No. 21: Write a menu driven program to demonstrate add, display, update, delete and exit. Performed on a table product containing (pid, pname, pprice) 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 product (pid int primary key,pname varchar(20),pprice float(5,2))")

c="y"

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

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

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

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

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

        pid=int(input("Enter product Id: "))

        pname=input("Enter product Name: ")

        pprice=float(input("Enter product Price: "))

        mycursor.execute("insert into product values(%s,%s,%s)",(pid,pname,pprice))

        con.commit()

    elif(choice==2):

        mycursor.execute("select * from product")

        myproducts=mycursor.fetchall()

        for x in myproducts:

            print(x)

    elif(choice==3):

        pid=int(input("Enter the product id for update: "))

        pname=input("Enter product New Name: ")

        pprice=float(input("Enter product New Price: "))

        mycursor.execute("update product set pname=%s,pprice=%s where pid=%s",(pname,pprice,pid))

        con.commit()

    elif(choice==4):

        pid=int(input("Enter the product id for delete: "))

        mycursor.execute("delete from product where pid=%s",(pid,))

        con.commit()

    elif(choice==5):

        break

    else:

        print("Wrong Choice")

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