Python mySQL Update, Working but not updating table

Question

I have a python script which needs to update a mysql database, I have so far:

dbb = MySQLdb.connect(host="localhost", 
       user="user", 
       passwd="pass", 
       db="database") 
try:
   curb = dbb.cursor()
   curb.execute ("UPDATE RadioGroups SET CurrentState=1 WHERE RadioID=11")
   print "Row(s) were updated :" +  str(curb.rowcount)
   curb.close()
except MySQLdb.Error, e:
   print "query failed<br/>"
   print e

The script prints Row(s) were updated : with the correct number of rows which have a RadioID of 11. If I change the RadioID to another number not present in the table it will say Row(s) were updated :0. However the database doesn't actually update. The CurrentState field just stays the same. If I copy and past the SQL statement in to PHPMyAdmin it works fine.

score 99 · Accepted Answer · answered Mar 07 '13 at 13:03

99

use

dbb.commit()

after

curb.execute ("UPDATE RadioGroups SET CurrentState=1 WHERE RadioID=11")

to commit all the changes that you 'loaded' into the mysql server

answered Mar 07 '13 at 13:03

Lazykiddy

1,525
1
11
18

2

Thank You. This has fixed it – user2144306 Mar 07 '13 at 13:36

Cyclone · Answer 2 · 2015-05-12T01:14:47.290

28

As the @Lazykiddy pointed out, you have to commit your changes after you load them into the mysql.

You could also use this approach to enable the auto commit setting, just after the MySQL connection initialization:

dbb.autocommit(True)

Then, it will automatically commit the changes you made during your code execution.

edited May 12 '15 at 01:14

answered Jun 14 '14 at 04:48

Cyclone

14,839
23
82
114

3

This begs the question though, will this function make the database commit happen after every query kind, even SELECT that does not alter the table in any way? – Aaron Nov 27 '16 at 01:00

score 12 · Answer 3 · edited Feb 19 '18 at 10:35

12

the two answers are correct. However, you can also do this:

dbb = MySQLdb.connect(host="localhost", 
   user="user", 
   passwd="pass", 
   db="database",
   autocommit=True)

add autocommit=True

edited Feb 19 '18 at 10:35

Mohideen bin Mohammed

18,813
10
112
118

answered Dec 26 '17 at 09:29

Lex Bryan

750
1
9
18

1

great so simple – Mohideen bin Mohammed Feb 19 '18 at 10:34

Python mySQL Update, Working but not updating table

3 Answers3

Linked

Related