Skip to content

Instantly share code, notes, and snippets.

@ianfoo
Created February 10, 2023 01:17
Show Gist options
  • Save ianfoo/23681b841723e15fd4e2f295eaf7cfec to your computer and use it in GitHub Desktop.
Save ianfoo/23681b841723e15fd4e2f295eaf7cfec to your computer and use it in GitHub Desktop.
MySQL insert transaction with Python
➜ ~ 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 |
+-----------+
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment