Pyodbc SQL CRUD – Delete: Examples with MySQL

So far in the Pyodbc CRUD series, we’ve seen examples of how to: 1)Create new rows of data with INSERT 2)Read some data with SELECT 3)Modify existing data with UPDATE. Rounding out the CRUD operations is perhaps the most powerful command in SQL: DELETE. When it’s time for those rows of data to disappear, DELETE comes in swinging, makes it happen, and doesn’t look back. It’s been said uncountable times before, but, it is always worth repeating; DELETE completely removes data. Enough with the antics, let’s see it in action using pyodbc…

blur of colors passing quickly by
Photo by Michael Dziedzic on Unsplash

OS, Software, and DB used:

  • OpenSuse Leap 15.1
  • MySQL 8.0.18
  • pyodbc 4.0.28

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!

Pyodbc CRUD Series

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

As in previous posts, I’ll use these connection handlers for the MySQL database interactions with pyodbc:

1
2
3
4
5
6
7
In [1]: import pyodbc                                                                                    

In [2]: conn = pyodbc.connect("""DRIVER={MySQL ODBC 8.0 Unicode Driver};
   ...:     SERVER=localhost;DATBASE=learning;USER=my_user;PASSWORD=my_password;""")
   ...: cur = conn.cursor()

In [3]: cur.execute("USE learning;")

The current data set present in the ‘friends’ table is shown below:

1
2
3
4
5
6
7
8
9
10
11
In [5]: friends = cur.execute("""SELECT * FROM friends;""").fetchall()                                                                                        

In [6]: for friend in friends:
   ...:     print(friend)
   ...:                                                                                                                                                      
('USA', 'Jim', 'Dandy', None, '476-111-1122', datetime.date(1977, 7, 19), 42)
('USA', 'Max', 'Maxer', None, '398-392-5656', datetime.date(1975, 1, 23), 44)
('CAN', 'Mary', 'Murphy', None, '212-543-9420', datetime.date(1978, 3, 23), 41)
('USA', 'Charlie', 'Charles', None, '888-767-2323', datetime.date(1971, 8, 22), 48)
('USA', 'Humpty', 'Dumpty', None, '118-257-7344', datetime.date(1971, 11, 22), 48)
('USA', 'Roger', 'Dodger', None, '234-767-3983', datetime.date(1975, 8, 22), 44)

Let’s see what the column names are in order to provide specific filter criteria to the WHERE clause:

1
2
3
4
5
6
7
8
9
10
11
12
In [7]: cols = cur.columns(table='friends')                                                                                                                  

In [12]: for name in cols:
    ...:     print(name.column_name)
    ...:                                                                                                                                                      
country
first_name
last_name
state
phone_num
birthday
age

I’ll go ahead and DELETE the row with the age value of 42. Like always, since we are passing in user input – albeit it is mine – directly to the database in the form of an SQL statement, establishing a practice of using parameterized queries, is a good habit to keep.

1
2
3
4
5
6
7
8
9
10
11
12
In [13]: age_value = 42                                                                                                                                        

In [14]: try:
   ...:     conn.autocommit = False
   ...:     cur.execute("""DELETE FROM friends WHERE age = ?;""", age_value)
   ...: except pyodbc.DatabaseError as e:
   ...:     raise e
   ...:     cur.rollback()
   ...: else:
   ...:     cur.commit()
   ...: finally:
   ...:     conn.autocommit = True

No errors after the statement executed. Let’s verify that row was removed with this query:

1
2
3
4
5
6
7
8
9
10
In [15]: friends = cur.execute("""SELECT * FROM friends;""").fetchall()                                                                                        

In [16]: for friend in friends:
   ...:     print(friend.first_name, friend.age)
   ...:                                                                                                                                                      
Max 44
Mary 41
Charlie 48
Humpty 48
Roger 44

Based on the query results, we can see that the row with the ‘age’ value of 42 has been deleted from the ‘friends’ table.

As in this simple example, the key to controlling DELETE lies in filtering the target rows using the WHERE clause. Had there been no WHERE clause in this query, all rows would have been removed from the table.

Familiarity with CRUD operations can only help if you plan to use the Pyodbc driver for interactions with a database. Now that you have a solid understanding of them, you can explore all of the many advanced operations that database drivers provide to enhance your workflow, becoming more proficient in whatever your tasks might happen to be.

I hope you have enjoyed the series overall and if you have any questions, suggestions, or simply would like to comment on any of the posts, please do so in the comments below. Thank you so much 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.

Hey thanks for commenting! Leave a Reply

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