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.

Lazykiddy · Accepted Answer · 2013-03-07 13:03:34Z

100

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 7, 2013 at 13:03

Lazykiddy

1,5352 gold badges12 silver badges18 bronze badges

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

Comments

Cyclone · Accepted Answer · 2015-05-12 01:14:47Z

29

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, 2015 at 1:14

answered Jun 14, 2014 at 4:48

Cyclone

15.4k23 gold badges88 silver badges116 bronze badges

1 Comment

Aaron Over a year ago

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?

Mohideen bin Mohammed · Accepted Answer · 2018-02-19 10:35:01Z

13

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, 2018 at 10:35

Mohideen bin Mohammed

20.4k11 gold badges121 silver badges131 bronze badges

answered Dec 26, 2017 at 9:29

Lex Bryan

7701 gold badge10 silver badges19 bronze badges

Collectives™ on Stack Overflow

Python mySQL Update, Working but not updating table

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related