Throughout the MySQL Shell CRUD with Python series, we’ve visited specific methods to 1) Populate tables with new data through the use of insert(). 2) Read and see the existing data with select(). 3) Modify the present data using update(). In this post, I’ll cover examples of removing rows of data in MySQL with the Python Shell delete() method. As with other DML commands, the affected rows are dependent upon using the where() method. Keep reading to learn how…
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!
MySQL Shell CRUD with Python Series
Be sure and visit the below posts in the series for a refresher on any of the CRUD methods covered so far:
- MySQL Shell CRUD with Python: Create – with examples
- MySQL Shell CRUD with Python: Read – with examples
- MySQL Shell CRUD With Python: Update – with examples
For the examples, I’ll use data from the fictitious ‘friends’ table that I have used previously throughout several CRUD-related posts. With the get_table() method, I can essentially get a handle on that table, interacting with it through the ‘friends’ object:
1 2 3 | MySQL localhost:33060+ ssl learning Py > friends = db.get_table('friends') MySQL localhost:33060+ ssl learning Py > friends <table:friends> |
We can see all present rows and all columns by calling an unfiltered select() method, understanding what data and meta-data are currently available:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MySQL localhost:33060+ ssl learning Py > friends.select() +---------+------------+-------------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-------------+-------+--------------+------------+-----+ | USA | Max | Maxer | K | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | K | 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 | | MEX | Jerome | Harper-Hall | C | 321-232-9866 | 1976-09-01 | 43 | | USA | Jupyter | Moonbeam | M | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-------------+-------+--------------+------------+-----+ 8 rows in set (0.0700 sec) |
Getting right to the topic at hand, suppose I want to remove any row that has an ‘age’ value of 43. That can be a test in the where() method, enabling just that row – or rows – to be deleted.
How does that command look in MySQL Shell CRUD with Python?
See the below section of code…
1 2 | MySQL localhost:33060+ ssl learning Py > friends.delete().where('age = 43').execute() Query OK, 1 item affected (0.2208 sec) |
I’ll run a simple select(), showing through the returned results, that the row with an age value of 43 has been removed:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl learning Py > friends.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | K | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | K | 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 | M | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0009 sec) |
That was quite simple in MySQL Shell Python mode just as you would expect. Yet, as always with UPDATE and DELETE, they have the potential to affect multiple – or all – rows in a table so use with them with the utmost of caution.
That wraps up the MySQL Shell CRUD with Python series. As always, I would like to know your thoughts on what has been covered as a whole through the comments below so please leave one if you would like. Thank you for reading!
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: Delete – with examples”