Displaying records from MySQL table using Treeview


Youtube Live session on Tkinter

MySQL: Records in Treeview
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.

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 tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")  
# Creating tkinter my_w
my_w = tk.Tk()
my_w.geometry("400x280") 
my_w.title("www.plus2net.com")  
# 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('''SELECT * from student LIMIT 0,10''')
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()
This will print 10 rows of records from student table.

Treeview with tag_configure()

tag_configure() with Treeview
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

Alternate row colour in 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

Background colour of Treeview
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

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()
Displaying MySQL records using Entry or Label Ttkboostrap Tableview Generating Invoice by adding products using Treeview
Treeview Treeview insert Dynamic Creation of Header & Columns in Treeview Pagination of Records of MySQL Delete Records Select -Edit-update MySQL Product table using Treeview Query window & displaying records in Treeview
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer