MySQLdb Autocommit's Pit

Today write a small function, inside to update the database with MYSQLDB, the statement is as follows

sql = "Update%s.account_operation set Status=1 where username= '%s '"% (allresdbinfos[' db '), username)

　　

After the variable is replaced, it looks like the following

Update suspects.account_operation set Status=1 where username= ' [email protected] '

　　

There is no problem with the statement, there is also a record of username as ' [email protected] ' in the database, and the manual execution is correct (status is updated to 1 correctly)

But the strange thing is that when you update with MYSQLDB in Python, it doesn't work.

The reason is that suspects.account_operation the InnoDB engine for this table, InnoDB is a transactional engine, and there is a autocommit variable that controls whether the transaction is committed automatically. InnoDB the default autocommit=1, which means that each statement is a transaction and will be automatically committed. However, if you set autocommit=0 to turn off auto-commit, we will need to commit manually. All updates prior to commit are within a transaction and will not take effect if not commit.

When you connect with a MySQL client, the autocommit=1 is automatically submitted. Interestingly, MySQLdb This library is autocommit=0 by default, so it needs to be submitted manually.

The following is a description of MySQLdb for autocommit, starting with version 1.2.0, autocommit is disabled by default.

Links: Http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away

My Data disappeared! (or won ' t go away!)

Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you is using InnoDB tables or some other type of the transactional table type, you'll need to do connection.commit () befor E Closing the connection, or else none of the your changes would be written to the database.

Conversely, you can also with Connection.rollback () to throw away any changes "ve made since the last commit.

Important Note:some SQL Statements--specifically DDL statements like CREATE TABLE--is non-transactional, so they can ' t is rolled back, and they cause pending transactions to commit.

The above is for InnoDB, for non-transactional engines such as MyISAM, there is no transaction concept, just update, autocommit is 0 or 1 has no effect.

MySQLdb Autocommit's Pit