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

 

'''Practical No: 25: - Write a menu driven program to demonstrate add, display, update, delete and exit, performed on a Employee table containing (eid, ename, salary) 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 Employee (eid int primary key, ename varchar(20), salary float(8,2))")

c="y"

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

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

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

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

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

        eid=int(input("Enter Employee Id: "))

        ename=input("Enter Employee Name: ")

        salary=input("Enter Employee Salary: ")

        mycursor.execute("insert into Employee values(%s,%s,%s)",(eid,ename,salary))

        con.commit()

    elif(choice==2):

        mycursor.execute("select * from Employee")

        myemp=mycursor.fetchall()

        for x in myemp:

            print(x)

    elif(choice==3):

        eid=int(input("Enter the Employee id for update: "))

        ename=input("Enter Employee New Name: ")

        salary=input("Enter Employee New Salary: ")

        mycursor.execute("update Employee set ename=%s,salary=%s where eid=%s",(ename,salary,eid))

        con.commit()

    elif(choice==4):

        eid=int(input("Enter the Employee id for delete: "))

        mycursor.execute("delete from Employee where eid=%s",(eid,))

        con.commit()

    elif(choice==5):

        break

    else:

        print("Wrong Choice")

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

Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:43:08) [MSC v.1926 32 bit (Intel)] on win32

Type "help", "copyright", "credits" or "license()" for more information.

>>> 

= RESTART: C:/Users/S P SHARMA/AppData/Local/Programs/Python/Python38-32/Practical_25.py

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 1

Enter Employee Id: 101

Enter Employee Name: S P SHARMA

Enter Employee Salary: 234567

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 1

Enter Employee Id: 102

Enter Employee Name: Shubham

Enter Employee Salary: 123456

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 1

Enter Employee Id: 103

Enter Employee Name: Saanvi

Enter Employee Salary: 134567

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 2

(101, 'S P SHARMA', 234567.0)

(102, 'Shubham', 123456.0)

(103, 'Saanvi', 134567.0)

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 3

Enter the Employee id for update: 102

Enter Employee New Name: Shubham Bhardwaj

Enter Employee New Salary: 174568

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 2

(101, 'S P SHARMA', 234567.0)

(102, 'Shubham Bhardwaj', 174568.0)

(103, 'Saanvi', 134567.0)

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 4

Enter the Employee id for delete: 101

Press 'y' for continue and 'n' for exit: y

1. Press 1 for add new Employee: 

2. Press 2 for Show the details of Employees: 

3. Press 3 for Update Employee Details: 

4. Press 4 for Delete Employee Details: 

5. Press 5 for Exit: 

Enter Your Choice 1 or 2 or 3 or 4 or 5: 2

(102, 'Shubham Bhardwaj', 174568.0)

(103, 'Saanvi', 134567.0)

Press 'y' for continue and 'n' for exit: n

>>>