Python mysql does not commit

Question

Having worked with other programming/scripting languages, I am completely baffled by Python's mysql interface (I am a bit new to Python) I am unable to commit my changes. I have stripped the code to the bare minimum:

#!/usr/bin/python

import mysql.connector as mariadb

mariadb_connection = mariadb.connect(
    user='testdb', 
    password='testdb',
    database='testdb',
    host='127.0.0.1',
    autocommit=True
)
mariadb_connection.autocommit=True
cursor = mariadb_connection.cursor(buffered=True)
cursor.execute( "UPDATE testdb SET descr='konijn' WHERE number=14549")
mariadb_connection.commit()
mariadb_connection.close()

I would have expected that the autocommit would do the trick, but it doesn't. Also, the mariadb_connection.commit() does nothing to commit the changes. The database is (for as much as relevant):

    number      INTEGER,
    type        VARCHAR(255),
    file        VARCHAR(255),
    year        INTEGER,
    month       INTEGER,
    descr       VARCHAR(4096)

I am able to commit changes using the same credentials using TCL/Tk, so it should not be a permission problem.

What am I doing wrong?

Answer 1

After a server reboot (unrelated to this), I am now unable to reproduce my problem. Everything works as expected. I would have loved to know what was wrong, but it seems to have been more a server/configuration problem than a programming issue.

For the benefit of those who stumble upon this question looking for wisdom (ahem) a small explanation of what I learned.

Contrary to the other languages I use (Perl, TCL) I use frequently, Python turns autocommit off by default. Also, the mysql CLI starts with autocommit on. But Python's PEP0249 states:

Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on

.

The problem is that if a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

So, you have three choices:

• Turn on autocommit
• Explicitly commit your changes
• Lose your data :-)

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

import mysql.connector as mariadb
connection = mariadb.connect(user='testdb', password='testdb',
    database='testdb', host='127.0.0.1',autocommit=True)

or separately:

connection.autocommit=True

Explicitly committing the changes is done with

connection.commit()

Note that the commit is done via the connection to the database, not via the cursor.

Additionally, I thought it might have been a locking issue, but with this script:

import mysql.connector as mariadb
number=input('->')
mariadb_connection = mariadb.connect(user='testdb', password='testdb',database='testdb', host='127.0.0.1')
cursor = mariadb_connection.cursor(buffered=True)
cursor.execute( "UPDATE testdb SET descr='konijntje' WHERE number=%s",(number,))
print 'rowcount',cursor.rowcount
number=input('->')
mariadb_connection.commit()
mariadb_connection.close()

I could verify that the second launch on the same record waits until the first releases the lock.

So thank you for your time, patience and reassurance that my code was not as wrong as I thought it was.