bgg_sql_functions.py

import sqlite3
from datetime import datetime, date
from operator import itemgetter



firstmonth = '2016-05'
lastmonth = '2016-06'

# Connect to SQLite Database
db_loc = 'boardgamecollection_xml.sqlite' # name of database
conn = sqlite3.connect(db_loc)
c = conn.cursor()


def sql(query):
    c.execute(query)
    
    out =  c.fetchall()
    return out

def pprint(out):
    toprint = ''
    # for row in out:
    #     for item in row:
    #         toprint += str(item) + " - "
    #     toprint = toprint[:-3]
    #     print toprint
    #     toprint = ''
        
    numCol = len(out[0])-1
    s = "{:<50} "
    for col in xrange(numCol):
        s += "{: <50} "
    for row in out:
        print(s.format(*row))



def percentcomplete(c):
    
    c.execute("SELECT max(date) FROM collection WHERE date < '{dt}%'".format(dt = date.today().strftime('%Y-%m')))
    prev_month = c.fetchone()[0]
    prev_month = datetime.strptime(prev_month, '%Y-%m-%d')

    c.executescript("""
                    DROP TABLE IF EXISTS countbggid;
                    
                    CREATE TEMP TABLE IF NOT EXISTS countbggid
                    (bggid INTEGER);
                    
                    INSERT INTO countbggid
                    SELECT count(bggid)
                    FROM collection
                    WHERE date like '{dt}%'
                    """.format(dt = prev_month.strftime('%Y-%m')))
    query = ''' select count(a.bggid)*100.0 / b.bggid from collection a
                cross join countbggid b
                where a.date like '{dt}%'
    '''.format(dt = date.today().strftime('%Y-%m'))
    
    # c.execute(query)
    # tot = c.fetchone()[0]

    pprint(sql(query))

def upandcomers(c,firstmonth, lastmonth, count):
    # Create temp table with the last month's data:
    c.executescript("""
    DROP TABLE IF EXISTS lastmonth;
    
    CREATE TEMP TABLE IF NOT EXISTS lastmonth 
    (bggid INTEGER, name VARCHAR(100), owned INTEGER);
    
    INSERT INTO lastmonth
    SELECT c.bggid, g.name, c.owned
    FROM collection c
    INNER JOIN games g ON c.bggid = g.bggid
    WHERE c.date like '{mn}%';
    """.format(mn=lastmonth))
    
    
    query = '''
    SELECT l.bggid, l.name, round(l.owned*1.0/f.owned,2) as ratio--, l.owned, f.owned
    FROM lastmonth l 
    INNER JOIN collection f on f.bggid = l.bggid
    INNER JOIN games g ON g.bggid = l.bggid
    WHERE f.date like '{mn}%' AND l.owned > 0 and f.owned > 50 and g.isexpansion <> 1
    ORDER BY ratio DESC 
    LIMIT {cnt};
    '''.format(mn=firstmonth, cnt = count)
    
    # query = '''
    # SELECT l.bggid, l.name, round((l.owned-f.owned)*1.0/f.owned,2) AS ratio
    # FROM lastmonth l 
    # INNER JOIN collection f on f.bggid = l.bggid
    # INNER JOIN games g ON g.bggid = l.bggid
    # WHERE f.date like '{mn}%' AND l.owned > 0 and f.owned > 50 and g.isexpansion <> 1
    # ORDER BY ratio DESC 
    # LIMIT {cnt}    
    #         '''.format(mn=firstmonth, cnt = count)
    
    print("{:<50} {: <50} {: <50}".format("BGGid", "Game Name", "Hotness Ratio"))
    print("{:<50} {: <50} {: <50}".format("----", "----", "----"))
    pprint(sql(query))

def gamesbypub(c, x):
    query = '''SELECT publishers.name, count(gamepub.pubid) AS countpub
                FROM publishers
                INNER JOIN gamepub ON publishers.pubid = gamepub.pubid
                GROUP BY publishers.name
                ORDER BY countpub DESC LIMIT {cnt}'''.format(cnt = x)

    pprint(sql(query))

def ownershipbypub(c, x):
    ## TO DO: add ability to search by pub name, date
    ## TO DO: this isn't working in python
    query = '''
            SELECT publishers.name, sum(collection.owned) as sumowned
            FROM publishers
            INNER JOIN gamepub ON gamepub.pubid = publishers.pubid
            INNER JOIN games ON gamepub.bggid = games.bggid
            INNER JOIN collection ON collection.bggid = games.bggid
            GROUP BY publishers.name, collection.date
            ORDER BY collection.date DESC, sumowned DESC LIMIT {cnt}
    '''.format(cnt = x)

    # print query
    pprint(sql(query))
    
query = '''SELECT publishers.name, sum(collection.owned) as sumowned FROM publishers INNER JOIN gamepub ON gamepub.pubid = publishers.pubid INNER JOIN games ON gamepub.bggid = games.bggid INNER JOIN collection ON collection.bggid = games.bggid GROUP BY publishers.name, collection.date ORDER BY collection.date DESC, sumowned DESC LIMIT 10'''

        
# upandcomers(c,'2016-06', '2016-07', 10)

# conn.close()


def scanning(c, comm):
    if comm == "":
        True
    elif comm == "help" or comm == "h":
        functions = ["upandcomers", "gamesbypub"]
        description = ["Ranks games within month range (YYYY-mm) by number owned last month / number owned first month.", "Number of games for each publisher, ranked"]
        usage = ["upandcomers first month (YYYY-mm) lastmonth X", "gamesbypub X"]
        
        result = []
        for i in xrange(len(functions)):
                result.append([functions[i], description[i], usage[i]])
                
        result = sorted(result, key=itemgetter(0))
        
        print("{:<20} {: <100} {: <20}".format("Function", "Description", "Usage"))
        print("{:<20} {: <100} {: <20}".format("----", "----", "----"))
        for row in result:
                print("{:<20} {: <100} {: <20}".format(*row))
    elif comm[0:11] == "upandcomers":
        comm = comm.lstrip("upandcomers ")
        firstmonth = comm[0:7]
        lasmonth = comm[8:15]
        count = comm[16:len(comm)]
        
        upandcomers(c,firstmonth, lastmonth, count)
    elif comm[0:10] == "gamesbypub":
        count = comm.lstrip("gamesbypub ")
        gamesbypub(c, count)
    elif comm[0:14] == "ownershipbypub":
        startTime = datetime.datetime.now()
        count = comm.lstrip("ownershipbypub ")
        ownershipbypub(c, count)
        print str(datetime.datetime.now() - startTime)

# while True:
#     comm = raw_input('Enter command: ')
#     
#     scanning(c, comm)
#