Deleting records in a table using python program.
In python program design GUI
in which farme1 contains two buttons DELETE and DISPLAY. After pressing
DELETE button new form is opened, which contains spinbox which is populated
with employee ids stored in employee table. Select one of the employee id in
spinbox and press right button of the mouse. Then record with that employee is
will be deleted.Then press BACK button which displays the first frame and select
DISPLAY button, a new frame is opened and all current records in a table will be
displayed. U can display records before delete and after delete. I am already
created employee table in my3.db and inserted records in previous program.
These are the records in employee table before delete
C:\sqlite3>sqlite3 my3.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select * from employee;
1|ram|22|male|MTECH|30000.0|Assistant professor|02-02-1988
5|suma|30|female|MTECH|1000.0|Assistant professor|03-01-1999
9|GAYATHRI|23|female|BE|10000.0|Assistant professor|02-01-2016
123|SACHIN|18|male|MTECH|20000.0|Assistant professor|01-01-2003
34|KAMALA|55|female|PHD|6000.0|professor|02-05-1990
38|KAVERI|55|female|PHD|100000.0|professor|02-03-1990
78|SUHAS|27|male|BE|7000.0|Assistant professor|03-08-2017
576|gfh|18|female|MTECH|123.0|Assistant professor|01-01-2000
63|shivu|30|male|MTECH|30000.0|Assocaite professor|02-02-2013
678|padamini|26|female|BE|3000.0|Assistant professor|05-01-2019
900|RAGHU|26|male|BE|10000.0|Assistant professor|03-04-2001
11111|JHKJ|19|male|SSLC|2000.0|peon|01-01-2000
777|HKLJL|19|female|BE|1000.0|Assistant professor|01-01-2000
#python program to delete a record from employee table and display the rcords before and after
#delete using GUI
from tkinter import *
import sqlite3
#on clicking buttons on frame the raise_frame function is invoked
def raise_frame(frame):
#when delete button is selected second frame is raised which allows user to delete
#record. First select empid from spinbox and press mouse right button
if frame==f1:
frame.tkraise()
#place existing employee ids in spinobx
l1=Label(f1,text="select Empid").place(x=50,y=200)
v1=IntVar()
le=[]
conn = sqlite3.connect('c:\sqlite3\my3.db')
c = conn.cursor()
r1=c.execute("select empid from employee")
for i in r1:
le.append(int(i[0]))
conn.close()
te=tuple(le)
s1=Spinbox(f1,font=('Arial',14),value=te, textvariable=v1,width=3)
s1.place(x=200,y=200)
def show1(self):
eid=int(v1.get())
conn1 = sqlite3.connect('c:\sqlite3\my3.db')
c1 = conn1.cursor()
sql="delete from employee where empid=?"
r3=c1.execute(sql,(eid,))
conn1.commit()
if c1.rowcount==1:
l15=Label(f1,text="Record deleted sucessfully ",fg="red").place(x=700,y=300)
conn1.close()
#once spinbox item is selected press right button to invoke show1 fucnction
s1.bind("<Button-3>",show1)
if frame==f:
frame.tkraise()
if frame==f2:
frame.tkraise()
l10=Label(f2,text="Current Records in employee Table",fg="red",font=('Arial',14))
l10.place(x=50,y=10)
t=Text(f2,width=70,height=15,wrap=WORD)
t.place(x=200,y=100)
conn3 = sqlite3.connect('c:\sqlite3\my3.db')
c3 = conn3.cursor()
r=c3.execute("select * from employee")
for i in r:
t.insert(END,i)
t.insert(END,'\n')
conn3.close()
#main program create three frame
r=Tk()
r.geometry('1000x1000')
f=Frame(r,width=1000,height=1000)
f.place(x=20,y=20)
f1=Frame(r,width=1000,height=1000)
f1.place(x=20,y=20)
f2=Frame(r,width=1000,height=1000)
f2.place(x=20,y=20)
#place update and display button on frame 1
#on clicking update button it opens new form where by selecting empid salary of that employee can be
updated
#on clicking display button it display button display the current records in employee table
b=Button(f,text="DELETE",font=('Arial',20,'bold'),fg='red',command=lambda:raise_frame(f1)).place(x=40
0,y=50)
b1=Button(f,text="DISPLAY",font=('Arial',20,'bold'),fg='red',command=lambda:raise_frame(f2)).place(x=
400,y=150)
#place back button on farme 2 and 3
b2=Button(f1,text="BACK",font=('Arial',20,'bold'),fg='red',command=lambda:raise_frame(f)).place(x=55
0,y=550)
b3=Button(f2,text="BACK",font=('Arial',20,'bold'),fg='red',command=lambda:raise_frame(f)).place(x=55
0,y=550)
raise_frame(f)
r.mainloop()
Output:
After executing the program
Press display button to see the records before delete
Press BACK button to link back to home page
Press delete button
Select empid press right button, u will get message record is deleted
Empid 34 is selected and is deleted
Press back button to link back to home page
Press display Button to verify record is deleted or not
Press BACK button to link back to home page
Check at command prompt to verify whether a record is deleted or not
sqlite> select * from employee;
1|ram|22|male|MTECH|30000.0|Assistant professor|02-02-1988
5|suma|30|female|MTECH|1000.0|Assistant professor|03-01-1999
9|GAYATHRI|23|female|BE|10000.0|Assistant professor|02-01-2016
123|SACHIN|18|male|MTECH|20000.0|Assistant professor|01-01-2003
38|KAVERI|55|female|PHD|100000.0|professor|02-03-1990
78|SUHAS|27|male|BE|7000.0|Assistant professor|03-08-2017
576|gfh|18|female|MTECH|123.0|Assistant professor|01-01-2000
63|shivu|30|male|MTECH|30000.0|Assocaite professor|02-02-2013
678|padamini|26|female|BE|3000.0|Assistant professor|05-01-2019
900|RAGHU|26|male|BE|10000.0|Assistant professor|03-04-2001
11111|JHKJ|19|male|SSLC|2000.0|peon|01-01-2000
777|HKLJL|19|female|BE|1000.0|Assistant professor|01-01-2000
There is no record in table with empid=34
