MySQL insert transaction with Python

shell-log.txt

➜  ~ python /tmp/tx.py
(3, 'from python, at 2023-02-10 01:11:07.107806')
(4, 'from python, at 2023-02-10 01:11:07.108763')
(5, 'from python, at 2023-02-10 01:11:07.109734')
(6, 'from python, at 2023-02-10 01:11:07.110544')
(7, 'from python, at 2023-02-10 01:11:07.111276')
➜  ~ mysql -P 33306 -h 127.0.0.1 -u root -e "select * from test.foo;"
+----+--------------------------------------------+
| id | mapping                                    |
+----+--------------------------------------------+
|  3 | from python, at 2023-02-10 01:11:07.107806 |
|  4 | from python, at 2023-02-10 01:11:07.108763 |
|  5 | from python, at 2023-02-10 01:11:07.109734 |
|  6 | from python, at 2023-02-10 01:11:07.110544 |
|  7 | from python, at 2023-02-10 01:11:07.111276 |
+----+--------------------------------------------+
➜  ~ mysql -P 33306 -h 127.0.0.1 -u root -e "select version();"
+-----------+
| version() |
+-----------+
| 5.7.41    |
+-----------+

tx.py

from datetime import datetime
import mysql.connector

# SOCKET = '/tmp/mysql.sock'
connect_args = {
    'host': '127.0.0.1',
    'port': 33306,
    'user': 'root',
    'database': 'test',
}

cnx = mysql.connector.connect(**connect_args)

cursor = cnx.cursor()

# Start the transaction
cnx.start_transaction()

for i in range(5):
    # Execute the INSERT statement
    cursor.execute("INSERT INTO foo (mapping) "
                   "VALUES (%s)", (f'from python, at {datetime.now()}',))

    # Retrieve the inserted row
    cursor.execute("SELECT * FROM foo "
                   "WHERE id = LAST_INSERT_ID()")
    result = cursor.fetchone()
    print(result)

# Commit the transaction
cnx.commit()

# Close the cursor and the connection
cursor.close()
cnx.close()