Paging script using rows of data from MySQL database table using tkinter

Connect to MySQL database display rows from MySQL.

The script is same and only the Database connection part is changed to use MySQL database. So read the tutorial on Paging script for SQLite database.

First page of paging records in Tkinter window

The only changes here is the connection string with login details. This part is highlighted.

#https://www.plus2net.com/python/tkinter-mysql-paging.php
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/db_name")
###### 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 = 8; # No of records to be shown per page.
##### tkinter window ######
import tkinter  as tk 
from tkinter import * 
my_w = tk.Tk()
my_w.geometry("350x200") 
def my_display(offset):    

    q="SELECT * from student LIMIT "+ str(offset) +","+str(limit)
    r_set=my_conn.execute(q);
    i=0 # row value inside the loop 
    for student in r_set: 
        for j in range(len(student)):
            e = Entry(my_w, width=10, fg='blue') 
            e.grid(row=i, column=j) 
            e.insert(END, student[j])
        i=i+1
    while (i= 0):
        b2["state"]="active"  # enable Prev button
    else:
        b2["state"]="disabled"# disable Prev button      
my_display(0)        
my_w.mainloop()

Resolving Stale Data Issues in SQLAlchemy by Reopening the Connection

The issue arose because SQLAlchemy's open connection was caching data, preventing updated database changes from being reflected in the Tkinter application when switching between pages. The solution was to close and reopen the database connection before each query execution. By reopening the connection every time the my_display() function is called, fresh data is fetched from the database, ensuring that any backend changes are displayed correctly in the Tkinter interface. This ensures the most up-to-date data is always shown.

def my_display(offset):    
    global my_conn
    my_conn.close()  # Close the existing connection
    my_conn = create_engine("mysql+mysqldb://id:pwlocalhost/my_db").connect() 
    q="SELECT * from student LIMIT "+ str(offset) +","+str(limit)
	--------
	--------

Transitioning to SQLAlchemy’s New Connection Method with connect() and text()

In recent versions of SQLAlchemy, you are required to explicitly call the connect() method when working with the engine to ensure that the database connection is established properly.
SQLAlchemy’s New Connection Method with connect() and text()

View and Download tkinter-mysql-paging ipynb file ( .html format )

Pagination using Treeview Tutorial on Paging script

Python Tkinter Dictionary Python- Tutorials

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

Pagination of records from MySQL table

Resolving Stale Data Issues in SQLAlchemy by Reopening the Connection

Transitioning to SQLAlchemy’s New Connection Method with connect() and text()

Subhendu Mohapatra