1

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.

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

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.

Overview Gui and DB tables

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()
Improve this question
3
  • @stovfl Unfortunately this does not answer my question. Because I need to get both the values for the category and subcategory comboboxes from the sqlite DB it is a very different situation as opposed to the one described in the post above. Crucially I find it impossible to reference the value selected in the category combobox in my filter for the query to fetch the possible options in the subcategory combobox. Commented Mar 21, 2020 at 11:16
  • @stovfl I can populate both the category and subcategory comboboxes with the values from the sqlite DB. However, I cannot filter the values in the subcategory combobox based on the value selected in the category combobox. I have adapted my original question to better reflect this. Commented Mar 21, 2020 at 12:42
  • @stovfl thank you for the advice, I could not find the info U need in the link, but I am trying to find more information regarding this issue in event-driven programming information. Commented Mar 24, 2020 at 20:45

1 Answer 1

Reset to default
2

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

Core Points:

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

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

  2. Query the id of the selected item and pass it to 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])

  3. Query the name's based on the id and configure the 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))

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

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

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you very much for your answer. At first it did not work for me but I kept adapting it and after a few minor tweaks (and installing a different python version) it works!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.