treeview python sqlite3的tkinter搜索框

Question

所以我想做一个简单的库存系统CRUD程序。到目前为止，我已经能够添加新项目，修改和删除它们。我还缺少最后一件事，那就是能够从树视图中搜索，该树视图显示了sqlite3数据库中的所有项目。我希望有一个搜索框，在那里我可以只键入项目的名称，程序将打印和选择搜索的项目，然后我可以做一些事情，如修改或删除所选项目

到目前为止，我的代码是：

from tkinter import Tk, Button, PhotoImage, Label, LabelFrame, W, E, N, S, Entry, END, StringVar, Scrollbar, Toplevel
from tkinter import ttk
import sqlite3



class Inventory:
    db_filename = 'stock.db'
    def __init__(self, root):
        self.root = root
        self.create_gui()
        ttk.style = ttk.Style()
        ttk.style.configure('Treeview', font=('helvetica',10))
        ttk.style.configure('Treeview.Heading', font=('helvetica', 12, 'bold'))

    def execute_db_query(self, query, parameters=()):
        with sqlite3.connect(self.db_filename) as conn:
            print(conn)
            print('You have successfully connected to the Database')
            cursor = conn.cursor()
            query_result = cursor.execute(query, parameters)
            conn.commit()
        return query_result

    def create_gui(self):
        self.create_left_icon()
        self.create_label_frame()
        self.create_message_area()
        self.create_tree_view()
        self.create_scrollbar()
        self.create_bottom_buttons()
        self.view_items()

    def create_left_icon(self):
        photo = PhotoImage(file='./icons/logo.png')
        label = Label(image=photo)
        label.image = photo
        label.grid(row=0, column=0)

    def create_label_frame(self):
        labelframe = LabelFrame(self.root, text='Input New Items', bg='sky blue', font='helvetica 10')
        labelframe.grid(row=0, column=1, padx=8, pady=8, sticky='ew')
        Label(labelframe, text='Name of Item:', bg='sky blue', fg='black').grid(row=1, column=1, sticky=W, pady=2, padx=15)
        self.typefield = Entry(labelframe)
        self.typefield.grid(row=1, column=2, sticky=W, padx=5, pady=2)
        Label(labelframe, text='Stock Card ID:', bg='sky blue', fg='black').grid(row=2, column=1, sticky=W, pady=2, padx=15)
        self.cardfield = Entry(labelframe)
        self.cardfield.grid(row=2, column=2, sticky=W, padx=5, pady=2)
        Label(labelframe, text='Count:', bg='sky blue', fg='black').grid(row=3, column=1, sticky=W, pady=2, padx=15)
        self.countfield = Entry(labelframe)
        self.countfield.grid(row=3, column=2, sticky=W, padx=5, pady=2)
        Button(labelframe, text='Add', command=self.on_add_item_button_clicked, fg='black').grid(row=4, column=2, sticky=E, padx=5, pady=5)

    def create_message_area(self):
        self.message = Label(text='', fg='red')
        self.message.grid(row=3, column=1, sticky=W)

    def create_tree_view(self):
        self.tree = ttk.Treeview(height=10, columns=('card', 'stock'), style='Treeview')
        self.tree.grid(row=6, column=0, columnspan=3)
        self.tree.heading('#0', text='Name of Item', anchor=W)
        self.tree.heading('card', text='Stock Card ID', anchor=W)
        self.tree.heading('stock', text='Count', anchor=W)

    def create_scrollbar(self):
        self.scrollbar = Scrollbar(orient='vertical', command=self.tree.yview)
        self.scrollbar.grid(row=6, column=3, rowspan=10, sticky='sn')

    def create_bottom_buttons(self):
        Button(text='Delete', command=self.on_delete_selected_button_clicked).grid(row=8, column=0, sticky=W, pady=10, padx=20)
        Button(text='Edit Stock', command=self.on_modify_selected_button_clicked).grid(row=8, column=1, sticky=W)

    def on_add_item_button_clicked(self):
        self.add_new_item()

    def on_delete_selected_button_clicked(self):
        self.message['text'] = ''
        try:
            self.tree.item(self.tree.selection())['values'][0]
        except IndexError as e:
            self.message['text'] = 'Select at least one item to be deleted'
            return
        self.delete_items()

    def on_modify_selected_button_clicked(self):
        self.message['text'] = ''
        try:
            self.tree.item(self.tree.selection())['values'][0]
        except:
            self.message['text'] = 'Select at least one item to be modified'
            return
        self.open_modify_window()


    def add_new_item(self):
        if self.new_items_validated():
            query = 'INSERT INTO items_list VALUES(NULL, ?, ?, ?)'
            parameters = (self.typefield.get(), self.cardfield.get(), self.countfield.get())
            self.execute_db_query(query, parameters)
            self.message['text'] = '{} has been added'.format(self.typefield.get())
            self.typefield.delete(0, END)
            self.cardfield.delete(0, END)
            self.countfield.delete(0, END)
            self.view_items()

        else:
            self.message['text'] = 'All entry field must be filled'
            self.view_items()

    def new_items_validated(self):
        return len(self.typefield.get()) !=0 and len(self.cardfield.get()) != 0 and len(self.countfield.get()) != 0

    def view_items(self):
        items = self.tree.get_children()
        for item in items:
            self.tree.delete(item)
        query = 'SELECT * FROM items_list ORDER BY TipeBarang'
        item_entries = self.execute_db_query(query)
        for row in item_entries:
            self.tree.insert('', 0, text=row[1], values=(row[2], row[3]))

    def delete_items(self):
        self.message['text']=''
        name = self.tree.item(self.tree.selection())['text']
        query = 'DELETE FROM items_list WHERE TipeBarang = ?'
        self.execute_db_query(query, (name,))
        self.message['text'] = '{} has been deleted'.format(name)
        self.view_items()


    def open_modify_window(self):
        name = self.tree.item(self.tree.selection())['text']
        old_stock = self.tree.item(self.tree.selection())['values'][1]
        self.transient = Toplevel()
        self.transient.title('Update Stock')
        Label(self.transient, text='Name: ').grid(row=0, column=1)
        Entry(self.transient, textvariable=StringVar(
            self.transient, value=name), state='readonly').grid(row=0, column=2)
        Label(self.transient, text='Old Stock Count: ').grid(row=1, column=1)
        Entry(self.transient, textvariable=StringVar(
            self.transient, value=old_stock), state='readonly').grid(row=1, column=2)

        Label(self.transient, text='New Stock Count: ').grid(row=2, column=1)
        new_stock_number_entry_widget = Entry(self.transient)
        new_stock_number_entry_widget.grid(row=2, column=2)

        Button(self.transient, text='Update Item', command=lambda: self.update_stock(
            new_stock_number_entry_widget.get(), old_stock, name)).grid(row=3, column=2, sticky=E)

        self.transient.mainloop()

    def update_stock(self, newstock, old_stock, name):
        query = 'UPDATE items_list SET JumlahStok=? WHERE JumlahStok=? AND TipeBarang=?'
        parameters = (newstock, old_stock, name)
        self.execute_db_query(query, parameters)
        self.transient.destroy()
        self.message['text'] = '{} Stock Count has been updated'.format(name)
        self.view_items()



if __name__ == "__main__":
    root = Tk()
    root.title("Inventory System")
    root.resizable(width=False, height=False)
    application = Inventory(root)
    root.mainloop()

下面，我还附上了我迄今为止制作的程序的截图。非常感谢你的帮助

程序屏幕截图：https://i.stack.imgur.com/1kJch.png

Answer 1

这里有几种不同的方法可以实现搜索功能。您可以添加一个创建Toplevel window的搜索按钮，并根据需要向其添加小部件。或者更简单地说，您可以使用simpledialog从用户处获取文本输入，如下所示：

queryString = simpledialog.askstring("Search", "Enter item name to search:")

然后将生成的查询字符串传递给数据库函数，对其进行清理以防止SQL注入，然后SELECT * FROM table WHERE Item = 'queryString'

然后使用此查询返回的数据填充树视图

Answer 2

def Search():
    if SEARCH.get() != "":
        tree.delete(*tree.get_children())
        conn = sqlite3.connect("db_member.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM `member` WHERE `firstname` LIKE ? OR `lastname` LIKE ?", ('%'+str(SEARCH.get())+'%', '%'+str(SEARCH.get())+'%'))
        fetch = cursor.fetchall()
        for data in fetch:
            tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()

这就是使用Sqlite3和Tkinter从Python中的特定列进行搜索的方法，您只需更改两件事：

• “成员”的名称，这是您创建的表的名称
• firstname和lastname是列的名称，SEARCH.get（）是输入框中的变量