GameTracker Data Scrubber

datascrubber.py

"""
datascrubber.py

Quick and dirty script to pull the data from google spreadsheet into the GameTracker database
Inserts players (manually identified), games, and all sessions
"""
import csv
import sys
import itertools
from collections import defaultdict

csvFile = 'boardgameresults.csv'
outputFile = 'boardGameInsertScript.txt'

game_column = "Game"
coop_column = "Co-Op?"
played_date_column = 'Date'
player1_column = 'Player 1 (Score)'
player2_column = 'Player 2 (Score)'
player3_column = 'Player 3 (Score)'
player4_column = 'Player 4 (Score)'
winner_column = 'Winner'
notes_column = 'Notes'

player_insert_template = "INSERT INTO players (id, name, powers, created_at, updated_at) VALUES ({0}, '{1}', '', now(), now());"
game_insert_template = "INSERT INTO games (id, name, coop, created_at, updated_at) VALUES ({0}, '{1}', {2}, now(), now());"
session_insert_template = "INSERT INTO sessions (played, notes, game_id, created_at, updated_at) VALUES ('{0}', '{1}', {2}, now(), now());"
session_players_insert_template = 'INSERT INTO session_players (score, "placing", team_number, player_id, session_id, created_at, updated_at) VALUES ({0}, {1}, null, {2}, {3}, now(), now());'

game_id_dict = {}
player_id_dict = {'Candyce': 1, 'Brian': 2, 'Josh': 3, 'Echo': 4, 'Madelynn': 5, 'Kendra': 6, 'Sara': 7, 'Khem': 8, 'Mindy': 9, 'Kristalyn': 10, 'Taylor': 11, 'Unknown': -1}

def player_inserts():
  ret_list = []

  for name, identifier in player_id_dict.iteritems():
    ret_list.append(player_insert_template.format(identifier, name))

  return ret_list


def game_inserts(reader):
    # Create dictionary of {game, isCoOp} pairs
  unique = dict()
  for game in reader:
    if game["Game"] not in unique:
      unique[game[game_column]] = game[coop_column] == 'x'

  # assume we are starting at 1
  gameId = 1
  insertScript = lambda name,isCoOp: game_insert_template.format(gameId, escape(name), isCoOp)
  ret_list = []
  for g,c in unique.items():
    ret_list.append(insertScript(g,c))
    game_id_dict[g] = gameId
    gameId += 1

  return ret_list
  
def session_inserts(reader):
  session_insert = []
  # assume we are starting at 1
  session_id = 1
  for session in reader:
    game_id = game_id_dict[session[game_column]]
    session_insert.append(session_insert_template.format(session[played_date_column], escape(session[notes_column]), game_id))
    # Insert each player session
    # TODO calculate placing based upon scores
    player1 = session[player1_column]
    player2 = session[player2_column]
    player3 = session[player3_column]
    player4 = session[player4_column]
    winner_string = session[winner_column]

    player_1_score = get_player_score(player1)
    player_2_score = get_player_score(player2)
    player_3_score = get_player_score(player3)
    player_4_score = get_player_score(player4)

    sorted_scores = sorted(set([player_1_score, player_2_score, player_3_score, player_4_score]), reverse=True)

    if player1:
      session_insert.append(session_player_insert(player1, session_id, get_placing(player1, sorted_scores, winner_string)))
    if player2:
      session_insert.append(session_player_insert(player2, session_id, get_placing(player2, sorted_scores, winner_string)))
    if player3:
      session_insert.append(session_player_insert(player3, session_id, get_placing(player3, sorted_scores, winner_string)))
    if player4:
      session_insert.append(session_player_insert(player4, session_id, get_placing(player4, sorted_scores, winner_string)))

    session_id += 1

  return session_insert

def session_player_insert(player_string, session_id, placing):
  """ 
  player_string will be in format '<name> (<score>)'
  """
  score = get_player_score(player_string)
  # If score is not set (-1) then convert to null
  score = 'null' if score == -1 else score
  name = get_player_name(player_string)
  player_id = player_id_dict[name]
  return session_players_insert_template.format(score, placing, player_id, session_id)

def get_player_score(player_string):
  """
  Parses the score out of the player player_string
  Rounds to nearest int in cases of decimal
  """
  if '(' not in player_string:
    return -1
  else:
    parsed = float(player_string[player_string.index('(')+1:player_string.index(')')])
    return int(round(parsed))
def get_player_name(player_string):
  """
  Parses the name out of the player player_string
  """
  return player_string if '(' not in player_string else player_string[:player_string.find('(')].strip()

def get_placing(player_string, places, winner_string):
  score = get_player_score(player_string)
  name = get_player_name(player_string)
  if score >= 0:
    return places.index(score) + 1
  else:
    # If there is no score, then check the winner string to see if name is present
    # If we aren't in the winner string, then we lost
    if winner_string.find(name) >= 0:
      return 1
    else:
      return 2

def escape(str):
  """
  Escape single quote from csv to avoid postgres script error
  """
  return str.replace("'", "''")


#Perform Work
f = open(csvFile, 'rt')
o = open(outputFile, 'w')
try:
  o.write('--Insert players\n')
  for line in player_inserts():
    o.write("%s\n" % line)
  reader = csv.DictReader(f)
  # Read into a list as the file is short and we don't want to re-seek after each operation
  data = list(reader)
  o.write('--Insert games\n')
  for line in game_inserts(data):
    o.write("%s\n" % line)
  # player_inserts(data)
  o.write('--Insert sessions\n')
  for line in session_inserts(data):
    o.write("%s\n" % line)
finally:
  f.close()
  o.close()