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

Question

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;"

Answer 1

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()