ABC Infotech Pvt. Ltd. needs to store, retrieve and delete the records of its employees.

Q. ABC Infotech Pvt. Ltd. needs to store, retrieve and delete the records of its employees. Develop an interface that provides front-end interaction through Python, and stores and updates records using MySQL.

The operations on MySQL table "emp" involve reading, searching, updating and deleting the records of employees.

(a) Program to read and fetch all the records from EMP table having salary more than 70000.

Answer :-

import mysql.connector
db1 = mysql.connector.connect (host = "localhost", user = "root", password = "pathwalla", database = "company")
cursor = db1.cursor()

sql = "SELECT FROM EMP WHERE SALARY> 70000;"

try:
    cursor.execute(sql)
    resultset = cursor.fetchall ()
    for row in resultset:
        empno = row [0]
        ename = row [1]
        salary = row [2]
    print (("empno-3d, ename=%s, salary-8f") % (empno, ename, salary))
except:
    print ("Error: unable to fetch data")

db1.close()

(b) Program to update the records of employees by increasing salary by 1000 of all those employees who are getting less than 80000.

Answer :-

db1 = mysql.connector.connect (host = "localhost", user = "root", password = "pathwalla", database = "company")
cursor = db1.cursor()
sql = "UPDATE EMP SET salary = salary +1000 WHERE salary < 80000;"
try:
    cursor.execute (sql)
    db1.commit()
except:
    db1.rollback()
db1.close()

(c) Program to delete the record on the basis of inputted salary.

Answer :-

db1 = mysql.connector.connect (host = "localhost", user = "root", password = "", database = "company")
cursor = db1.cursor()
sal = int(input ("Enter salary whose record to be deleted: "))
sql = "DELETE FROM EMP WHERE salary < '%d';"% (sal)
try:
    cursor.execute(sql)
    print (cursor.rowcount, end = " record(s) deleted")
    db1.commit()
except:
    db1.rollback()
    db1.close()