Rollback multiple queries in PyMySQL

Question

A similar question to this has been posted but unfortunately none of the suggested solutions worked for me.

I wanted to see if I could write two queries in one try clause and have them both rollback if one of them threw an exception. In order to make sure an exception was thrown, I just tried to create the same table twice.

def function():
        try:
            cursor = connection.cursor()
                
            q1 = "CREATE TABLE 0_test_table (column1 varchar(255))"
            cursor.execute(q1)
            
            q1 = "CREATE TABLE 0_test_table (column1 varchar(255))"
            cursor.execute(q1)
                
        except pymysql.Error as e:
            print("Exception encountered")
            print(e)
            try:
                connection.rollback()
            except:
                print("rollback failed")
                pass
            
        else:
            connection.commit()
            connection.close()
                   
function()

Which printed:

Exception encountered
(1050, "Table '0_test_table' already exists")

Since the exception was thrown by the queries and then not by the rollback, I assumed it must have rolled back the changes. I checked the database and the table had been created. Why didn't the rollback work? This also begs the question as to what the commit method does. Since I never reached the else in my code, I never reached the commit method and yet things were clearly committed.

I attempted to adapt an example I found on the O'Reilly site here. This lead me to have:

def function():
    try:
        connection.begin()
        cursor = connection.cursor()
            
        q1 = "CREATE TABLE 0_test_table (column1 varchar(255))"
        cursor.execute(q1)
        
        q1 = "CREATE TABLE 0_test_table (column1 varchar(255))"
        cursor.execute(q1)
        
        cursor.close()
        connection.commit()
            
    except pymysql.Error as e:
        print("Exception encountered")
        print(e)
        try:
            connection.rollback()
        except:
            print("rollback failed")
            pass
               
function()

Which again printed:

Exception encountered
(1050, "Table '0_test_table' already exists")

And yet again the table had been created in the database again.

Answer 1

As you can see in the quote, A ROLLBACK of a CREATE TABLE can't be done.

"The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction."

see manual