PyMySQL - Rollback after executing multiple statements

Question

I have a MySQL (InnoDB) table that I need to truncate before inserting new data. However if the INSERT fails for any reason, I would like to rollback to the state before the truncation (i.e. the table cannot be empty).

Using Python 3.5.0 and PyMySQL 0.7.5, I have come up with the following.

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='xxx',
                             user='xxx',
                             password='xxx',
                             db='tmp',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit=False)

try:
    cursor = connection.cursor()
    cursor.execute("TRUNCATE TABLE `users`;")
    cursor.execute("ISERT INTO `users` (`email`, `password`) VALUES ('okok', 'foo');")
except:
    connection.rollback()
    connection.close()
    raise
else:
    connection.commit()
    connection.close()

Note that I have made a typo in the INSERT statement to make it fail on purpose, to check if the TRUNCATE could be rolled back. This is not the case, if the table contains data before I run this script, it ends up empty.

What am I doing wrong? Is it possible to rollback a TRUNCATE this way?

Answer 1

So, based on Arthur's answer, here is the script that works:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='xxx',
                             user='xxx',
                             password='xxx',
                             db='tmp',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit=False)

try:
    cursor = connection.cursor()
    cursor.execute("DELETE FROM `users`;")
    cursor.execute("INSERT INTO `users` (`email`, `password`) VALUES ('okok', 'foo');")
except:
    connection.rollback()
    connection.close()
    raise
else:
    connection.commit()
    connection.close()

Again, thank you!