How do I update my stock database to reduce itself once a customer orders

Time to ask from the almighty stack overflow questions again. So my problem is that I am developing in TKINTER and have implemented and integrated 2 tables in one database on the application. I would like my table products to update once an order is placed on basket table and it happens once the self.data_entry method is called. The thing is when my stock_update method runs, it updates the database but it wipes the stock value of the other products. I’m just wondering if my stock_update method has the wrong syntax for updating sql.

BEFORE Running

product_id	product_name	stock
1	product1	60
1	product2	60

AFTER

product_id	product_name	stock
1	product1	59
1	product2	NULL

class DatabaseHandler:

def data_entry(self, cust_id, prod_id, prod_name, delivery_type, quantity, delivery_cost, price, subtotal):
    print("Data Entry Method is called")
    connection = sqlite3.connect("anothertrial.db")
    cursor = connection.cursor()
    # YA "value" in the following line should be "values"
    insert_query = "INSERT INTO basket(cust_id, product_id,  prod_name, delivery_type, quantity, delivery_cost, price, subtotal) VALUES(?,?,?,?,?,?,?,?)"
    cursor.execute(insert_query,
                   (cust_id, prod_id, prod_name, delivery_type, quantity, delivery_cost, price, subtotal))
    connection.commit()
    connection.close()
    print("Data Entry Method is finished")
def connection(self):
    print("Database is now connected")
    db_conn = sqlite3.connect("anothertrial.db")
    cursor = db_conn.cursor()
    my_query = "CREATE TABLE IF NOT EXISTS basket(order_id INTEGER PRIMARY KEY AUTOINCREMENT, cust_id TEXT, product_id INTEGER, prod_name TEXT, delivery_type TEXT, quantity TEXT, delivery_cost TEXT, price TEXT, subtotal TEXT, FOREIGN KEY (product_id) REFERENCES products(product_id)) "
    cursor.execute(my_query)
    db_conn.commit()
    db_conn.close()
    print("Database connection is finished")

def product_connection(self):
    print("Product Table Creation Method")
    db_connect = sqlite3.connect("anothertrial.db")
    cursor = db_connect.cursor()
    product_table_creation = "CREATE TABLE IF NOT EXISTS products(product_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
                             "product_name TEXT, stock INTEGER)"
    cursor.execute(product_table_creation)
    db_connect.commit()
    db_connect.close()
    print("Product Table has been created")

def stock_update(self):
    print("Stock Update Method")  # I use Print to Test My Method If It Will Run
    db_connect = sqlite3.connect("anothertrial.db")
    cursor = db_connect.cursor()
    stock_update_query = "UPDATE products SET stock = stock - (SELECT quantity FROM basket WHERE " 
                         "basket.product_id = " 
                         "products.product_id) "
    cursor.execute(stock_update_query)
    db_connect.commit()
    db_connect.close()
    print("Stock Updated")  # I use Print to Test My Method If It Will Run


def add_item(self):
    global total
    global delivery_charge
    global chosen_item_id
    product_id_dict = {"Gundam": 1, "Starwars": 2, "Paw Patrol": 3, "Peppa Pig": 4, "Cars Disney": 5,
                       "Teddy Bear": 6}
    item_dict = {"": 0, "Gundam": 10, "Starwars": 20, "Paw Patrol": 30, "Peppa Pig": 15, "Cars Disney": 15,
                 "Teddy Bear": 10}

    delivery_dict = {"Free Delivery": 0, "Standard": 2, "Express": 5}

    delivery_type = self.delivery_opt_var.get()
    item = self.product_name_var.get()
    price = (self.prod_cost_var.get())
    qty = self.quantity_var.get()

    for product, cost in item_dict.items():
        for delivery, charge in delivery_dict.items():
            for selected_product, prod_id in product_id_dict.items():
                if item == product and delivery == delivery_type and item == selected_product:
                    chosen_item_id = prod_id
                    delivery_charge = charge
                    price = cost
                    total = round(price * qty + delivery_charge, 2)

    self.product_id_var.set(chosen_item_id)
    self.prod_cost_var.set(price)
    self.delivery_charge_var.set(delivery_charge)
    self.subtotal_var.set(total)

    self.customer_basket.data_entry(self.order_cust_id_var.get(),
                                    self.product_id_var.get(),
                                    self.product_name_var.get(),
                                    self.delivery_opt_var.get(),
                                    self.quantity_var.get(),
                                    self.delivery_charge_var.get(),
                                    self.prod_cost_var.get(),
                                    self.subtotal_var.get())

Answer

I revised the query. Hope this work:

stock_update_query = "UPDATE products SET stock = stock - (SELECT quantity FROM basket WHERE " 
                     "basket.product_id = " 
                     "products.product_id) " 
                     "WHERE EXISTS (SELECT quantity " 
                     "FROM basket "
                     "WHERE product_id = products.product_id)"

Advertisement

Answer