Notes about pymysql connections

python_pymysql_notes.md

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

thanks for your sharing!

Can I find the connection as an attribute of a cursor?

Thanks

Thanks for sharing! A question: since multi-connections are isolated from each other, what should I do to let the 2nd connection get the changes done by the 1st connection? ( In my case, do a commit in conn 1 after insert, then select in conn 2 doesn't work; but do a commit in conn 2 before select works. It doesn't look like a good solution tho...) Many thanks!

Just wondering if any way to achieve

START TRANSACTION;
SELECT quantity FROM product WHERE product_id = 5 FOR UPDATE;
UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;
COMMIT;

Just wondering if any way to achieve

START TRANSACTION;
SELECT quantity FROM product WHERE product_id = 5 FOR UPDATE;
UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;
COMMIT;

Hi, you can connection.begit for this:

connection = pymysql.connect(user='user', db='test')
connection.begin()
connection.cursor().execute('SELECT quantity FROM product WHERE product_id = 5 FOR UPDATE;')
connection.cursor().execute('UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;')
connection.commit()
connection.close()

Thanks for sharing! A question: since multi-connections are isolated from each other, what should I do to let the 2nd connection get the changes done by the 1st connection? ( In my case, do a commit in conn 1 after insert, then select in conn 2 doesn't work; but do a commit in conn 2 before select works. It doesn't look like a good solution tho...) Many thanks!

You should commit() the insertion with first connection and you can access it with second connection without any problem.