When I execute an UPDDATE query statement that is parameterized with both the column name and the value, it fails:
db.execute("UPDATE users SET %s = %s WHERE user_id = %s;", (key, user[key], user_id))
The error message is
ProgrammingError: syntax error at or near "'firstname'"
UPDATE users SET 'firstname' = 'John' WHERE 'use...
I have a database table "users" with primary id field "user_id" and two fields "firstname" and "lastname" (each of them of type varchar). I also have a dictionary "user" with keys "user_id", "firstname" and "lastname" (all strings). Now to update my database at some point in my script, I want to loop through the dictionary to update the database:
for key in user.keys():
if key != "user_id":
db.execute("UPDATE users SET %s = %s WHERE user_id = %s;", (key, user[key], user_id))
This, however, results in the error message quoted above.
The dirty solution I found for this is to insert the key in my query string directly:
db.execute("UPDATE users SET " + key + " = %s WHERE user_id = %s;", (user[key], user_id))
...but I read everywhere that this is bad practice because of vulnerability to SQL injection (Bonus question: If I actually get the keys from my "user" dictionary from the table or hard-coded part of my script, what can go wrong?). So what is the right, non-hacky solution for this issue?
Edit: The variants that I already tried, without success, are:
db.execute("UPDATE users SET %s = %s WHERE 'user_id' = %s;", (str(key), str(user[key]), user_id))
db.execute("UPDATE users SET %s = %s WHERE 'user_id' = %s;", (key, str(user[key]), user_id))
db.execute("UPDATE users SET %s = %s WHERE user_id = %s;", (key, str(user[key]), user_id))
db.execute("""UPDATE users SET %s = %s WHERE user_id = %s;""", (key, user[key], user_id))
db.execute(r"UPDATE users SET %s = %s WHERE user_id = %s;", (key, user[key], user_id))
