51

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?

Esteban Küber
  • 36,388
  • 15
  • 79
  • 97
Adam Chetnik
  • 1,906
  • 5
  • 27
  • 38

5 Answers5

86

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

Esteban Küber
  • 36,388
  • 15
  • 79
  • 97
  • 7
    Paolo's answer is better. http://stackoverflow.com/questions/1307378/python-mysql-update-statement/1307413#1307413 – Esteban Küber Aug 20 '09 at 16:37
  • But this one will work with every backend. This version doesn't do any validation on the input, while Paolo's way will make sure to escape the variables content. – Esteban Küber Aug 20 '09 at 16:39
  • 7
    Do *not* do it this way. You leave yourself wide open to SQL injection attacks. Paulo has the right answer, because it ensures that the values are properly escaped before passing them to the db. – Daniel Roseman Aug 20 '09 at 19:19
  • 3
    Ripped off Paolo's answer, as I can't delete accepted answer. – Esteban Küber Aug 20 '09 at 23:20
  • the triple double-quotes is mandatory? If yes what is their purpose? – dios231 Sep 28 '18 at 15:40
  • 1
    @dios231 the triple quotes allow you to write multiline strings. If you were to print the `repr` of the above text it would be: `"\n UPDATE tblTableName\n SET Year=%s, Month=%s, Day=%s, Hour=%s, Minute=%s\n WHERE Server=%s\n"` – Esteban Küber Sep 30 '18 at 23:36
54

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.

Paolo Bergantino
  • 480,997
  • 81
  • 517
  • 436
23

Here is the correct way：

import MySQLdb

if __name__ == '__main__':
    connect = MySQLdb.connect(host="localhost", port=3306,
                              user="xxx", passwd="xxx", db='xxx', charset='utf8')

    cursor = connect.cursor()

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

    connect.commit()
    connect.close()

P.S. Don't forget connect.commit(), or it won't work

Little Roys
  • 5,383
  • 3
  • 30
  • 28
  • yes commit is needed. As in my case without commit, it was putting a lock on whole table and then definitely no query was working on that table. – Hammad Hassan Jun 13 '18 at 09:37
4

Neither of them worked for me for some reason.

I figured it out that for some reason python doesn't read %s. So use (?) instead of %S in you SQL Code.

And finally this worked for me.

   cursor.execute ("update tablename set columnName = (?) where ID = (?) ",("test4","4"))
   connect.commit()
SVK
  • 1,004
  • 11
  • 25
0

@Esteban Küber is absolutely right.

Maybe one additional hint for bloody beginners like me. If you speciify the variables with %s, you have to follow this principle for EVERY input value, which means for the SET-variables as well as for the WHERE-variables.

Otherwise, you will have to face a termination message like '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 '%s WHERE'

M. Straube
  • 15
  • 3