Transaction in MySQL Shell – Python mode with examples

Although my day job has lately been filled primarily with MySQL and PHP development – which I absolutely enjoy – I still make spare time for MySQL and Python hacking; particularly in the MySQL Shell. For whatever reason, I am drawn back to this specific environment. SAVEPOINT, COMMIT, and START TRANSACTION are integral commands for working within a database transaction. MySQL Shell fully supports these commands. We can even execute them in Python mode! Continue reading to see them in action…

green fish net piled up
Photo by Jonas Jacobsson on Unsplash

OS, Software, and DB used:

  • OpenSuse Leap 15.1
  • MySQL 8.0.19

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

MySQL Shell Session Information

We need some session information for working in the shell with TRANSACTION‘s. The following transaction methods are all part of the Session class that is utilized in the example queries that follow:

  • start_transaction()
  • commit()
  • rollback()
  • rollback_to() – Requires a string argument of a named savepoint.
  • release_savepoint() – Requires a string argument of a named savepoint to release.
  • set_savepoint() – Requires a string argument of a savepoint to initiate.

I’ll store session connection information for easier processing in a variable, ‘my_sess’:

1
 MySQL  localhost:33060+ ssl  learning  Py > my_sess = db.get_session()

I am working in the ‘learning’ schema (or database), having 3 tables:

1
2
3
4
5
6
7
8
9
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.get_current_schema()
<schema:learning>

 MySQL  localhost:33060+ ssl  learning  Py > db.get_tables()
[
    <table:file_names>,
    <table:friends>,
    <table:name_dates>
]

The target table for these examples is a fictitious ‘friends’ table with this present data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 MySQL  localhost:33060+ ssl  learning  Py > friend = db.get_table('friends')
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | K     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | K     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | K     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | K     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | K     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | K     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | M     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.1207 sec)

I want to redo that…

Just like you execute START TRANSACTION; in a regular MySQL command-line session, you use the same-named start_transaction() method in Py mode, calling it on the (current) session. Below I use the ‘my_sess’ object variable to initiate a TRANSACTION:

1
2
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.start_transaction()
Query OK, 0 rows affected (0.0005 sec)

Next, I execute an UPDATE on the ‘state’ column row value(s):

1
2
3
4
 MySQL  localhost:33060+ ssl  learning  Py > friend.update().set('state','L').execute()
Query OK, 7 items affected (0.0011 sec)

Rows matched: 7  Changed: 7  Warnings: 0

(Tip: Read, MySQL Shell CRUD With Python: Update – with examples, to learn more about this staple CRUD operation in MySQL Shell Python mode.)

There was no WHERE clause present in the previous statement. Therefore, all ‘state’ column row values have been modified, shown in this select() method call result set:

1
2
3
4
5
6
7
8
9
10
11
12
13
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | L     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | L     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | L     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | L     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | L     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | L     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | L     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0007 sec)

Uh-oh. What if I meant to actually only target rows with an ‘age’ column value of 41? Oops…

But, I am in luck (this time at least)!. Since I am working within a TRANSACTION, I can undo those changes by calling the rollback() method:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.rollback()
Query OK, 0 rows affected (0.4368 sec)
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | K     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | K     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | K     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | K     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | K     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | K     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | M     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0008 sec)

Since no commit() was issued in the TRANSACTION, calling rollback() restored the ‘state’ column row values back to ‘K’ – their previous value.

Go ahead and store that…

Suppose as you perform work in the shell, you make changes to data that you wish to keep. But, you are working within a TRANSACTION. What do you do?
Call the commit() method:

1
2
3
4
5
6
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.start_transaction()
Query OK, 0 rows affected (0.0004 sec)
 MySQL  localhost:33060+ ssl  learning  Py > friend.update().set('state','L').execute()
Query OK, 7 items affected (0.0010 sec)

Rows matched: 7  Changed: 7  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.commit()
Query OK, 0 rows affected (0.1860 sec)
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | L     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | L     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | L     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | L     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | L     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | L     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | L     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0008 sec)

Be mindful that once commit() is executed, there is no undo. Those changes are now written to disk, for better or worse.

Named marker…

A SAVEPOINT is a named point in time, so to speak, that you can restore any changes back to while you work in a TRANSACTION. However, you must create a SAVEPOINT. SAVEPOINT‘s are not available unless you establish one.

How do you create a SAVEPOINT in MySQL Shell? With the set_savepoint() method. Unlike several of the other Session class methods we have seen so far, you must provide a string argument for the SAVEPOINT‘s name when executing set_savepoint().

I’ll make another modification to some data in the ‘friends’ table:

1
2
3
4
5
6
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.start_transaction()
Query OK, 0 rows affected (0.0005 sec)
 MySQL  localhost:33060+ ssl  learning  Py > friend.update().set('state','P').execute()
Query OK, 7 items affected (0.0010 sec)

Rows matched: 7  Changed: 7  Warnings: 0

I am content with that UPDATE and want to store those changes to disk. Yet, I still have more work to do. By creating the below ‘my_save’ SAVEPOINT, if I make other edits and need to revert the changes, I can basically restore the data to its state, at the named ‘my_save’ SAVEPOINT:

1
2
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.set_savepoint('my_save')
my_save

1
2
3
4
 MySQL  localhost:33060+ ssl  learning  Py > friend.update().set('state','A').execute()
Query OK, 7 items affected (0.0005 sec)

Rows matched: 7  Changed: 7  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | A     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | A     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | A     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | A     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | A     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | A     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | A     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0009 sec)

Recall in the previous update() method call, all row ‘state’ column values were set to ‘A’. Calling the rollback_to() method, all those values have been reset to ‘P’ (the column values at the ‘my_save’ SAVEPOINT state):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.rollback_to('my_save')
 MySQL  localhost:33060+ ssl  learning  Py > friend.select()
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA     | Max        | Maxer     | P     | 398-392-5656 | 1975-01-23 |  44 |
| CAN     | Mary       | Murphy    | P     | 212-543-9420 | 1978-03-23 |  41 |
| USA     | Charlie    | Charles   | P     | 888-767-2323 | 1971-08-22 |  48 |
| USA     | Humpty     | Dumpty    | P     | 118-257-7344 | 1971-11-22 |  48 |
| USA     | Roger      | Dodger    | P     | 234-767-3983 | 1975-08-22 |  44 |
| USA     | Jim        | Russ      | P     | 424-060-3875 | 1975-05-05 |  44 |
| USA     | Jupyter    | Moonbeam  | P     | 198-654-2827 | 1978-07-22 |  41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0008 sec)

Finishing up, we release (or remove) the SAVEPOINT and free up any resources, by calling the release_savepoint() method:

1
 MySQL  localhost:33060+ ssl  learning  Py > my_sess.release_savepoint('my_save')

Recommended Reading

Visit the listed resources below for more information and insight into SAVEPOINT‘s and transactions in MySQL:

Bonus: I highly recommend the book, Introducing MySQL Shell: Administration Made Easy with Python by Charles Bell (You can find it on Apress or Amazon). The book is simply fantastic and brings you up to speed on the MySQL Shell in general, but with more of a focus on Python. I continue to learn so much from this book. Pick it up!

I have said before but it is worth repeating: MySQL Shell in Python mode is a fantastic environment to work in with your database data. Give it a try!

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.