python sql-dictionary join

sql-dicts join.py

#!/usr/bin/env 

# goal is to use the dictionary and return the value
# using the key in a sql statement.
# eg instead of having this table in the DB we can hack it in
# even import it from excel.

import sqlite3


def print_sql():
    """
    send reults of query to print output
    """
    results = cur.fetchall()
    for row in results:
        print row

testdata = (
    ('OBD1', 'order1'),
    ('OBD2', 'order2'),
    ('CB07', 'order3'),
    ('CB08', 'order4'),
    ('CB09', 'order5'),
    ('CB10', 'order6'),
    ('CB11', 'order7'),
    ('CB07', 'order8'),
    ('CB08', 'order9'),
    ('OBD1', 'order10'),
    ('OBD2', 'order11'),
    ('OBD1', 'order12'),
    ('OBD2', 'order13'),
    ('OBD1', 'order14'),
    ('OBD2', 'order15'),
    ('OBD3', 'order16')  # intentionally not in dict to see how
    # to manage left outer join style query
)


def creates():
    """
    SQLITE3 database setup. only needs to be run once
    """
    cur.execute("DROP TABLE IF EXISTS order_location")
    cur.execute('create table order_location (location char, orderid char);')
    cur.executemany("""
        insert into order_location (location, orderid ) values (?,?);
        """, testdata)
    con.commit()

# here's the dictionary we're going to use as a lookup
# uses business logic to group things

from_location_group = {
    'OBD1': 'Online',
    'OBD2': 'Online',
    'CB07': 'Retail',
    'CB08': 'Retail',
    'CB09': 'Retail',
    'CB10': 'Retail',
    'CB11': 'Retail'
}

con = sqlite3.connect('dicttest.db')
cur = con.cursor()

creates()

cur.execute("""select orderid, location
               from order_location""")
order_location = cur.fetchall()

for orderid, location in order_location:
    print orderid, location, from_location_group.get(location, "")
    # using the dict[key] method provides an error where 
    # there is no matching key. Instead use dict.get(key,"")
    # the quotes can be any arbitrary replacement for a missing item