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".