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!
If you have been reading my blog here in the last couple of months then you are aware that I’ve been in the CRUD. But, not in the bad kind of way. Not the muck. I’m referring to fundamental SQL CRUD: Create, Read, Update, and Delete operations. Be sure and visit any of the links in the closing section on these recent posts.
There is a new kid on the CRUD block. That’s right. The MySQL Shell has its own CRUD also. The difference is, you can perform this CRUD using any of 3 different languages: SQL, Javascript, and Python.
I’m well-versed in the first, not at all with the 2nd, and getting better with the 3rd so that is where I will focus, starting with the Create part of CRUD.
It’s no secret that in order for new rows of data to find their way into our tables, we use INSERT.
How does it look in the Shell with Python?
Good thing you asked because I have the answer. And, it is quite simple…
When you log in to the Shell using the mysqlx module, several objects are provided for you to use. One such object is a db object. The db object can then be used in combination with other available methods.
For example, by calling the python get_tables() method on the db object, you can retrieve all the present tables in the current database or schema:
1 2 3 4 | MySQL localhost:33060+ ssl learning Py > db.get_tables() [ <table:friends> ] |
To target a specific table, use the get_table() method, passing in the table name as a string you want to access. Here I assign the ‘friends’ table to a ‘friend’ object, then retrieve a count of the number of rows using the count() method:
1 2 3 | MySQL localhost:33060+ ssl learning Py > friend = db.get_table('friends') MySQL localhost:33060+ ssl learning Py > friend.count() 5 |
In order to INSERT some data, we need to know the column names. If you don’t know them, here is one way to find out using Python in the Shell. (Note: I am still learning about the Shell with Python and plan to blog about any handy meta-data methods I come across that would streamline this process. If you know of any, feel free to share in the comments below.)
Using the select() method on the ‘friend’ object, I am essentially getting all columns and rows of data from the table, unfiltered, and assigning that information to a ‘rows’ variable (object):
1 | MySQL localhost:33060+ ssl learning Py > rows = friend.select().execute() |
Then, I call the get_column_names() method on that ‘rows’ object, storing the column names in what turns out to be a Python list (by default I assume):
1 2 3 4 5 6 7 8 9 10 11 | MySQL localhost:33060+ ssl learning Py > cols = rows.get_column_names() MySQL localhost:33060+ ssl learning Py > cols [ "country", "first_name", "last_name", "state", "phone_num", "birthday", "age" ] |
Pretty cool!
Table class insert() method…
The Table class insert() method actually does the inserting in the Shell in Python mode. Used in conjunction with the values() method, again, you don’t have to write any SQL to carry out this operation. Since I already have the table columns names stored in the ‘cols’ list variable, I can supply it as the columns arguments to the insert() method versus naming each column individually. Then all that’s left to do is provide the column values you want stored for the row in the values() method and you’re good to go:
1 2 | MySQL localhost:33060+ ssl learning Py > friend.insert(cols).values('USA','Jim','Russ','K','424-060-3875','1975-05-05',44).execute() Query OK, 1 item affected (0.1957 sec) |
And as shown below, that row has been inserted:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl learning Py > rows = friend.select().execute() MySQL localhost:33060+ ssl learning Py > rows +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | NULL | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | NULL | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | NULL | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | NULL | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | NULL | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | K | 424-060-3875 | 1975-05-05 | 44 | +---------+------------+-----------+-------+--------------+------------+-----+ 6 rows in set (0.0009 sec) |
One thing to note is that without calling the execute() method, the insert() is not actually carried out.
Let’s use that knowledge, a python list, and a little trickery to carryout multiple INSERTs in one statement.
First, we’ll create 2 insert() objects, excluding the call to execute() on each of them:
1 2 | MySQL localhost:33060+ ssl learning Py > row_one = friend.insert(cols).values('USA','Jerome','Harper','C','321-232-9866','1976-09-01',43) MySQL localhost:33060+ ssl learning Py > row_two = friend.insert(cols).values('USA','Jupyter','Moonbeam','M','198-654-2827','1978-07-22',41) |
Then append both of those objects to a list:
1 2 3 | MySQL localhost:33060+ ssl learning Py > load_rows = [] MySQL localhost:33060+ ssl learning Py > load_rows.append(row_one) MySQL localhost:33060+ ssl learning Py > load_rows.append(row_two) |
Finally, iterate through the list in a for loop, calling execute() on each item:
1 2 3 | MySQL localhost:33060+ ssl learning Py > for row in load_rows: -> row.execute() Query OK, 1 item affected (0.1326 sec) |
Using the select() method we can see both rows have been added to the ‘friends’ table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MySQL localhost:33060+ ssl learning Py > friend.select().execute() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | NULL | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | NULL | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | NULL | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | NULL | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | NULL | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | K | 424-060-3875 | 1975-05-05 | 44 | | USA | Jerome | Harper | C | 321-232-9866 | 1976-09-01 | 43 | | USA | Jupyter | Moonbeam | M | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 8 rows in set (0.0008 sec) |
That’s pretty cool if I don’t say so myself. Do you know of any other ways to accomplish these multi-row inserts in the MySQL Shell with Python? Please share them in the comments below. I’d love to know of them.
As I mentioned above, I have plenty to say about SQL CRUD so do have a look at some of the blog posts I have written about it if you are so inclined.
SQL CRUD Basics Series
- SQL CRUD Basics Part 1 – Create
- SQL CRUD Basics Part 2 – Read
- SQL CRUD Basics Part 3 – Update
- SQL CRUD Basics Part 4 – Delete.
Pyodbc CRUD Series
Visit other posts that are part of the Pyodbc CRUD series:
- Pyodbc SQL CRUD – Create: Examples with MySQL
- Pyodbc SQL CRUD – Read: Examples with MySQL
- Pyodbc SQL CRUD – Update: Examples with MySQL
- Pyodbc SQL CRUD – Delete: Examples with MySQL
As someone who already uses – and values – the power of Python, with the ability to leverage it like this in an SQL environment is absolutely remarkable. To state the obvious that I am ecstatic is well, obvious.
Be sure and subscribe to the blog for the next part of the series in which we start reading the stored table data with MySQL Shell and Python, exploring the Read aspect of CRUD.
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.
4 thoughts on “MySQL Shell CRUD with Python: Create – with examples.”