基于sqlite DB值的Python依赖组合框 [英] Python dependant combobox based on sqlite DB values

问题描述

我是python的新手，我正在尝试使用GUI创建预算跟踪应用程序。为此，我输入了我在计划中花费的每笔金额。这些金额存储在sqlite DB中，其中每个金额都有一个常规类别，每个类别都有多个子类别。这样便可以根据链接到每个输入金额的类别和子类别来生成统计信息。

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 DB，这些可以通过组合框在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数据库（例如2）中可以找到的ID_Category更改 Categorybox.get（）时，代码起作用，并且子类别组合框中的可能子类别被筛选为仅显示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表

#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()

推荐答案

问题 ：根据第一个组合框中的选定选项，从 sqlite3 表中查询值第二个组合框中的。

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

---

核心点：

1. 绑定对事件'<<<<" ComboboxSelected>>''的回调p>
   
   

1. Bind a callback to the event '<<ComboboxSelected>>'

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 并配置 SubCategory（Combobox） 值。

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

---

进口和数据

---

Imports and Data

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

自定义组合框类别

Customized Combobox Category

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

< h2>自定义组合框子类别

Customized Combobox SubCategory

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)

用法

Usage

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

Tested with Python: 3.5 - 'TclVersion': 8.6 'TkVersion': 8.6

这篇关于基于sqlite DB值的Python依赖组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！