insert() method, demonstrating how simple it is to add new rows of data to a table using Python in the MySQL Shell. Now that the data is stored, if we want to retrieve any of it – for reading – we need to SELECT it, right? Luckily, there is a select() method available we can use in Python mode in the shell, making this operation relatively simple. But, as simple as it is, the power lies in the combinations of other similar class methods used for filtering. Interested? Keep reading…
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!
The focus for this post utilizes several of the TableSelect class methods.
Here is a description of the table used for these examples, which stores walking metrics data I keep up with as I work towards better health and a more manageable weight:
1 2 3 4 5 6 7 8 9 10 11 12 | MySQL localhost:33060+ ssl walking SQL > +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | day_walked | date | YES | | NULL | | | cal_burned | decimal(4,1) | YES | | NULL | | | miles_walked | decimal(4,2) | YES | | NULL | | | duration | time | YES | | NULL | | | mph | decimal(2,1) | YES | | NULL | | | shoe_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 6 rows in set (0.0028 sec) |
Switching to \py mode, we will create a table object using the get_table() method. Applying the count() method to the walking_stats variable, we can see there are 234 rows present in the ‘walking_stats’ table:
1 2 3 | MySQL localhost:33060+ ssl walking Py > walking_stats = db.get_table('walking_stats') MySQL localhost:33060+ ssl walking Py > walking_stats.count() 234 |
The ‘Read’ aspect of SQL CRUD is all about seeing the stored data. To do that, we use SELECT. In MySQL Shell Python mode, the select() method is available for this operation:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MySQL localhost:33060+ ssl walking Py > walking_stats.select().limit(10) +------------+------------+--------------+----------+-----+---------+ | day_walked | cal_burned | miles_walked | duration | mph | shoe_id | +------------+------------+--------------+----------+-----+---------+ | 2019-01-02 | 181.1 | 1.76 | 00:33:18 | 3.2 | 3 | | 2019-01-07 | 207.3 | 2.03 | 00:38:07 | 3.2 | 4 | | 2019-01-08 | 218.2 | 2.13 | 00:40:07 | 3.2 | 4 | | 2019-01-09 | 193.0 | 1.94 | 00:35:29 | 3.3 | 4 | | 2019-01-10 | 160.2 | 1.58 | 00:29:27 | 3.2 | 4 | | 2019-01-11 | 206.3 | 2.03 | 00:37:55 | 3.2 | 4 | | 2019-01-13 | 253.2 | 2.49 | 00:46:33 | 3.2 | 4 | | 2019-01-14 | 177.6 | 1.78 | 00:32:39 | 3.3 | 4 | | 2019-01-15 | 207.0 | 2.03 | 00:38:03 | 3.2 | 4 | | 2019-01-16 | 248.7 | 2.42 | 00:45:43 | 3.2 | 4 | +------------+------------+--------------+----------+-----+---------+ 10 rows in set (0.1160 sec) |
I realize I may have jumped ahead somewhat by using the limit() method in this query. However, I didn’t want to flood our screens with too many rows, so I chained it on in order to reduce the number of them displayed.
The point from this first example is that without naming specific columns in the select() method, all of them are returned in the query results.
I’ll change that by supplying the ‘day_walked’, ‘cal_burned’, and ‘duration’ columns as arguments to select(). You will also notice that in this query, we actually restrict the number of rows by filtering with the where() method:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('day_walked','cal_burned','duration').where('cal_burned < 175') +------------+------------+----------+ | day_walked | cal_burned | duration | +------------+------------+----------+ | 2019-01-10 | 160.2 | 00:29:27 | | 2019-01-28 | 170.2 | 00:31:17 | | 2019-02-22 | 164.0 | 00:30:09 | | 2019-03-21 | 140.6 | 00:25:51 | | 2019-04-03 | 152.7 | 00:28:04 | | 2019-04-18 | 160.2 | 00:29:27 | | 2019-04-23 | 111.4 | 00:20:29 | | 2019-05-12 | 152.1 | 00:27:58 | | 2019-05-17 | 164.4 | 00:30:14 | +------------+------------+----------+ 9 rows in set (0.0007 sec) |
The Shell where() method works just the same as the SQL WHERE clause, restricting rows based on a predicate test for truth.
One of the more interesting – and powerful – things is the ability to use some of the Python standard library methods in the MySQL shell while in \py mode.
In this next query, I use the format() method with the where() method, filtering out rows that do not have a ‘day_walked’ MONTHNAME() value of ‘November’:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('day_walked','duration').where("MONTHNAME(day_walked) = '{0}'".format('November')) +------------+----------+ | day_walked | duration | +------------+----------+ | 2019-11-01 | 01:07:37 | | 2019-11-03 | 01:07:13 | | 2019-11-04 | 01:07:39 | | 2019-11-06 | 01:05:47 | | 2019-11-08 | 01:05:06 | | 2019-11-10 | 01:07:06 | | 2019-11-12 | 01:05:46 | | 2019-11-13 | 01:07:00 | | 2019-11-14 | 01:06:57 | | 2019-11-15 | 01:05:12 | | 2019-11-17 | 01:05:34 | | 2019-11-18 | 01:07:25 | | 2019-11-19 | 01:08:00 | | 2019-11-21 | 01:07:59 | | 2019-11-24 | 01:05:06 | | 2019-11-25 | 01:04:44 | | 2019-11-27 | 01:05:33 | | 2019-11-29 | 01:03:48 | +------------+----------+ 18 rows in set (0.0013 sec) |
Oftentimes, you must accept – and use – input from users to filter SELECT query results. We know there is too much risk in sending off user-supplied input directly to the database.
Luckily the bind() method is available for such a scenario.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('day_walked','duration').where("MONTHNAME(day_walked) = :month_name").bind('month_name','November') +------------+----------+ | day_walked | duration | +------------+----------+ | 2019-11-01 | 01:07:37 | | 2019-11-03 | 01:07:13 | | 2019-11-04 | 01:07:39 | | 2019-11-06 | 01:05:47 | | 2019-11-08 | 01:05:06 | | 2019-11-10 | 01:07:06 | | 2019-11-12 | 01:05:46 | | 2019-11-13 | 01:07:00 | | 2019-11-14 | 01:06:57 | | 2019-11-15 | 01:05:12 | | 2019-11-17 | 01:05:34 | | 2019-11-18 | 01:07:25 | | 2019-11-19 | 01:08:00 | | 2019-11-21 | 01:07:59 | | 2019-11-24 | 01:05:06 | | 2019-11-25 | 01:04:44 | | 2019-11-27 | 01:05:33 | | 2019-11-29 | 01:03:48 | +------------+----------+ 18 rows in set (0.0013 sec) |
Multiple placeholders are allowed in one query, however, you must call bind() for each one of them:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('day_walked','duration').where("MONTHNAME(day_walked) = :month_name AND duration > :time_walked").bind('month_name','November').bind('time_walked','01:06:00') +------------+----------+ | day_walked | duration | +------------+----------+ | 2019-11-01 | 01:07:37 | | 2019-11-03 | 01:07:13 | | 2019-11-04 | 01:07:39 | | 2019-11-10 | 01:07:06 | | 2019-11-13 | 01:07:00 | | 2019-11-14 | 01:06:57 | | 2019-11-18 | 01:07:25 | | 2019-11-19 | 01:08:00 | | 2019-11-21 | 01:07:59 | +------------+----------+ 9 rows in set (0.1198 sec) |
More than simple selects…
MySQL shell in Python mode queries can be quite complex. Aggregation, limiting, and ordering are all supported just as you would expect in regular SQL.
In this query, I’ll SUM() the ‘miles_walked’ for each month. We can see GROUP BY is fully supported via the group_by() method:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('MONTHNAME(day_walked)','SUM(miles_walked)').group_by('MONTHNAME(day_walked)') +-------------------------+---------------------+ | MONTHNAME(`day_walked`) | SUM(`miles_walked`) | +-------------------------+---------------------+ | January | 47.43 | | February | 51.97 | | March | 42.34 | | April | 42.13 | | May | 43.30 | | June | 62.14 | | July | 83.75 | | August | 78.21 | | September | 66.39 | | October | 78.04 | | November | 63.74 | +-------------------------+---------------------+ 11 rows in set (0.0018 sec) |
It’s no secret that query results order is random and never guaranteed. Not to worry. The order_by() method works just as you would expect (even with default ASC ordering).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('MONTHNAME(day_walked)','SUM(miles_walked)').group_by('MONTHNAME(day_walked)').order_by('SUM(miles_walked)') +-------------------------+---------------------+ | MONTHNAME(`day_walked`) | SUM(`miles_walked`) | +-------------------------+---------------------+ | April | 42.13 | | March | 42.34 | | May | 43.30 | | January | 47.43 | | February | 51.97 | | June | 62.14 | | November | 63.74 | | September | 66.39 | | October | 78.04 | | August | 78.21 | | July | 83.75 | +-------------------------+---------------------+ 11 rows in set (0.0018 sec) |
Want the results ordered from greatest to least for the SUM() value? Just specify DESC and you’re all good:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('MONTHNAME(day_walked)','SUM(miles_walked)').group_by('MONTHNAME(day_walked)').order_by('SUM(miles_walked) DESC') +-------------------------+---------------------+ | MONTHNAME(`day_walked`) | SUM(`miles_walked`) | +-------------------------+---------------------+ | July | 83.75 | | August | 78.21 | | October | 78.04 | | September | 66.39 | | November | 63.74 | | June | 62.14 | | February | 51.97 | | January | 47.43 | | May | 43.30 | | March | 42.34 | | April | 42.13 | +-------------------------+---------------------+ 11 rows in set (0.0019 sec) |
As a totally irrelevant aside, how wild is it that the 2 months with the most ‘miles_walked’ values are the absolute hottest, hell-on-earth months of the year (I hate the heat). But, I somehow managed to do more walking in those months than the others. Go figure lol.
How can you retrieve just the top 3 results from that previous query? Using the LIMIT clause right?
Is it supported?
You bet it is:
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('MONTHNAME(day_walked)','SUM(miles_walked)').group_by('MONTHNAME(day_walked)').order_by('SUM(miles_walked) DESC').limit(3) +-------------------------+---------------------+ | MONTHNAME(`day_walked`) | SUM(`miles_walked`) | +-------------------------+---------------------+ | July | 83.75 | | August | 78.21 | | October | 78.04 | +-------------------------+---------------------+ 3 rows in set (0.0022 sec) |
We are also fully aware that in SQL if we want to filter the returned rows based on an aggregate – SUM() in this instance – the HAVING clause is used. And just as you would expect, there is a having() method for this type of query:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl walking Py > walking_stats.select('MONTHNAME(day_walked)','SUM(miles_walked)').group_by('MONTHNAME(day_walked)').having('SUM(miles_walked) < 50').order_by('SUM(miles_walked) DESC') +-------------------------+---------------------+ | MONTHNAME(`day_walked`) | SUM(`miles_walked`) | +-------------------------+---------------------+ | January | 47.43 | | May | 43.30 | | March | 42.34 | | April | 42.13 | +-------------------------+---------------------+ 4 rows in set (0.0021 sec) |
That’s right folks. It is all pretty much there. A new way – or better yet, another way – to retrieve data from tables in MySQL. All without one bit of SQL (not that I have anything against it by any stretch of the imagination).
Look for more MySQL Shell CRUD with Python to come. In part 3, we modify present data with UPDATE.
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.
2 thoughts on “MySQL Shell CRUD with Python: Read – with examples”