Pagination of records from MySQL table
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.
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
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
Python Video Tutorials