How to update a SQL database based of a user input in python

Question

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.

Can we capture the Exception Message, to understand what could be going wrong. Something like following except Exception as e: print(str(e)) before the add_cart = "INSERT INTO Shopping_cart(item_ID, item_name, item_quantity) VALUES (?, ?, ?)". — Nishant Jain
– Nishant Jain, Commented Dec 21, 2022 at 13:23
If you import "traceback" module, you can call "traceback.print_exc()" in the except-block to see the full traceback of the exception. — Michael Butscher
– Michael Butscher, Commented Dec 21, 2022 at 13:36
Not entirely related to the problem, but as a best practice, you should not use a bare except: - stackoverflow.com/questions/54948548/… — dskrypa
– dskrypa, Commented Dec 21, 2022 at 13:46
I wasn't taught the good practice of using try and except. Sorry about the inconvenience. Here is what I get when i use except Exception as e: print(str(e)): can only concatenate tuple (not "int") to tuple — Jeremy Panthier
– Jeremy Panthier, Commented Dec 21, 2022 at 13:54

Jason Chia · Accepted Answer · 2022-12-21 14:15:58Z

1

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.

edited Dec 21, 2022 at 14:15

answered Dec 21, 2022 at 13:48

Jason Chia

1,1701 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Panthier Over a year ago

I have used the '''except Exception as e: print(str(e))''' that someone has suggested to see the error, and here is what I get when I implement the cursor.fetchone()[0]: can only concatenate str (not "int") to str, I think the fix is as easy as putting the int in a list but I could be wrong.

Jason Chia Over a year ago

You have to look at the traceback carefully to which line is throwing the error. Have added an addendum based on my experience on the concat error. It shouldn't be throwing by cursor.fetchone()[0] but rather by quantity_selector+=1. Since the value of cursor.fetchone()[0] is a string, simply cast it as integer and the problem should be solved.

Jeremy Panthier Over a year ago

Thank you very much it now works. I will be sure to try and use the try and except properly in the future.

Collectives™ on Stack Overflow

How to update a SQL database based of a user input in python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related