I have a table with single empty row inserted. Later I want to update column with some value into the row. I am trying to compose a function which will work for all the columns as shown
def update_column(field, value): print(field, value) sql = '''UPDATE `table name` SET %s = %s WHERE `ID` = 1''' params = (field,value) execute_sql(sql, params)
upon execution update_column(‘fieldName’, 10), getting following error. Any help is appreciated.
Failed to execute command UPDATE TABLE2 SET %s = %s WHERE
ID
= 1 in database due to: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”TEST’ = 10 WHEREID
= 1′ at line 1
Not sure if %s for the field name is the acceptable use case or not. If anyone familiar with such cases, please let me know if I am missing anything here.
Advertisement
Answer
You can’t use query parameters for column names (or table names, sql keywords, expressions, lists, etc.).
A query parameter is filled in as if it were a quoted string, after escaping any literal quote characters in that string.* That’s why it ends up as 'TEST' = 10
and not TEST = 10
.
For column names, you have to do that yourself, without the parameter treatment.
It’s up to you to make sure this is safe. If you use the back-ticks, then the only thing you have to do is make sure your field
variable does not contain literal back-tick characters.
field.replace('`', '') sql = '''UPDATE `table name` SET `{field}` = %s WHERE `ID` = 1'''.format(field=field)
* This is true for Python, which interpolates parameter values into the string before it submits the SQL query to the server. That’s not a real query parameter; a real parameter leaves the placeholder until after the query is parsed. Python doesn’t support that kind of parameter.