Python database transactions

Posted by Simon Walker on Sat 19 September 2015

pymysql

  • Defaults to autocommit=False
connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('insert into test (value) values (10)')
connection.close()

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('select value from test')
# => []

To commit changes to the database, #commit() must be called:

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('insert into test (value) values (10)')
# Call the commit line
connection.commit()
connection.close()

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('select value from test')
# => [(10, )]

With a context manager on a connection, an implicit transaction is opened:

with pymysql.connect(user='user', db='test') as cursor:
    cursor.execute('insert into test (value) values (10)')

with pymysql.connect(user='user', db='test') as cursor:
    cursor.execute('select value from test')
    # => [(10, )]

The connect function includes an autocommit parameter:

connection = pymysql.connect(user='user', db='test', autocommit=True)
cursor = connection.cursor()
cursor.execute('insert into test (value) values (10)')
connection.close()

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('select value from test')
# => [(10, )]

Multiple cursors can see any changes made within a transaction

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor2 = connection.cursor()
cursor.execute('insert into test (value) values (10)')
cursor2.execute('select value from test')
# => [(10, )]

Multiple connections are isolated from each other

connection = pymysql.connect(user='user', db='test')
connection2 = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor2 = connection2.cursor()
cursor.execute('insert into test (value) values (10)')
cursor2.execute('select value from test')
# => []

Using a connection as a context manager starts and commits a transaction, or rolls back on error

connection = pymysql.connect(user='user', db='test')
# Implicit transaction, and automatic #commit call
with connection as cursor:
    cursor.execute('insert into test (value) values (10)')
connection.close()

connection = pymysql.connect(user='user', db='test')
cursor = connection.cursor()
cursor.execute('select value from test')
# => [(10, )]

tags: python