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.

Listing records in SQL Server using Python

This is the second in the series of using Python to manipulate a SQL Server database via ODBC. This post shows how to list the contents of a table. I  created a new Python script below which imports the connection module from last week's blog post. 

It then creates a cursor object (Current Set of Records) based on the connection. Using the cursor it executes an SQL query to retrieve the contents of the table into the cursor. The pyodbc method fetchall() retrieves all the rows from the cursor and stores them into the rows list.  Next the script iterates through the rows list and prints out the tuples. We will look at filtering rows in future posts.

Python script

Below is the script we used for this week's demo. See my prior post on how to set up the ODBC connection.

Result set

The following shows the list of records retrieved (or at least the first 20 or 30):

Next week we will look at join operations and filtering records. The python script for this week, crud_bk_s2.py, is available on our Google drive.

Using SQL Server with Python

I am starting a brief series of posts on manipulating a SQL Server database with Python. This is a beginner level tutorial for students wanting to get started with this activity. Python provides access to a large ecosystem of data science functions above the normal SQL Query results. We will eventually work our way up to these. But first we will start with the basics: creating an ODBC connection to a SQL Server database followed by a series of posts on the standard CRUD (Create, Read, Update, Delete) functions.

These examples were done using Windows 10. To follow along you will need to have Python installed (we are using Python 3) as well as SQL Server (it should not matter too much which version so long as it is fairly recent. We are using v16). The tutorial uses a build script which we have supplied on our Google drive. This creates a sample database called bookstore. Install this and then create an ODBC data source using the Windows ODBC data sources applet. If need be, download and install the odbc driver 17 from Microsoft first.

To connect your python scripts to SQL Server you need one more component, the Python package to connect to an ODBC data source: pyodbc. You may find you already have it installed but if not you can just install pyodbc using pip in the command window.

>pip install pyodbc

With the above in place you can adjust and run the following Python script to connect to the database. Modify the server to equal <your computer name>\<SQL Server instance name>. 

import pyodbc
# assumes bookstore database has been created in sql server
def open_database():
    """ Open connection to bookstore database using ODBC"""
    cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'Server=HP10ALL-IN-1\SQLEXPRAS2016A1;'
                      'Database=bookstore;'
                      'Trusted_Connection=yes;')
    print('\nConnected to SQL Server with ODBC');
    return cnxn  
# main program    
cnxn = open_database()
cnxn.close()
print("Database connection closed")

I have used Windows Authentication but you can use SQL Server authentication if you prefer. You just need to supply the user name and password in lieu of Trusted_Conection=yes.

Once this is working you can proceed to the next steps which we will provide in following posts: 1) read data, 2) add records, 3) update data, 4) delete records. After that we will extend the series to include DDL - creating and dropping databases and tables, etc. We will also cover creating and using a stored procedure.