Inside the function my_upd() we will first clear the previous selection if any for the second Combobox cb2. Then run the query to get the name list from the student table by using the selected option of first Combobox ( sel.get() ). The output is stored in the list my_list2 and the same is used as option value of the second Combobox cb2 ( cb2['values']=my_list2 )
def my_upd(*args):
cb2.set('') # remove the previous selected option
query="SELECT name FROM student WHERE class='"+sel.get()+"'"
my_data=my_conn.execute(query) # SQLAlchem engine result
my_list2 = [r for r, in my_data] # create a list
cb2['values']=my_list2
Full code is here
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
#my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")
query="SELECT distinct(class) as class FROM student"
my_data=my_conn.execute(query) # SQLAlchem engine result
my_list = [r for r, in my_data] # create a list
import tkinter as tk
from tkinter import ttk
my_w = tk.Tk()
my_w.geometry("300x150") # Size of the window
my_w.title("www.plus2net.com") # Adding a title
my_list2=[]
def my_upd(*args):
cb2.set('') # remove the previous selected option
query="SELECT name FROM student WHERE class='"+sel.get()+"'"
my_data=my_conn.execute(query) # SQLAlchem engine result
my_list2 = [r for r, in my_data] # create a list
cb2['values']=my_list2
sel=tk.StringVar()
cb1 = ttk.Combobox(my_w, values=my_list,width=15,textvariable=sel)
cb1.grid(row=1,column=1,padx=30,pady=30)
sel.trace('w',my_upd) # track the change event
cb2 = ttk.Combobox(my_w, values=my_list2,width=15)
cb2.grid(row=1,column=2)
my_w.mainloop() # Keep the window open
Linking Three Comboboxes
To the above code we will include one more Combobox (total three) to filter the student names who has got more than or equal to 60 ( Pass ) and those who got less than 60 ( Fail ).
To check the result we will include Mark with the name of the student in our final ( 3rd here ) drop down combobox. We joined student name and mark and return the column using CONCAT().
Here is a sample query using CONCAT().
query="SELECT CONCAT(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"'"
Here the main change is updating the Query ( SQL ) to filter the records based on the selection of option.
Based on the user selection of option ( All, Pass , Fail ) from the Combobox the query is prepared.
if(sel1.get() =='All'):
query="SELECT CONCAT(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"'"
elif(sel1.get()=='Pass'):
query="SELECT concat(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"' AND mark >= 60"
else:
query="SELECT concat(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"' AND mark < 60"
To triggering the function to update the third list we will use trace() method of second drop down Combo box. Here sel1 is the textvariable connected to 2nd combobox.
sel1.trace('w',my_upd) # track the change event of second list
Tkinter Three interlined comboboxes filtering records based on selected options of users.
Full code is here.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
#my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")
query="SELECT distinct(class) as class FROM student"
my_data=my_conn.execute(query) # SQLAlchem engine result
my_list = [r for r, in my_data] # create a list using class
import tkinter as tk
from tkinter import ttk
my_w = tk.Tk()
my_w.geometry("400x150") # Size of the window
my_w.title("www.plus2net.com") # Adding a title
my_list2=[]
def my_upd(*args):
cb2.set('') # remove the previous selected option
if(sel1.get() =='All'):
query="SELECT CONCAT(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"'"
elif(sel1.get()=='Pass'):
query="SELECT concat(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"' AND mark >= 60"
else:
query="SELECT concat(name,'-',mark) FROM student \
WHERE class='"+sel.get()+"' AND mark < 60"
my_data=my_conn.execute(query) # SQLAlchem engine result
my_list2 = [r for r, in my_data] # create a list
cb2['values']=my_list2
sel=tk.StringVar()
cb1 = ttk.Combobox(my_w, values=my_list,width=15,textvariable=sel)
cb1.grid(row=1,column=1,padx=5,pady=30)
my_list1=['All','Pass','Fail'] # Options for the second combobox
sel1=tk.StringVar(value='All') # variable to store the selection
cb1_a=ttk.Combobox(my_w,values=my_list1,textvariable=sel1,width=5)
cb1_a.grid(row=1,column=2,padx=5)
cb2 = ttk.Combobox(my_w, values=my_list2,width=15)
cb2.grid(row=1,column=3)
sel.trace('w',my_upd) # track the change event of first list
sel1.trace('w',my_upd) # track the change event of second list
my_w.mainloop() # Keep the window open