基于sqlite DB值的Python相关组合框

2022-01-02 00:00:00 python tkinter sqlite combobox

我是 Python 新手，我正在尝试使用 GUI 制作预算跟踪应用程序.为此，我输入了我在该计划中花费的每一笔金额.这些数量存储在一个 sqlite 数据库中，其中每个数量都有一个通用类别，每个类别都有多个子类别.这样就可以根据与输入的每个金额相关联的类别和子类别生成统计数据.

I am new to python and I am trying to make a budget tracking application, with GUI. To do this I enter every amount I spend in the programme. These amounts are stocked in an sqlite DB, where every amount has a general category and every category has multiple subcategories. This will make it possible to generate statistics, based on the category and subcategory linked to every amount entered.

我可以选择的所有可能的类别和子类别都存储在一个 sqlite 数据库中，并且可以通过组合框在 GUI 中选择这些.我的目标是，当我在类别组合框中选择某个类别时，子类别组合框中仅显示链接到该类别的子类别.

All the possible categories and subcategories that I can select are stored in an sqlite DB and these are can be selected in the GUI via comboboxes. My objective is that when I select a certain category in the category combobox, only the subcategories linked to this category are displayed in the subcategory combobox.

我遇到的问题是我无法引用类别组合框中选择的值来过滤将在子类别组合框中可见的值.

The problem I am encountering is that I cannot reference the value selected in the category combobox to filter the values that will be visible in the subcategory combobox.

我的代码导致问题的部分如下:

The part of my code that causes problems is the following: 

query = c.execute('SELECT Name_Subcategory FROM Subcategory WHERE ID_Category == Categorybox.get()')

当我通过可以在我的 sqlite 数据库中找到的 ID_Category 更改Categorybox.get()"时(例如 2)，代码有效并且子类别组合框中可能的子类别被过滤以仅显示具有 ID_Category 值的子类别2".

When I change "Categorybox.get()" by an ID_Category which can be found in my sqlite DB (for example 2) the code works and the possible subcategories in the subcategory comboboxes are filtered to only show those subcategories with ID_Category value "2".

有人知道解决这个问题的方法吗?预先感谢您提供的任何建议.

Does anyone know a way to resolve this issue? Thanks in advance for any advice you can give.

概览 Gui 和 DB 表

Overview Gui and DB tables

#connect database
conn = sqlite3.connect("Budget.db")

#create cursor
c = conn.cursor()


# Combobox_Category
def Cat_opt():
    conn = sqlite3.connect('Budget.db')
    query = c.execute('SELECT Name_Category FROM Category')
    data = []
    for row in c.fetchall():
        data.append(row[0])
    return data
    conn.close(row=1, column=0)

Category_box = Combobox(width=27)
Category_box['value'] = Cat_opt()

# Combobox_Subcategory
def Subcat_opt():
    conn = sqlite3.connect('Budget.db')
    query = c.execute('SELECT Name_Subcategory FROM Subcategory WHERE ID_Category == Categorybox.get()')
    data = []
    for row in c.fetchall():
        data.append(row[0])
    return data
    conn.close(row=1, column=0)

Subcategory_box = Combobox(width=27)
Subcategory_box['value'] = Subcat_opt()

推荐答案

问题:根据第一个Combobox中选择的选项，从sqlite3表中查询values第二个Combobox.

Question: Based on the selected option in the first Combobox, query from a sqlite3 table the values of the second Combobox.

<小时>

核心要点:

  1. 给事件绑定一个回调'<<>' 

self.bind('<<ComboboxSelected>>', self.on_selected)

  • 查询选中项的id，并传给SubCategory.configure(query=<id>) 

    def on_selected(self, event):
    _query = 'SELECT id FROM Category WHERE name == "{}"'.format(self.get())
    self.subcategory.configure(query=self.db.query(_query)[0])

  • 根据id查询name并配置SubCategory(Combobox) values>.

    def configure(self, cnf=None, **kw):
    ...
        _query = 'SELECT name FROM SubCategory WHERE ID_Category == {}'.format(_query)
        super().configure(values=self.db.query(_query))

    • <小时>

    导入和数据

    import tkinter as tk
import tkinter.ttk as ttk
import sqlite3


class DB:
    conn = None

    def __init__(self):
        if not DB.conn:
            DB.conn = sqlite3.connect(':memory:')

            print('DB.__init__()'.format())
            cur = DB.conn.cursor()
            cur.execute("CREATE TABLE IF NOT EXISTS Category(name TEXT, id INTEGER)")

            for _id, name in enumerate(('Revenues', 'Assets', 'Living expenses'), 1):
                cur.execute("INSERT INTO Category(id, name) VALUES (?,?)", (_id, name))

            cur.execute("CREATE TABLE IF NOT EXISTS SubCategory(name TEXT, id INTEGER, ID_Category INTEGER)")
            for _id, name in enumerate(('Salary', 'Saving account', 'Interest costs'), 1):
                cur.execute("INSERT INTO SubCategory(id, name, ID_Category) VALUES (?,?,?)", (_id, name, _id))

    def query(self, query):
        cur = DB.conn.cursor()
        return [record[0] for record in cur.execute(query)]

    自定义组合框类别

    class Category(ttk.Combobox):
    def __init__(self, parent, **kwargs):
        self.subcategory = kwargs.pop('subcategory', None)
        self.db = kwargs.pop('db', None)

        # defaults
        kwargs['width'] = kwargs.get('width', 27)
        super().__init__(parent, values=(), **kwargs)

        self.configure(values=self.db.query('SELECT name FROM Category'))
        self.bind('<<ComboboxSelected>>', self.on_selected)

    def on_selected(self, event):
        _query = 'SELECT id FROM Category WHERE name == "{}"'.format(self.get())
        self.subcategory.configure(query=self.db.query(_query)[0])

    自定义组合框子类别

    class SubCategory(ttk.Combobox):
    def __init__(self, parent, **kwargs):
        self.db = kwargs.pop('db', None)

        # defaults
        kwargs['width'] = kwargs.get('width', 27)
        super().__init__(parent, values=(), **kwargs)

    def configure(self, cnf=None, **kw):
        _query = kw.pop('query', None)
        if _query is not None:
            _query = 'SELECT name FROM SubCategory WHERE ID_Category == {}'.format(_query)
            super().configure(values=self.db.query(_query))
            self.event_generate('<Button-1>')
        else:
            super().configure(cnf, **kw)

    用法

    class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.geometry('200x200')

        self.db = DB()

        subcategory = SubCategory(self, db=self.db)
        category = Category(self, subcategory=subcategory, db=self.db)

        category.pack()
        subcategory.pack()

        category.event_generate('<Button-1>')


if __name__ == "__main__":
    App().mainloop()

    使用 Python 测试:3.5 - 'TclVersion':8.6 'TkVersion':8.6

    相关文章