Python MySQLdb update query fails

Question

Okay. I've built here a mysql query browser, like navicat. Using MySQLdb to perform queries.

Here's the weird part. When i run the query through the program(using MySQLdb), it gives me success, affected rows = 1, but when i look at it in phpmyadmin, the value hasn't changed.

so before i perform the query, i print it out, copy and paste into phpmyadmin's query window, hit go and it works. So long story short, update query isn't working, but when i copy and paste into phpmyadmin, it works.

self.tbl.sql.use(self.tbl.database) # switches to correct database. I've printed this and it uses the corrected db
if self.tbl.sql.execute(query) == True:
 print sql_obj.rows_affected() # returns 1 (since i only do 1 query)

And here's the part of the SQL class

def execute(self, query):
 try:
 self.cursor.execute(query)
 return True
 except MySQLdb.ProgrammingError as error:
 print "---->SQL Error: %s" % error
 return False
 except MySQLdb.IntegrityError as e:
 print "--->SQL Error: %s" % e 
 return False

So any ideas what could be happening?

Answer 1

I believe @Jason Creighton and @S.Lott are correct.

At least if the table that you're updating is on a transactional storage engine. InnoDB is transactional, ISAM is not.

You either have to call commit() on your connection object before closing it, or you must set the connection to autocommit mode. I am not sure how you do that for a MySQLdb connection, I guess you either set an argument to the connection constructor, or set a property after creating the connection object.

Something like:

conn = mysql.connection(host, port, autocommit=True)
# or
conn = mysql.connection(host, port)
conn.autocommit(True)

• sqram: Ahh i see. Yes this was the problem. Although you did post the answer answer straight forward, I will have to mark Jason's as the accepted one, as he answered first, with a link to the solution. Thanks very much though, I will give it 1+! :)
• Alex Martelli: Yep! Your second guess is the right one -- conn.autocommit(True) works (though explicit commits are still better;-).

Answer 2

Just a guess: Perhaps the code in Python is running within a transaction, and the transaction might need to be explicitly committed?

Edit: There's an entry in the MySQLdb FAQ that might be relevant.

• S.Lott: +1: No commit means the change is invisible to everyone else.