Pyodbc SQL CRUD – Update: Examples with MySQL

Likely, some of the data you store will remain unchanged. Yet, the majority of it changes rapidly depending on its nature and the purpose it serves. How do you modify data already present in your tables? We know that to CREATE new rows of data in a table, we employ INSERT, and to see the data on hand, SELECT is there. However, to change it, that is the job of the UPDATE command. Keep reading to see a couple of simple examples using the pyodbc Python driver…

pair of hands holding loose coins
Photo by Kat Yukawa 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:

In this post, I will use a fictitious ‘friends’ table for the examples. The structure and present data are shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
 MySQL  localhost:33060+ ssl  learning  SQL > DESC friends;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| country    | char(3)     | YES  |     | NULL    |       |
| first_name | varchar(25) | NO   |     | NULL    |       |
| last_name  | varchar(25) | NO   |     | NULL    |       |
| state      | char(2)     | YES  |     | NULL    |       |
| phone_num  | char(12)    | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.0028 sec)

1
2
3
4
5
6
7
8
9
10
11
12
 MySQL  localhost:33060+ ssl  learning  SQL > SELECT * FROM friends;
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num    | birthday   | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| NULL    | Jim        | Dandy     | NULL  | 476-111-1122 | 1977-07-19 |  42 |
| NULL    | Max        | Maxer     | NULL  | 398-392-5656 | 1975-01-23 |  44 |
| NULL    | Mary       | Moore     | NULL  | 212-543-9420 | 1978-03-23 |  41 |
| NULL    | Charlie    | Charles   | NULL  | 888-767-2323 | 1971-08-22 |  48 |
| NULL    | Humpty     | Dumpty    | NULL  | 118-257-7344 | 1971-11-22 |  48 |
| NULL    | Roger      | Dodger    | NULL  | 234-767-3983 | 1975-08-22 |  44 |
+---------+------------+-----------+-------+--------------+------------+-----+
6 rows in set (0.0008 sec)

I’ll also have the below connection handlers for the operations between Pyodbc and MySQL:

1
2
3
4
5
6
7
8
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;")                                                                    
Out[3]: <pyodbc.Cursor at 0x7f666d8f3cd8>

We will get straight into it. Let’s modify those NULL markers that are present in the ‘country’ column with something more meaningful. Since we are essentially accepting user input, we must use parameterized queries to help protect against SQL Injection.

We will also impose a sort of ‘safety net’ by performing the work within Python’s version of a TRANSACTION (in this particular context anyways). By setting the connection autocommit attribute to False, allows us the ability to perform a ROLLBACK automatically thanks to the except block, should we run into any exceptions during the UPDATE.

Below is the code to process the UPDATE for changing the ‘country’ column values:

1
2
3
4
5
6
7
8
9
10
11
12
In [4]: country = 'USA'                                                                                  

In [5]: try:
   ...:     conn.autocommit = False
   ...:     cur.execute("""UPDATE friends SET country = ?;""", country)
   ...: except pyodbc.DatabaseError as e:
   ...:     raise e
   ...:     cur.rollback()
   ...: else:
   ...:     cur.commit()
   ...: finally:
   ...:     conn.autocommit = True

Let’s verify those changes with a follow-up Read query using pyodbc:

1
2
3
4
5
6
7
8
9
10
11
12
13
In [6]: sql_query = """SELECT * FROM friends;"""                                                        

In [7]: rows = cur.execute(sql_query)                                                                    

In [8]: for row in rows:
   ...:     print(row.country)
   ...:                                                                                                  
USA
USA
USA
USA
USA
USA

The UPDATE is successful.

In this next example, I will UPDATE 2 columns with pyodbc. While the logic is simple, this is still an effective way of managing your data with pyodbc, targeting a MySQL database (in this example at least).

I’ll set 3 variables to use in the parameterized query: ‘can_country’ – to represent the country value, ‘f_name’ as a first name value, and ‘l_name’ for the last name:

1
2
3
4
5
In [5]: can_country = 'CAN'                                                                                                                                  

In [6]: f_name = 'Mary'                                                                                                                                      

In [7]: l_name = 'Murphy'

I’ll also use a variable to hold the actual UPDATE statement instead of hard-coding it directly in the call to execute(), showing it is possible to form the statements in this manner. And, just like in the first example – as a form of assurance – all statements are executed in a try/except/else/finally block:

1
2
3
4
5
6
7
8
9
10
11
12
13
In [9]: update_sql = """UPDATE friends SET country = ?, last_name = ? WHERE first_name = ?;"""                                                                

In [10]: try:
    ...:     conn.autocommit = False
    ...:     cur.execute(update_sql, can_country, l_name, f_name)
    ...: except pyodbc.DatabaseError as e:
    ...:     raise e
    ...:     cur.rollback()
    ...: else:
    ...:     cur.commit()
    ...: finally:
    ...:     conn.autocommit = True
    ...:

Notice the UPDATE statement has 3 ? placeholders for all 3 of the variables I set. These are used in the call to execute() and are substituted in the query, at the appropriate location. Therefore, their ordering in the execute() method is critical.

Let’s verify all went according to plan by executing a SELECT query for the specific row just updated:

1
2
3
4
5
6
7
8
In [11]: mary_row = """SELECT * FROM friends WHERE first_name = 'Mary';"""                                                                                    

In [12]: rows = cur.execute(mary_row)                                                                                                                        

In [13]: for row in rows:
    ...:     print(row)
    ...:                                                                                                                                                      
('CAN', 'Mary', 'Murphy', None, '212-543-9420', datetime.date(1978, 3, 23), 41)

Perfect!

You are crushing it! You now know how to UPDATE an existing row – or rows – of data in your tables using pyodbc. But, what if you want to completely get rid of a row or multiple rows? Well there is one sure-fire way to do that in SQL and that is with the DELETE command and that will be covered in the next and final post of this series. Don’t miss it!!

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.