Python Dependant Combobox Based On Sqlite Db Values

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

Solution 1:

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

   defon_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.

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


classDB:
    conn = Nonedef__init__(self):
        ifnot 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 inenumerate(('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 inenumerate(('Salary', 'Saving account', 'Interest costs'), 1):
                cur.execute("INSERT INTO SubCategory(id, name, ID_Category) VALUES (?,?,?)", (_id, name, _id))

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

Customized Combobox Category

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

    defon_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

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

    defconfigure(self, cnf=None, **kw):
        _query = kw.pop('query', None)
        if _query isnotNone:
            _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