Adding options to Tkinter Combobox by using data from MySQL,SQLite table,CSV file and JSon string

Tkinter Combobox options taken from MySQL, SQLite database table, CSV file and Json string

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

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

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

We will first establish connection to MySQL database by using sqlalchemy. You can download and use the sql dump of student table here.

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

my_data=engine.execute(query) # SQLAlchem engine result set

Using this result set of SQLalchem we will create one list.

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

We will connect my_list to our Combobox.

Part 2 : Creating Combobox 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

Set the Combobox and add the option values

cb1 = ttk.Combobox(my_w, values=my_list,width=15)
cb1.grid(row=1,column=1,padx=30,pady=30)

Show the window

my_w.mainloop()

Full code is here ( Update your MySQL id , pw and db_name )

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/db_name")
#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

cb1 = ttk.Combobox(my_w, values=my_list,width=15)
cb1.grid(row=1,column=1,padx=30,pady=30)
cb1.current(2) # default selected option
my_w.mainloop()  # Keep the window open

Using SQLite Database

In above code only the connection part will be changed. Download or create the SQLite database with sample data from here. The connection object my_conn is to be created by using SQLite database, so in above code this the 3^rd line is to be changed.
For MySQL

my_conn = create_engine("mysql+mysqldb://userid:password@localhost/db_name")

For SQLite ( Change the path )

my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")

Using JSON to add options to Combobox

Here is the sample JSON file.
We used append method to add elements to the list.

import tkinter as tk
from tkinter import ttk
import json
my_w = tk.Tk()
my_w.geometry("300x150")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

path="D:\\my_data\\student.json" # sample json file, use your path 
fob=open(path,)
data=json.load(fob)
names=[]
for student in data:
    names.append(student['name'])
	
cb1 = ttk.Combobox(my_w,values=names)
cb1.grid(row=1,column=1,padx=10,pady=20)
cb1.current(2) # default selected option

fob.close() # close the file pointer 
my_w.mainloop()  # Keep the window open

JSON data from remote server

Json data from remote server as options of Combobox in Tkinter window

Here is a sample URL to get JSON data of student table

https://www.plus2net.com/php_tutorial/student.json

Here we are using urlopen to read data from remote server. We will get error if we don't get any data from the server.
urllib.error.URLError: <urlopen error [WinError 10061] No connection could be made because the target machine actively refused it.

import json
from urllib.request import urlopen
url="https://www.plus2net.com/php_tutorial/student.json" #Json file path 
f=urlopen(url)   # open 
data=json.load(f) # data collected 
names=[r['name'] for r in data]  # get a list of names 

import tkinter as tk # 
from tkinter import ttk 
my_w = tk.Tk() # create blank window
my_w.geometry("300x150")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

sel=tk.StringVar() # string variable 

cb1 = ttk.Combobox(my_w, values=names,width=7, textvariable=sel)
cb1.grid(row=1,column=1,padx=10,pady=20)

my_w.mainloop()  # Keep the window open

PHP script to output Json string from MySQL database

Using CSV file

We can use one Comma Separated Values( CSV ) file and use one of the column as options for a combobox.
Download the sample student CSV file from here

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
names=[]
path="D:\\my_data\\student.csv" # sample CSV file, use your path
fob=open(path,)
headings = next(fob) # removing header row
for rec in fob:
  student=rec.split(',')
  print(student)
  names.append(student[1]) # name added

cb1 = ttk.Combobox(my_w,values=names)
cb1.grid(row=1,column=1,padx=10,pady=20)

fob.close() # close the file pointer 
my_w.mainloop()  # Keep the window open

Using Excel file

Combobox options taken from Excel page using openpyxl or Pandas dataframe.

Download sample student Excel file. We have used openpyxl to read data from Excel file.

from openpyxl import load_workbook
wb = load_workbook(filename='E:\data\student.xlsx', read_only=True)
ws = wb['student'] # connecting to sheet

Here is the code to collect name column (r[1]) data including the header from the Worksheet ws of the excel file. Extend the rows by changing the values of max_col.

l1=[r[1] for  r in ws.iter_rows(max_col=5,max_row=5,values_only=True)]

We can also collect column data like this.

l1=[]
for data in ws.iter_rows(min_row=2,max_col=5,max_row=5,values_only=True):
    l1.append(data[1])

We can use Pandas to create a DataFrame, we will use read_excel(). Using tolist() we will create one list from the name column.

import pandas as pd
df = pd.read_excel("F:\data\student.xlsx") # dataframe
l1 = df["name"].values.tolist() # get the list using name column

Full code is here.

import tkinter as tk
from tkinter import ttk
from openpyxl import load_workbook
wb = load_workbook(filename='E:\data\student.xlsx', read_only=True)
ws = wb['student'] # connecting to sheet
l1=[r[1] for  r in ws.iter_rows(max_col=5,max_row=5,values_only=True)]
my_header=l1.pop(0) # remove the column header 
print(l1) # Show column 1,2 - id, name

my_w = tk.Tk()
my_w.geometry("300x150")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title
sel = tk.StringVar()  # string variable
cb1 = ttk.Combobox(
    my_w, values=l1, width=15, textvariable=sel
)  # Create a combobox
cb1.grid(row=1, column=1, padx=20, pady=30)  # Place the combobox
my_w.mainloop()

Combobox options from database table

Adding products to generate Invoice using Tkinter Treeview

We will collect names from our Student table and show the names as options of the Combobox. Once the user select one the option as name of the student then other details of the record like id, class, mark and gender of the selected student will be displayed.

Adding options to Combobox from database table and displaying row details on selection of record

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
from sqlalchemy import create_engine

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
query = "SELECT *  FROM student LIMIT 0,5"
my_data = list(my_conn.execute(query))  # SQLAlchem engine result set
my_dict = {}  # Create an empty dictionary
my_list = []  # Create an empty list
for row in my_data:
    my_dict[[row][0][0]] = row  # id as key
    my_list.append(row[1])  # name as list
# Print the other values for matching Name
def my_upd(*args):  # *args is used to pass any number of arguments
    l1.config(text="")  # Clear the label
    for i, j in my_dict.items():  # Loop through the dictionary
        if j[1] == sel.get():  #
            # print(i, j[0], j[1], j[2], j[3], j[4])
            l1.config(
                text=str(j[0]) + "," + j[1] + "," + j[2] + "," + str(j[3]) + "," + j[4]
            )


sel = tk.StringVar()  # string variable
cb1 = ttk.Combobox(
    my_w, values=my_list, width=15, textvariable=sel
)  # Create a combobox
cb1.grid(row=1, column=1, padx=20, pady=30)  # Place it
l1 = tk.Label(my_w, text="details", bg="yellow")  # Create a label
l1.grid(row=1, column=2)  # Place it
sel.trace("w", my_upd)  # Call the function on change
my_w.mainloop()  # Keep the window open

Combobox Two interlinked Comboboxes
Listbox OptionMenu

Python Tkinter Button Python- Tutorials

Subscribe to our YouTube Channel here

Combobox data source from MySQL,SQLite,CSV & Json