Error 1064 mysql in UPDATE function

Question

Guys I’m having a problem with the command on UPDATE, I’m using Python 3.8.5 and mysql 5.7.31.

value_id = 10
value_column = 'nome'
ID = 'rodrigo'


comando_sql = "UPDATE user SET %s='%s' WHERE id=%s"
value = (str(value_column),str(ID),str(value_id))

cursor.execute(comando_sql,value)
Database.commit()

But he makes that mistake: mysql.connector.errors.Programmingerror: 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 '''name'=''Rodrigo'' WHERE id='10'' at line 1

And the funny thing when I type cursor.execute("UPDATE user SET nome = 'rodrigo' WHERE id=10") he accepts without error.

Answer 1

I believe the problem lies in the formation of command with values.

Once you’ve tagged python-3.x in the question I would suggest using f-string as below:

value_id = 10
value_column = 'nome'
ID = 'rodrigo'

comando_sql = f"UPDATE user SET {value_column}='{ID}' WHERE id={value_id}"

cursor.execute(comando_sql)
Database.commit()

I hope it helps