GRADE 12: PRACTICAL 18

'''WAP using Python-MySQL connectivity to create the given table and insert values. Create a menu driven program for : MENU: 1.DISPLAY DESIRED NUMBER OF RECORDS 2.DISPLAY TOTAL NUMBER OF ROWS 3.UPDATE SALARY 4.SEARCH EMPLOYEES WITH SALARY ABOVE RS. 50000

5.DELETE RECORD

TABLE : EMPLOYEE +-------+---------------+---------+--------+-----+------------+ | EOCDE | ENAME | DEPCODE | salary | AGE | JOINDATE | +-------+---------------+---------+--------+-----+------------+ | 15 | SAMEER SHARMA | 123 | 75000 | 39 | 2007-04-01 | | 21 | RAGVINDER K | 101 | 86000 | 29 | 2005-11-11 | | 81 | RAJESH KUMAR | 119 | 74500 | 48 | 2008-12-11 | | 89 | SANJEEV P | 101 | 92600 | 54 | 2009-01-12 | | 93 | PRAGYA JAIN | 123 | 32000 | 29 | 2006-08-05 | +-------+---------------+---------+--------+-----+------------+ '''

import mysql.connector as my

conn=my.connect(host='localhost',user='root',password='123456')

if conn.is_connected():

    print('Connected to MySQL Successfully')

else:

    print('Connection is unsuccessful')

cur=conn.cursor(buffered=True)#this ensures that the cursor error doesn't occur even if all records are not read

cur.execute('drop database if exists database18')

cur.execute('create database database18')

cur.execute('use database18')

cur.execute('''create table employee

(ECODE int primary key,ENAME char(30),

DEPCODE int, SALARY int, AGE int, JOINDATE date)''')

cur.execute('''insert into employee values

(15,'SAMEER SHARMA',123,75000,39,'2007-04-01'),

(21,'RAGVINDER K',101,86000,29,'2005-11-11'),

(81,'RAJESH KUMAR',119,74500,48,'2008-12-11'),

(89,'SANJEEV P',101,92600,54,'2009-01-12'),

(93,'PRAGYA JAIN',123,32000,29,'2006-08-05')''')

conn.commit()

def menu():

    while True :

        ch=int (input("""

1.DISPLAY DESIRED NUMBER OF RECORDS

2.DISPLAY TOTAL NUMBER OF ROWS

3.UPDATE SALARY

4.SEARCH EMPLOYEES WITH SALARY ABOVE RS. 50000

5.DELETE RECORD

6.EXIT

YOUR CHOICE : """))

        if ch==1:

            disp_many()

        elif ch==2:

            tot_rows()

        elif ch==3:

            update()

        elif ch==4:

            search()

        elif ch==5:

            delete()

        else :

            cur.close()

            conn.close()

            break

def disp_many():

    cur.execute("select * from employee")

    no=int(input("Enter no of records you want to display ? "))

    recs = cur.fetchmany(no)

    for a,b,c,d,e,f in recs:

        print(str(a).ljust(5),b.ljust(20),str(c).ljust(5),str(d).ljust(10),str(e).ljust(5),f)

    cur.reset()

def tot_rows():

    cur.execute("select * from employee")

    recs = cur.fetchall()

    print("Total rows : ",cur.rowcount)

    cur.reset()

def update():

    ecode= int(input("Enter emp code whose sal you want to update"))

    cur.execute("select * from employee where ecode=%s",(ecode,))

    rec = cur.fetchone()

    cur.reset()

    if not rec:

        print('Ecode not found')

        return

    print("Record found : ",rec)

    sal=int(input("Enter revised salary"))

    cur.execute("""update employee set salary = %s

where ecode=%s""",(sal,ecode))

    cur.reset()

    conn.commit()

def search():

    print("Employees with SALARY > 50000")

    cur.execute("select * from employee where salary>50000")

    recs = cur.fetchall()  

    if not recs:

        print('NONE')

        cur.reset()

        return

    for a,b,c,d,e,f in recs:      print(str(a).ljust(5),b.ljust(20),str(c).ljust(5),str(d).ljust(10),str(e).ljust(5),f)

    cur.reset()

def delete():

    ecode= int(input("Enter emp code whose sal you want to delete"))

    cur.execute("select * from employee where ecode=%s",(ecode,))

    rec = cur.fetchone()

    cur.reset()

    if not rec:

        print('Ecode not found')

        return

    print("Record found : ",rec)

    ans=input("Are you sure you want to delete(y/n)")

    if ans=='n':

        return

    cur.execute("delete from employee where ecode=%s",(ecode,))

    cur.reset()

    conn.commit()

  

menu()