Python Tkinter : Search And Filter Data In Treeview With Select Options Using Python Tkinter And MySQL

Python Tkinter How To Filter Data In Html Table Using Select Options In Python Tkinter And MySQL Database

Python Tkinter How To Filter Data In Html Table Using Select Options In Python Tkinter And Python Tkinter : Search And Filter Data In Treeview With Select Options Using Python Tkinter And MySQL

in this Python Tkinter tutorial we will see how to search data from mysql database table in python and display the results in a tkinter treeview using a combobox (select option).

Source Code:

import tkinter as tk
from tkinter import *
from tkinter import ttk
import mysql.connector

root = Tk()
root.title("Search, Filter And Display Data")

connection = mysql.connector.connect(host='localhost', user='root', password='',
                                     port='3306', database='test_py')
c = connection.cursor()

bkg = "grey"

frame = tk.Frame(root, bg=bkg)

options = ['A','B','C','D','E','F','G','H']
selected = StringVar(frame)
selected.set(options[0])

combobox = ttk.Combobox(frame, textvariable=selected, values=options,
                        font=('verdana',14))

trv = ttk.Treeview(frame, columns=(1,2,3,4,5), height=15, show="headings")

trv.column(1, anchor=CENTER, stretch=NO, width=100)
trv.column(2, anchor=CENTER, stretch=NO, width=100)
trv.column(3, anchor=CENTER, stretch=NO, width=100)
trv.column(4, anchor=CENTER, stretch=NO, width=100)
trv.column(5, anchor=CENTER, stretch=NO, width=100)

trv.heading(1, text="ID")
trv.heading(2, text="First Name")
trv.heading(3, text="Last Name")
trv.heading(4, text="Email")
trv.heading(5, text="Age")

def displayData():
    c.execute('SELECT * FROM `users_2`')
    users = c.fetchall()

    for user in users:
        trv.insert('','end', value=(user[0], user[1], user[2], user[3], user[4]))

def search(eventObject):
    # clear treeview
    for item in trv.get_children():
        trv.delete(item)

    val = combobox.get()
    c.execute("""SELECT * FROM `users_2` WHERE `firstname` LIKE %s or 
               `lastname` like %s""",("%"+val+"%","%"+val+"%"))
    users = c.fetchall()

    for row in users:
        trv.insert('',END, values=row)

combobox.bind("<>", search)

combobox.grid(row=0, column=0)
trv.grid(row=1, column=0)

frame.grid(row=0, column=0)

displayData()

root.mainloop()