Display Edit & Update record from MySQL table

Tkinter Displaying records in Tkinter Delete MySQL record

Edit and Update MySQL database records in Tkinter window


Tkinter to display MySQL table records and on selection edit and update data using GUI widgets

Display records

We will use one function display() to keep the code to display the records. After editing and updating the record we can call this function display() again to collect fresh data from database to reflect the changes.
Displaying Records from MySQL table in Tkinter
def display():
    my_cursor=my_conn.execute("SELECT * FROM student limit 0,10")
    global i
    i=0 
    for student in my_cursor: 
        for j in range(len(student)):
            e = Label(my_w,width=10, text=student[j],
                relief='ridge', anchor="w")  
            e.grid(row=i, column=j) 
            
        e = tk.Button(my_w,width=5, text='Edit',relief='ridge',
             anchor="w",command=lambda k=student[0]:edit_data(k))  
        e.grid(row=i, column=5)     
        i=i+1

Edit Records

Each row of record inside the display() function will carry one Edit button. Once the button is clicked the id of the record or row will be passed to another function edit_data(). Inside the function edit_data() the details of the record with matching ID is taken from MySQL database and displayed on the Tkinter GUI ( as default data ) using Entry buttons to edit.
row=my_conn.execute("SELECT * FROM student WHERE id=%s",id)
    s = row.fetchone() # row details as tuple
Displaying Record details based on user entered record ID in Tkinter
    e1_str_id=tk.StringVar(my_w) # String variable 
    e2_str_name=tk.StringVar(my_w)
    e1_str_id.set(s[0]) # id is stored 
    e2_str_name.set(s[1]) # Name is stored 
User can change the data presented using the Entry widget. Here String variables are used to hold the default data and to collect any user changes.
    e1=tk.Entry(my_w,textvariable=e1_str_id,width=10,state='disabled')
    e1.grid(row=i,column=0)
    e2=tk.Entry(my_w,textvariable=e2_str_name,width=10)
    e2.grid(row=i,column=1)

Update record

Once the update button is clicked the data as entered ( edited ) by user is collected and UPDATE sql query is used to update the record by using matching ID.
row=my_conn.execute("SELECT * FROM student WHERE id=%s",id)
    s = row.fetchone() # row details as tuple
This update part is kept inside another function my_update(). One tuple data is created by using all the user entered data . String variable get() method is used to collect the user entered data.
data=(e2_str_name.get(),e3_str_class.get(),e4_str_mark.get(),e5_str_gender.get(),e1_str_id.get())
Parametrized query is used to update the record by using WHERE condition with id.
id=my_conn.execute("UPDATE student SET name=%s,class=%s,\
            mark=%s,gender=%s WHERE id=%s",data)
After updating the record we can remove the edit row by using grid_slaves().
for w in my_w.grid_slaves(i): # remove the edit row
            w.grid_forget()
Finally we can call the display() function to show the updated record with other rows of data.

Full code is here
import tkinter  as tk 
from tkinter import *
from sqlalchemy import create_engine 
my_w = tk.Tk()
my_w.geometry("450x350") 
global i
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
def display():
    my_cursor=my_conn.execute("SELECT * FROM student limit 0,10")
    global i
    i=0 
    for student in my_cursor: 
        for j in range(len(student)):
            e = Label(my_w,width=10, text=student[j],
                relief='ridge', anchor="w")  
            e.grid(row=i, column=j) 
            #e.insert(END, student[j])
        e = tk.Button(my_w,width=5, text='Edit',relief='ridge',
             anchor="w",command=lambda k=student[0]:edit_data(k))  
        e.grid(row=i, column=5)     
        i=i+1
display()
def edit_data(id): # display to edit and update record
    global i # start row after the last line of display
    #collect record based on id and present for updation. 
    row=my_conn.execute("SELECT * FROM student WHERE id=%s",id)
    s = row.fetchone() # row details as tuple

    e1_str_id=tk.StringVar(my_w) # String variable 
    e2_str_name=tk.StringVar(my_w)
    e3_str_class=tk.StringVar(my_w)
    e4_str_mark=tk.StringVar(my_w)
    e5_str_gender=tk.StringVar(my_w)

    e1_str_id.set(s[0]) # id is stored 
    e2_str_name.set(s[1]) # Name is stored 
    e3_str_class.set(s[2]) # class is stored 
    e4_str_mark.set(s[3]) # mark is stored 
    e5_str_gender.set(s[4]) # gender  is stored 
        
    e1=tk.Entry(my_w,textvariable=e1_str_id,width=10,state='disabled')
    e1.grid(row=i,column=0)
    e2=tk.Entry(my_w,textvariable=e2_str_name,width=10)
    e2.grid(row=i,column=1)
    e3=tk.Entry(my_w,textvariable=e3_str_class,width=10)
    e3.grid(row=i,column=2)
    e4=tk.Entry(my_w,textvariable=e4_str_mark,width=10)
    e4.grid(row=i,column=3)
    e5=tk.Entry(my_w,textvariable=e5_str_gender,width=10)
    e5.grid(row=i,column=4)
    b2 = tk.Button(my_w,text='Update',command=lambda: my_update(),
                relief='ridge', anchor="w",width=5)  
    b2.grid(row=i, column=5) 
    def my_update(): # update record 
        data=(e2_str_name.get(),e3_str_class.get(),e4_str_mark.get(),e5_str_gender.get(),e1_str_id.get())
        id=my_conn.execute("UPDATE student SET name=%s,class=%s,\
            mark=%s,gender=%s WHERE id=%s",data)
        print("Row updated  = ",id.rowcount) 
        for w in my_w.grid_slaves(i): # remove the edit row
            w.grid_forget()
        display()   # refresh the data  
my_w.mainloop()

Connect to MySQL database

We can use SQLAlchemy library to manage MySQL database.
from sqlalchemy import create_engine 
my_conn = create_engine("mysql+mysqldb://userid: password@localhost/my_db")
We used my_conn in our script as the connection object.

Displaying record based on user entered data
Download Zip file with sample Scripts to manage MySQL database records using Python Tkinter
Displaying MySQL table records in Python Tkinter Delete MySQL table records

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer