Don’t forget the COMMIT in MySQL
Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you’ll have to commit (or roll back) your inserts, deletes or updates.
I’ve seen it a few times now with people being surprised that no data is going into the tables. It’s not so a silly problem in the end. If you are used to the defaults in MySQL you don’t have to commit anything since it is automatically done for you.
Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it’s good practice to be explicit and commit in your code. Remember the Zen of Python!
Here is just a small example to show it. Uses MySQL Connector/Python, but it should also work with the other MySQL database interfaces:
import mysql.connector
cnx = mysql.connector.connect(db='test')
cur = cnx.cursor()
cur.execute("""CREATE TABLE innodb_t1 (
id INT UNSIGNED NOT NULL,
c1 VARCHAR(128),
PRIMARY KEY (id)
) ENGINE=InnoDB""")
ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"
cur.execute(ins,
(1,'MySQL Support Team _is_ already the best',))
cnx.commit()
cur.close()
cnx.close()
Facebook
LinkedIn
Twitter
Hi Geert, you use COMMIT since the CREATE TABLE statements requires an implicit commit or would you use the commit also after an INSERT?
Thanks,
Mattia
The CREATE TABLE statement is just there to complete the example. You have to commit after an INSERT or any DML (like my post says).
Thanks for the reply. I’ve aslo seen that using cnx.set_autocommit(True) can be another option to use instead of repeatedly use the commit() function. Btw since InnoDB is a transactional engine it means that for every DML it appends ‘START TRANSACTION’ or am I missing something?
Thanks,
Mattia
@gerva
MySQL by default does autocommit, regardless of Storage engine used. For example, if you do an INSERT INTO.. It will commit it once you execute the statement.
The Python DB API 2.0 requires to have auto-commit turned off, so you need to commit every time (which is OK!). You can indeed force auto-commit back on in the Connector/Python, that’s up to you.
If you use InnoDB, you probably want transactions. BEGIN; UPDATE..; INSERT..; INSERT..; DELETE..; COMMIT; .. If you need that, you should _not_ turn on auto-commit, and do an explicit commit.
Remember Python Zen: “Explicit is better than implicit.”