Two linked OptionMenu to display category and subcategory
Two dependent drop down list boxes are used for selection of any item ( subcategory ) based on selection of main category. Here is one demo of two HTML dropdown lists developed using PHP and MySQL.
« DEMO : two dependent drop down list box in PHP MySQL
We will develop similar dependant OptionMenu script by using Tkinter window and connecting to MySQL or SQLite database.
Steps to create the script
Connect to Database (SQLite or MySQL)
Here is the code for connecting to SQLite database, the variable my_conn is used in the script further. We can easily change below two lines of code to get connected to MySQL database.
As we are collecting two columns from the table, so we will use one dictionary in place of a list for our optionmenu ( om1 ). From the record set r_set the dictionary my_dict1 is created at third line here. Our dictionary my_dict1 is having cat_id as keys and category as values.
Creating OptionMenu om1
While creating the option menu we will use the values ( category ) of the dictionary my_dict1 *my_dict1.values() . We used * to unpack the list of values of dictinary and return as items.
query="SELECT cat_id,category FROM category" # Query
r_set=my_conn.execute(query);
my_dict1=dict((x,y) for x,y in r_set) # create dictionary
options1 = tk.StringVar(my_w)
options1.set(my_dict1[1]) # default value
om1 =tk.OptionMenu(my_w, options1, *my_dict1.values())
om1.grid(row=2,column=5)
Add Second OptionMenu om2
To display only the subcategory column the second OptionMenu om2 created using a list. We are using a list here as we are only displaying subcategory value and not carrying any other data. If required we can use dictionary here if two column data is to be used.
We are using options1.trace('w',my_show) to trigger the function my_show(*args) whenever the value of StringVaroptions1 changes. So whenever we change the selection of om1, the function my_show(*args) is executed.
def my_show(*args):
for i,j in my_dict1.items():
if j==options1.get():
cat_id=i
--------------
--------------
options1.trace('w',my_show)
In above code, inside the function my_show(*args), we are first reading the vlaue of user selection of OptionMenu om1. This value we will get by using options.get(). Using this value of selected element we will collect the corresponding cat_id. So our variable cat_id will store the cat_id value of user selected category of the OptionMenu om1.
Remove all options of om2
We need to fill om2 with our new subcategory values of matching cat_id, so all previously hold values are to be removed.
options2.set('') # remove default selection only
om2['menu'].delete(0,'end') # remove full list
Add matching options to om2
Using our cat_id we will collect all matching subcategory column values from subcategory table. The returned record set from database r_set2 is used to create a listmy_list2. Here we are using a list ( not a dictionary ) as a single column data subcategory is used.
query="SELECT subcategory FROM subcategory WHERE cat_id='"+str(cat_id)+"'"
r_set2=my_conn.execute(query);
my_list2 = [r for r, in r_set2] # create a list
for opt in my_list2:
om2['menu'].add_command(label=opt, command=tk._setit(options2, opt))
options2.set(my_list2[0]) # default value set
Here is the full code.
# www.plus2net.com
# download updated script at https://www.plus2net.com/python/tkinter-OptionMenu-proj4.php
import sqlite3
my_conn = sqlite3.connect('my_db.db')
###### end of connection ####
import tkinter as tk
my_w = tk.Tk()
my_w.geometry("200x200") # Size of the window
my_w.title("www.plus2net.com") # Adding a title
query="SELECT cat_id,category FROM category"
r_set=my_conn.execute(query);
my_dict1=dict((x,y) for x,y in r_set)
#print(my_dict1)
options1 = tk.StringVar(my_w)
#options.set(my_dict1[0]) # default value
om1 =tk.OptionMenu(my_w, options1, *my_dict1.values())
om1.grid(row=2,column=5)
options2 = tk.StringVar(my_w)
my_list2=['Subcategory list']
options2.set(my_list2[0])
om2 =tk.OptionMenu(my_w, options2, *my_list2)
om2.grid(row=2,column=6)
def my_show(*args):
for i,j in my_dict1.items():
if j==options1.get():
cat_id=i
options2.set('') # remove default selection only, not the full list
om2['menu'].delete(0,'end') # remove full list
query="SELECT subcategory FROM subcategory WHERE cat_id='"+str(cat_id)+"'"
r_set2=my_conn.execute(query);
my_list2 = [r for r, in r_set2] # create a list
#print(my_list2)
for opt in my_list2:
om2['menu'].add_command(label=opt, command=tk._setit(options2, opt))
options2.set(my_list2[0]) # default value set
options1.trace('w',my_show)
my_w.mainloop()