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.
