Here is an implementation using context managers that take care of closing the connection and the cursor. I find it a bit more readable with the explicit usage of the rollback
method:
with pymysql.connect(**credentials) as conn:
try:
with conn.cursor() as cursor:
# run queries here
conn.commit()
except:
conn.rollback()
raise
Here, credentials
is a dict with the connection details, e.g.
credentials = {'host': 'localhost', 'port': 3306, 'user': 'user', 'password':'password'}
As an example, let query01
insert some values into an initially empty table table
with fields a
and b
and query02
read them back to you:
query01 = "INSERT INTO table (a, b) VALUES (1, 2), (3,4);"
query02 = "SELECT * FROM table;"
Running both queries and then raising an error will rollback all changes:
with pymysql.connect(**credentials) as conn:
try:
with conn.cursor() as cursor:
cursor.execute(query01)
cursor.execute(query02)
print(cursor.fetchall()) # Returns ((1, 2), (3,4))
raise ValueError("Some error occured, revert changes.")
conn.commit()
except:
conn.rollback()
While query02
did return the values written to table
by query01
, the table is empty again after the rollback. Remove the line raising the ValueError
, then everything gets committed and the values remain in the table.
START TRANSACTION
andBEGIN
both start a transaction, so use either of them, but not both in your query. – Minnick