Retrieving a table count of rows using the COUNT(*) aggregate function in MySQL is a straight-forward query. What if I told you that with the MySQL Shell, there are actually 3 possible ways – between 2 Shell modes (\sql and \py) – to retrieve a table row count? Would you be interested in knowing about them? Honestly, one of the queries will not surprise you in the least bit, as you are likely already using it. However, the other 2 queries – in this context – are specific to MySQL Shell Python mode. You can likely execute these queries in Javascript Mode. However, I am not versed in Javascript programming nor MySQL Shell Javascript mode so those queries are not covered here. Continue reading to see the example queries in Python mode…
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.20
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!
I am using the ‘film’ table from the Sakila Sample Database for many of the example queries.
As I mentioned in the opening paragraph, one form of this particular query is nothing special. You have written this query numerous times yourself:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl sakila SQL > SELECT COUNT(*) FROM film; +----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set (0.0014 sec) |
However, in MySQL Shell Python mode (\py) the same query changes slightly (syntax-wise).
To isolate just the ‘film’ table, I’ll call the get_table() method supplying ‘film’ as the table name argument:
1 | MySQL localhost:33060+ ssl sakila Py > film = db.get_table('film') |
To my surprise (I don’t know why; the MySQL Shell is downright phenomenal IMO), the COUNT() aggregate function can be passed into the select() method as a lone argument:
1 | MySQL localhost:33060+ ssl sakila Py > rows = film.select('COUNT(*)').execute() |
Let’s see what value that ‘rows’ object has:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl sakila Py > rows +----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set (0.0014 sec) |
Same query results as those returned from the first query executed.
It doesn’t stop there either. There is more. The in-built Table class count() method (appropriately named) provides the same functionality.
In this example, I simply call count() on the ‘film’ object, again retrieving the tables’ row count:
1 2 | MySQL localhost:33060+ ssl sakila Py > count_rows = film.count() [ccen lang="python" no_links="true"] |
MySQL localhost:33060+ ssl sakila Py > count_rows 1000 [/ccen]
The count() method returns an accurate count of rows in the ‘film’ table by chaining the ‘film’ Shell object and count() method together.
Be Mindful of Nulls…
We know that the COUNT() aggregate function counts total rows. However, when a column name is used as an argument instead of (*), COUNT() ignores any NULL values. Does MySQL Shell Python mode conform to the same behavior when COUNT(col_value) is used in the select() method?
Let’s see…
I have this data in a fictitious ‘friends’ table in another database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL localhost:33060+ ssl learning Py > friends = db.get_table('friends') MySQL localhost:33060+ ssl learning Py > friends_rows = friends.select().execute() MySQL localhost:33060+ ssl learning Py > friends_rows +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | k | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | NULL | 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 | NULL | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0009 sec) |
Notice in the following queries, the select() method accounts for the NULL marker difference between simply counting the total rows of the table, and counting the actual values of the ‘state’ column (which has 2 NULL values):
1 2 3 4 5 6 7 8 | MySQL localhost:33060+ ssl learning Py > friends_rows_count = friends.select('COUNT(*)').execute() MySQL localhost:33060+ ssl learning Py > friends_rows_count +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.0047 sec) |
1 2 3 4 5 6 7 8 | MySQL localhost:33060+ ssl learning Py > friends_state_count = friends.select('COUNT(state)').execute() MySQL localhost:33060+ ssl learning Py > friends_state_count +----------------+ | COUNT(`state`) | +----------------+ | 5 | +----------------+ 1 row in set (0.0010 sec) |
The same functionality as is expected with COUNT() used as in the manner demonstrated here.
Just in case you are not aware, MySQL Shell rocks! It is a fantastic and fun environment in which to work with data. Even though I am more of an SQL Developer/Backend programmer per se, should I ever have to manage a MySQL database from a DBA’s perspective, there is no question to which working environment I would use for versions >= 8.0.
How are you using the MySQL Shell Python mode in your workflow? Tell me all about it in the comments below.
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.