need to update my SQL database with new number but I cant seem to be able to do so. I'm using python with SQLite I'm not really any good at SQL so any help would be appreciated. I couldn't find a way to update it straight away so I thought a work around would be to get the number from the database then turn it back into a string to be enter back in. I'm sure I'm making this more difficult then it needs to be ive been stuck for a long time and really just dont know what to do now

def delete():
    #create a database or connect ot one
    conn = sqlite3.connect('Food_item.db')

    #create cursor
    c = conn.cursor()
    
   
    # Delete a record
    if (len(f_name_editor.get()) != None and len(l_name_editor.get()) == 0):
        print ("hello")
        c.execute("DELETE from FOOD WHERE oid= "   f_name_editor.get())
        
    elif (len(f_name_editor.get()) != None and len(l_name_editor.get()) != None):
        
        c.execute("SELECT QUANTITY FROM FOOD WHERE ADMIN_ID= "  f_name_editor.get())
        record_id = f_name_editor.get()
        records = c.fetchone()[0]
        sub_num= l_name_editor.get()
        new_items= (records-int(sub_num))
        Removed_items =str(new_items)
        print(Removed_items)
        
        c.execute("UPDATE QUANTITY FROM FOOD  ="  Removed_items " WHERE ADMIN_ID= " f_name_editor.get())

CodePudding user response：

The UPDATE SQL in your code is invalid, it should be:

c.execute('UPDATE FOOD SET QUANTITY = ? WHERE ADMIN_ID = ?',
          (Removed_items, f_name_editor.get()))
conn.commit()

Better use placeholders to avoid SQL injection and you need to call conn.commit() to make the update effective.

Note that if l_name_editor.get() is the amount to be deducted from QUANTITY, you don't need to get the current QUANTITY. Just use one UPDATE SQL as below:

c.execute('UPDATE FOOD SET QUANTITY = QUANTITY - ? WHERE ADMIN_ID = ?',
          (l_name_editor.get(), f_name_editor.get()))
conn.commit()