How do I get updated rows from Sqlite3 to display in Tkinter?

Question

I am creating a program for me and my friends small shop. I am using Python, Sqlite3, and Tkinter. I am able to display records from Sqlite3 through Tkinter but when I insert new records/data, it inserts into Sqlite3 but Tkinter does not display the new record inserted. Here is my code; I hope it is understandable.

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

as Bryan Oakley points out. You are currently only querying your SQL database on your initial program run. Also, when you run your submitprod() function you need to call your update() function to add the new data to your treeview.

I would suggest the following adjustments to your current code.

Add a query database function so you can query the latest product data. This can be called in your submitprod() function and on initial program run.

Add a database insert function. You can add both of these to your submitprod() function and then update your tree view from there.

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

One thing to remember when working with SQL databases is the need to close the cursor and connection. SQLite particularly does not like concurrent querying of databases which is something to note if your application database grows in size.