PYTHON: Update MULTIPLE COLUMNS with python variables

Question

Hello everybody,

I'm trying to write a valid mysql statement that would allow me to update multiple columns in one record with values provided as python variables.

My statement would look like this:

db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="dbname")
cursor = db.cursor()
sql_update = "UPDATE table_name SET field1=%s, field2=%s, field3=%s, field4=%s, field5=%s, field6=%s, field7=%s, field8=%s, field9=%s, field10=%s WHERE id=%s" % (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, id)
cursor.execute(sql_update)
cursor.close ()
db.commit()
db.close()

While trying to execute the query I keep receiving information that there is an error in my SQL syntax. I can't locate it though. Please, maybe someone can point me my mistake or show me how it should be written?

Answer 1

You are using string formatting, while what you SHOULD be doing is using a parametrized query. Do it like this:

cursor.execute("UPDATE table_name SET field1=%s ... field10=%s WHERE id=%s", (var1,... var10, id))

Did you really need to post it with 10 variables? It was so frustrating to format, I gave up.

Answer 2

Maybe it's about apostrophes around string/VARCHAR values:

sql_update = "UPDATE table_name SET field1='%s', field2='%s', field3='%s', field4='%s', field5='%s', field6='%s', field7='%s', field8='%s', field9='%s', field10='%s' WHERE id='%s'" % (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, id)