Adam Chetnik Adam Chetnik - 1 year ago 73
MySQL Question

Python MYSQL update statement

I'm trying to get this Python MYSQL update statement correct(With Variables):

cursor.execute ("UPDATE tblTableName SET Year=%s" % Year ", Month=%s" % Month ", Day=%s" % Day ", Hour=%s" % Hour ", Minute=%s" Minute "WHERE Server=%s " % ServerID)


Any ideas where I'm going wrong?

Answer Source

It should be:

cursor.execute ("""
   UPDATE tblTableName
   SET Year=%s, Month=%s, Day=%s, Hour=%s, Minute=%s
   WHERE Server=%s
""", (Year, Month, Day, Hour, Minute, ServerID))

You can also do it with basic string manipulation,

cursor.execute ("UPDATE tblTableName SET Year=%s, Month=%s, Day=%s, Hour=%s, Minute=%s WHERE Server='%s' " % (Year, Month, Day, Hour, Minute, ServerID))

but this way is discouraged because it leaves you open for SQL Injection. As it's so easy (and similar) to do it the right waytm. Do it correctly.

The only thing you should be careful, is that some database backends don't follow the same convention for string replacement (SQLite comes to mind).