Displaying records from MySQL table using Treeview

";require "templates/body_start.php";?>
Connect to MySQL database display rows from MySQL. student table with SQL Dump
We will display records from MySQL Student table using Treeview in Tkinter window.
First we will import ttk module.

from tkinter import ttk

Connect to MySQL database

from sqlalchemy import create_enginemy_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.

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_enginemy_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, textmy_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 Treeviewimport tkinter as tk    my_w = tk.Tk()my_w.geometry("400x280")  # width and height of windowmy_w.title("www.plus2net.com")  # title# Using treeview widgettrv = ttk.Treeview(my_w, selectmode='browse')trv.grid(row=1, column=1, padx=20, pady=20)# number of columnstrv["columns"] = ("1", "2", "3", "4", "5")# Defining headingtrv['show'] = 'headings'# width of columns and alignmenttrv.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 columnstrv.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 tabler_set = my_conn.execute(text('SELECT * from student LIMIT 0,10'))# Adding rows of data to Treeviewfor 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.

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 )

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 clamstyle.theme_use("clam")style.configure("Treeview", background="black",                 fieldbackground="black", foreground="white")

Managing style of Treeview →

Dynamic column configuration

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 ttkimport tkinter as tkfrom sqlalchemy import create_enginemy_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")  # Creating tkinter my_wmy_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 widgettrv = 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.



Here is the full code with vertical scrollbar

from tkinter import ttkimport tkinter as tkfrom sqlalchemy import create_enginemy_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")  # Creating tkinter my_wmy_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 widgettrv = 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 Scrollbartrv.configure(yscrollcommand=vs.set)  # connect to Treeviewvs.grid(row=1,column=2,sticky='ns')my_w.mainloop()

← Displaying MySQL records using Entry or LabelTtkboostrap Tableview → Generating Invoice by adding products using Treeview »
TreeviewTreeview insert Dynamic Creation of Header & Columns in Treeview » Pagination of Records of MySQLDelete RecordsSelect -Edit-update MySQL Product table using Treeview » Query window & displaying records in Treeview »