如何从Sqlite3获取更新的行以显示在Tkinter中？

Question

我正在为我和我朋友的小店创建一个程序。我正在使用Python、Sqlite3和Tkinter。我可以通过Tkinter显示Sqlite3中的记录，但当我插入新记录/数据时，它会插入Sqlite3，但Tkinter不会显示插入的新记录。这是我的密码；我希望这是可以理解的

import tkinter
from tkinter import*
from tkinter import ttk, LabelFrame
import tkinter.messagebox
import sqlite3


conn = sqlite3.connect('new1.db')


def update(show):
    for i in show:
        trv.insert('', 'end', values=i)


def submitprod():

   conn = sqlite3.connect('new1.db')

   c = conn.cursor()
   c.execute("INSERT INTO products VALUES (:pdesc, :qty, :prce, :uprce)",
             {
                  'pdesc': pdesc.get(),
                  'qty': qty.get(),
                  'prce': prce.get(),
                  'uprce': uprce.get()


             })

   conn.commit()

#reset
   pdesc.delete(0, END)
   qty.delete(0, END)
   prce.delete(0, END)
   uprce.delete(0, END)


c = conn.cursor ()
top = tkinter.Tk()

box1 = LabelFrame(top, text="Product Entry")
box1.pack (fill="both", expand="yes", padx=20, pady=10)
box2 = LabelFrame(top, text="Products")
box2.pack (fill="both", expand="yes", padx=20, pady=10)

#product labels and entry

pdesc = Entry(box1, width=30)
pdesc.grid(row=1, column=4, padx=20)
qty = Entry(box1, width=30)
qty.grid(row=2, column=4, padx=20)
prce = Entry(box1, width=30)
prce.grid(row=3, column=4, padx=20)
uprce = Entry(box1, width=30)
uprce.grid(row=4, column=4, padx=20)



pdesc_label = Label(box1, text='Product')
pdesc_label.grid(row=1, column=5)
qty_label = Label(box1, text='Quantity')
qty_label.grid(row=2, column=5)
prce_label = Label(box1, text='Price')
prce_label.grid(row=3, column=5)
uprce_label = Label(box1, text='Unit Price')
uprce_label.grid(row=4, column=5)

#products
trv = ttk.Treeview(box2, column=(1,2,3,4,5,6), show="headings", height="20")
style=ttk.Style(trv)
style.configure('Treeview', rowheight=20)

trv.pack(side=LEFT)
trv.heading(1, text="Product ID")
trv.heading(2, text="Product Description")
trv.heading(3, text="Quantity")
trv.heading(4, text="Price")
trv.heading(5, text="Unit Price")
trv.heading(6, text="Return Percentage")

#data for products
conn = sqlite3.connect('new1.db')

c = conn.cursor()
query = "SELECT oid, productdesc, qty, price, uprice from products"
c.execute(query)
show = c.fetchall()
update(show)

btn2 = ttk.Button(box1, text='Enter', command=submitprod)
btn2.grid(row=6, column=4, columnspan=1, pady=10, padx=10, ipadx=10)

top.title("Test")
top.geometry("1500x1200")
top.mainloop()

Answer 1

正如布莱恩·奥克利指出的那样。您当前仅在初始程序运行时查询SQL数据库。另外，在运行submitprod()函数时，需要调用update()函数将新数据添加到树视图中

我建议对您当前的代码进行以下调整

添加查询数据库功能，以便查询最新的产品数据。这可以在submitprod()函数中调用，也可以在程序初始运行时调用

添加一个数据库插入函数。您可以将这两者添加到submitprod()函数中，然后从那里更新树视图

import tkinter
from tkinter import*
from tkinter import ttk, LabelFrame
import tkinter.messagebox
import sqlite3


def update(show):
    for i in show:
        trv.insert('', 'end', values=i)

def query_database():
    query = "SELECT oid, productdesc, qty, price, uprice from products"

    conn = sqlite3.connect('new1.db')
    c = conn.cursor()
    c.execute(query)
    show = c.fetchall()

    return show

def database_insert():
    conn = sqlite3.connect('new1.db')
    c = conn.cursor()
    c.execute("INSERT INTO products VALUES (:pdesc, :qty, :prce, :uprce)",{
          'pdesc': pdesc.get(),
          'qty': qty.get(),
          'prce': prce.get(),
          'uprce': uprce.get()})

    conn.commit()

def submitprod():
    database_insert()
    current_db_data = query_database()
    update(current_db_data)
    
    #reset
    pdesc.delete(0, END)
    qty.delete(0, END)
    prce.delete(0, END)
    uprce.delete(0, END)

top = tkinter.Tk()
top.title("Test")
top.geometry("1500x1200")

box1 = LabelFrame(top, text="Product Entry")
box1.pack (fill="both", expand="yes", padx=20, pady=10)
box2 = LabelFrame(top, text="Products")
box2.pack (fill="both", expand="yes", padx=20, pady=10)

#product labels and entry

pdesc = Entry(box1, width=30)
pdesc.grid(row=1, column=4, padx=20)
qty = Entry(box1, width=30)
qty.grid(row=2, column=4, padx=20)
prce = Entry(box1, width=30)
prce.grid(row=3, column=4, padx=20)
uprce = Entry(box1, width=30)
uprce.grid(row=4, column=4, padx=20)
    
pdesc_label = Label(box1, text='Product')
pdesc_label.grid(row=1, column=5)
qty_label = Label(box1, text='Quantity')
qty_label.grid(row=2, column=5)
prce_label = Label(box1, text='Price')
prce_label.grid(row=3, column=5)
uprce_label = Label(box1, text='Unit Price')
uprce_label.grid(row=4, column=5)

#products
trv = ttk.Treeview(box2, column=(1,2,3,4,5,6), show="headings", height="20")
style=ttk.Style(trv)
style.configure('Treeview', rowheight=20)

trv.pack(side=LEFT)
trv.heading(1, text="Product ID")
trv.heading(2, text="Product Description")
trv.heading(3, text="Quantity")
trv.heading(4, text="Price")
trv.heading(5, text="Unit Price")
trv.heading(6, text="Return Percentage")

btn2 = ttk.Button(box1, text='Enter', command=submitprod)
btn2.grid(row=6, column=4, columnspan=1, pady=10, padx=10, ipadx=10)

# load database data on initial startup of app
initial_data = query_database()
update(initial_data)

top.mainloop()

使用SQL数据库时需要记住的一件事是需要关闭游标和连接。SQLite特别不喜欢数据库的并发查询，如果应用程序数据库的大小增加，则需要注意这一点