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.