1. Home
2. MySQL Connector/Python Tutorial
3. Handling Transactions with Connector/Python

Handling Transactions with Connector/Python

Last updated on July 27, 2020

Transactions #

A transaction is a way to execute a set of SQL statements such that either all of the statements execute successfully or none at all. In other words, all the statements in a transaction are executed as a single unit. If any one of the statements in a transaction fails, then the database is rolled back to the point at which transaction began.

Note: In MySQL, only InnoDB table supports transaction.

autocommit mode in MySQL #

By default, whenever you execute a SQL statement, MySQL immediately writes the result of the statement to the database. Another way to think about this is that the MySQL executes each statement as a separate transaction which implicitly starts when you execute the statement and ends as soon as the statement is done executing. This behavior is called the autocommit mode.

To view the current autocommit setting, type the following in the MySQL shell:

1
2
3
4
5
6
7
8
9

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

mysql>

As you can see, the @@autocommit system variable is set to 1 which means that the autocommit mode is enabled.

If you disable the autocommit mode then the SQL statements that follow will be part of a transaction, and you will have to commit them manually using the COMMIT statement before the MySQL session ends, otherwise, the changes made by the statements will be rolled back.

To disable the autocommit mode set AUTOCOMMIT to 0, as follows:

1
2

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

To verify the changes, let's print the value of @@autocommit system variable again.

1
2
3
4
5
6
7
8
9

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql>

The autocommit mode is now disabled. From now on, you will have to explicitly commit the statements before the MySQL session ends. If you fail to do so, changes made by the statements will not be saved into the database.

Let's see this behavior in action:

Right now we have 4080 rows in the city table.

1
2
3
4
5
6
7

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4080 |
+----------+
1 row in set (0.00 sec)

Let's now see what would happen if we try to execute a SQL statement which deletes all the rows from the city table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> 
mysql> delete from city;
Query OK, 4080 rows affected (0.30 sec)

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>

It looks like all the rows from the city table is deleted. However, this is not case as we haven't committed the transaction (via the COMMIT statement) yet.

Quit the current MySQL session by hitting Ctrl+D or typing exit.

1
2
3
4

mysql> 
mysql> exit
Bye 
$

Relaunch the mysql-client and query the city table again.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

$ mysql -u root -p
Enter password: 
mysql> 
mysql> use world
Database changed
mysql> 
mysql> 
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4080 |
+----------+
1 row in set (0.00 sec)

mysql>

As expected, no rows are deleted from the city table. However, if we hadn't disabled the autocommit mode (SET AUTOCOMMIT=0) before executing the DELETE statement, all the rows from the city table would have been deleted.

Also, when we restart a new session the autocommit mode is again turned on. Thus, again individual SQL statements will be executed in a separate transaction.

Now, you know how the autocommit works, let's now shift our attention to how MySQL Connector/Python deals with the transaction.

Performing Transactions with MySQL Connector/Python #

By default, Connector/Python turns the autocommit mode off. This is the reason, why we had to use the commit() method of the connection object to commit the changes. You can also use the rollback() method of the connection object to rollback the transaction.

To enable the autocommit mode set autocommit argument of the connection object to True.

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

The following listing demonstrates the behavior of Connector/Python when autocommit=True.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

import mysql.connector
from mysql.connector import errors

try:

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

    cursor = db.cursor()

    sql1 = """
    create table employees(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary INT NOT NULL
    )
    """

    cursor.execute(sql1)

    sql2 = """
    insert into employees(name, salary) value('John', 15000)
    """

    cursor.execute(sql2)

    sql2 = """
    insert into employees(name, salary) value('Bob', 20000)
    """

    cursor.execute(sql2)

    # db.commit()  # we don't need to call commit() because autocommit=True

    print('Table created successfully.')

except errors.Error as e:
    print(e)

finally:
    cursor.close()
    db.close()

Expected Output:

Table created successfully.

This script creates an employees table and inserts two rows into it. Since we have set autocommit=True, we don't need to call the commit() method manually to commit the changes.

If you look at the employees table, it should look like this:

1
2
3
4
5
6
7
8

mysql> select * from employees;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | John |  15000 |
|  2 | Bob  |  20000 |
+----+------+--------+
2 rows in set (0.00 sec)

Now let's see what would happen if we try to execute the same script with autocommit=False.

First off, drop the employees table using the DROP statement:

1
2

mysql> drop table employees;
Query OK, 0 rows affected (0.54 sec)

Next, set the autocommit=False and run the script again.

This time you will get the same output as before. However, if you look at the world database you will find that the employees table is still gets created but without any rows.

This happens because DDL statements such as CREATE, DROP, TRUNCATE etc. always perform an implicit commit. This behavior is not just specific to MySQL, in fact, Oracle does the same thing. For this reason, you should not try to include such statements in a transaction.

start_transaction() method #

If autocommit is enabled (i.e autocommit=True) and you want to execute a set of statements as a single unit, then you must explicitly start the transaction using the start_transaction() method of the connection object.

Once you have executed all the statements in a transaction you can either commit them by calling the commit() method or undo them by calling the rollback() method.

Once you have executed all the statements in a transaction you can commit them using the commit() method. To undo the changes call the rollback() method instead of commit().

If a transaction is already in progress then invoking start_transaction() raises ProgrammingError exception. You can check for an ongoing transaction using the in_transaction property. The in_transaction returns True, if a transaction is active, otherwise False.

The following listing demonstrates start_transaction() method in action.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

import mysql.connector
from mysql.connector import errors

try:

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

    cursor = db.cursor()

    db.start_transaction()

    # these two INSERT statements are executed as a single unit

    sql1 = """
    insert into employees(name, salary) value('Tom', 19000)
    """

    sql2 = """
    insert into employees(name, salary) value('Leo', 21000)
    """

    cursor.execute(sql1)
    cursor.execute(sql2)

    db.commit()  # commit changes

    print('Transaction committed.')

except errors.Error as e:
    db.rollback()  # rollback changes
    print("Rolling back ...")
    print(e)

finally:
    cursor.close()
    db.close()

Expected Output:

Transaction committed.

This script tries to execute two INSERT statement in a transaction, which means that either both of the statements execute successfully or none at all.

The start_transaction() method can also be used when autocommit is disabled to change the default behavior of the transaction. To learn more about options available visit the documentation.