Displaying rows of data from MySQL database table using tkinter

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 library

my_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 queries

my_conn.execute("SELECT * FROM student limit 0,10")  # Execute the SQL query to fetch 10 rows
i = 0  # Initialize row counter

for 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.connector
import tkinter as tk
from tkinter import *

my_w = tk.Tk()
my_w.geometry("400x250")  # width and height of the window

my_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 = 0

for 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 + 1

my_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, text

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()  # connection object, connection string

import tkinter as tk
from tkinter import *

my_w = tk.Tk()  # parent window
my_w.geometry("400x250")  # width and height of window

####### end of connection ####

r_set = my_conn.execute(text("SELECT * FROM student limit 0,10"))
i = 0

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

my_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

Subscribe to our YouTube Channel here

	02-12-2020
Hey, your code is working very well. Thank You so much. But there is one query. Every time the table is displayed in the Tk() window it goes to the top left corner. What is to be done if i want it somewhere in the middle?

	04-12-2020
my_w.geometry('400x250+450+350') Here 450 and 350 are x and y position. You can change this value to position it at different locations. You can further improve it by reading user screen resolution and then apply it to dynamically position the window by changing these values.

	04-12-2020
Don't use space while adding values to geometry(). This will not work my_w.geometry('400x250 + 450 + 350')

	20-07-2023
Hi, this is working very well for me, how do I make the labels update each time I make a change to the database? thank you

	22-07-2023
You have to refresh the view to reflect the changes. Here we are using grid view so there is a way to refresh the grid view. Grid view details are here, Grid use grid_forget() to remove than and create again.

	01-11-2020
Thanks a lot...Loved it!! It works for me!

	21-11-2020
Thanks a lot

	02-07-2021
thank you soo much !!!!!!!!!!!!

Displaying records from MySQL table