Help with subtracting values using SQLite & Python

[Extra]

Hello,

I made an inventory system using SQLite and Python. I'm working on a 'checkoutInventory()' function that let's a user add items to a cart and automatically subtracts the items current quantity from the quantity that the user is taking out.

My Question is how do I program a statement that subtracts the user's value from the Quantity column in the SQLite database?

Thanks in advance?

My checkoutInventory() Function right now:

#---------------------------------------------------------------
#                 Checkout Inventory Items 
#---------------------------------------------------------------
def checkoutInventory():

    #Connect to the inventory database (inventory.db)
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()

    print('=============================')
    print('= Checkout From Inventory =')
    print('=============================')
    print("Find an item to checkout by entering it's ID or Name")
    print('(1) Checkout Item by ID')
    print('(2) Checkout Item by Name')
    print('(3) View Cart')
    CHOICE = input("Enter choice: ")

    #----- Update Item by ID -----
    #Choose Item to update by ID
    if CHOICE == '1':
        userQueryID = input('Item ID: ')

        #->Show info for the currently selected item
        cursor.execute('SELECT * FROM items WHERE ID = ?', (userQueryID,))
        result = cursor.fetchall()
        #Print Results/Info in rows
        print('\n--------------------')
        print('Current Info For Item: ' + userQueryID)
        print('--------------------')
        for row in result:
            print('\n--------------------')
            print("Item ID: ", row[0])
            print("Item Name: ", row[1])
            print("Item Quantity: ", row[2])
            print("Item Price: $", row[3])
            print("Item Sell Price: $", row[4])
            print("Item Description: ", row[5])
            print("Item Category: ", row[6])
            print("Item Location: ", row[7])
            print("Last Updated: ", row[10])
            print('--------------------\n')

        #-->Bring up checkout menu
        print('------------------------------')
        print("Would you like to add this item to cart?")
        print('(y) Yes')
        print('(n) No')
        CHOICE = input("Enter choice: ")

        #Yes
        if CHOICE == 'y' or CHOICE == 'Y':
            #Ask for quantity
            userQueryQuantity = int(input('How much quantity are you taking: '))
            #Remove the quantity from the inventory
            #TODO: Update this Update statement so it subtracts user's value from current quantity:
            cursor.execute("UPDATE items SET Quantity = ? WHERE ID = ?" , (userQueryQuantity, userQueryID,))
            connection.commit()
            #Add item to cart
            #TODO: Make somthing that stores the currently selected item (maybe a list?)
            #That way the user can add more stuff to the cart or actually checkout(which will clear the cart
            # since the quantity values have alreday beeen subtracted)
            print('The item with the ID: ' + userQueryID + 'has been added to cart')

        #No
        elif CHOICE == 'n' or CHOICE == 'N':
            #Return to Checkout Menu
            checkoutInventory()

[deanhystad]

To change an inventory count you can query the current count, modify the returned value, and update the database using the new value. This will work fine if you are the only user. If there are multiple users you'll want the database to perform the operation.

[Extra]

To change an inventory count you can query the current count, modify the returned value, and update the database using the new value. This will work fine if you are the only user. If there are multiple users you'll want the database to perform the operation.

That makes sense, but the part I'm having trouble with is querying the current count.
Do just make a SELECT statement hat selects the quantity of the item? But how do I put that in a python variable?

[deanhystad]

After a query you use cursor.fetchone() or curser.fetchall() to get the query results.

[Extra]

So I have this:

               #Yes
        if CHOICE == 'y' or CHOICE == 'Y':
            #Ask for quantity
            userQueryQuantity = int(input('How much quantity are you taking: '))
            #Get current Item quantity by ID
            cursor.execute("SELECT Quantity FROM items WHERE ID = ?",(userQueryID,))
            currentQuantity = cursor.fetchall()
            #Subtract the current quantity from the quantity the user is taking
            updatedQuantity = currentQuantity - userQueryQuantity
            #Update the quantity
            cursor.execute("UPDATE items SET Quantity = ? WHERE ID = ?" , (updatedQuantity, userQueryID,))
            connection.commit()

            #Add item to cart
            #TODO: Make somthing that stores the currently selected item (maybe a list?)
            #That way the user can add more stuff to the cart or actually checkout(which will clear the cart
            # since the quantity values have alreday beeen subtracted)
            print('The item with the ID: ' + userQueryID + 'has been added to cart')

But when it tries to do the subtraction calculation I get this error:

Error:
    updatedQuantity = currentQuantity - userQueryQuantity
TypeError: unsupported operand type(s) for -: 'list' and 'int'

How do I fix this?

[ndc85430]

Are you expecting several results from the query or just one? If the latter, use fetchone instead of fetchall.

Perhaps you'd do well to work through some tutorial on SQL with Python?

[Extra]

Are you expecting several results from the query or just one? If the latter, use fetchone instead of fetchall.

Perhaps you'd do well to work through some tutorial on SQL with Python?

I had tried fetchone before and it didn't work.

I get this:

Error:
    updatedQuantity = currentQuantity - userQueryQuantity
TypeError: unsupported operand type(s) for -: 'tuple' and 'int'

[ndc85430]

Is it a tuple with one item inside?

I think you really need to do a tutorial. Also, you should be trying to understand the errors and debug your program instead of expecting us to hold your hand the whole way.

[Extra]

Is it a tuple with one item inside?

I think you really need to do a tutorial. Also, you should be trying to understand the errors and debug your program instead of expecting us to hold your hand the whole way.

Well I know the issue is somewhere here in the way I try to fetch the Quantity(an Int) and store it in a python variable, then try to subtract it from an the user input (also an Int) but I don't know why I keep getting a type error.

            #Get current Item quantity by ID
            currentQuantity = cursor.execute("SELECT Quantity FROM items WHERE ID = ?",(userQueryID,)).fetchone()
            #Subtract the current quantity from the quantity the user is taking
            updatedQuantity = currentQuantity - userQueryQuantity
            #Update the quantity
            cursor.execute("UPDATE items SET Quantity = ? WHERE ID = ?",(updatedQuantity, userQueryID,))

I am trying to understand my errors and get it working and I've been searching the internet but I can't find anything that covers subtracting python variables with SQLite (The closest I got is subtracting values from 2 SQLite tables. And I'd rather not store the current Quantity in a separate Table then subtract it from my main Table. I'll do that as a last result if I can't get this working). And If you could recommend a good tutorial covering this area that would be greatly appreciated.

[Extra]

Never mind, I managed to get it.

I just did the calculation inside the UPDATE statement instead of externalizing it like I was doing.

 #Update the quantity
            cursor.execute("UPDATE items SET Quantity = Quantity - ? WHERE ID = ?",(userQueryQuantity, userQueryID,))
            connection.commit()

Here's the full chunk of code for anyone else has a similar issue that I had (that way you can reference this):

  #Yes
        if CHOICE == 'y' or CHOICE == 'Y':
            #Ask for quantity
            userQueryQuantity = int(input('How much quantity are you taking: '))
            
            #Update the quantity
            cursor.execute("UPDATE items SET Quantity = Quantity - ? WHERE ID = ?",(userQueryQuantity, userQueryID,))
            connection.commit()

            #Add item to cart
            #TODO: Make something that stores the currently selected item (maybe a list?)
            #That way the user can add more stuff to the cart or actually checkout(which will clear the cart
            # since the quantity values have already been subtracted)
            print('The item with the ID: ' + userQueryID + ' has been added to cart')