MySQL Shell CRUD with Python: Create – with examples.

I honestly feel like MySQL hit a home run with the release of version 8. Having included so many fantastic features, it is hard to single one out above the others. However, there is one – besides Window Functions – that is really a game-changer. To be exact, the one actually makes 3 (whatever kind of sense that makes). I am talking about the MySQL Shell, the Document Store, and the X DevAPI. The focus of this post is on the Shell itself. What is it about the Shell that intrigues me? I’m sure your curiosity is piqued so keep reading to find out about the functionality you simply cannot ignore…
hand holding a sea shell out
Photo by Matt Cannon 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!


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

Pyodbc CRUD Series

Visit other posts that are part of the Pyodbc CRUD series:


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.

Advertisements

Hey thanks for commenting! Leave a Reply

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