Pagination of records from MySQL table using Treeview of Tkinter

Connect to MySQL database display rows from MySQL.
Read our tutorial on how to display MySQL records in Treeview.
We will add pagination feature to this.

Tkitner Treeview paging of MySQL records by LIMIT query using Previous and next set of records

Query with variables

The variable offset will carry the starting record number and the variable limit value is set at beginning of the script. Each time the Next or previous button is clicked a different offset value is passed to the function.

q="SELECT * from student LIMIT "+ str(offset) +","+str(limit)

Adding buttons for browsing previous and next pages

Buttons carry different value based on this calculation.

back = offset - limit # This value is used by Previous button
next = offset + limit # This value is used by Next button       
b1 = tk.Button(my_w, text='< Prev', command=lambda: my_display(back))
b1.grid(row=2,column=2,sticky='E')
b2 = tk.Button(my_w, text='Next >', command=lambda: my_display(next))
b2.grid(row=2,column=3)

Enable and disable buttons based on the record position

Our variable no_rec holds the value of total number or records and it is calculated at the beginning of the script.

r_set=my_conn.execute("SELECT count(*) as no from STUDENT")
data_row=r_set.fetchone()
no_rec=data_row[0] # Total number of rows in table

If we are at starting point ( offset=0) then we can't move further back, so the Previous button should be disabled.
If we are at last page then we can't move further, so the Next button should be disabled.

    if(no_rec <= next): 
        b2["state"]="disabled" # disable next button
    else:
        b2["state"]="active"  # enable next button
        
    if(back >= 0):
        b1["state"]="active"  # enable Prev button
    else:
        b1["state"]="disabled"# disable Prev button

The treeview is kept inside a function my_display(), this function is called always when any button is used. This will read fresh rows from the database and display based on the input offset value.
With Tkinter - Treeview the Full code is here

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")

###### end of connection ####
r_set=my_conn.execute("SELECT count(*) as no from STUDENT")
data_row=r_set.fetchone()
no_rec=data_row[0] # Total number of rows in table
limit = 5; # No of records to be shown per page.
##### tkinter window ######
from tkinter import ttk
import tkinter  as tk 
my_w = tk.Tk()
my_w.geometry("400x400") 
my_str = tk.StringVar() # to display query at end
def my_display(offset):    
    ###
    trv = ttk.Treeview(my_w, selectmode ='browse')
  
    trv.grid(row=1,column=2,padx=20,pady=20,columnspan=3)
# number of columns
    trv["columns"] = ("1", "2", "3","4","5")
  
# Defining heading
    trv['show'] = 'headings'
  
# width of columns and alignment 
    trv.column("1", width = 30, anchor ='c')
    trv.column("2", width = 80, anchor ='w')
    trv.column("3", width = 80, anchor ='c')
    trv.column("4", width = 80, anchor ='c')
    trv.column("5", width = 80, anchor ='c')
  
# Headings  
# respective columns
    trv.heading("1", text ="id")
    trv.heading("2", text ="Name")
    trv.heading("3", text ="Class")
    trv.heading("4", text ="Mark")  
    trv.heading("5", text ="Gender")
    ###
    q="SELECT * from student LIMIT "+ str(offset) +","+str(limit)
    r_set=my_conn.execute(q);

    for dt in r_set: 
        trv.insert("", 'end',iid=dt[0], text=dt[0],
               values =(dt[0],dt[1],dt[2],dt[3],dt[4]))

    # Show buttons 
    back = offset - limit # This value is used by Previous button
    next = offset + limit # This value is used by Next button       
    b1 = tk.Button(my_w, text='< Prev', command=lambda: my_display(back))
    b1.grid(row=2,column=2,sticky='E')
    b2 = tk.Button(my_w, text='Next >', command=lambda: my_display(next))
    b2.grid(row=2,column=3)

    if(no_rec <= next): 
        b2["state"]="disabled" # disable next button
    else:
        b2["state"]="active"  # enable next button
        
    if(back >= 0):
        b1["state"]="active"  # enable Prev button
    else:
        b1["state"]="disabled"# disable Prev button 
    # for your understanding of how the offset value changes
    # query is displayed here, it is not part of the script 
    my_str.set(q + '\n' + "next: " + str(next) + "\n back:"+str(back))
    l1 = tk.Label(my_w, textvariable=my_str)
    l1.grid(row=3,column=1,columnspan=3)
my_display(0)        
my_w.mainloop()

Display MySQL records in Treeview MySQL Treeview Select & Delete record
Displaying MySQL records using Entry or Label Treeview insert Records of MySQL Select -Edit-update MySQL Product table using Treeview

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Pagination of MySQL rows in Treeview

Query with variables

Adding buttons for browsing previous and next pages

Enable and disable buttons based on the record position

Subscribe