Updating MySQL table record after user edit row from a list of records using tkinter

Edit and Update MySQL database records in Tkinter window

Download the zip file with source codes at the end of this tutorial.

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)

Number of records updated

We can display the number of records updated by the query like this. If there is no change in data then we will get 0 as output

print("Row updated  = ",id.rowcount)

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

Collect the unique class from student table of MySQL database by using Query. From the data create one list and use the same as option values of an OptionMenu

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
Select -Edit-update MySQL Product table using Treeview

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Display Edit & Update record from MySQL table

Display records

Edit Records

Update record

Number of records updated

Connect to MySQL database

Subscribe