wrong entries in sqlite database and tkinter

[epsilondatum]

hello all...first time here,need some advice please!
my case: when i add a new product in my program,its values go to customers fields,not in the products (where they belong).
product details,go to customers...
the customers part works fine, but i can not manage to get my products added and displayed correctly.
i use two tables in my database,one for customers, and one for products.

here is the full code i use, please tell me if more info needed!

'''
Customer Management System

Version: EPSILON 06.5
Date: 09/04/2024
Author: Errikos Ntinos
Status: devel

changelog:
added new table products and its functions
made some minor changes to fields size and GUI in general

todo: fix horizontal scrollbar
'''

from tkinter import *
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import webbrowser
import csv
import os

mydata = []
mainwindow = tk.Tk()

# at first, define the needed functions

# Function to display the help file
def open_html_file():
    file_path = r".\help.html"  # Using a raw string to avoid escaping
    webbrowser.open_new_tab(file_path)

# Function to display the message box
def show_message_box():
    messagebox.showinfo("Information", "Customer Management System\n"
    "\n"
    "Version name: Igor\n"
    "\n"
    "Developed with love and Python""\n"
    "\n"
    "Epsilon Datum Web and I.T. Services""\n"
    "\n"
    "2024""\n"
    "\n"
    )

def update(rows):
    global mydata
    mydata = rows
    trv.delete(*trv.get_children())
    for i in rows:
        trv.insert('', 'end', values=i)

def customer_search():#was: search
    q2 = q.get()
    query = "SELECT id, first_name, last_name, age, email, phone FROM customers WHERE first_name LIKE ? OR last_name LIKE ? OR id LIKE ? OR age LIKE ? OR email LIKE ? OR phone LIKE ?"
    cursor.execute(query, ('%'+q2+'%', '%'+q2+'%' , '%'+q2+'%' , '%'+q2+'%', '%'+q2+'%', '%'+q2+'%'))# add , '%'+q2+'%', '%'+q2+'%'
    rows = cursor.fetchall()
    update(rows)

# under test added on 6.05 - changed the original def clear
def clear():
    # Query to fetch data from the products table
    product_query = "SELECT product_id, product_name, product_pieces, product_price, product_desc FROM products"
    cursor.execute(product_query)
    product_rows = cursor.fetchall()

    # Query to fetch data from the customers table
    customer_query = "SELECT id, first_name, last_name, age, email, phone FROM customers"
    cursor.execute(customer_query)
    customer_rows = cursor.fetchall()

    # Combine the rows from both tables
    rows = product_rows + customer_rows
    
    # Update the GUI with the combined rows
    update(rows)
    
def getrow(event):
    item = trv.item(trv.focus())
    print("Item values:", item['values'])  # Add this line for debugging
    t1.set(item['values'][0])
    t2.set(item['values'][1])
    t3.set(item['values'][2])
    t4.set(item['values'][3])
    t5.set(item['values'][4])# for email
    t6.set(item['values'][5])# for phone 
    t7.set(item['values'][6])# for product_id
    t8.set(item['values'][7])# for product_name
    t9.set(item['values'][8])# for product_pieces
    t10.set(item['values'][9])# for product_price
    t11.set(item['values'][10])# for product_desc
    
def update_customer():
    fname = t2.get()
    lname = t3.get()
    age = t4.get()
    custid = t1.get()
    email = t5.get() # for email
    phone = t6.get() # for phone
    
    if messagebox.askyesno("UPDATE CUSTOMER ?"):
        query = "UPDATE customers SET first_name = ?, last_name = ?, age = ? , email = ? , phone = ? WHERE id = ?" 
        cursor.execute(query, (fname, lname, age, email, phone, custid))
        mydb.commit()
        clear()
    else:
        return True

def add_new_customer():
    fname = t2.get()
    lname = t3.get() 
    age = t4.get()
    email = t5.get()# added email
    phone = t6.get()# added phone
    query = "INSERT INTO customers(id, first_name, last_name, age, email, phone, registration_date) VALUES(NULL, ?, ?, ?, ?, ?, DATE('now'))" #added email and ? , ?
    cursor.execute(query, (fname, lname, age, email, phone)) #added email,phone
    mydb.commit()
    clear()

def delete_customer():
    customer_id = t1.get()
    if messagebox.askyesno("DELETE CUSTOMER ?"):
        query = "DELETE FROM customers WHERE id = ?"
        cursor.execute(query, (customer_id,))
        mydb.commit()
        clear()
    else:
        return True

def add_new_product():
    product_id = t7.get()
    product_name = t8.get()
    product_pieces = t9.get()
    product_price = t10.get()
    product_desc = t11.get()
    query = "INSERT INTO products(product_id, product_name, product_pieces, product_price, product_desc) VALUES(?, ?, ?, ?, ?)" #change NULL to ?  10-4-24
    cursor.execute(query, (product_id, product_name, product_pieces, product_price, product_desc)) #added prod_id 10-4-24
    mydb.commit()
    clear()


def update_product():
    product_id = t7.get()
    product_name = t8.get()
    product_pieces = t9.get()
    product_price = t10.get()
    product_desc = t11.get()
    
    if messagebox.askyesno("UPDATE PRODUCT ?"):
        query = "UPDATE products SET product_name = ?, product_pieces = ?, product_price = ? , product_desc = ? WHERE product_id = ?"
        cursor.execute(query, (product_name, product_pieces, product_price, product_desc, product_id))
        mydb.commit()
        clear()
    else:
        return True

def delete_product():
    product_id = t7.get()
    if messagebox.askyesno("DELETE PRODUCT ?"):
        query = "DELETE FROM products WHERE product_id = ?"
        cursor.execute(query, (product_id,))
        mydb.commit()
        clear()
    else:
        return True

def confirm_quit():
    if messagebox.askokcancel("Exit Customer Management System", "Are you sure you want to quit?"):
        mainwindow.quit()

def export():
    if len(mydata) < 1:
        messagebox.showerror("No Data", "No data available to export!")
        return False# added indent
    fln = filedialog.asksaveasfilename(initialdir=os.getcwd(), title="Save CSV", filetypes=(("CSV File", "*.csv"), ("All Files", "*.*")))
    # test block
    if not fln.endswith('.csv'):
        fln += '.csv'
    
    with open(fln, mode='w', encoding='utf-8') as myfile:#added ,encoding='utf-8'
        exp_writer = csv.writer(myfile, delimiter=',')
        for i in mydata:
            exp_writer.writerow(i)        
    return True  #added chatgpt
    
def importcsv():
    mydata.clear()
    fln = filedialog.askopenfilename(initialdir=os.getcwd(), title="Open CSV", filetypes=(("CSV File", "*.csv"), ("All Files", "*.*")))
    with open(fln, mode='r', encoding='utf-8') as myfile:
        csvread = csv.reader(myfile, delimiter=',')
        for i in csvread:
            mydata.append(i)
    update(mydata)
    return True  

def savedb():
    if messagebox.askyesno("Confirmation", "Are you sure you want to save data to Database?"):
        for i in mydata:
            # Check if the item has at least four elements before accessing them
            if len(i) >= 7:#change to 5 from 4, then from 5 to 6 for phone,then 7 from 6
                uid = i[0]
                fname = i[1]
                lname = i[2]
                age = i[3]
                email = i[4]
                phone = i[5]
                query = "INSERT INTO customers(id, first_name, last_name, age, email, phone, registration_date) VALUES(NULL, ?, ?, ?, DATETIME('now'))" #added email,phone
                cursor.execute(query, (fname, lname, age, email, phone))#added email,phone
            else:
                print("Error: Incomplete data for insertion")
        mydb.commit()
        clear()   
    else:
        return False

# main menu
menu = tk.Menu(mainwindow)
mainwindow.configure(menu=menu)
# help menu
help_menu = tk.Menu(menu, tearoff=False)
help_menu.add_command(label='Help file', command=open_html_file)
menu.add_cascade(label='Help', menu=help_menu)
# about menu
about_menu = tk.Menu(menu, tearoff=False)
#about_menu.add_command(label='About', command=open_html_file)
about_menu.add_command(label='About', command=show_message_box)
menu.add_cascade(label='About', command=show_message_box)
# exit menu
exit_menu = tk.Menu(menu, tearoff=False)
exit_menu.add_command(label='Exit', command=confirm_quit)
menu.add_cascade(label='Exit', menu=exit_menu)

# Connect to SQLite database
mydb = sqlite3.connect('epsilon.db')
cursor = mydb.cursor()

# Create customers table if it doesn't exist
cursor.execute('''CREATE TABLE IF NOT EXISTS customers
                  (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, age INTEGER, email TEXT, phone TEXT, registration_date DATE)''')
mydb.close()# close connection,and restart it to make the new table

# reconnect to SQLite database,to create the other table
mydb = sqlite3.connect('epsilon.db')
cursor = mydb.cursor()

# Create customers table if it doesn't exist
cursor.execute('''CREATE TABLE IF NOT EXISTS products
                  (product_id INTEGER PRIMARY KEY, product_name TEXT, product_pieces INTEGER, product_price REAL, product_desc TEXT)''')

# the variables we'll use
q = tk.StringVar()
t1 = tk.StringVar()
t2 = tk.StringVar()
t3 = tk.StringVar()
t4 = tk.StringVar()
t5 = tk.StringVar()#added email
t6 = tk.StringVar()#added phone
t7 = tk.StringVar() # add product_id
t8 = tk.StringVar()#added product_name
t9 = tk.StringVar()#added product_pieces
t10 = tk.StringVar()#added product_price
t11 = tk.StringVar()#added product_desc


wrapper1 = ttk.LabelFrame(mainwindow, text="Customer List")
wrapper2 = ttk.LabelFrame(mainwindow, text="Search Database")
wrapper3 = ttk.LabelFrame(mainwindow, text="Customer Data")
wrapper4 = ttk.LabelFrame(mainwindow, text="CSV Functions")
wrapper5 = ttk.LabelFrame(mainwindow, text="Products Data")
wrapper6 = ttk.LabelFrame(mainwindow, text="Copyright: Epsilon Datum - Errikos Ntinos , 2024")

wrapper1.pack(fill="both", expand="yes", padx=20, pady=10)# original pady=10)
wrapper2.pack(fill="both", expand="yes", padx=20, pady=10)
wrapper3.pack(fill="both", expand="yes", padx=20, pady=10)
wrapper4.pack(fill="both", expand="yes", padx=20, pady=10)
wrapper5.pack(fill="both", expand="yes", padx=20, pady=10)
wrapper6.pack(fill="both", expand="yes", padx=20, pady=10)


trv = ttk.Treeview(wrapper1, columns=(1,2,3,4,5,6,7,8,9,10,11),show="headings", height="5")# added 7-11
style = ttk.Style(trv)
style.configure('Treeview', rowheight=20)# rowheight=30 original

trv.pack(side=LEFT)#added on 4.03 for the scrollbar
trv.place(x=0, y=0) #added on 4.03 to show the scrollbar original: trv.place(x=0, y=0)
#trv.heading('#0', text="ZABARAKATRANEMIA")#added on 4.03 - do i need it? COMMENTED TO TEST
trv.heading(1, text="Customer ID")
trv.heading(2, text="First Name")
trv.heading(3, text="Last Name")
trv.heading(4, text="Age")
trv.heading(5, text="Email")
trv.heading(6, text="Phone")
trv.heading(7, text="Product_id") 
trv.heading(8, text="Product_name") 
trv.heading(9, text="Product_pieces") 
trv.heading(10, text="Product_price") 
trv.heading(11, text="Product_desc") 

# SETTINGS FOR FIELDS WIDTH
#trv.column('#0', width=75, minwidth=75)# original value width=50, minwidth=100)
trv.column('#1', width=150, minwidth=100)#original value width=250, minwidth=200)
trv.column('#2', width=150, minwidth=100)#original value width=250, minwidth=200)
trv.column('#3', width=250, minwidth=100)#original value width=250, minwidth=200)
trv.column('#4', width=60, minwidth=45)#original value width=250, minwidth=200)
trv.column('#5', width=250, minwidth=100)
trv.column('#6', width=150, minwidth=100)#
trv.column('#7', width=75, minwidth=75)#
trv.column('#8', width=200, minwidth=100)#
trv.column('#9', width=200, minwidth=90)#
trv.column('#10', width=200, minwidth=100)#
trv.column('#11', width=200, minwidth=100)#

trv.bind('<Double 1>', getrow)
# create the export-import-save buttons
exportbtn = Button(wrapper4, text="Export CSV", command=export)
exportbtn.pack(side=tk.LEFT, padx=10, pady=10)

importbtn = Button(wrapper4, text="Import CSV", command=importcsv)
importbtn.pack(side=tk.LEFT, padx=10, pady=10)

savebtn = Button(wrapper4, text="Save Data", command=savedb)
savebtn.pack(side=tk.LEFT, padx=10, pady=10)

# the scrollbars block
# vertical scrollbar
yscrollbar = ttk.Scrollbar(wrapper1, orient="vertical", command=trv.yview)
yscrollbar.pack(side=RIGHT, fill="y")
trv.configure(yscrollcommand=yscrollbar.set)
# end of scrollbars block

# query the database....
query = "SELECT id, first_name, last_name, age , email, phone FROM customers" # added email,phone
cursor.execute(query)
rows = cursor.fetchall()
update(rows)

lbl = ttk.Label(wrapper2, text="Search")
lbl.pack(side=tk.LEFT, padx=10)
ent = ttk.Entry(wrapper2, textvariable=q)
ent.pack(side=tk.LEFT, padx=6)
btn = ttk.Button(wrapper2, text="Search", command=customer_search)
btn.pack(side=tk.LEFT, padx=6)
clrbtn = ttk.Button(wrapper2, text="Clear", command=clear)
clrbtn.pack(side=tk.LEFT, padx=6)
#open_button.pack(side=tk.LEFT, padx=6)

lbl1 = ttk.Label(wrapper3, text="Customer ID")
lbl1.grid(row=0, column=0, padx=5, pady=3)
ent1 = ttk.Entry(wrapper3, textvariable=t1)
ent1.grid(row=0, column=1, padx=5, pady=3)

lbl2 = ttk.Label(wrapper3, text="First Name")
lbl2.grid(row=1, column=0, padx=5, pady=3)
ent2 = ttk.Entry(wrapper3, textvariable=t2)
ent2.grid(row=1, column=1, padx=5, pady=3)

lbl3 = ttk.Label(wrapper3, text="Last Name")
lbl3.grid(row=2, column=0, padx=5, pady=3)
ent3 = ttk.Entry(wrapper3, textvariable=t3)
ent3.grid(row=2, column=1, padx=5, pady=3)

lbl4 = ttk.Label(wrapper3, text="Age")
lbl4.grid(row=3, column=0, padx=5, pady=3)
ent4 = ttk.Entry(wrapper3, textvariable=t4)
ent4.grid(row=3, column=1, padx=5, pady=3)

#added email
lbl5 = ttk.Label(wrapper3, text="Email")
lbl5.grid(row=4, column=0, padx=5, pady=3)
ent5 = ttk.Entry(wrapper3, textvariable=t5)
ent5.grid(row=4, column=1, padx=5, pady=3)

#added phone
lbl6 = ttk.Label(wrapper3, text="Phone")
lbl6.grid(row=5, column=0, padx=5, pady=3)
ent6 = ttk.Entry(wrapper3, textvariable=t6)
ent6.grid(row=5, column=1, padx=5, pady=3)

# products block - change to wrapper5 from wrapper3

#added product_id
lbl7 = ttk.Label(wrapper5, text="Product ID")
lbl7.grid(row=6, column=0, padx=5, pady=3)
ent7 = ttk.Entry(wrapper5, textvariable=t7)
ent7.grid(row=6, column=1, padx=5, pady=3)

#added product_name
lbl8 = ttk.Label(wrapper5, text="Product Name")
lbl8.grid(row=7, column=0, padx=5, pady=3)
ent8 = ttk.Entry(wrapper5, textvariable=t8)
ent8.grid(row=7, column=1, padx=5, pady=3)

#added product_pieces
lbl9 = ttk.Label(wrapper5, text="Product_pieces")
lbl9.grid(row=8, column=0, padx=5, pady=3)
ent9 = ttk.Entry(wrapper5, textvariable=t9)
ent9.grid(row=8, column=1, padx=5, pady=3)

#added product_price
lbl10 = ttk.Label(wrapper5, text="Product_price")
lbl10.grid(row=9, column=0, padx=5, pady=3)
ent10 = ttk.Entry(wrapper5, textvariable=t10)
ent10.grid(row=9, column=1, padx=5, pady=3)

#added product_desc
lbl11 = ttk.Label(wrapper5, text="Product_desc")
lbl11.grid(row=10, column=0, padx=5, pady=3)
ent11 = ttk.Entry(wrapper5, textvariable=t11)
ent11.grid(row=10, column=1, padx=5, pady=3)

# customers buttons
update_btn = ttk.Button(wrapper3, text="Update Customer", command=update_customer)
add_btn = ttk.Button(wrapper3, text="Add New Customer", command=add_new_customer)
delete_btn = ttk.Button(wrapper3, text="Delete Customer", command=delete_customer)

add_btn.grid(row=11, column=0, padx=5, pady=3) # changed row to 11 from row=6
update_btn.grid(row=11, column=1, padx=5, pady=3)
delete_btn.grid(row=11, column=2, padx=5, pady=3)

# products buttons
update_product_btn = ttk.Button(wrapper5, text="Update Product", command=update_product)
add_product_btn = ttk.Button(wrapper5, text="Add New Product", command=add_new_product)
delete_product_btn = ttk.Button(wrapper5, text="Delete Product", command=delete_product)

add_product_btn.grid(row=11, column=0, padx=5, pady=3) # changed row to 11 from row=6
update_product_btn.grid(row=11, column=1, padx=5, pady=3)
delete_product_btn.grid(row=11, column=2, padx=5, pady=3)


mainwindow.title("Customer Management System")
mainwindow.geometry("1920x768")
#Maximize the window using state property
mainwindow.state('zoomed')
#mainwindow.resizable(False,False) #locks the dimensions
mainwindow.mainloop()

######### END OF MAIN CODE - DO NOT EDIT BELOW THIS LINE !!!! #########

'''
allagi sti line 100 apo  
query = "INSERT INTO customers(id, first_name, last_name, age, registration_date) VALUES(NULL, %s, %s, %s, NOW())"


based on these videos:
https://www.youtube.com/watch?v=VT8hV6rH4Gk
'''

[deanhystad]

New products are added to the products database. The problem is in how you update the table.

def clear():
    # Query to fetch data from the products table
    product_query = "SELECT product_id, product_name, product_pieces, product_price, product_desc FROM products"
    cursor.execute(product_query)
    product_rows = cursor.fetchall()

    # Query to fetch data from the customers table
    customer_query = "SELECT id, first_name, last_name, age, email, phone FROM customers"
    cursor.execute(customer_query)
    customer_rows = cursor.fetchall()

    # Combine the rows from both tables
    rows = product_rows + customer_rows

    # Update the GUI with the combined rows
    update(rows)

Notice that customer rows and table rows are treated the same, but in your table the customer information starts at column 0 and the product information starts at column 6.

You could adjust the product rows, but I think it makes more sense to separate the customer and product treeviews. When I want to edit/view customer information I don't want to see product information. I would write the user interface to have tabbed views for working on the user database or the product database. That would make the window much smaller and easier to work with.

Here's a quick and dirty example that uses a ttk Notebook to select what table to view. I didn't include any database stuff, but it would be easy to open a SQL database and get the table information from the database instead of hardcoding as in this example.

import tkinter as tk
from tkinter import ttk
from dataclasses import dataclass
from typing import Type


@dataclass
class Column():
    """Information for a table column."""
    id: str
    title: str
    width: int
    type: Type
    var: tk.StringVar = None

    @property
    def value(self):
        """Return value for column."""
        return self.var.get()

    @value.setter
    def value(self, new_value):
        """Set value for column."""
        return self.var.set(new_value)


class Table():
    """Information about a database table."""
    def __init__(self, id, title, columns):
        self.id = id
        self.title = title
        self.columns = []
        for row in columns:
            id, title, width, type_ = row
            self.columns.append(Column(id, title, width, type_))

    @property
    def ids(self):
        """Return list of column ID's."""
        return [column.id for column in self.columns]

    @property
    def titles(self):
        """Return list of column titles/names."""
        return [column.title for column in self.columns]


class TableView(tk.Frame):
    """Controls for viewing/editing a database table"""
    def __init__(self, parent, table, rows=20):
        super().__init__(parent)
        self.table = table

        # Make treeview object to display table values
        frame = tk.Frame(self, background="blue")
        frame.pack(side=tk.TOP, expand=True, fill=tk.BOTH)
        self.view = ttk.Treeview(frame, columns=table.ids, height=rows)
        self.view.pack(side=tk.LEFT, expand=True, fill=tk.BOTH)
        self.view.column("#0", width=20)
        self.view.bind('<Button 1>', self.select_row)
        scroll = ttk.Scrollbar(frame, orient=tk.VERTICAL, command=self.view.yview)
        scroll.pack(side=tk.LEFT, fill=tk.Y)
        self.view.configure(yscrollcommand=scroll.set)
    
        # Make Entry objects for editing row values.
        frame = tk.Frame(self)
        frame.pack(side=tk.TOP, expand=True, fill=tk.X)
        self.edit = []
        for i, column in enumerate(table.columns):
            self.view.column(column.id, width=column.width)
            self.view.heading(i, text=column.title)
            label = tk.Label(frame, text=column.title)
            label.grid(row=i // 3, column=i % 3 * 4, sticky="e")
            column.var = tk.StringVar()
            entry = tk.Entry(frame, textvariable=column.var, width=40)
            entry.grid(row=i // 3, column=i % 3 * 4 + 1, sticky="news")

    def select_row(self, _):
        """Update Entries to display values from selected row."""
        values = self.view.item(self.view.focus())["values"]
        for column, value in zip(self.table.columns, values):
            column.value = value

    def update_item(self):
        """Update values in for item with matching id."""
        values = [column.value for column in self.table.columns]
        id = values[0]
        for item in self.view.get_children():
            if self.view.item(item, "values")[0] == id:
                self.view.focus(item)
                self.view.item(item, values=values)
                break

    def add(self):
        """Add new row using values from entries."""
        values = [column.value for column in self.table.columns]
        self.view.insert('', tk.END, values=values)
        for column in self.table.columns:
            column.value=""

    def delete(self):
        """Delete selected row."""
        print(self.view.get_children())


class MainPanel(tk.Tk):
    # A database editor window.
    def __init__(self, tables):
        super().__init__()
        self.tables = tables

        # Make a notebook page for each table in database.
        self.notebook = ttk.Notebook(self)
        self.pages = []
        for table in tables:
            page = TableView(self.notebook, table)
            self.notebook.add(page, text=table.title)
            self.pages.append(page)
        self.notebook.pack()

        # Make buttons for adding/deleting/updating table rows.
        frame = tk.Frame(self)
        frame.pack(expand=True, fill=tk.X, pady=10)
        button = ttk.Button(frame, text="Add New", command=self.add)
        button.pack(side=tk.LEFT, expand=True, fill=tk.X, padx=10)
        button = ttk.Button(frame, text="Update", command=self.update_item)
        button.pack(side=tk.LEFT, expand=True, fill=tk.X)
        button = ttk.Button(frame, text="Delete", command=self.delete)
        button.pack(side=tk.LEFT, expand=True, fill=tk.X, padx=10)

    def page(self):
        """Return TableView object for selected notebook page."""
        index = self.notebook.index(self.notebook.select())
        return self.pages[index]

    def add(self):
        """Add row button callback."""
        self.page().add()

    def update_item(self):
        """Update table to reflect entry values."""
        self.page().update_item()

    def delete(self):
        """Delete row button callback."""
        self.page().delete()


# Make some tables to play with.
tables = (
    Table("customer", "Customer", (
        ("id", "ID", 100, str),
        ("name", "Name", 300, str),
        ("address", "Address", 300, str),
        ("phone", "Phone Number", 100, str))
    ),
    Table('products', 'Products', (
        ("id", "ID", 100, str),
        ("name", "Name", 300, str),
        ("price", "Price", 100, float),
        ("count", "Count", 100, int))
    )
)
MainPanel(tables).mainloop()

[epsilondatum]

thank you very much! will give it a try....