database.py

#!/usr/bin/python

import os
import sqlite3

"""
Database requirement for SILS Information Technology Competency Test

http://ils.unc.edu/waiver/Information_Technology_Competency_Test.pdf
"""

CURDIR = os.path.dirname(__file__)
DB_PATH = os.path.join(CURDIR, 'database.db')


##############################
#      Database commands     #
##############################

CREATE_PUBLISHERS_TABLE = """
CREATE TABLE publishers(
    pk INTEGER,
    name TEXT,
    city TEXT,
    state TEXT,
    country TEXT
);
"""

CREATE_BOOKS_TABLE = """
CREATE TABLE books(
    pk INTEGER,
    title TEXT,
    date INTEGER,
    price NUMERIC,
    copies INTEGER,
    callno TEXT,
    publisher INTEGER,
    FOREIGN KEY(publisher) REFERENCES publishers(pk)
);
"""

QUERY_1 = """
SELECT title FROM books
WHERE publisher=(
    SELECT pk FROM publishers
    WHERE country='GB' AND name LIKE '%Oxford University%'
);
"""

QUERY_2 = """
SELECT COUNT(*) FROM books
WHERE publisher=(
    SELECT pk FROM publishers
    WHERE country='GB' AND name LIKE '%Oxford University%'
);
"""

##############################
#          Functions         #
##############################

def build_publishers_table(conn):
    c = conn.cursor()
    c.execute('DROP TABLE IF EXISTS publishers;')
    c.execute(CREATE_PUBLISHERS_TABLE)

    # Open publishers file & skip header
    pubs_file = open(os.path.join(CURDIR, 'publishers.csv'), 'rb')
    pubs_file.next()

    # Populate database, making sure to strip any whitespace.
    c.executemany(u'INSERT INTO publishers VALUES (?, ?, ?, ?, ?);',
                  (rec.decode('utf-8').rstrip().split('|') for rec in pubs_file))
    return

def build_books_table(conn):
    c = conn.cursor()
    c.execute('DROP TABLE IF EXISTS books;')
    c.execute(CREATE_BOOKS_TABLE)

    # Same as in build_publishers_table()
    books_file = open(os.path.join(CURDIR, 'books.csv'), 'rb')
    books_file.next() # skip header

    c.executemany(u'INSERT INTO books VALUES (?, ?, ?, ?, ?, ?, ?);',
                  (rec.decode('utf-8').rstrip().split('|') for rec in books_file))
    return

def show_menu():
    print('\n========================================')
    print('\t(p) Print queries'
          '\n\t(1) Execute query 1'
          '\n\t(2) Execute query 2'
          '\n\t(q) Quit')
    print('========================================\n')
    decision = raw_input('Choice? ')
    
    if decision == 'p':
        print('\nQuery 1:\n=========={}'.format(QUERY_1))
        print('\nQuery 2:\n=========={}'.format(QUERY_2))
    if decision == '1':
        with sqlite3.connect(DB_PATH) as conn:
            c = conn.cursor()
            result = map(lambda rec: rec[0], c.execute(QUERY_1).fetchall())
        print('\nBooks associated with Oxford University Press, GB: \n\n' +
              '\n'.join(result))
    elif decision == '2':
        with sqlite3.connect(DB_PATH) as conn:
            c = conn.cursor()
            result = c.execute(QUERY_2).fetchone()
        print('\n' + str(result[0]) +
              ' books associated with Oxford University Press, GB')
              
    if decision in ['p', '1', '2']:
        show_menu() # show menu again if valid option was selected


##############################
#      The actual thing      #
##############################

if __name__ == '__main__':
    with sqlite3.connect(DB_PATH) as conn:
        print(u'Building publishers table...')
        build_publishers_table(conn)
        print(u'Building books table...')
        build_books_table(conn)
        conn.commit()
    show_menu()