These days, I mostly program in PHP with MySQL as the database, which is just fine by me. I have had a long-time interest in MySQL (SQL in general), and after several steady months of programming in PHP, I must say I have really come into the language and developed a fondness for it. All that being said, I still enjoy using and learning the MySQL Shell in Python mode. As Database Developers, we often need the LAST INSERT ID value from a previous INSERT statement on a column that has the AUTO_INCREMENT attribute. MySQL Shell has a get_auto_increment_value() method we can call against a Shell object result and retrieve that value. Continue reading and see examples of the MySQL Shell get_auto_increment_value() method used in Python mode…
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.21
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 get_auto_increment_value() method simple example
Getting started, I’ll create a practice table containing 2 columns: an ‘id’ column with the AUTO_INCREMENT attribute and a ‘name’ column of type VARCHAR, using the MySQL Shell in \py mode. I’ll store the CREATE TABLE statement in a variable name ‘CREATE_TBL’ for easier processing:
CREATE TABLE auto_test(id INTEGER AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY at_id (id)) ENGINE=InnoDB"""
The CREATE TABLE command itself does not have a dedicated shell method (at the time of writing that I am aware of). In order to execute that type of MySQL command, we must call the sql() method against a session object, providing the MySQL statement as the argument:
Query OK, 0 rows affected (2.0548 sec)
With the table named, ‘auto_test’, I’ll pass in ‘auto_test’ as the parameter to the get_table() method, creating a working access handle on the table itself. We can see in calling the count() method against the ‘auto_test’ table object, that the ‘auto_test’ table as of now, is empty:
mysql-py [learning]> auto_test.count()
0
Using the insert() and values() methods, I’ll add a single row to the ‘auto_test’ table, chaining on the execute() method and completing the INSERT:
Query OK, 1 item affected (0.3200 sec)
Since the ‘id’ column has been created with the AUTO_INCREMENT attribute, we can retrieve the LAST_INSERT_ID value by executing the MySQL statement, SELECT LAST_INSERT_ID(). Again, we need to use the sql() method for this query:
mysql-py [learning]> last_insert_id
[
1
]
The output shows the value of 1 as the last inserted id. Confirming with a follow-up select(), we see that is correct:
+----+--------+
| id | name |
+----+--------+
| 1 | Joshua |
+----+--------+
1 row in set (0.0009 sec)
Inherently, there is nothing wrong or incorrect in doing this. However, there is another Shell type of way we can use.
Let’s insert another row into the ‘auto_test’ table:
However, this time around, we use the MySQL Shell get_auto_increment_value() method and retrieve the most recently inserted id:
mysql-py [learning]> last_insert_id
2
In querying with the select() method, we see the expected results:
+----+---------+
| id | name |
+----+---------+
| 1 | Joshua |
| 2 | Jessica |
+----+---------+
2 rows in set (0.0009 sec)
MySQL Shell get_auto_increment_value() method with multiple connections
Shown in the following example queries, we can see that the MySQL Shell get_auto_increment_value() method works in other sessions just as it should.
Let’s establish another connection, insert() a row, and then check the get_auto_increment_value():
mysql-py [learning]> auto_test2 = db.get_table('auto_test')
mysql-py [learning]> result2 = auto_test2.insert('name').values('Jeremy').execute()
mysql-py [learning]> sess_2_insert_id = result2.get_auto_increment_value()
mysql-py [learning]> sess_2_insert_id
3
Executing this select(), we can see the last inserted id is 3:
mysql-py [learning]> auto_test.select().execute()
+----+---------+
| id | name |
+----+---------+
| 1 | Joshua |
| 2 | Jessica |
| 3 | Jeremy |
+----+---------+
3 rows in set (0.0009 sec)
Now back in session 1, I’ll execute() a multi-valued insert() by chaining multiple values() methods together and assigning the results to a result object named ‘another’:
mysql-py [learning]> another.get_auto_increment_value()
4
In the query results above, the call to get_auto_increment_value() shows 4 as the last inserted id, which is for the ‘Malory’ row as that particular row is the first one in the multi-row insert:
+----+---------+
| id | name |
+----+---------+
| 1 | Joshua |
| 2 | Jessica |
| 3 | Jeremy |
| 4 | Malory |
| 5 | Manny |
+----+---------+
5 rows in set (0.0011 sec)
Semi-related: I mentioned in the opening paragraph that I program in PHP a great deal. Check out the post, PHP PDO lastInsertId() method with examples in MySQL, where I cover examples of retrieving the last insert id using PHP’s PDO.
Once again, the MySQL Shell is right on the mark, providing DBA’s and Developers the get_auto_increment_value() for these specific types of queries in which we need the value for that last inserted id value.
Recommended Reading
I’ve written several blog posts on MySQL Shell Python mode so do visit any of the below posts that you are interested in:
- Basic Data Analysis with MySQL Shell Python mode
- MySQL Shell Python mode for multiple ALTER TABLE statements – easily
- Exploring .count() and COUNT() – MySQL Shell Python mode
- Transaction in MySQL Shell – Python mode with examples
- CREATE TABLE using Python in the MySQL Shell – with examples.
- ALTER TABLE ADD COLUMN – MySQL Shell Python style
For in-depth information on the methods used in the post and other MySQL Shell Python mode related material, visit the online X DevAPI User Guide for MySQL Shell in Python Mode documentation.
As always, if you see any corrections in the code I need to make or improve on, please let me know via the comments below.
MySQL Shell is a fantastic, alternative environment in which to work with data. If you need to retrieve the last insert id from a MySQL column with the AUTO_INCREMENT attribute, try the get_auto_increment_value() method.
Like what you have read? See anything incorrect? Please comment below and thank you 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.
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, are 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.
One thought on “MySQL Shell get_auto_increment_value() method – Python mode”