How to search and delete record from MySQL database table in Python Tkinter using Object Oriented Programming Method (OOP)

 

connection.py

import mysql.connector
import sys

def Connect():
    conn=None
    try:
        conn=mysql.connector.connect(
            host='localhost',
            username='root',
            password='',
            database='level4b'
        )

    except:
        print("Error", sys.exc_info())

    finally:

        return conn

libs.py

class Libs():
    def __init__(self, cid=0,
    name=None, dob=None, email=None, password=None):
        self.cid=cid
        self.name=name
        self.dob=dob
        self.email=email
        self.password=password

    #Getter
    def getCid(self):
        return self.cid

    def getName(self):
        return self.name

    def getDob(self):
        return self.dob

    def getEmail(self):
        return self.email

    def getPassword(self):
        return self.password

    #setter
    def setCid(self, cid):
        self.cid=cid

    def setName(self, name):
        self.name=name

    def setDob(self, dob):
        self.dob=dob

    def setEmail(self, email):
        self.email=email

    def setPassword(self, password):
        self.password=password

    def __str__(self):
        return ('{},{},{},{},{}'.format(self.cid,
    self.name, self.dob, self.email, self.password))

backend.py

from Hancie.connection import Connect
import mysql.connector
import sys

def search_record(customerID):
    conn=None
    sql="""SELECT * FROM customers WHERE cid=%s"""
    values=(customerID,)
    result=None
    try:
        conn=Connect()
        cursor=conn.cursor()
        cursor.execute(sql, values)
        result=cursor.fetchone()
        cursor.close()
        conn.close()


    except:
        print("Error", sys.exc_info())

    finally:
        del values, sql, conn
        return result

def delete_record(record):
    conn=None
    sql="""DELETE FROM customers WHERE cid=%s"""
    values=(record,)
    deleteResult=False
    try:
        conn=Connect()
        cursor=conn.cursor()
        cursor.execute(sql, values)
        conn.commit()
        cursor.close()
        conn.close()
        deleteResult=True

    except:
        print("Error", sys.exc_info())

    finally:
        del values, sql, conn
        return deleteResult

gui.py

from tkinter import *
from tkcalendar import DateEntry
from tkinter import messagebox
from Hancie.backend import search_record,delete_record
from Hancie.libs import Libs


class Hancie():
    def __init__(self, main):
        self.main=main
        self.main.title("Hancie e-Learning Studio")
        self.main.geometry("500x400")

        myfont=('Tahoma',16)

        searchlbl = Label(self.main, text="Search ID:", font=myfont)
        searchlbl.place(x=20, y=50)

        idtxt = Entry(self.main, font=myfont)
        idtxt.place(x=120, y=50)

        def search_record720():

            id=idtxt.get()
            result=search_record(id)
            if result!=None:

                nametxt.delete(0, END)
                nametxt.insert(0,result[1])

                dobtxt.delete(0, END)
                dobtxt.insert(0, result[2])

                emailtxt.delete(0, END)
                emailtxt.insert(0, result[3])

                passwordtxt.delete(0, END)
                passwordtxt.insert(0, result[4])

                messagebox.showinfo("Hancie e-Learning", "Record found")

            else:
                messagebox.showerror("Hancie e-Learning","Record not found")

        searchbtn = Button(self.main,command=search_record720,  font=myfont, text="Search")
        searchbtn.place(x=350, y=40)

        namelbl=Label(self.main, text="Name:", font=myfont)
        namelbl.place(x=20, y=100)

        nametxt=Entry(self.main, font=myfont)
        nametxt.place(x=120, y=100)

        doblbl=Label(self.main, text="DOB:", font=myfont)
        doblbl.place(x=20, y=150)

        dobtxt=DateEntry(self.main, font=myfont)
        dobtxt.place(x=120, y=150)

        emailbl = Label(self.main, text="Email:", font=myfont)
        emailbl.place(x=20, y=200)

        emailtxt = Entry(self.main, font=myfont)
        emailtxt.place(x=120, y=200)

        passwordlbl = Label(self.main, text="Password:", font=myfont)
        passwordlbl.place(x=20, y=250)

        passwordtxt = Entry(self.main, font=myfont)
        passwordtxt.place(x=120, y=250)

        def delete():

            id=idtxt.get()
            deleteResult = delete_record(id)
            if deleteResult == True:
                messagebox.showinfo("Customer", "The data is deleted")
            else:
                messagebox.showerror("Customer", "Error Occurred")

        deletebtn = Button(self.main,  font=myfont,command=delete, text="Delete")
        deletebtn.place(x=120, y=300)



if __name__=='__main__':
    main=Tk()
    Hancie(main)
    main.mainloop()

Output