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.

SQL Join Using pyodbc

This is the second post in retrieving information from SQL Server using pyodbc. It shows a little more advanced script than the last post. It shows a join operation between two tables. As can be seen this is easily done using pyodbc. In this example the SQL statement is enclosed in triple quotes which allows for a natural style of writing the query. The query lists book sales grouped by isbn. We restricted the output to the first 10 books. This requires two tables: the books table and the orderlines table which has an isbn foreign key to the books table. Isbn is the primary key of the books table. Below is the query. We are using the fetchall() method of the cursor object which returns all the rows, this being a small database.

import pyodbc

import bookstore_connect_odbc as bookstore

def list_books(cnxn):

    """ List books sales totals by isbn"""

    cursor = cnxn.cursor()

    sql = """select top 10 ol.isbn, title, sum(cost_line) as total_sales

        from books b join order_lines ol

        on ol.isbn = b.isbn

        group by ol.isbn, title

        order by title"""

    cursor.execute(sql)

    rows = cursor.fetchall()

    for row in rows:

        # print(type(row))

        print(row.isbn, row.title,

            format(row.total_sales,'.2f'))

# main program    

print("Welcome to ODBC Database Manipulation")

cnxn = bookstore.connect()

print("Our book sales by isbn...")

list_books(cnxn)

cnxn.close()

print("Database connection closed")

Below are the top 10 rows of the result set:

In the next posts we will cover the update and insert CRUD operations using Python and pyodbc.

Parameterized ODBC book query

Last week we did a query that retrieved book information for a selected ISBN. This post shows an improved version of the query - a parameterized query. For a student database the prior query is OK. But for anything public or in production the query should be parameterized to prevent an SQL Injection attack. This is done by placing a ? in the SQL statement where the variable would go. Then in the cursor execute you follow the actual query with the parameters to be interpolated into the SQL statement, effectively replacing the ? placeholders. The script below illustrates this. The results are the same as last week's regular query. We may not always use these parameterized queries in this series of posts but this is how you would/should do it.

# retrieve book information using a parameterized ODBC query

# d d'urso ocdatabases laguna niguel, ca 92677

import pyodbc

import bookstore_connect_odbc as bookstore

def get_book_info(cnxn, isbn):

    """ Show key book properties for given isbn"""

    cursor = cnxn.cursor()

    sql = "select * from books where isbn = ?"

    cursor.execute(sql, isbn)

    info = cursor.fetchone()

    if info == None:

        print("isbn not found")

        return

    print("Isbn: " + info.isbn)

    print("Title: " + info.title)

    print("Author: " + info.author_name)

    print("Retail price: " + format(info.retail_price,'.2f'))

    print("Publication year: "+ str(info.publication_year))

# main program    

print("Welcome to ODBC Database Manipulation")

cnxn = bookstore.connect()

isbn = '0-125-3344-1'

print("Book Summary for isbn: " + isbn)

get_book_info(cnxn, isbn)

cnxn.close()

print("Database connection closed")

Filtering SQL select using pyodbc connection

As noted last week this post shows how to filter the results of an SQL select statement. The code is very similar to the list operation shown last week. But it retrieves only one row from the cursor based on the isbn primary key. To do this it uses the pyodbc fetchone() method which returns the next row in the cursor. First we build the SQL statement using single quotes to surround the isbn. This is then passed to the cursor object which executes the fetchone() method to retrieve the next row from the cursor. If the book was found the information is printed out.

Now for the caveats: the SQL statement below is built by concatenating a literal string with a variable. In this case, isbn. This is to be avoided in a production setting as it can lead to SQL injection if the isbn variable is populated from a client-server form or webpage. In a subsequent post we will cover how to avoid this using parameterized queries.

import pyodbc

import bookstore_connect_odbc as bookstore

def get_book_info(cnxn, isbn):

    """ List books in books table"""

    cursor = cnxn.cursor()

    sql = "select * from books where isbn = " + "'" + isbn + "'"

    cursor.execute(sql)

    info = cursor.fetchone()

    if info == None:

        print("isbn not found")

        return

    print("Isbn: " + info.isbn)

    print("Title: " + info.title)

    print("Author: " + info.author_name)

    print("Retail price: " + format(info.retail_price,'.2f'))

    print("Publication year: "+ str(info.publication_year))

# main program    

print("Welcome to ODBC Database Manipulation")

cnxn = bookstore.connect()

isbn = '0-125-3344-1'

print("Book Summary for isbn: " + isbn)

get_book_info(cnxn, isbn)

cnxn.close()

print("Database connection closed")

Here are the results of executing the above script in the bookstore database:

Welcome to ODBC Database Manipulation

Connected to to SQL Server with ODBC

Book Summary for isbn: 0-125-3344-1

Isbn: 0-125-3344-1

Title: Black Tulip, The

Author: Dumas, Alexandre

Retail price: 18.95

Publication year: 1902

Database connection closed

PS X:\Usr\San_Juan\Courses\pysql302\Scripts>

Note that if the book is not found in the database the fetchone() method will return None and the script will just print "isbn not found".

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.