Insert,Delete and Update
from tkinter import *
import mysql.connector
import datetime
def IsValidDate(InputDate):
day = InputDate[:2]
month = InputDate[3:5]
year = InputDate[6:]
IsValid = True
try :
datetime.datetime(int(year),int(month),int(day))
except ValueError :
IsValid = False
return IsValid
def FormatDBDate(InputDate):
dt = InputDate[6:] + "-" + InputDate[3:5] + "-" + InputDate[:2]
return dt
def FormatEDDate(InputDate):
dt = InputDate[8:] + "/" + InputDate[5:7] + "/" + InputDate[:4]
return dt
def DeleteEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep):
conn=mysql.connector.connect(user='root',password='harsha',host='localhost', database='office')
cur=conn.cursor()
try:
result = tk.messagebox.askquestion("Delete", "Are You Sure?", icon='warning')
if result == 'yes':
if eid.get()=="":
msg=tk.messagebox.showinfo("Warning","Emp ID is required!")
else:
cur.execute("DELETE FROM employee WHERE emp_id='%s'" % (eid.get()))
conn.commit()
msg=tk.messagebox.showinfo("Message","Deleted successfully!")
ClearEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep)
except Error as error:
conn.rollback()
msg=tk.messagebox.showinfo("Error",error)
finally:
cur.close()
conn.close()
def SearchEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep):
conn=mysql.connector.connect(user='root',password='harsha',host='localhost', database='office')
cur=conn.cursor()
try:
if eid.get()=="":
msg=tk.messagebox.showinfo("Warning","Emp ID is required!")
else:
cur.execute("SELECT id, emp_id, emp_name, gender, dob, doj, address, city, postalcode, mobile, email, dep_id FROM employee WHERE emp_id='%s'" % (eid.get()))
row = cur.fetchone()
if row == None:
msg=tk.messagebox.showinfo("Warning","Employee not found!")
else:
val = str(row[0])
id.configure(text=val)
ename.insert(0, row[2])
gen.set(row[3])
dob.insert(0, FormatEDDate(str(row[4])))
doj.insert(0, FormatEDDate(str(row[5])))
adr.insert(0, row[6])
cty.insert(0, row[7])
pcd.insert(0, row[8])
mob.insert(0, row[9])
eml.insert(0, row[10])
dep.set(row[11])
except:
conn.rollback()
msg=tk.messagebox.showinfo("Error","Something went wrong when save employee details!")
finally:
cur.close()
conn.close()
def SaveEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep):
conn=mysql.connector.connect(user='root',password='harsha',host='localhost', database='office')
cur=conn.cursor()
if eid.get()=="":
msg=tk.messagebox.showinfo("Warning","EmployeeID is required!")
elif ename.get()=="":
msg=tk.messagebox.showinfo("Warning","Employee Name is required!")
elif dob.get()=="":
msg=tk.messagebox.showinfo("Warning","DOB is required!")
elif doj.get()=="":
msg=tk.messagebox.showinfo("Warning","DOJ is required!")
elif adr.get()=="":
msg=tk.messagebox.showinfo("Warning","Address is required!")
elif cty.get()=="":
msg=tk.messagebox.showinfo("Warning","City is required!")
elif pcd.get()=="":
msg=tk.messagebox.showinfo("Warning","Postal Code is required!")
elif mob.get()=="":
msg=tk.messagebox.showinfo("Warning","Mobile is required!")
elif eml.get()=="":
msg=tk.messagebox.showinfo("Warning","Email is required!")
else:
if IsValidDate(dob.get())==False:
msg=tk.messagebox.showinfo("Warning","Invalid DOB!")
elif IsValidDate(doj.get())==False:
msg=tk.messagebox.showinfo("Warning","Invalid DOJ!")
else:
fdob = FormatDBDate(dob.get())
fdoj = FormatDBDate(doj.get())
try:
if int(id.cget("text"))==0:
cur.execute("SELECT id, emp_id, emp_name, gender, dob, doj, address, city, postalcode, mobile, email, dep_id FROM employee WHERE emp_id='%s'" % (eid.get()))
row = cur.fetchone()
if row == None:
cur.execute("insert into employee (emp_id, emp_name, gender, dob, doj, address, city, postalcode, mobile, email, dep_id) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(eid.get(),ename.get(),gen.get(), fdob,fdoj,adr.get(),cty.get(),pcd.get(),mob.get(),eml.get(),dep.get()))
conn.commit()
msg=tk.messagebox.showinfo("Message","Employee inserted successfully!")
ClearEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep)
else:
msg=tk.messagebox.showinfo("Warning","Employee ID already exists! Use another ID.")
else:
cur.execute("update employee set emp_name=%s,gender=%s, dob=%s,doj=%s,address=%s,city=%s,postalcode=%s,mobile=%s,email=%s,dep_id=%s where emp_id=%s",(ename.get(),gen.get(), fdob,fdoj,adr.get(),cty.get(),pcd.get(),mob.get(),eml.get(),dep.get(),eid.get()))
conn.commit()
msg=tk.messagebox.showinfo("Message","Employee updated successfully!")
ClearEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep)
except:
msg=tk.messagebox.showinfo("Error","Something went wrong when save employee details!")
finally:
cur.close()
conn.close()
def ClearEmployee(id, eid, ename, dob, doj, adr, cty, pcd, mob, eml, gen, dep):
val = "0"
id.configure(text=val)
eid.delete(0, END)
ename.delete(0, END)
dob.delete(0, END)
doj.delete(0, END)
adr.delete(0, END)
cty.delete(0, END)
pcd.delete(0, END)
mob.delete(0, END)
eml.delete(0, END)
gen.set("M")
dep.set(3)
def EditEmployee():
femp = pnl
gen = StringVar()
gen.set("M")
dep = IntVar()
dep.set(1)
tit=tk.Label(femp, text="EMPLOYEE")
tit.config(bg="#ffffff",fg='#800000', font=('times', 16, 'bold'))
tit.place(x=20,y=5)
lblEmpID=tk.Label(femp, text="Emp ID")
lblEmpID.config(bg="#ffffff",font=('times', 14, 'bold'))
lblEmpID.place(x=150,y=55)
txtEmpID = tk.Entry(femp,bd=3,width=50)
txtEmpID.place(x=350,y=60)
lblID=tk.Label(femp, text="0")
lblID.config(fg="#ffffff")
lblID.place(x=680,y=60)
btnSearch=tk.Button(femp,text="Search",command=lambda:SearchEmployee(lblID, txtEmpID, txtEmployeeName, txtDOB, txtDOJ, txtAddress, txtCity, txtPostalCode,txtMobile, txtEmail, gen, dep),bd=3,width=8)
btnSearch.config(font=('times', 12, 'bold'),activebackground='snow4')
btnSearch.place(x=680,y=55)
lblEmployeeName=tk.Label(femp, text="Employee Name")
lblEmployeeName.config(bg="#ffffff",font=('times', 14, 'bold'))
lblEmployeeName.place(x=150,y=115)
txtEmployeeName = tk.Entry(femp,bd=3,width=50)
txtEmployeeName.place(x=350,y=120)
lblGender=tk.Label(femp, text="Gender")
lblGender.config(bg="#ffffff", font=('times', 14, 'bold'))
lblGender.place(x=150,y=155)
rdoGenderMale = tk.Radiobutton(femp,
text="Male",
padx = 20,
variable=gen,
value="M")
rdoGenderFemale = tk.Radiobutton(femp,
text="Female",
padx = 20,
variable=gen,
value="F")
rdoGenderMale.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoGenderMale.place(x=350,y=155)
rdoGenderFemale.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoGenderFemale.place(x=450,y=155)
lblDOB=tk.Label(femp, text="Date of birth")
lblDOB.config(bg="#ffffff",font=('times', 14, 'bold'))
lblDOB.place(x=150,y=195)
txtDOB = tk.Entry(femp,bd=3,width=35)
txtDOB.place(x=350,y=200)
msDOB=tk.Label(femp, text="dd/mm/yyyy")
msDOB.config(bg="#ffffff",font=('times', 12), fg="#aaaaaa")
msDOB.place(x=580,y=200)
lblDOJ=tk.Label(femp, text="Date of join")
lblDOJ.config(bg="#ffffff",font=('times', 14, 'bold'))
lblDOJ.place(x=150,y=235)
txtDOJ = tk.Entry(femp,bd=3,width=35)
txtDOJ.place(x=350,y=240)
msDOJ=tk.Label(femp, text="dd/mm/yyyy")
msDOJ.config(bg="#ffffff",font=('times', 12), fg="#aaaaaa")
msDOJ.place(x=580,y=235)
lblAddress=tk.Label(femp, text="Address")
lblAddress.config(bg="#ffffff",font=('times', 14, 'bold'))
lblAddress.place(x=150,y=275)
txtAddress = tk.Entry(femp,bd=3,width=50)
txtAddress.place(x=350,y=280)
lblCity=tk.Label(femp, text="City")
lblCity.config(bg="#ffffff",font=('times', 14, 'bold'))
lblCity.place(x=150,y=315)
txtCity = tk.Entry(femp,bd=3,width=50)
txtCity.place(x=350,y=320)
lblPostalCode=tk.Label(femp, text="Postal Code")
lblPostalCode.config(bg="#ffffff",font=('times', 14, 'bold'))
lblPostalCode.place(x=150,y=355)
txtPostalCode = tk.Entry(femp,bd=3,width=50)
txtPostalCode.place(x=350,y=360)
lblMobile=tk.Label(femp, text="Mobile")
lblMobile.config(bg="#ffffff",font=('times', 14, 'bold'))
lblMobile.place(x=150,y=395)
txtMobile = tk.Entry(femp,bd=3,width=50)
txtMobile.place(x=350,y=400)
lblEmail=tk.Label(femp, text="Email")
lblEmail.config(bg="#ffffff",font=('times', 14, 'bold'))
lblEmail.place(x=150,y=435)
txtEmail = tk.Entry(femp,bd=3,width=50)
txtEmail.place(x=350,y=440)
lblDept=tk.Label(femp, text="Department")
lblDept.config(bg="#ffffff",font=('times', 14, 'bold'))
lblDept.place(x=150,y=495)
rdoDeptAdmin = tk.Radiobutton(femp,
text="Admin",
padx = 20,
variable=dep,
value="1")
rdoDeptAccts = tk.Radiobutton(femp,
text="Accounts",
padx = 20,
variable=dep,
value="2")
rdoDeptSales = tk.Radiobutton(femp,
text="Sales",
padx = 20,
variable=dep,
value="3")
rdoDeptProduction = tk.Radiobutton(femp,
text="Production",
padx = 20,
variable=dep,
value="4")
rdoDeptAdmin.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoDeptAdmin.place(x=350,y=500)
rdoDeptAccts.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoDeptAccts.place(x=480,y=500)
rdoDeptSales.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoDeptSales.place(x=350,y=540)
rdoDeptProduction.config(bg="#ffffff",font=('times', 14, 'bold'))
rdoDeptProduction.place(x=480,y=540)
btnSave=tk.Button(femp,text="Save",command=lambda:SaveEmployee(lblID, txtEmpID, txtEmployeeName, txtDOB, txtDOJ, txtAddress, txtCity, txtPostalCode,txtMobile, txtEmail, gen, dep),bd=3,width=8)
btnSave.config(font=('times', 12, 'bold'))
btnSave.place(x=330,y=600)
btnClear=tk.Button(femp,text="Clear",command=lambda:ClearEmployee(lblID, txtEmpID, txtEmployeeName, txtDOB, txtDOJ, txtAddress, txtCity, txtPostalCode,txtMobile, txtEmail, gen, dep),bd=3,width=8)
btnClear.config(font=('times', 12, 'bold'))
btnClear.place(x=420,y=600)
btnDelete=tk.Button(femp,text="Delete",command=lambda:DeleteEmployee(lblID, txtEmpID, txtEmployeeName, txtDOB, txtDOJ, txtAddress, txtCity, txtPostalCode,txtMobile, txtEmail, gen, dep),bd=3,width=8)
btnDelete.config(font=('times', 12, 'bold'))
btnDelete.place(x=510,y=600)
mlr.add(femp)
femp.mainloop()
root = tk.Tk()
w = root.winfo_screenwidth()
h = root.winfo_screenheight()
root.geometry("%dx%d+0+0" % (w, h))
root.state("zoomed")
root.title("MyOffice")
by = PanedWindow(root, orient=VERTICAL, background="#092151")
by.pack(fill=BOTH, expand=1)
tl = PanedWindow(by, height=100, bd=0, background="#092151")
tl.pack(fill=BOTH)
by.add(tl)
mlh = PanedWindow(by, orient=HORIZONTAL)
mlh.pack(fill=BOTH, expand=1)
by.add(mlh)
mll = PanedWindow(mlh, height=700, width=200, bd=1, bg="#cccccc")
mlh.add(mll)
mlr = PanedWindow(mlh, height=700, width=800, bd=1, bg="#ffffff")
mlh.add(mlr)
bl = PanedWindow(by, height=30, bg="#000000")
by.add(bl)
w = tk.Label(tl, text="JOHNS MANVILLE",anchor='center', bd=2, bg="#4a7fec", fg="#dedede")
w.config(font=('Arial', 24, 'bold'))
w.place(x=65,y=130)
w.pack(fill=BOTH)
tl.add(w)
blt = tk.Label(bl, text="Developed By: HARSHANTH C.T.",bd=2, bg="#092151", fg="#dedede")
blt.config(font=('Arial', 14))
blt.pack(fill=BOTH)
bl.add(blt)
pnl = Frame(mlr, bg="#ffffff")
btnEditEmployee = tk.Button(mll, text="Employee", command=EditEmployee)
btnEditEmployee.pack(side=tk.TOP, ipadx=35, padx=30, pady=30)
#btnEditProducts = tk.Button(mll, text="Products", command=EditProduct)
#btnEditProducts.pack(side=tk.TOP, ipadx=30, padx=30, pady=10)
EditEmployee()
root.mainloop()
Output:
Insert:
Update:
Note: For Updating enter required emp id and click search then click save button.
Delete:
Note:for Deleting just enter the emp id and click Delete button.It will popup a message click yes to delete or click no to cancel it.
Comments
Post a Comment