Updating records in a table using python program.
In python program design
GUI in which farme1 contains two buttons UPDATE and DISPLAY. After pressing
UPDATE 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. The existing salary of selected
employee is displayed in a text widget. In a entry widget enter a new salary and
press enter button. Once enter button is pressed U will get message record is
updated. 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 update and after update. I am already
created employee table in my3.db and inserted records in previous program.
These are the records in employee table before update
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|1000000.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
sqlite>
#python program to UDATE a record of employee table and display the rcords before and after update
#using GUI
from tkinter import *
import sqlite3
#on clicking buttons on frame the raise_frame function is invoked
def raise_frame(frame):
#when update button is selected second frame is raised which allows user to update
#record. First select empid from spinbox and press mouse right button
#then current salary of employee is displayed in text widget
#enter new salary in entry widget and press enter button
#once record is updated sucessful a message is dispalyed
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)
l4=Label(f1,text="Old Salary",font=('Arial',14)).place(x=550,y=100)
t=Text(f1,width=25,height=2)
t.place(x=650,y=100)
l5=Label(f1,text="Enter a new salary").place(x=550,y=150)
s9=DoubleVar()
e3=Entry(f1,width=20,font=('Arial',14),textvariable=s9)
e3.place(x=650,y=150)
#palce the existing salary of selected empid in text widget
def show1(self):
eid=int(v1.get())
conn1 = sqlite3.connect('c:\sqlite3\my3.db')
c1 = conn1.cursor()
sql="select salary from employee where empid=?"
r3=c1.execute(sql,(eid,))
for i in r3:
t.insert(END,i[0])
conn1.close()
#To enter new salary and update a record
def show2(self):
new=s9.get()
new=float(new)
print(new)
if new!=0.0:
conn2 = sqlite3.connect('c:\sqlite3\my3.db')
c2 = conn2.cursor()
sql1="update employee set salary=? where empid=?"
c2.execute(sql1,(new,eid))
conn2.commit()
if c2.rowcount==1:
l15=Label(f1,text="Record updated sucessfully ",fg="red").place(x=700,y=300)
conn2.close()
else:
final=Label(f1,text="plz insert a salary ",fg="red").place(x=550,y=200)
#once new salary is entered in entry press enter key to call show2 function
e3.bind("<Return>",show2)
#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="UDATE",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 update
Press BACK button to link back to home page
Press update button
Select empid press right button, then current salary will be displayed in text box and enter new salary in
entry widget and press enter button,u will get message record is inserted successfuly
Press back button to link back to home page
Press display Button to check the updated record
Press BACK button to link back to home page
Check at command prompt to verify whether a record is updated 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
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
sqlite> select * from employee where empid=34;
34|KAMALA|55|female|PHD|6000.0|professor|02-05-1990
Record marked in red is the one which is updated.
