When we collect data from the source ( database table or Excel or CSV file ) we are not sure how many columns will be available. Here are some SQL which collects different number of columns from the table
SELECT id,name FROM student
SELECT id,name,class FROM student
SELECT * FROM student
First and second query will collect two and three columns respectively from our student table. The third query will return all the columns of the student table.
This way we will not have any fixed number of columns and based on the source or data, we have to create the columns.
For our easy understanding let us create one list and use the elements as column headers. We can replace our list with data from Database table.
l1=['id','Name','Class','Mark','Gender']
Using this list we will add our columns and headers ( list elements ) to our Treeview.
We will replace the list l1 in above code with our data column headers and for that we have used our MySQL table. Note that source can be SQLite database or Excel or Pandas DataFrame or any other source.
Tkinter Treeview displaying data rows with headers from different source like CSV file & Excel
MySQL database table as data source
from sqlalchemy import create_engine,text
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
my_conn = my_conn.connect()
r_set=my_conn.execute(text('SELECT * FROM student'))
l1=[r for r in r_set.keys()] # List of column headers
r_set=[r for r in r_set] # Rows of data
#l1=['id','Name','Class','Mark','Gender']
from sqlalchemy import create_engine,text
my_conn = create_engine("sqlite:///E:\\testing\\sqlite\\my_db.db")
my_conn=my_conn.connect()
r_set=my_conn.execute(text('SELECT * FROM student'))
l1=[r for r in r_set.keys()] # List of column headers
r_set=[r for r in r_set]
import pandas as pd
df = pd.read_excel('D:\student.xlsx') # create dataframe using xlsx file
l1=list(df) # List of column names as headers
r_set=df.to_numpy().tolist() # Create list of list using rows
from openpyxl import load_workbook
wb = load_workbook(filename='D:\student.xlsx', read_only=True)
ws = wb['Sheet1']
r_set = [row for row in ws.iter_rows(values_only=True)]
l1=r_set.pop(0) # Collect the first row as column headers
wb.close()# Close the workbook after reading
import csv
file = open('D:\student.csv') # file object for student.csv file
csvreader = csv.reader(file)
l1 = []
l1 = next(csvreader) # column headers as first row
r_set = [row for row in csvreader]
Adding headers and columns to Treeview ( dynamically )
The list l1 is created using any of the above data sources. Here one sample list is given.
#l1=['id','Name','Class','Mark','Gender'] # sample list for testing
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=1,column=1,padx=30,pady=20) # grid to place
trv['height']=5 # Number of rows to display, default is 10
trv['show'] = 'headings'
# Adding headers and columns using lists
trv["columns"] = l1
# Defining headings, other option in tree
# width of columns and alignment
for i in l1:
trv.column(i, width = 100, anchor ='c')
# Headings of respective columns
trv.heading(i, text =i)
Above example will create the headings and columns, we will add data by using r_set we created using our data sources.
Adding data to Treeview
We will add data collected from the above query to Treeview.
for dt in r_set:
v=[r for r in dt] # collect the row data as list
trv.insert("",'end',iid=v[0],values=v)
Note that in above code our all columns data will be inserted as row. Here based on the query we used, number of columns will be collected and the same will be used to add row. Try to change the query or source by calling different columns and same will be reflected automatically while adding the data.
Number of rows to display
In our Treeview we can manage the number of records to display . Note that here we have the browse option to scroll to more records.
trv['height']=5 # Number of rows to display, default is 10
Tkinter Treeview displaying data from different source like Pandas DataFrame MySQL & SQLite table
Full code with different data sources is here.
Un-comment the data source you want to use.
from tkinter import ttk
import tkinter as tk
# Creating tkinter my_w
my_w = tk.Tk() # root or parent window
my_w.geometry("560x280") # width and height of window
my_w.title("www.plus2net.com") # title
# MySQL database as datasource
from sqlalchemy import create_engine,text
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()
r_set=my_conn.execute(text('SELECT * FROM student'))
l1=[r for r in r_set.keys()] # List of column headers
r_set=[r for r in r_set] # Rows of data
### SQLite database as data source ###
'''
from sqlalchemy import create_engine,text
my_conn = create_engine("sqlite:///E:\\testing\\sqlite\\my_db.db")
my_conn=my_conn.connect()
r_set=my_conn.execute(text('SELECT * FROM student'))
l1=[r for r in r_set.keys()] # List of column headers
r_set=[r for r in r_set]
'''
# Using Pandas DataFrame as data source
'''
import pandas as pd
df = pd.read_excel('D:\student.xlsx') # create dataframe
l1=list(df) # List of column names as headers
r_set=df.to_numpy().tolist() # Create list of list using rows
'''
# openpyxl library for reading data from Excel file
'''
from openpyxl import load_workbook
wb = load_workbook(filename='D:\student.xlsx', read_only=True)
ws = wb['Sheet1']
r_set = [row for row in ws.iter_rows(values_only=True)]
l1=r_set.pop(0) # Collect the first row as column headers
wb.close()# Close the workbook after reading
'''
# Using CSV file
'''
import csv
file = open('C:\\data\\student.csv') # Path of CSV data file
csvreader = csv.reader(file)
l1 = []
l1 = next(csvreader) # column headers as first row
r_set = [row for row in csvreader]
'''
# Data Sources to create header list and row of data is over.
#l1=['id','Name','Class','Mark','Gender'] # sample list for testing
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=1,column=1,padx=30,pady=20) # grid to place
trv['height']=5 # Number of rows to display, default is 10
trv['show'] = 'headings'
# Adding headers and columns using lists
trv["columns"] = l1
# Defining headings, other option in tree
# width of columns and alignment
for i in l1:
trv.column(i, width = 100, anchor ='c')
# Headings of respective columns
trv.heading(i, text =i)
## Adding data to treeview
for dt in r_set:
v=[r for r in dt] # creating a list from each row
trv.insert("",'end',iid=v[0],values=v) # adding row
#data=[1,'Alex','Four',45,'Male'] # sample data to insert
#trv.insert("",'end',iid=1,values=data)
my_w.mainloop() # keep the window open
Tkinter Treeview to display report based on selection of Month from Combobox from MySQL table
Dynamic Treeview columns in Tkinter | Update Data | Python GUI Tutorial | #Python #Tkinter