Python PDBC with OOPs Part – 3

Program 1

# PDBC with OOPS
import MySQLdb
class EmployeeDatabase:
    # Connection
    def __init__(self):
          self.con=MySQLdb.connect(host='localhost',user='root',password='root',database='college')
          #print("Connection success")

  # Insert record 
    def insertdata(self,name,dept,salary,gender,city):
        try: 
            sql="insert into employee values(%d, '%s','%s',%d,'%s','%s')"
            self.cur=self.con.cursor()
            value=(self.maxid,name,dept,salary,gender,city)
            n=self.cur.execute(sql %value)
            self.con.commit()
            print("Record inserted : ",n)
        except Exception as obj:
             print(obj) 

    def autoGenId(self):
         self.maxid=100 
         sql="select max(eid) from employee"
         self.cur=self.con.cursor()
         n=self.cur.execute(sql) 
         if(n==1):
          row=self.cur.fetchone()
          self.maxid=row[0]
          self.maxid=self.maxid+1
          # print(self.maxid)



    def searchall(self):
         sql="select * from employee"
         self.cur=self.con.cursor()
         self.cur.execute(sql)
         result=self.cur.fetchall()
         for row in result:
              print("%d,%s,%s,%d,%s,%s"%(row[0],row[1],row[2],row[3],row[4],row[5]))

    def searchbyid(self,id):
         sql="select * from employee  where eid={}"
         sql=sql.format(id)
         self.cur=self.con.cursor()
         self.cur.execute(sql)
         row=self.cur.fetchone()
         if(row):
              print("%d,%s,%s,%d,%s,%s"%(row[0],row[1],row[2],row[3],row[4],row[5]))
         else:
              print("********No Record Found***********")

    # Delete Record

    def deletebyid(self,id):
         sql="select * from employee  where eid={}"
         sql=sql.format(id)
         self.cur=self.con.cursor()
         self.cur.execute(sql)
         row=self.cur.fetchone()
         if(row):
              print("%d,%s,%s,%d,%s,%s"%(row[0],row[1],row[2],row[3],row[4],row[5]))
              choice=input("Are you  sure want to delete: ")
              choice=choice.upper()
              if(choice=="YES"):
                   sql="delete from employee where eid={}"
                   sql=sql.format(id)
                   n=self.cur.execute(sql)
                   self.con.commit()
                   print("Record deleted: ",n)

         else:
              print("********No Record Found***********")

    # connection close
    def conclose(self):
         self.con.close()
         


# Calling (Main)
E=EmployeeDatabase()

while(True):
    print("-----------------Menu------------------")
    print("\t 1. Insert Record")
    print("\t 2. Search Record By Id")
    print("\t 3. Search All Record")
    print("\t 4. Delete Record by Id")
    print("\t 5. Exit")
    print("-------------------------------------------")
    ch=int(input("Enter your choice: "))
    if(ch==1):
         E.autoGenId() 
         print("Employee Id: ",E.maxid)
         empname=input("Enter employee name: ")
         empdept=input("Enter employee Depatrment: ")
         empsalary=int(input("Enter employee salary: "))
         empgender=input("Enter Gender of employee: ")
         empcity=input("Enter employee City: ")
         E.insertdata(empname,empdept,empsalary,empgender,empcity)
         
    elif(ch==2):     
        empid=int(input("Enter employee id for search: "))
        E.searchbyid(empid)
    elif(ch==3):     
         E.searchall()
    elif(ch==4):     
         empid=int(input("Enter employee id for delete: "))
         E.deletebyid(empid)
    elif(ch==5):    
         E.conclose()
         break
    else:
         print("Invalid choice")