Apr-12-2024, 05:43 PM
(This post was last modified: Apr-12-2024, 05:43 PM by 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!
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 '''