How can I do begin transaction in pymysql ? (mysql)
Asked Answered
S

3

14

I want to use run below mysql script using with pymysql.

START TRANSACTION;
BEGIN;
insert into ~~~ 
COMMIT;

my python source code is

connection = pymysql.connect(~~~~~~~)
     with connection.cursor() as cursor :
         connection.begin()
         cursor.execute(~~.sql)
         connection.commit()
connection.close()

My question is "connection.begin()" is the same thing "START TRANSACTION; BEGIN;" ? I want to use "START TRANSACTION; BEGIN;"

Sungkiang answered 11/7, 2016 at 1:59 Comment(1)
According to the docs, START TRANSACTION and BEGIN both start a transaction, so use either of them, but not both in your query.Minnick
S
7

According to the PyMySQL docs/example (singular...this doesn't seem like a very well-supported package), by default auto-commit is off, so you do need to run connection.commit() to actually finish the transaction.

Their example:

import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save changes.
    connection.commit()

finally:
    connection.close()
Spooky answered 11/7, 2016 at 3:8 Comment(3)
Thank you for answering the question. I already used connection.commit. But sometimes, database is occured 'Lock wait timeout exceeded'. So I want to set transaction begin.Sungkiang
How does this work with MyISAM tables which don't have transactions to begin with? And what if we wanted to use a different transaction mode?Landsman
@Landsman I guess it doesn't work for MyISAM tables then. Regarding the transactional level, the connect method has an init_command argument that can be used to run initial SQL statements when connecting such as "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;".Minnick
D
0

pymysql.connections.Connection.begin

see manual for more info

Diarist answered 10/4, 2021 at 20:11 Comment(1)
All the manual currently has to offer for an explanation for the begin method is "Begin transaction.". Not very enlightening. Can you provide a minimal working example using begin to run a transaction?Minnick
M
0

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.

Minnick answered 21/8 at 14:11 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.