Python Tkinter Treeview displaying mysql

Question

I have a treeview in python tkinter and It displays in the screen. The only problem is that how can I make the first column to output 'available' if quantity is more than zero or 'out of stock' if the quantity of the item is zero and the query from database should start in the second column as 1st column is the status. Thank you

class Inventory:
def __init__(self, top=None):
    top.geometry("1366x768")
    top.resizable(0, 0)
    top.title("Inventory")

    self.userReg_Form = Label(inv)
    self.userReg_Form.place(relx=0, rely=0, width=1366, height=768)
    self.userReg_FormImage = Image.open("./images/Inventory_availability.png")
    self.userReg_FormImageResized = self.userReg_FormImage.resize((1366, 768), Image.ANTIALIAS)
    self.userReg_FormImage = ImageTk.PhotoImage(self.userReg_FormImageResized)
    self.userReg_Form.configure(image=self.userReg_FormImage)

    self.scrollbarx = Scrollbar(inv, orient=HORIZONTAL)
    self.scrollbary = Scrollbar(inv, orient=VERTICAL)
    self.tree = ttk.Treeview(inv)
    self.tree.place(relx=0.2, rely=0.2, width=880, height=550)
    self.tree.configure(
        yscrollcommand=self.scrollbary.set, xscrollcommand=self.scrollbarx.set
    )
    self.tree.configure(selectmode="extended")

    self.tree.bind("<<TreeviewSelect>>", self.on_tree_select)

    self.scrollbary.configure(command=self.tree.yview)
    self.scrollbarx.configure(command=self.tree.xview)

    self.scrollbary.place(relx=0.954, rely=0.203, width=22, height=548)
    self.scrollbarx.place(relx=0.307, rely=0.924, width=884, height=22)

    self.tree.configure(
        columns=(
            "Status",
            "Product ID",
            "Product_Name",
            "Quantity",
            "Supplier",
            "Price",
            "Purchase Date",
            "Expiration Date",
        )
    )

    self.tree.heading("Status", text="Status", anchor=W)
    self.tree.heading("Product ID", text="Product ID", anchor=W)
    self.tree.heading("Product_Name", text="Product_Name", anchor=W)
    self.tree.heading("Quantity", text="Quantity", anchor=W)
    self.tree.heading("Supplier", text="Supplier", anchor=W)
    self.tree.heading("Price", text="Price", anchor=W)
    self.tree.heading("Purchase Date", text="Purchase Date", anchor=W)
    self.tree.heading("Expiration Date", text="Expiration Date", anchor=W)


    self.tree.column("#0", stretch=NO, minwidth=0, width=0)
    self.tree.column("#1", stretch=NO, minwidth=0, width=80)
    self.tree.column("#2", stretch=NO, minwidth=0, width=260)
    self.tree.column("#3", stretch=NO, minwidth=0, width=100)
    self.tree.column("#4", stretch=NO, minwidth=0, width=120)
    self.tree.column("#5", stretch=NO, minwidth=0, width=80)
    self.tree.column("#6", stretch=NO, minwidth=0, width=80)
    self.tree.column("#7", stretch=NO, minwidth=0, width=80)
    self.tree.column("#8", stretch=NO, minwidth=0, width=100)

    self.DisplayData()

def DisplayData(self):
    cur.execute("SELECT * FROM inventory")
    fetch = cur.fetchall()
    for data in fetch:
        self.tree.insert("", "end", values=(data))

sel = []
def on_tree_select(self, Event):
    self.sel.clear()
    for i in self.tree.selection():
        if i not in self.sel:
            self.sel.append(i)

Below is the mySQL Create table:

my_cursor.execute("CREATE TABLE IF NOT EXISTS inventory("
                "prod_id VARCHAR(10) NOT NULL PRIMARY KEY,"
                "prod_name VARCHAR(255) NOT NULL,"
                "quantity VARCHAR(255) NOT NULL,"
                "supplier VARCHAR(255) NOT NULL,"
                "price DECIMAL(10,2) NOT NULL,"
                "purchase_date DATE NOT NULL,"
                "expiration_date DATE NOT NULL)")

acw1668 · Accepted Answer · 2021-07-04 03:21:49Z

0

There are two ways to do it:

include status in the SQL statement:

    def DisplayData(self):
        cur.execute("SELECT IF(Quantity > 0, 'available', 'out of stock'), inventory.* FROM inventory")
        fetch = cur.fetchall()
        for data in fetch:
            self.tree.insert("", "end", values=data)

prepend the status before each result record:

    def DisplayData(self):
        cur.execute("SELECT * FROM inventory")
        fetch = cur.fetchall()
        for data in fetch:
            self.tree.insert("", "end", values=("available" if int(data[2]) > 0 else "out of stock", *data))

answered Jul 4, 2021 at 3:21

acw1668

48k5 gold badges30 silver badges39 bronze badges

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

1 Comment

Nymph Over a year ago

can I add an icon instead of displaying Available and out of stock in text format? I am new in tkinter treeview. Thank you

Collectives™ on Stack Overflow

Python Tkinter Treeview displaying mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related