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