Generating Monthly report from Database table based on month selection and using Treeview to display

Tkinter Treeview to display report based on selection of Month from Combobox from MySQL table

Query based on user selection of Month from Combobox

We will first create the window with Combobox and on selection of Month we will show the Query string.

Here we have used trace method of StringVar() str to trigger the function on change of selection of Combobox.

Inside the function my_upd() we will read the selected value of the month by using sel.get() and then apply config() to display the text on Label lb

Here is the code to create the query.

import tkinter as tk
from tkinter import ttk
my_w = tk.Tk()
my_w.geometry("600x450")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

def my_upd(*args):
    month=sel.get() # read the selected month 
    lb.config(text="SELECT * FROM my_tasks WHERE \
    DATE_FORMAT( dt, '%b' ) ='"+month+"'")

sel=tk.StringVar() # string variable 

months=['Jan','Feb','Mar','Apr','May','Jun','Jul',
    'Aug','Sep','Oct','Nov','Dec']

cb1 = ttk.Combobox(my_w, values=months,width=7, textvariable=sel)
cb1.grid(row=0,column=0,padx=10,pady=20)

lb=tk.Label(my_w,text='Query',bg='yellow') # To display Query
lb.grid(row=1,column=2) 

sel.trace('w',my_upd) # Trigger on change of selection 
my_w.mainloop()  # Keep the window open

We will connect to MySQL database.

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

usrid : Your MySql login user id
pw : Your MySql login password
localhost : Address of MySQL server, IP address or localhost
my_db : Database name of MySQL where our table is available.

Report on selection of Month from MySQL table in Treeview

Report on selection of Month from MySQL table in Treeview

Full code is here

import tkinter as tk
from tkinter import *
from tkinter import ttk
my_w = tk.Tk()
my_w.geometry("1000x600")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
def my_upd(*args):
    global trv
    month=sel.get() # Collecting the selected month name
    query="SELECT id,tasks,status,DATE_FORMAT(dt,'%%d-%%b-%%Y') as dt FROM my_tasks WHERE \
        DATE_FORMAT( dt, '%%b' ) ='"+month+"'"
    lb.config(text=query) # display query 
    r_set=my_conn.execute(query)
    l1=[r for r in r_set.keys()] # List of column headers 
    r_set=[r for r in r_set] # Rows of data

    trv['height']=10 # Number of rows to display, default is 10
    trv['show'] = 'headings' 
    # column identifiers 
    trv["columns"] = l1
    print(l1)
    # Defining headings, other option in tree
    # width of columns and alignment 

    for i in l1:
        trv.column(i, width =80, anchor ='w',stretch=NO)
    # Headings of respective columns
    for i in l1:
        trv.heading(i, text =i)
		
    for item in trv.get_children(): # delete all rows 
        trv.delete(item)
		
    for dt in r_set:
        #print(dt)
        v=[r for r in dt] # collect the row data as list 
        trv.insert("",'end',iid=v[0],values=v)

sel=tk.StringVar() # string variable 

months=['Jan','Feb','Mar','Apr','May','Jun','Jul',
    'Aug','Sep','Oct','Nov','Dec']
cb1 = ttk.Combobox(my_w, values=months,width=7, textvariable=sel)
cb1.grid(row=0,column=0,padx=5,pady=20)

lb=tk.Label(my_w,text='Query',bg='yellow')
lb.grid(row=1,column=0,columnspan=3)
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=2,column=0,columnspan=3,padx=5,pady=20)

sel.trace('w',my_upd)


my_w.mainloop()  # Keep the window open

Create Sample table with data ( MySQL )

For SQLite database un-comment the my_conn and remove the line connecting to MySQL database.

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path='D:\\testing\\sqlite\\my_db.db' #Change the path 
#my_conn = create_engine('sqlite:///'+ my_path)
my_conn =create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
try:
    my_conn.execute('DROP TABLE my_tasks')
    my_conn.execute("\
        CREATE TABLE IF NOT EXISTS my_tasks(id integer primary key, \
                      tasks text, \
                      status Boolean,\
                      dt datetime  \
                      );")  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print('tasks  Table created successfully..')
try:
  r_set=my_conn.execute("INSERT INTO `my_tasks` \
  (`id`, `tasks`, `status`,dt) VALUES \
\
(1, 'My tasks 1 ', True,'2024-04-01'),\
(11, 'My tasks 11 ', True,'2024-04-11'),\
(21, 'My tasks 21 ', True,'2024-04-21'),\
(31, 'My tasks 31 ', True,'2024-05-01'),\
(41, 'My tasks 41 ', True,'2024-05-11'),\
(51, 'My tasks 51 ', True,'2024-05-21'),\
(61, 'My tasks 61 ', True,'2024-05-31'),\
(71, 'My tasks 71 ', True,'2024-06-10'),\
(81, 'My tasks 81 ', True,'2024-06-20'),\
(91, 'My tasks 91 ', True,'2024-06-30'),\
(101, 'My tasks 101 ', True,'2024-07-10'),\
(111, 'My tasks 111 ', True,'2024-07-20'),\
(121, 'My tasks 121 ', True,'2024-07-30'),\
(131, 'My tasks 131 ', True,'2024-08-09'),\
(141, 'My tasks 141 ', True,'2024-08-19'),\
(151, 'My tasks 151 ', True,'2024-08-29'),\
(161, 'My tasks 161 ', True,'2024-09-08'),\
(171, 'My tasks 171 ', True,'2024-09-18'),\
(181, 'My tasks 181 ', True,'2024-09-28'),\
(191, 'My tasks 191 ', True,'2024-10-08'),\
(201, 'My tasks 201 ', True,'2024-10-18'),\
(211, 'My tasks 211 ', True,'2024-10-28'),\
(221, 'My tasks 221 ', True,'2024-11-07'),\
(231, 'My tasks 231 ', True,'2024-11-17'),\
(241, 'My tasks 241 ', True,'2024-11-27'),\
(251, 'My tasks 251 ', True,'2024-12-07'),\
(261, 'My tasks 261 ', True,'2024-12-17'),\
(271, 'My tasks 271 ', True,'2024-12-27'),\
(281, 'My tasks 281 ', True,'2025-01-06'),\
(291, 'My tasks 291 ', True,'2025-01-16'),\
(301, 'My tasks 301 ', True,'2025-01-26'),\
(311, 'My tasks 311 ', True,'2025-02-05'),\
(321, 'My tasks 321 ', True,'2025-02-15'),\
(331, 'My tasks 331 ', True,'2025-02-25'),\
(341, 'My tasks 341 ', True,'2025-03-07'),\
(351, 'My tasks 351 ', True,'2025-03-17'),\
(361, 'My tasks 361 ', True,'2025-03-27'),\
(371, 'My tasks 371 ', True,'2025-04-06'),\
(381, 'My tasks 381 ', True,'2025-04-16'),\
(391, 'My tasks 391 ', True,'2025-04-26'),\
(401, 'My tasks 401 ', True,'2025-05-06'),\
(411, 'My tasks 411 ', True,'2025-05-16'),\
(421, 'My tasks 421 ', True,'2025-05-26'),\
(431, 'My tasks 431 ', True,'2025-06-05'),\
(441, 'My tasks 441 ', True,'2025-06-15'),\
(451, 'My tasks 451 ', True,'2025-06-25'),\
(461, 'My tasks 461 ', True,'2025-07-05'),\
(471, 'My tasks 471 ', True,'2025-07-15'),\
(481, 'My tasks 481 ', True,'2025-07-25'),\
(491, 'My tasks 491 ', True,'2025-08-04')")
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print('No of Records added : ',r_set.rowcount)

Using SQLite3 connection

MySQL accepts month as %b for matching query, while using SQLite we have to use month as 01, 02 ,03 ..... It is not a good idea to display numbers inside the Combobox for user to select.

Here is a dictionary with month names ( Jan, Feb ..) as Keys and Month numbers (01, 02 .. ) as values. We have taken all keys of the dictionary to display as options of the Combobox.

my_dict={'Jan':'01','Feb':'02','Mar':'03','Apr':'04',
'May':'05','Jun':'06','July':'07','Aug':'08','Sep':'09',
 'Oct':'10','Nov':'11','Dec':'12'}

#months=list(my_dict.values()) # showing Month number as options for Combobox 
months=[r for r in my_dict.keys()] # Month names as options

Once the user select the month as ( Keys as Month names ) , then matching matching month number we have to use inside our function and query.

query="SELECT id,tasks,status,strftime('%d-%m-%Y',dt) as dt \
    FROM my_tasks WHERE strftime('%m',dt)='"+my_dict[sel.get()]+"'"

Getting the keys or column headers in SQLite database

l1=my_conn.execute("PRAGMA table_info(my_tasks);")
l1=[r[1] for r in l1] # list of column headers

Note that here as we are using one different query to get the column names, so we have kept the headers outside the function.

Full code is here.

import tkinter as tk
from tkinter import *
from tkinter import ttk
my_w = tk.Tk()
my_w.geometry("700x450")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title
my_path='D:\\testing\\sqlite\\my_db.db' #Change the path 
import sqlite3 
my_conn = sqlite3.connect(my_path)
#from sqlalchemy import create_engine ## to use SQLAlchemy 
#my_conn = create_engine('sqlite:///'+ my_path) 
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
def my_upd(*args):
    for item in trv.get_children():
        trv.delete(item)
    query="SELECT id,tasks,status,strftime('%d-%m-%Y',dt) as dt \
    FROM my_tasks WHERE strftime('%m',dt)='"+my_dict[sel.get()]+"'"
    lb.config(text=query)
    r_set=my_conn.execute(query)
    
    for dt in r_set:
        v=[r for r in dt]
        trv.insert('','end',iid=v[0],values=v)

sel=tk.StringVar()

my_dict={'Jan':'01','Feb':'02','Mar':'03','Apr':'04',
'May':'05','Jun':'06','July':'07','Aug':'08','Sep':'09',
 'Oct':'10','Nov':'11','Dec':'12'}

#months=list(my_dict.values()) # showing Month number as options for Combobox 
months=[r for r in my_dict.keys()] # Month names as options 

cb1=ttk.Combobox(my_w,values=months,width=7,textvariable=sel)
cb1.grid(row=0,column=0,padx=1,pady=20)

lb=tk.Label(my_w,text='Query',bg='yellow',width=100)
lb.grid(row=1,column=0,padx=1)
sel.trace('w',my_upd)

trv=ttk.Treeview(my_w,selectmode='browse')
trv.grid(row=2,column=0,columnspan=3,padx=5,pady=20)

l1=my_conn.execute("PRAGMA table_info(my_tasks);")
l1=[r[1] for r in l1] # list of column headers 
    
trv['height']=10 # Number of rows 
trv['show']='headings'
trv['columns']=l1
for i in l1:
    trv.column(i,width=70,anchor='w',stretch=NO)
for i in l1:
    trv.heading(i,text=i)

my_w.mainloop()  # Keep the window open

To create the SQLite database table , use the above code ( given for MySQL ) and use this line if you are using SQLite3

import sqlite3
my_path='D:\\testing\\sqlite\\my_db.db' #Change the path 
my_conn = sqlite3.connect(my_path)

or continue with SQLAlchemy connection and create the SQLite table.

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
#import sqlite3

my_path='D:\\testing\\sqlite\\my_db.db' #Change the path 

my_conn = create_engine('sqlite:///'+ my_path)

Display MySQL records in Treeview Pagination of MySQL records in Treeview
Displaying MySQL records using Entry or Label Dynamic Creation of Header & Columns in Treeview
Delete MySQL record Treeview Treeview insert Treeview parent child node Select -Edit-update MySQL Product table using Treeview

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Monthly report based on user Selection

Create Sample table with data ( MySQL )

Using SQLite3 connection

Subscribe