Python Tkinter Treeview displaying mysql

Question

我在python tkinter中有一个树视图，它显示在屏幕上。唯一的问题是，如果数量超过零，我如何使第一列输出"可用"，或者如果项目的数量为零，则如何使第一栏输出"缺货"，并且来自数据库的查询应该从第二栏开始，因为第一栏是状态。谢谢

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)

下面是mySQL创建表：

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

Answer 1

有两种方法：

1. 在SQL语句中包含状态：

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)

2. 在每个结果记录之前预先准备状态：

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