I am trying to make a shopping cart code in python using sql, but when I try and update the shopping cart database table, it doesn't work and runs the except code, and I am not sure where the error is with the code, but it just prints "Added to cart successfully" so instead of printing "Updated cart successfully", so the second part of the code works, as it adds the item to the database, but when I try and put in the same item again, it doesn't do the first part of the code.

search_bar_2 = input("Enter what item you would like to look up >>> ")
        #ask the user what item they are looking for specifically
        try:
            sql = "SELECT * FROM Items_in_stock WHERE item_name = ?"
            #select everything from items_in_stock where the item_name has a placeholder that will be determined later.
            cursor.execute(sql, (search_bar_2,))
            #Executes the sql and adds the search_bar_2 as the value for the placeholder.
            item = cursor.fetchone()
            #gets one item from the database and saves it as item in python
            print("id: ", item[0], "\nname of item: ", item[1], "\nquantity: ", item[2])
            #prints each element of the tuple item seperatly with the corresponding text beforehand
            add_to_cart = input("Would you like to add this item to your cart? (y/n) >>> ")
            #asks the user if they want to save the item displayed to them to their cart. this response will either need to be a y for yes or n for no.
            if add_to_cart == 'y':
                # if the user answered yes
                try:
                    sql3 = ('SELECT item_quantity FROM Shopping_cart WHERE item_name = ?')
                    cursor.execute(sql3, (search_bar_2,))
                    quantity_selector = cursor.fetchone()
                    quantity_selector  = 1
                    cursor.execute('UPDATE Shopping_cart SET item_quantity = ? WHERE item_name = ?', (quantity_selector, search_bar_2))
                    connection.commit()
                    print("Updated cart successfully")
                except:
                    add_cart = "INSERT INTO Shopping_cart(item_ID, item_name, item_quantity) VALUES (?, ?, ?)"
                    #adds the items to the cart, to do so, a placeholder was given to each element of shopping_cart that will need to be filled with the information of the items.
                    item_values = (item[0], item[1], 1)
                    cursor.execute(add_cart, item_values)
                    connection.commit()
                    print("Added to cart successfully")
        except:
            print("There is an error with the search item.") 

the bit that doesn't work is the bit after "If add_to_cart == 'y'" Thanks in advance for the help.

CodePudding user response：

Quick Solution: quantity_selector is not what you think it is.

quantity_selector = cursor.fetchone() #Probably returns
-> (1,) 
quantity_selector =1 #FAILS going to your except clause

Consider:

quantity_selector = cursor.fetchone()[0] #Fails if no result so goes to except as expected
-> 1
quantity_selector =1 
-> 2

On a side note, it really isn't a good idea to use a bare try/except all the time without specifying clearly what you are trying to do or what errors you are suppressing or use as logic. You may accidentally hide errors that would have popped up.

Addendum: Since the value is a string the simplest correction would be

quantity_selector = int(cursor.fetchone()[0]) #Fails if no result so goes to except as expected. Now casts the value as integer so that the addition will work. 
-> 1
quantity_selector =1 
-> 2

ALthough the above is the simplest fix, it probably means you should probably change the schema of your sql table to keep it as integers so you don't get weird behaviours such as letters in a column that should be integers.