Populating 2nd OptionMenu with matching options on selection of first OptionMenu using data from SQLite or MySQL database table.

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.
First OptionMenu for selection of Category

First OptionMenu for selection of Category

Second OptionMenu for selection of Subcategory

Steps to create the script
Steps for two dependant OptionMenus SQLite database

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.

The script is same so it will work without any issue with both SQLite and MySQL database. Here is the connection string with MySQL database. Use the SQL dump from here to create tables

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

Using SQLite

import sqlite3
my_conn = sqlite3.connect('my_db.db')

Download or Create my_db.db SQLite Database

Create Tkinter window

This is our common code to display the Tkinter window. In this window we will add two OptionMenus

import tkinter as tk
my_w = tk.Tk()
my_w.geometry("250x200")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

Populate the first OptionMenu om1

Read how to fill optionmenu with data from MySQL table here. Here also we will use query to collect cat_id and category columns from category table. Our record set r_set holds data of two columns

Use of dictionary

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.

options2 = tk.StringVar(my_w)
my_list2=['Subcategory list']
options2.set(my_list2[0]) # default value 
om2 =tk.OptionMenu(my_w, options2, *my_list2)
om2.grid(row=2,column=6)

On Selection or Change of Options of om1

We are using options1.trace('w',my_show) to trigger the function my_show(*args) whenever the value of StringVar options1 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 list my_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()

View & Download tkinter-optionmenu-proj4 ipynb file (.html format)

User can remove all or add all the options to an OptionMenu. User can select any option and remove the same by Clicking a button.

Remove all or remove Selective options of an OptionMenu.
OptionMenu
Create one OptionMenu with option values taken from MySQL database table

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here