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