Database does not update automatically with MySQL and Python

Question

I'm having some trouble updating a row in a MySQL database. Here is the code I'm trying to run:

import MySQLdb

conn=MySQLdb.connect(host="localhost", user="root", passwd="pass", db="dbname")
cursor=conn.cursor()

cursor.execute("UPDATE compinfo SET Co_num=4 WHERE ID=100")
cursor.execute("SELECT Co_num FROM compinfo WHERE ID=100")
results = cursor.fetchall()

for row in results:
    print row[0]

print "Number of rows updated: %d" % cursor.rowcount

cursor.close()
conn.close()

The output I get when I run this program is:

4
Number of rows updated: 1

It seems like it's working but if I query the database from the MySQL command line interface (CLI) I find that it was not updated at all. However, if from the CLI I enter UPDATE compinfo SET Co_num=4 WHERE ID=100; the database is updated as expected.

What is my problem? I'm running Python 2.5.2 with MySQL 5.1.30 on a Windows box.

davidism · Accepted Answer · 2016-08-23 16:09:59Z

67

I am not certain, but I am going to guess you are using a INNODB table, and you haven't done a commit. I believe MySQLdb enable transactions automatically.

Call conn.commit() before calling close.

From the FAQ: Starting with 1.2.0, MySQLdb disables autocommit by default

edited Aug 23, 2016 at 16:09

davidism

128k31 gold badges417 silver badges348 bronze badges

answered Dec 21, 2008 at 8:08

Zoredache

39.9k9 gold badges50 silver badges63 bronze badges

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

1 Comment

Sergei Over a year ago

I find it handy cause you don't have to manage rollback manually if something goes wrong. In case you need multiple queries to update the database.

davidism · Accepted Answer · 2016-08-23 16:10:40Z

31

MySQLdb has autocommit off by default, which may be confusing at first. Your connection exists in its own transaction and you will not be able to see the changes you make from other connections until you commit that transaction.

You can either do conn.commit() after the update statement as others have pointed out, or disable this functionality altogether by setting conn.autocommit(True) right after you create the connection object.

edited Aug 23, 2016 at 16:10

davidism

128k31 gold badges417 silver badges348 bronze badges

answered Dec 21, 2008 at 13:19

ʞɔıu

48.7k36 gold badges109 silver badges155 bronze badges

4 Comments

MarkR Over a year ago

Setting conn.autocommit(true) after connecting means that it sends two superfluous commands to the server- it would be better if there was a way of avoiding them.

ʞɔıu Over a year ago

it would be better if there were such a way, I agree. but it's not like it's an expensive statement.

MarkR Over a year ago

Expensive is a matter of opinion... if your server is some distance away, you've still got two needless round-trips.

David Hall Over a year ago

I did an explicit conn.commit after a command, the rowcount said all was ok but looking at it outside of that app, it had failed. Setting it to autocommit did the trick. Don't know if this is a bug : but thanks ʞɔıu -- helped me a lot!!

muhuk · Accepted Answer · 2008-12-21 10:11:28Z

9

You need to commit changes manually or turn auto-commit on.

The reason SELECT returns the modified (but not persisted) data is because the connection is still in the same transaction.

answered Dec 21, 2008 at 10:11

muhuk

16.1k10 gold badges63 silver badges100 bronze badges

1 Comment

Tangwz Over a year ago

I want to know why I turned auto-commit on in mysql but need to conn.commit() too?

MarkR · Accepted Answer · 2008-12-21 10:53:43Z

3

I've found that Python's connector automatically turns autocommit off, and there doesn't appear to be any way to change this behaviour. Of course you can turn it back on, but then looking at the query logs, it stupidly does two pointless queries after connect to turn autocommit off then back on.

answered Dec 21, 2008 at 10:53

MarkR

63.8k15 gold badges120 silver badges156 bronze badges

Comments

urek mazino · Accepted Answer · 2022-09-23 21:19:14Z

2

Connector/Python Connection Arguments

Turning on autocommit can be done directly when you connect to a database:

import mysql.connector as db
conn = db.connect(host="localhost", user="root", passwd="pass", db="dbname", autocommit=True)

MySQLConnection.autocommit Property

Or separately:

import MySQLdb

conn = MySQLdb.connect(host="localhost", user="root", passwd="pass", db="dbname")
cursor = conn.cursor()
conn.get_autocommit()        # will return **False**
conn.autocommit(True)        # will make it True
conn.get_autocommit()        # Should return **True** now
cursor = conn.cursor()

Explicitly committing the changes is done with

conn.commit()

edited Sep 23, 2022 at 21:19

urek mazino

3073 silver badges17 bronze badges

answered Jan 21, 2021 at 14:29

Milovan Tomašević

8,9612 gold badges58 silver badges49 bronze badges

Comments

Youth overturn · Accepted Answer · 2021-08-16 06:52:27Z

0

I have to execute SET autocommit=true on my mysqlWorkbench app script

answered Aug 16, 2021 at 6:52

Youth overturn

4316 silver badges7 bronze badges

Collectives™ on Stack Overflow

Database does not update automatically with MySQL and Python

6 Answers 6

1 Comment

4 Comments

1 Comment

Comments

Connector/Python Connection Arguments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related