Practical - 20: Python MySql Connectivity for class12 CS and IP

 

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

'''Write a menu driven program to demonstrate add, display, update, delete and exit. Performed on a table Book containing

(bid, bname, bprice) 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 book (bid int primary key,bname varchar(20),bprice float(5,2))")

c="y"

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

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

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

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

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

        bid=int(input("Enter Book Id: "))

        bname=input("Enter Book Name: ")

        bprice=float(input("Enter Book Price: "))

        mycursor.execute("insert into book values(%s,%s,%s)",(bid,bname,bprice))

        con.commit()

    elif(choice==2):

        mycursor.execute("select * from book")

        mybooks=mycursor.fetchall()

        for x in mybooks:

            print(x)

    elif(choice==3):

        bid=int(input("Enter the book id for update: "))

        bname=input("Enter Book New Name: ")

        bprice=float(input("Enter Book New Price: "))

        mycursor.execute("update book set bname=%s,bprice=%s where bid=%s",(bname,bprice,bid))

        con.commit()

    elif(choice==4):

        bid=int(input("Enter the book id for delete: "))

        mycursor.execute("delete from book where bid=%s",(bid,))

        con.commit()

    elif(choice==5):

        break

    else:

        print("Wrong Choice")

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