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

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

Part I	Part II
Create a list by using data from MySQL student table	Create an Spinbox 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 Spinbox.

Part 2 : Creating Spinbox 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 Spinbox and add the option values

font1=('Times',24,'bold')
cb1 = tk.Spinbox(my_w, values=my_list,width=10,font=font1)
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
my_w = tk.Tk()
my_w.geometry("300x150")  # Size of the window 
my_w.title("www.plus2net.com")  # Adding a title

font1=('Times',24,'bold')
cb1 = tk.Spinbox(my_w, values=my_list,width=10,font=font1)
cb1.grid(row=1,column=1,padx=30,pady=30)

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 Spinbox

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

import tkinter as tk
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'])
font1=('Times',24,'bold')		
cb1 = tk.Spinbox(my_w,values=names,font=font1,width=10)
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

Using CSV file

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

import tkinter as tk
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 = tk.Spinbox(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

Spinbox
Listbox OptionMenu

Python Tkinter Button Python- Tutorials

Subscribe to our YouTube Channel here

Python tkinter Spinbox

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

Part 2 : Creating Spinbox using the elements of the list

Using SQLite Database

Using JSON to add options to Spinbox

Using CSV file

Subscribe