Database as source for Listbox options

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

Vertical scrollbar of Listbox
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.
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer