Skip to content

Latest commit

 

History

History
81 lines (64 loc) · 2.09 KB

1.3_python_pymysql_notes.md

File metadata and controls

81 lines (64 loc) · 2.09 KB
Raw

Database transactions

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')
# => []