About MySQLdb conn.autocommit(True)

Question

I have installed python 2.7 64bit,MySQL-python-1.2.3.win-amd64-py2.7.exe.

I use the following code to insert data :

class postcon:
    def POST(self):
        conn=MySQLdb.connect(host="localhost",user="root",passwd="mysql",db="dang",charset="utf8")  
        cursor = conn.cursor()
        n = cursor.execute("insert into d_message (mid,title,content,image) values(2,'xx','ccc','fff')")
        cursor.close()
        conn.close()
        if n:
            raise web.seeother('/')

This results in printing n as 1, but in mysql client data aren't visible.

google says I must add conn.autocommit(True).

but I don't know why MySQLdb turns it off;

possible duplicate of [Database does not update automatically with MySQL and Python](http://stackoverflow.com/questions/384228/database-does-not-update-automatically-with-mysql-and-python) — , Aug 21 '12 at 16:47
@chidan autocommit = false it the default setting of MySQLdb. — Allan Ruin, Mar 29 '14 at 14:38

MikA · Answer 1 · 2019-01-09T09:19:11.587

17

by default MySQLdb autocommit is false,

You can set autocommit to True in your MySQLdb connection like this,

conn=MySQLdb.connect(host="localhost",user="root",passwd="mysql",db="dang",charset="utf8")
conn.get_autocommit()        #will return **False**
conn.autocommit(True)
conn.get_autocommit()        #Should return **True** now
cursor = conn.cursor()

edited Jan 09 '19 at 09:19

answered Jul 10 '17 at 09:41

MikA

5,184
5
33
42

I have enabled autocommit. Here is the code : db = MySQLdb.connect(host=host, port=port, user=username, passwd=password, db=database, cursorclass=MySQLdb.cursors.DictCursor) db.autocommit = True cursor = db.cursor() But it doesnt work either – Kiran May 01 '18 at 11:43
1

Thanks a lot, this is what I was looking for. I'm new to Python, and was not aware Python disables autocommit according to DB-API standard. – Anch0rman Aug 08 '18 at 16:25
2

This does not seems to be the case, `autocommit` is a method (tested with *mysqlclient 1.3.12* and *MySQL-python 1.2.3*). So `conn.autocommit(True)` is the correct answer here. `conn.autocommit = True` will just overwrite the method attribute without enabling it – Romuald Brunet Jan 04 '19 at 09:32
This solved my issue, even though my problem was quite different. I connect to the DB, after that a different process inserts new entries in a table, and then my script would try to fetch the entries, but nothing came back even though the DB had the record. With autocommit set to True, my cursor now is able to fetch the rows that are added from other processes after the connection starts. That was an unexpected behaviour, I'm glad this solved it. Thank you very much! – msb Oct 20 '20 at 23:05

score 6 · Accepted Answer · answered Aug 21 '12 at 16:45

I don't know if there's a specific reason to use autocommit with GAE (assuming you are using it). Otherwise, you can just manually commit.

class postcon:
    def POST(self):
        conn=MySQLdb.connect(host="localhost",user="root",passwd="mysql",db="dang",charset="utf8")  
        cursor = conn.cursor()
        n = cursor.execute("insert into d_message (mid,title,content,image) values(2,'xx','ccc','fff')")
        conn.commit() # This right here
        cursor.close()
        conn.close()
        if n:
            raise web.seeother('/')

Note that you probably should check if the insert happened successfully, and if not, rollback the commit.

score 1 · Answer 3 · answered Jan 21 '21 at 19:19

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)

or

import mysql.connector
db = mysql.connector.connect(option_files='my.conf', autocommit=True)

Or call conn.commit() before calling close.

About MySQLdb conn.autocommit(True)

3 Answers3

Connector/Python Connection Arguments

Linked

Related