Monthly report based on user Selection


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
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.

Using SQLite3 connection

Report using SQLite database
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
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-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer