wrong entries in sqlite database and tkinter

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