How do I insert variables in the UPDATE Statement sqlite3 with Python?

Question

If I want to insert a variable value in sqlite3 with python you code the following statement. For example:

import sqlite3
import random

conn = sqlite3.connect('testing.db')
c = conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS testing(Name TEXT, Telephone TEXT, ID REAL)")

var1 = input("Value:")
var2 = input("Value:")
var3 = random.randint(1,20)

c.execute("INSERT INTO testing VALUES(?, ?, ?)", (var1, var2, var3))

conn.commit()
conn.close()

What if I want to do the same with the UPDATE statement. I tried this and is giving me error:

column = input("Column to update:")
update_user = input("Value to update:")
field_name = input("Where field name equal to:")
value = input("Value")

c.execute("UPDATE testing SET ? = ? WHERE ? = ?", (column, update_user, field_name, value))

conn.commit()

And this is the error that I get:

sqlite3.OperationalError: near "?": syntax error

Possible duplicate of sqlite3.OperationalError: near "?": syntax error — Shubham
– Shubham, Commented Dec 31, 2016 at 21:06

alecxe · Accepted Answer · 2016-12-31 21:04:38Z

6

This is actually a common problem, in your update query, you cannot parameterize the column names. In other words:

UPDATE testing SET ? = ? WHERE ? = ?
               THIS^       THIS^

cannot be query placeholders and you have to insert them the usual way - via string formatting:

c.execute("""
    UPDATE 
        testing 
    SET 
        {0} = ? 
    WHERE 
        {1} = ?""".format(column, field_name), (update_user, value))

Though, you should be careful about properly sanitizing, validating the column and field_name values to prevent SQL injection attacks.

answered Dec 31, 2016 at 21:04

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

alecxe Over a year ago

Even though this was asked and answered before in different variations, I'll leave this answer since it points out what happens and what to do in this particular use case.

Collectives™ on Stack Overflow

How do I insert variables in the UPDATE Statement sqlite3 with Python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related