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.

The cost of fixing bugs

Here is an interesting chart from DeanOnDelivery.com. It is based on work by Capers Jones. The dollar figures reflect earlier times but the message is clear. Most software bugs are introduced during development and the cost of correction rises dramatically as time progresses through the SDLC phases. 

Inserting a record with pyodbc

Here is the Python code to insert a new record into the bookstore database using pyodbc. We have used a parameterized query again. The key statement is the cursor.execute() with two arguments - the SQL statement and the parameter list. The SQL statement lists the required fields we are going to insert into and a list of ?'s in the value list, one for each of the four parameters. The execute method has the SQL statement as one argument and a tuple with the actual values as the second. The default is not to commit the record so you have to use the commit method to persist the record.

The following shows the first few records in the database after the insert.