Displaying records from MySQL table

";require "templates/body_start.php";?>
Connect to MySQL database display rows from MySQL.
Display records from MySQL Student table in Tkinter window.
MySQL database records in Tkinter window

MySQL database records in Tkinter window

Display ten records of student table of MySQL database on Tkinter window using SELECT & LIMIT Query

Treeview	Displaying records from MySQL database in Tkinter window using Treeview
Delete	Deleting selected row from MySQL table and Treeview
Pagination	Pagination of records from MySQL table using Treeview
Paging	Pagination of records of MySQL table using Tkinter window
record display	Displaying record in a Tkinter window by taking user input as row id
record update	Select-Edit-Update row by using Treeview
Image display	Displaying Binary data from MySQL Blob column.
Image display	Displaying all records with Images from MySQL Blob column.
Image add	Inserting uploaded image to Blob column of MySQL table
Image add	Adding data to student table with Photo using Blob column of MySQL table
Image Update	Updating user uploaded image in MySQL Blob column
record add	Adding user entered data through Tkinter window to MySQL table
Login	System for Login with add user and listing users with delete option
Random	Display Random record from MySQL or SQLite Database.
Ticket No.	Generate unique string id using date after inserting record

Connect to MySQL database

We connected to MySQL database by using our userid, password and database name. You can update your MySQL login details here.

import mysql.connector  # Importing the MySQL connector librarymy_connect = mysql.connector.connect(  host="localhost",  # Hostname of the MySQL server  user="userid",     # MySQL username  passwd="password", # MySQL password  database="database_name" # Name of the database to connect)####### end of connection ####my_conn = my_connect.cursor()  # Create a cursor object to execute SQL queries

We will use my_conn in our further script as the connection object to get our records.

We will use Query with LIMIT to collect 10 records from the student table.

By using LIMIT Query we are restricting our number of records by getting 10 records only from MySQL database. You can change this figure to get different number of records from the database.

Note that MySQL database executes the query part only and return us one result set. This result set contains all our (10) records.
We used the returned MySQLCursor i.e my_conn as an iterator

We will use one for loop to collect each row of record from the data set. Each row of data ( here student ) is a tuple. So we used another for loop to display each element of this tuple.

While printing the elements of student ( a tuple ) no line break is added and once the printing of one row of data is completed ( without any line break ) then outside the for loop one line break is added by using print()

my_conn = my_connect.cursor()  # Create a cursor object for executing SQL queriesmy_conn.execute("SELECT * FROM student limit 0,10")  # Execute the SQL query to fetch 10 rowsi = 0  # Initialize row counterfor student in my_conn:  # Iterate through the result set    for j in range(len(student)):  # Iterate through each column in a row        print(student[j], end='')  # Print column value without line break    i = i + 1  # Increment the row counter    print()  # Line break at the end of one row

This will print 10 rows of records from student table.

Using tkinter

We will use one tkinter entry component to display each data in the window. In this variable student is a tuple and it contains one row of data. We used variable i as index for each row and variable j as each column of data.
The full code is here , Change the userid,password and database name of your MySQL database.

import mysql.connectorimport tkinter as tkfrom tkinter import *my_w = tk.Tk()my_w.geometry("400x250")  # width and height of the windowmy_connect = mysql.connector.connect(  host="localhost",  # Database host  user="id",      # Database user  passwd="password",    # Database password  database="my_db"  # Database name)my_conn = my_connect.cursor()  # Cursor for executing queries####### end of connection ####my_conn.execute("SELECT * FROM student limit 0,10")i = 0for student in my_conn:    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 + 1my_w.mainloop()

For simplicity, the login details are included directly in this code.However, for enhanced security, it is strongly recommended to keep your login credentials separate from the main script.You can achieve this by storing them in a JSON file, environment variables, or other secure methods, ensuring your sensitive information remains protected.
Storing MySQL Login details securely

Using SQLAlchemy

We can use SQLAlchemy library to manage MySQL database. Here is the same code using SQLAlchemy. Connection part is highlighted. Here also you have to change your MySQL login details like userid, password and database name.

from sqlalchemy import create_engine, textmy_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")my_conn = my_conn.connect()  # connection object, connection stringimport tkinter as tkfrom tkinter import *my_w = tk.Tk()  # parent windowmy_w.geometry("400x250")  # width and height of window####### end of connection ####r_set = my_conn.execute(text("SELECT * FROM student limit 0,10"))i = 0for 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 + 1my_w.mainloop()

Using Label in place of Entry in above code.

for student in r_set:     for j in range(len(student)):        e = Label(my_w,width=10, text=student[j])         e.grid(row=i, column=j)         #e.insert(END, student[j])    i=i+1

We can add border to each cell by using borderwidth=2, relief='ridge' and align the text to left by using anchor='w'.

e = Label(my_w,      width=10,      text=student[j],      borderwidth=2,      relief='ridge',      anchor="w")

Showing MySQL records using Label in Tkinter window

Adding column headers

Displaying MySQL records & adding border with alignment and showing column names as header

Note that if column header row is starting from first row ( row=0 ) then i value has to start from 1. We are using Labels to add one row with five columns. Use this code before the for loop.
records with column hdeader

e=Label(my_w,width=10,text='id',borderwidth=2, relief='ridge',anchor='w',bg='yellow')e.grid(row=0,column=0)e=Label(my_w,width=10,text='Name',borderwidth=2, relief='ridge',anchor='w',bg='yellow')e.grid(row=0,column=1)e=Label(my_w,width=10,text='Class',borderwidth=2, relief='ridge',anchor='w',bg='yellow')e.grid(row=0,column=2)e=Label(my_w,width=10,text='mark',borderwidth=2, relief='ridge',anchor='w',bg='yellow')e.grid(row=0,column=3)e=Label(my_w,width=10,text='Gender',borderwidth=2, relief='ridge',anchor='w',bg='yellow')e.grid(row=0,column=4)i=1

e = Label(my_w,width=10,  text='id',	borderwidth=2,relief='ridge',  anchor='w', bg='yellow')e.grid(row=0, column=0)  e = Label(my_w, width=10, text='Name',      borderwidth=2, relief='ridge', anchor='w', bg='yellow')  e.grid(row=0, column=1)  e = Label(my_w, width=10, text='Class',      borderwidth=2, relief='ridge', anchor='w', bg='yellow')  e.grid(row=0, column=2)  e = Label(my_w, width=10, text='mark',      borderwidth=2, relief='ridge', anchor='w', bg='yellow')  e.grid(row=0, column=3)  e = Label(my_w, width=10, text='Gender',      borderwidth=2, relief='ridge', anchor='w', bg='yellow')  e.grid(row=0, column=4)  i = 1

Using MySQL or SQLite

Only the line of code to create the connection object my_conn is different for SQLite and MySQL database. Rest of the code is common for both type databases.

Display records using SQLite database

We can display records based on the user entered data. We will ask user to enter id of the student and by using this id we will collect the details of the record from MySQL table.

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

Create one OptionMenu with option values taken from MySQL database table

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

Displaying MySQL records using Treeview

Pagination of records

If you have more records to display then you can break the total records in parts and display fixed number of records.
Tutorial on Paging script Select -Edit-update MySQL Product table using Treeview

Python Tkinter Dictionary Python- Tutorials