Listbox options taken from database

Listbox Options as source from MySQL or SQLite database table

We can use student table of MySQL database or from SQLite database. Here are two different connections for different databases.
Common connection engine

from sqlalchemy import create_engine,text

For MySQL database.

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn=my_conn.connect()

For SQLite database

path="sqlite:///C:\\testing\\my_db.db"  # SQLite database 
my_conn = create_engine(path)

We will use my_conn as connection object inside our script.

Sample script to show records from Database

To check the database table, run this script ( MySQL or SQLite )

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

my_conn=my_conn.connect()
query="SELECT name  FROM student LIMIT 0,5"
my_list=my_conn.execute(text(query))
for row in my_list:
    print(row[0])

Output

John Deo
Max Ruin
Arnold
Krish Star
John Mike

Integrate the above code to show the student table names as options of the listbox.

import tkinter as tk
my_w = tk.Tk()

# Database connection from here #
from sqlalchemy import create_engine,text
my_conn = create_engine("mysql+mysqldb://root:pw@localhost/my_db")
#path="sqlite:///C:\\testing\\my_db.db"  # SQLite database 
#my_conn = create_engine(path)
my_conn=my_conn.connect() # connection object 

my_w.geometry("400x250")  # Size of the window 
my_w.title("www.plus2net.com") # Window title
font1=['Arial',15,'normal'] # font type, size, style
lb1=tk.Listbox(my_w,height=6,font=font1,bg='lightgreen',
    selectbackground='lightyellow',selectforeground='black')
lb1.grid(row=1,column=1,padx=30,pady=20)


query="SELECT name  FROM student LIMIT 0,5" #SQL to get data
my_list=my_conn.execute(text(query)) # collect records 

for element in my_list:
    lb1.insert(tk.END,element[0]) # add options 

my_w.mainloop()  # Keep the window open

In the above code we have collected 5 records only, by removing the LIMIT, we can collect all records.

query="SELECT name  FROM student" # query to get data

Vertical Scrollbar

While displaying all the records we can add the vertical scrollbar. We have to add this lines

from tkinter import ttk
vs = ttk.Scrollbar(my_w,orient="vertical", command=lb1.yview)#V Scrollbar
lb1.configure(yscrollcommand=vs.set)  # connect to Treeview
vs.grid(row=1,column=2,sticky='ns')

Full code with vertical scrollbar is here .

import tkinter as tk
my_w = tk.Tk()
from tkinter import ttk

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

my_w.geometry("400x250")  # Size of the window 
my_w.title("www.plus2net.com") # Window title
font1=['Arial',15,'normal'] # font type, size, style
lb1=tk.Listbox(my_w,height=6,font=font1,bg='lightgreen',
       selectbackground='lightyellow',selectforeground='black')
lb1.grid(row=1,column=1,padx=30,pady=20)


query="SELECT name  FROM student "
my_list=my_conn.execute(text(query)) # collect records 

for element in my_list:
    lb1.insert(tk.END,element[0])

#adding vertical scrollbar 
vs = ttk.Scrollbar(my_w,orient="vertical", command=lb1.yview)#V Scrollbar
lb1.configure(yscrollcommand=vs.set)  # connect to Treeview
vs.grid(row=1,column=2,sticky='ns')

my_w.mainloop()  # Keep the window open

Listbox Tkinter Autocomplete using Entry & Listbox.

Python Tkinter Label Python- Tutorials

Subscribe to our YouTube Channel here

Database as source for Listbox options

Sample script to show records from Database

Vertical Scrollbar

Subscribe