(Python) cursor.execute(sql)

Question

def makeProductTable():
"""This creates a database with a blank table."""
with connect("products.db") as db:
 cursor = db.cursor()
 cursor.execute("""
 CREATE TABLE Product(
 ProductID integer,
 GTIN integer,
 Description string,
 StockLevel integer,
 Primary Key(ProductID));""")
 db.commit()
def editStockLevel():
with connect("products.db") as db:
 cursor = db.cursor()
 Product_ID=input("Please enter the id of the product you would like to change: ")
 Stock_Update=input("Please enter the new stock level: ")
 sql = "update product set StockLevel = ('Stock_Update') where ProductID = ('Product_ID');"
 cursor.execute(sql)
 db.commit()
return "Stock Level Updated." 

The first function is used to make the table and it shows my column titles, the second function is needed to update a specific value in the table.

But when this is ran the inputs are executed, however when all show all the products in the table the value for stock level doesn't change.

So I think the problem has something to do with the cursor.execute(sql) line.

Answer 1

Or something like this?

cur.execute("UPDATE Product set StockLevel = ? where ProductID = ?",(Stock_Update,Product_ID))

• KR29: @JW2209 . If it works, please mark it as answer. Thanks.

Answer 2

Yes; you're passing literal strings, instead of the values returned from your input calls. You need to use parameters in the statement and pass thme to the execute call.

sql= "update product set StockLevel = %s where ProductID = %s;"
cursor.execute(sql, (Stock_Update, Product_ID))