Displaying rows of data from MySQL database table using Treeview of tkinter

Connect to MySQL database display rows from MySQL.
We will display records from MySQL Student table using Treeview in Tkinter window.
First we will import ttk module.

from tkinter import ttk

Connecting and displaying MySQL table data in Tkinter window using Treeview insert with columns

Connect to MySQL database

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

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.

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.

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()

Displaying records: Checking connection

Run this code without using Tkinter to check your MySQL record retrieval. If this code is working fine then you can focus on Tkinter part.

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
r_set=my_conn.execute('''SELECT * from student LIMIT 0,10''');
for dt in r_set: 
    print(dt)

Integrating MySQL data to Treeview

In above code we have the record r_set which we can loop through and insert the rows to the Treeview. This is the line which does that.

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

To one Tkinter window we will integrate the above code. The Treeview we created will have header and columns. After creating all these we will insert the rows of data using above code.

With Tkinter the Full code is here

from sqlalchemy import create_engine, text
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()

## collect data from database to check
# r_set = my_conn.execute(text("SELECT * FROM student"))
# for row in r_set:
#     print(row)

from tkinter import ttk  # for Treeview
import tkinter as tk    

my_w = tk.Tk()
my_w.geometry("400x280")  # width and height of window
my_w.title("www.plus2net.com")  # title

# Using treeview widget
trv = ttk.Treeview(my_w, selectmode='browse')
trv.grid(row=1, column=1, padx=20, pady=20)

# 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='c')
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")

# getting data from MySQL student table
r_set = my_conn.execute(text('SELECT * from student LIMIT 0,10'))

# Adding rows of data to Treeview
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]))

my_w.mainloop()  # Keep the window open.

This will print 10 rows of records from student table.

Treeview with tag_configure()

We can add tags to show different styles to the rows. Here if the mark column value is more than or equal to 75 then the row colour will be green.

Tkinter Treeview row background colour configure using tag_configure() based on conditions

trv.tag_configure('pass', background='lightgreen')
trv.tag_configure('fail', background='yellow')

Adding to rows based on (one liner) if condition check

for dt in r_set:
    my_tag='pass' if dt[3] >=75 else 'fail' 
    trv.insert("", 'end',iid=dt[0], text=dt[0],
               values =(dt[0],dt[1],dt[2],dt[3],dt[4]), tags=(my_tag))

We can add more styles to the tag_configure().

trv.tag_configure('pass', background='lightgreen',font=('Times',12,'normal'))
trv.tag_configure('fail', background='yellow',font=('Times',12,'overstrike'))

Row colors based on Grade ( column value )

row colour based on grade using tag_configure() with Treeview

We will have 4 grades based on the Mark the student got. Here are 4 grades with different background colors

trv.tag_configure('A', background='lightgreen')
trv.tag_configure('B', background='lightblue')
trv.tag_configure('C', background='lightyellow')
trv.tag_configure('D', background='white')

Based on the mark we can have different colour for the row

r_set=my_conn.execute('''SELECT * from student LIMIT 0,10''')
for dt in r_set:
    if(dt[3]>=80): my_tag='A'
    elif(dt[3]>=70): my_tag='B'
    elif(dt[3]>=60): my_tag='C'
    else: my_tag='D'
    trv.insert("", 'end',iid=dt[0], text=dt[0],
               values =(dt[0],dt[1],dt[2],dt[3],dt[4]), tags=(my_tag))

Alternate colors for the rows of Treeview

trv.tag_configure('gray', background='lightgray')
trv.tag_configure('normal', background='white')
my_tag='normal' # default value 
# getting data from MySQL student table 
r_set=my_conn.execute('''SELECT * from student LIMIT 0,10''')
for dt in r_set:
    my_tag='gray' if my_tag=='normal' else 'normal' 
    trv.insert("", 'end',iid=dt[0], text=dt[0],
               values =(dt[0],dt[1],dt[2],dt[3],dt[4]),tags=(my_tag))

Adding background colour

Add these lines to the code before the last line.

style = ttk.Style(my_w) # set theam to clam
style.theme_use("clam")
style.configure("Treeview", background="black", 
                fieldbackground="black", foreground="white")

Managing style of Treeview

Dynamic column configuration

Showing multiple records in Tkinter Treeview with columns taken from MySQL database sample table

We can collect the column headers from the table and accordingly use them in our Treeview. This way our layout of Treeview will work even the columns returned from the MySQL database table changes.
How to create rows and columns dynamically in Treeviews

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")  
# Creating tkinter my_w
my_w = tk.Tk()
my_w.geometry("400x380") # width and height of the window 
my_w.title("www.plus2net.com")  

r_set=my_conn.execute("SELECT * from student LIMIT 0,5")
l1=[r for r in r_set.keys()] # List of column headers

# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse',columns=l1,
    show='headings',height=10)
trv.grid(row=1,column=1,padx=20,pady=20)

# set columns and headings for Treeview 
for i in l1:
    trv.column(i, anchor ='c', width=70)
    trv.heading(i, text =i)
# Adding rows of data from MySQL student table to treeview 
for row in r_set: 
    trv.insert("", 'end',iid=row[0], text=row[0],
               values =list(row))
my_w.mainloop()

Vertical Scrollbar for Treeview

When we are displaying more records than what is set as height option of the treeview, we have to provide a vertical scrollbar for navigation.

In above code the height option is set to 10 and there are more than 30 records in our student table. Here we will remove the LIMIT query and collect all the records.

Vertical & horizontal Scrollbar to navigate rows for Tkinter Treeview while displaying records

Here is the full code with vertical scrollbar

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")  
# Creating tkinter my_w
my_w = tk.Tk()
my_w.geometry("420x300") # width and height of the window 
my_w.title("www.plus2net.com")  

r_set=my_conn.execute("SELECT * from student") # collect all records 
l1=[r for r in r_set.keys()] # List of column headers

# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse',columns=l1,
    show='headings',height=10)
trv.grid(row=1,column=1,padx=20,pady=20)

# set columns and headings for Treeview 
for i in l1:
    trv.column(i, anchor ='c', width=70)
    trv.heading(i, text =i)
# Adding rows of data from MySQL student table to treeview 
for row in r_set: 
    trv.insert("", 'end',iid=row[0], text=row[0],
               values =list(row))

vs = ttk.Scrollbar(my_w,orient="vertical", command=trv.yview)#V Scrollbar
trv.configure(yscrollcommand=vs.set)  # connect to Treeview
vs.grid(row=1,column=2,sticky='ns')
my_w.mainloop()