BGGhotness.py

"""
    Pull hotness data from BGG (wwww.boardgamegeek.com) and put into SQLite Db
"""

from datetime import datetime, date
from xml.dom import minidom
import sqlite3, unicodedata, urllib2, time, sys

def translate(name):
    try:
        name = name.translate(None, "'&-:")
    except TypeError as e:
        name = unicode(name)
        name = strip_accents(name)
    
    name = name.encode("ascii", "ignore")
    name = name.replace("'", "")
    return name


def strip_accents(s):
    return ''.join(c for c in unicodedata.normalize('NFD', s)
                  if unicodedata.category(c) != 'Mn')

db_loc = 'gencon_test.sqlite'

# Games
url = 'https://www.boardgamegeek.com/xmlapi2/hot?type=boardgame'

# Connect to SQLite Db
conn = sqlite3.connect(db_loc)
c = conn.cursor()

# TO DO: Check to see if tables exist

print "HOT GAMES"
dom = minidom.parse(urllib2.urlopen(url))

games = dom.getElementsByTagName('item')

i = 0
for elem in games:
    bggid = int(games[i].attributes['id'].value)
    rank = int(games[i].attributes['rank'].value)
    
    query = "INSERT INTO hotgame (bggid, rank) VALUES ({bi}, {rk})".format(bi = bggid, rk = rank)
    c.execute(query)
    
    print str (rank) + ": " + str(bggid)  
    i += 1

# People
print "HOT PEOPLE"
url = 'https://www.boardgamegeek.com/xmlapi2/hot?type=boardgameperson'
dom = minidom.parse(urllib2.urlopen(url))
people = dom.getElementsByTagName('name')
item = dom.getElementsByTagName('item')

i = 0
for elem in people:
    person = people[i].attributes['value'].value
    rank = int(item[i].attributes['rank'].value)
    
    person = translate(person)

    
    query = "INSERT INTO hotperson (name, rank) VALUES ('{nm}', {rk})".format(nm = person, rk = rank)

    c.execute(query)
    
    print str(rank) + ": " + person.encode('utf-8')
    i += 1



# Companies
print "HOT COMPANIES"

url = 'https://www.boardgamegeek.com/xmlapi2/hot?type=boardgamecompany'
dom = minidom.parse(urllib2.urlopen(url))
company = dom.getElementsByTagName('name')
item = dom.getElementsByTagName('item')

i = 0

for elem in company:
    name = company[i].attributes['value'].value
    rank = int(item[i].attributes['rank'].value)
    
    name = translate(name)
    
    query = "SELECT pubid FROM publishers WHERE name LIKE '{nm}'".format(nm = name)
    c.execute(query)
    pubid = int(c.fetchone()[0])
    
    query = "INSERT INTO hotcomp (pubid, rank) VALUES ({pi}, {rk})".format(pi = pubid, rk = rank)
    
    c.execute(query)
    
    print str(rank) + ": " + name
    
    i += 1
    
    
    
conn.commit()
conn.close()