Python MYSQL update statement


Question

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?

1
39
8/21/2009 12:20:27 AM

Accepted Answer

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).

75
1/26/2012 4:08:50 PM

You've got the syntax all wrong:

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

For more, read the documentation.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon