SQLite database wont update with a Python variable

Question

I tried to update (add) the integer value of a cell named "product_qty" in a table in an SQLite database with a variable, with the following lines of code

x = int(TOPUPQTY.get())
cursor.execute("UPDATE 'product' SET product_qty = product_qty + x WHERE `product_name` LIKE ?", ('%'+str(TOPUPPRODUCT_NAME.get())+'%',))

I got an sqlite3.OperationalError: no such column: x. But when I tried substituting the variable x with a constant say 30, that is:

cursor.execute("UPDATE 'product' SET product_qty = product_qty + 30 WHERE `product_name` LIKE ?", ('%'+str(TOPUPPRODUCT_NAME.get())+'%',))

this worked very well, but i need to use a variable instead of a constant.

Please separate your code from the text. This makes it better readable. — Randrian
– Randrian, Commented Dec 6, 2019 at 9:48

Randrian · Accepted Answer · 2019-12-06 09:58:00Z

2

Add a placeholder "?" for the variable in the string.

x = 30
cursor.execute(f"UPDATE product SET product_qty = product_qty + ? WHERE product_name LIKE ?", (x, '%'+str(TOPUPPRODUCT_NAME.get())+'%',))

You should NOT put x in the string yourself e.g. with a formatting string, as then you have the possibility to get an sql-injection if the contents of x comes from an untrusted source. So better always rely on the formatting from the sqlite package itself.

answered Dec 6, 2019 at 9:58

Randrian

1,08512 silver badges25 bronze badges

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

1 Comment

Matthias Over a year ago

That's the way to go. I suggested an answer with string formatting because I wasn't sure that placeholders would work here but I checked it and everything is fine.

ghovat · Accepted Answer · 2019-12-06 10:10:24Z

2

You have written variable x inside double-quotes and when the cursor statement executes it tries to find column x in the table and that's why the error occurs.

You have to pass the variable's value to the query using a placeholder.

edited Dec 6, 2019 at 10:10

ghovat

1,0431 gold badge14 silver badges38 bronze badges

answered Dec 6, 2019 at 10:00

vinoth kumar

214 bronze badges

Collectives™ on Stack Overflow

SQLite database wont update with a Python variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related