Using pyodbc to update a record

This post shows how to use pyodbc to update a record. We are continuing with the bookstore case, updating a book price in our SQL Server database. The SQL syntax is simple:

update <table name>
set <field name> = value
where <field name> = value;

There are two items to pay particular attention to in the screenshot below: the placeholders in the sql statement denoted by ? and the params variable. Note that params is just a common variable name not a keyword. When the SQL statement is executed, the placeholders will be replaced by actual values from the params variable. Params must be a tuple with values in the same order as the placeholders. The values are matched to the placeholder by position, not name.

The following is the result. We updated the retail price of The Black Tulip by Alexandre Dumas. Note once again that a commit would need to be issued to persist the update.

Bookstore ERD

Below is the ERD (Entity Relationship Diagram) for the bookstore database used in this series. I should have posted it before. It is based on the database used in the SQL Clearly Explained text by Jan Harrington. It is a simple, straightforward design. One thing to note is the one-to-many relationship from customer to orders and then one-to-many to order_lines. There is then a many-to-one from the order_lines to the books. In this case the order_lines table is serving as an intersection table between the orders and books. All this is a a very common pattern in relational databases. It is one the student will encounter frequently when working with databases. Our little sample database design could be easily converted to serve many other similar sales or order processing use cases.

The diagram is from the diagram facility in SQL Server Management Studio.