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?

Esteban Küber · Accepted Answer · 2012-01-26 16:08:50Z

87

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 way^tm. 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).

edited Jan 26, 2012 at 16:08

answered Aug 20, 2009 at 16:33

Esteban Küber

37k15 gold badges82 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Esteban Küber Over a year ago

Paolo's answer is better. stackoverflow.com/questions/1307378/…

Esteban Küber Over a year ago

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.

Daniel Roseman Over a year ago

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.

Esteban Küber Over a year ago

Ripped off Paolo's answer, as I can't delete accepted answer.

Esteban Küber Over a year ago

@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"

|

Paolo Bergantino · Accepted Answer · 2009-08-20 16:35:46Z

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.

answered Aug 20, 2009 at 16:35

Paolo Bergantino

490k83 gold badges523 silver badges437 bronze badges

2 Comments

lolu Over a year ago

does it matter if I use """ update tblname ...""".format(var1, var2) instead?

Akhilesh_IN Over a year ago

is there a way to find the message rows affected message which we can see in MySQL workbench

Little Roys · Accepted Answer · 2018-05-18 07:49:17Z

24

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

edited May 18, 2018 at 7:49

answered Aug 6, 2016 at 8:20

Little Roys

5,8593 gold badges32 silver badges29 bronze badges

1 Comment

Hammad Hassan Over a year ago

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.

SVK · Accepted Answer · 2018-08-01 17:48:58Z

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

edited Aug 1, 2018 at 17:48

answered Aug 17, 2016 at 18:40

SVK

1,03413 silver badges25 bronze badges

1 Comment

BarathVutukuri Over a year ago

Your answer is wrong. You have to commit on connection, not on cursor. connection.commit() will do.

M. Straube · Accepted Answer · 2018-08-20 12:19:44Z

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'

answered Aug 20, 2018 at 12:19

M. Straube

154 bronze badges

Collectives™ on Stack Overflow

Python MYSQL update statement

5 Answers 5

6 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related