wrong entries in sqlite database and tkinter - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: wrong entries in sqlite database and tkinter (/thread-41945.html) |
wrong entries in sqlite database and tkinter - epsilondatum - Apr-12-2024 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 ''' RE: wrong entries in sqlite database and tkinter - deanhystad - Apr-13-2024 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() RE: wrong entries in sqlite database and tkinter - epsilondatum - Apr-23-2024 thank you very much! will give it a try.... |