Populating OptionMenu by using Unique data from SQLite database table.

OptionMenu with option values from SQLite database

In our student table we will collect unique student classes and use them as option of OptionaMenu.
The script will have two parts.

Part I	Part II
Create a list by using data from SQLite student table	Create an OptionMenu by using the elements of the list to display the options

Tkinter OptionMenu options from MySQL or SQLite Database tables for user selection

Part 1 : Creating a list using data from SQLite student table.

We will first establish connection to SQLite database .

The query.
To get the unique class names from the student table we will use DISTINCT sql. Here is the Query we used.

query="SELECT distinct(class) as class FROM student"

Getting records
We will get Sqlite result set with data by using the above query. We will convert this result set to a list as we are planning to use as options of OptionMenu.

query="SELECT distinct(class) as class FROM student"
r_set=my_conn.execute(query);

Using this reselt set (r_set) of SQLite we will create one list.

my_list = [r for r, in r_set] # create a  list

We will connect my_list to our OptionaMenu.

Part 2 : Creating OptionMenu using the elements of the list

We will create the window first

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

We will create a StringVar() and set the default value for the optionMenu.

options = tk.StringVar(my_w)
options.set(my_list[0]) # default value

Set the optionMenu and add the option values

om1 =tk.OptionMenu(my_w, options, *my_list)
om1.grid(row=2,column=5)

Show the window

my_w.mainloop()

Full code is here

import sqlite3
my_conn = sqlite3.connect('my_db.db')
###### end of connection ####

query="SELECT distinct(class) as class FROM student"
r_set=my_conn.execute(query);
my_list = [r for r, in r_set] # create a  list 

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

options = tk.StringVar(my_w)
options.set(my_list[0]) # default value

om1 =tk.OptionMenu(my_w, options, *my_list)
om1.grid(row=2,column=5)

my_w.mainloop()

Using SQLALchemy database connection

We can easily change to MySQL database by un-commenting the 2nd line and commenting the 3rd and 4th line.

from sqlalchemy import create_engine,text
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db") # MySQL
path="sqlite:///C:\\testing\\my_db.db"  # SQLite database , update path
my_conn = create_engine(path)
my_conn=my_conn.connect()

query="SELECT DISTINCT(class) as class FROM student"
my_data=my_conn.execute(text(query))
my_list=[r for r , in my_data] # create a list of 
#for row in my_data:
#    print(row)
#my_list=['One','Two','Three','Four']
import tkinter as tk

my_w = tk.Tk()
my_w.geometry("400x300")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

options = tk.StringVar(my_w)
options.set(my_list[1]) # default value

om1 =tk.OptionMenu(my_w, options, *my_list)
om1.grid(row=2,column=5,padx=100,pady=10)

menu=my_w.nametowidget(om1.menuname)
om1.config(font=['Arial',26]) # Set the menu font
menu.config(font=['Arial',16]) # Set option font 
my_w.mainloop()

Conclusion

We have collected data from SQLite table and created one list using the data. In part 2 we created the OptionMenu and connected the data list as option values.

View & Download tkinter-optionmenu-proj3-sqlite ipynb file (.html)

Using this concepts we can further develop applications where OptionMenu is used.

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

OptionMenu option values from SQLite table

Part 1 : Creating a list using data from SQLite student table.

Part 2 : Creating OptionMenu using the elements of the list

Using SQLALchemy database connection

Conclusion

Subscribe