def load_game(g, s): g_id = g['id'] opt = s['options'] try: starting_player = g['options']['startingPlayer'] except KeyError: starting_player = opt['startingPlayer'] num = opt['numPlayers'] var_eff = None if num == 2: var_eff = session.query(Variant.eff_2p).filter( Variant.variant == opt['variantName']).scalar() elif num in [3, 4]: var_eff = session.query(Variant.eff_34p).filter( Variant.variant == opt['variantName']).scalar() elif num == 5: var_eff = session.query(Variant.eff_5p).filter( Variant.variant == opt['variantName']).scalar() elif num == 6: var_eff = session.query(Variant.eff_6p).filter( Variant.variant == opt['variantName']).scalar() game = Game(g_id, opt['numPlayers'], g['players'], starting_player, opt['variantID'], opt['variantName'], opt['timed'], opt['timeBase'], opt['timePerTurn'], opt['speedrun'], opt['cardCycle'], opt['deckPlays'], opt['emptyClues'], opt['oneExtraCard'], opt['oneLessCard'], opt['allOrNothing'], opt['detrimentalCharacters'], s['score'], s['numTurns'], s['endCondition'], s['datetimeStarted'], s['datetimeFinished'], s['numGamesOnThisSeed'], s['tags'], g['seed'], var_eff) session.add(game) return game
def update_game(s): g_id = s['id'] opt = s['options'] game = session.query(Game).filter(Game.game_id == g_id).first() if game is None: logger.info(f"{g_id} doesn't exist in db.") return -1 num_players = session.query( Game.num_players).filter(Game.game_id == g_id).scalar() if num_players is not None: return 0 game.num_players = opt['numPlayers'] if game.starting_player is None: game.starting_player = opt['startingPlayer'] game.variant_id = opt['variantID'] game.variant = opt['variantName'] game.timed = opt['timed'] game.time_base = opt['timeBase'] game.time_per_turn = opt['timePerTurn'] game.speedrun = opt['speedrun'] game.card_cycle = opt['cardCycle'] game.deck_plays = opt['deckPlays'] game.empty_clues = opt['emptyClues'] game.one_extra_card = opt['oneExtraCard'] game.one_less_card = opt['oneLessCard'] game.all_or_nothing = opt['allOrNothing'] game.detrimental_characters = opt['detrimentalCharacters'] game.score = s['score'] game.num_turns = s['numTurns'] game.end_condition = s['endCondition'] game.date_time_started = s['datetimeStarted'] game.date_time_finished = s['datetimeFinished'] game.num_games_on_this_seed = s['numGamesOnThisSeed'] game.tags = s['tags'] return 1
def get_notes_ratio(username): """Gets ratio of player's notes per game. Parameters ---------- username : str Player name Returns ------- int Ratio of notes per game """ notes_list = session.query(PlayerNotes.game_id, PlayerNotes.notes) \ .filter(PlayerNotes.player == username) \ .all() player_notes_visible_cards_dict = { 'notes_count': 0, 'visible_cards_count': 0 } if len(notes_list) == 0: return 0 for note_row in notes_list: game_id = note_row.game_id game = session.query(Game)\ .filter(Game.game_id == game_id)\ .first() if game.num_players == 2 or game.speedrun == true(): continue notes_count = get_notes_count(game_id, username) if notes_count == 0: continue actions = session.query(GameAction.action_type).filter( GameAction.game_id == game.game_id).all() players = session.query( Game.players).filter(Game.game_id == game.game_id).scalar() cards_count = len( session.query(Card).filter(Card.seed == game.seed).all()) starting_cards_count = get_starting_cards_count( len(players), game.one_less_card, game.one_extra_card) drawn_cards_count = u.get_number_of_plays_or_discards(actions) visible_cards_count = min(starting_cards_count + drawn_cards_count, cards_count) player_notes_visible_cards_dict['notes_count'] += notes_count player_notes_visible_cards_dict[ 'visible_cards_count'] += visible_cards_count return u.p(player_notes_visible_cards_dict['notes_count'], player_notes_visible_cards_dict['visible_cards_count'])
def load_card_actions_and_clues(db_game, game_actions, deck): game_id = db_game.game_id players_orig = db_game.players num_players = db_game.num_players variant_id = db_game.variant_id variant = db_game.variant starting_player = db_game.starting_player one_less_card = db_game.one_less_card one_extra_card = db_game.one_extra_card players_mod = (players_orig[starting_player:] + players_orig[:starting_player]) suits, colors = session.query( Variant.suits, Variant.colors).filter(Variant.variant_id == variant_id).first() suits = [s.lower() for s in suits] colors = [c.lower() for c in colors] piles = init_piles(variant, suits) load_card_action_empty(deck, game_id, suits) current_card_ind = u.get_number_of_starting_cards(num_players, one_less_card, one_extra_card) cards_per_hand = u.get_number_of_cards_in_hand(num_players, one_less_card, one_extra_card) init_hands(current_card_ind, players_orig, cards_per_hand, game_id) card_actions = session.query(CardAction).filter( CardAction.game_id == game_id).all() for action in game_actions: if action.action_type == 4: return elif u.is_clued(action): create_clue(action, colors, game_id, num_players, players_mod, players_orig) elif action.action_type in [0, 1]: card_action = [ c for c in card_actions if c.card_index == action.target ][0] card_action = init_action_type(action, suits, card_action, piles) card_action.turn_action = action.turn + 1 if current_card_ind == len(deck): continue next_card_action = [ c for c in card_actions if c.card_index == current_card_ind ][0] next_card_action.turn_drawn = action.turn + 1 next_card_action.player = players_mod[action.turn % num_players] current_card_ind += 1
def get_times(username): """Calculates number of minutes per game and hours per day. Parameters ---------- username : str Player name Returns ------- times : list Total seconds, minutes per game and hours per day """ games = session.query(Game) \ .filter(Game.players.any(username))\ .order_by(Game.game_id)\ .all() days = group_stats(games) times = [0, len(games), days] for game in games: diff = game.date_time_finished - game.date_time_started times[0] += diff.total_seconds() # per game times[1] = times[0] / times[1] / 60 # per day excl. days with 0 games times[2] = times[0] / times[2] / 3600 return times
def count_purple_games(username, player_purple_date_dict): """Calculates number of games with purple players. Parameters ---------- username : str Player name player_purple_date_dict : dict Player names and dates when they became purple Returns ------- purple_games_count : int Number of games with purple players """ games = session.query(Game)\ .filter(Game.players.any(username))\ .filter(Game.num_players != 2)\ .filter(Game.speedrun == false())\ .all() purple_games_count = 0 for game in games: game_date = game.date_time_finished for player in game.players: if ( player != username and player in player_purple_date_dict.keys() and player_purple_date_dict[player] < game_date ): purple_games_count += 1 break return purple_games_count
def update_action_types(db_game): game_id, variant = db_game.game_id, db_game.variant actions = session.query(GameAction).filter( GameAction.game_id == game_id).all() suits = session.query( Variant.suits).filter(Variant.variant == variant).scalar() suits = [s.lower() for s in suits] piles = init_piles(variant, suits) card_actions = session.query(CardAction).filter( CardAction.game_id == game_id).all() for action in actions: if action.action_type in [0, 1]: card_action = [ c for c in card_actions if c.card_index == action.target ][0] init_action_type(action, suits, card_action, piles)
def init_hands(current_card_ind, players_orig, cards_per_hand, game_id): for i in range(current_card_ind): player = players_orig[i // cards_per_hand] card_action = session.query(CardAction)\ .filter(CardAction.game_id == game_id)\ .filter(CardAction.card_index == i)\ .first() card_action.player = player card_action.turn_drawn = 0
def get_alice_wr(username): """Gets number of games and wins played as both Alice and not Alice. Parameters ---------- username : str Player name Returns ------- dict Number of games and wins played as both Alice and not Alice, ratio of such games grouped by player """ games = session.query(Game) \ .filter(Game.players.any(username)) \ .filter(Game.num_players != 2) \ .filter(Game.speedrun == false()) \ .all() alice_list = session.query(Game)\ .filter(Game.players[Game.starting_player + 1] == username)\ .filter(Game.num_players != 2)\ .filter(Game.speedrun == 'false')\ .all() # num games overall total_games = len(games) # num wins overall total_wins = u.get_wins_db(games) # num games going first alice_games = len(alice_list) # num wins going first alice_wins = 0 for alice_game in alice_list: if alice_game.score == u.get_max_score(alice_game.variant): alice_wins += 1 # num game not going first bob_games = total_games - alice_games # num wins not going first bob_wins = total_wins - alice_wins formula = round((alice_wins / alice_games) / (bob_wins / bob_games), 2) return formula, alice_wins, alice_games, bob_wins, bob_games
def load_deck(g): deck = g['deck'] deck_res = session.query(Card) \ .filter(Card.seed == g['seed'])\ .all() if len(deck_res) != 0: return deck_res deck_res = [] for i in range(len(deck)): card = Card(g['seed'], i, deck[i]['suitIndex'], deck[i]['rank']) session.add(card) deck_res.append(card) return deck_res
def load_slots(g): card_actions = session.query( CardAction.card_index, CardAction.player, CardAction.turn_drawn, CardAction.turn_action, CardAction.game_id ) \ .filter( and_( CardAction.game_id == g.game_id, CardAction.turn_drawn != None )) \ .order_by(CardAction.card_index) \ .all() card_actions_copy = copy.deepcopy(card_actions) len_cards = u.get_number_of_cards_in_hand(g.num_players, g.one_less_card, g.one_extra_card) slots = [] for ca in card_actions: if ca.turn_drawn == 0: slot = len_cards - ca.card_index % len_cards else: slot = 1 slots.append(add_slot(ca, ca.turn_drawn, slot)) card_actions = [ca for ca in card_actions if ca.turn_action is not None] for ca in sorted(card_actions, key=lambda x: x.turn_action): card_slot = max( [s.slot for s in slots if s.card_index == ca.card_index]) len_drawn_cards = len([ a for a in card_actions_copy if a.turn_drawn >= ca.turn_drawn and a.player == ca.player and a.card_index != ca.card_index and ( a.turn_action is None or a.turn_action > ca.turn_action) ]) len_moved_cards = min(len_drawn_cards, card_slot - 1) card_actions_filtered = sorted( [ a for a in card_actions_copy if a.turn_drawn < ca.turn_action and a.player == ca.player and a.card_index != ca.card_index and (a.turn_action is None or a.turn_action > ca.turn_action) ], key=lambda x: -x.card_index)[:len_moved_cards] for i in card_actions_filtered: moved_card_slot = max( [s.slot for s in slots if s.card_index == i.card_index]) slots.append(add_slot(i, ca.turn_action, moved_card_slot + 1))
def get_purples_dates(): """Gets the date when a player became purple. Returns ------- player_purple_date_dict : dict A dictionary in format: "player: date" """ purples_list = u.open_file('../input/purples.txt') player_purple_date_dict = {} for player in purples_list: date = session.query(Game.date_time_started)\ .filter(Game.players.any(player))\ .order_by(Game.game_id)\ .first()[0] date = date.replace(year=date.year + 1) player_purple_date_dict[player] = date return player_purple_date_dict
def get_variants(username): """Gets list of variant from the db for a specified player, excluding 2-player games and speedruns. Parameters ---------- username : str Player name Returns ------- list Player's games without 2-player and speedruns """ return session.query(Game.variant) \ .filter(Game.players.any(username)) \ .filter(Game.num_players != 2) \ .filter(Game.speedrun == false()) \ .all()
def get_notes_count(game_id, username): """Gets number of player's notes. Parameters ---------- game_id : int Game id username : str Player name Returns ------- int Number of player's notes """ notes = session.query(PlayerNotes.notes) \ .filter(PlayerNotes.game_id == game_id) \ .filter(PlayerNotes.player == username) \ .scalar() return len([r for r in notes if r != ''])
def get_notes_stats(username): """Calculated frequency of words for a specified player. Parameters ---------- username : str Player name Returns ------- word_frequency_dict : dict Number of notes containing a specific word or symbol """ word_frequency_dict = {} notes_list = session.query(PlayerNotes.notes)\ .filter(PlayerNotes.player == username)\ .all() for notes in notes_list: word_frequency_dict = update_user_notes(notes[0], word_frequency_dict) return word_frequency_dict
def run(num_players): for seed in seeds: deck = session.query(Card).filter(Card.seed == seed[0]).all() # init starting hands if num_players == 3: hands = init_3p_hands(deck) else: hands = init_4p_hands(deck) if num_players == 3: cur_card = 15 else: cur_card = 16 # starting playable cards playable = [1, 1, 1, 1, 1] cur_player = 0 num_actions = 0 while num_actions <= 25: hand = hands[cur_player] status = False for card in hand: # print(card.card_index, playable) if playable[card.suit_index] == card.rank: playable[card.suit_index] += 1 status = True # print(card.suit_index, card.ratio) break if status is True: hand.append(deck[cur_card]) cur_card += 1 cur_player = (cur_player + 1) % num_players num_actions += 1 if status is False: break print(seed[0], '\t', playable) if playable == [6, 6, 6, 6, 6]: print(seed[0], '\t', 'yes')
def count_conditions(username): """Calculates number of games for each end condition. Parameters ---------- username : str Player name Returns ------- condition_count_dict : dict Number of games grouped by an end condition """ condition_count_dict = {i: 0 for i in range(11)} games = session.query(Game) \ .filter(Game.players.any(username)) \ .filter(Game.num_players != 2)\ .filter(Game.speedrun == false())\ .all() for game in games: ec = game.end_condition condition_count_dict[ec] += 1 condition_count_dict['total'] = len(games) return condition_count_dict
Frequency of words and number of common vocabularies grouped by a word users_count : int Number of players """ with open(f'../../output/notes/frequent_words.tsv', 'w', encoding='utf-8', newline='') as file: w = csv.writer(file, delimiter='\t', quotechar='"', quoting=csv.QUOTE_MINIMAL) w.writerow([ 'Words', 'Frequency', f'Number of vocabularies (Max = {users_count})' ]) for word, count_players_list in word_count_players_dict.items(): if count_players_list[0] >= 100: v_len = len(count_players_list[1]) r = str(v_len) w.writerow([word, count_players_list[0], r]) if __name__ == "__main__": users_portraits = {} users = session.query(Player.player).all() for user in users: user = user[0] users_portraits[user] = open_notes_stats(user) save_words(most_frequent(users_portraits), len(users))
def update_tags(s): g_id = s['id'] game = session.query(Game).filter(Game.game_id == g_id).first() game.tags = s['tags'] return 1
from datetime import datetime from json.decoder import JSONDecodeError import requests from sqlalchemy import func from database.db_connect import Game, session from py.utils import logger import py.utils as u import database.db_load as d last_id = session.query(func.max(Game.game_id)).scalar() # last_id = 443662 logger.info( f'{datetime.now().strftime("%d.%m.%Y %H:%M:%S")}\tstart:\t{last_id}') req_session = requests.Session() histories = {} while True: g_id = last_id + 1 g = u.export_game(g_id, req_session) if g != {}: if len(g['players']) == 0: logger.error(g_id) last_id += 1 continue player = g['players'][0] if player in histories.keys(): s = u.open_stats_by_game_id(histories[player], g_id) else: try: response = u.open_stats_from_id_start(player, last_id,
while num_actions <= 25: hand = hands[cur_player] status = False for card in hand: # print(card.card_index, playable) if playable[card.suit_index] == card.rank: playable[card.suit_index] += 1 status = True # print(card.suit_index, card.ratio) break if status is True: hand.append(deck[cur_card]) cur_card += 1 cur_player = (cur_player + 1) % num_players num_actions += 1 if status is False: break print(seed[0], '\t', playable) if playable == [6, 6, 6, 6, 6]: print(seed[0], '\t', 'yes') seeds = session.query(Card.seed)\ .distinct(Card.seed)\ .join(Game, Card.seed == Game.seed)\ .filter(Game.variant == 'No Variant')\ .all() # run(3) run(4)
import json from database.db_connect import session, Variant with open('../../resources/variants_1103.json', "r") as variants_file: variants = json.loads(variants_file.read()) for v in variants: print(v) variant = session.query(Variant).filter( Variant.variant == v['name']).first() if variant.suits: continue variant.suits = v['suits'] session.commit() session.close()
from database.db_connect import session, Variant with open('../../resources/variant_types.txt', 'r', encoding='utf-8') as f: variants = {} for line in f.readlines(): line = line.rstrip().split('\t') if 'null' in line[1]: line[1] = line[1].replace(' null', '') variants[line[0]] = line[1] with open('../../resources/types_from_doc.txt', 'r', encoding='utf-8') as f: variant_types = {} for line in f.readlines(): line = line.rstrip().split('\t') variant_types[line[0]] = [line[1], line[2], line[3], line[4]] variants_db = session.query(Variant).all() for v in variants_db: v.eff_2p = float(variant_types[variants[v.variant]][0].replace(',', '.')) v.eff_34p = float(variant_types[variants[v.variant]][1].replace(',', '.')) v.eff_5p = float(variant_types[variants[v.variant]][2].replace(',', '.')) v.eff_6p = float(variant_types[variants[v.variant]][3].replace(',', '.')) session.commit() session.close()
import csv from sqlalchemy import func import py.utils as u from database.db_connect import session, H, Game players = session.query(H.player).all() weights_easy = u.open_csv('weights_easy_.csv') weights_hard = u.open_csv('weights_hard_.csv') last_player = players.index(('will.i.am', )) f_easy = open('weights_easy__.csv', 'w', encoding='UTF-8') f_hard = open('weights_hard__.csv', 'w', encoding='UTF-8') w_easy = csv.writer(f_easy) w_hard = csv.writer(f_hard) for p in players[last_player:]: player = p[0] easy_check = player in [r[0] for r in weights_easy] hard_check = player in [r[0] for r in weights_hard] if easy_check and hard_check: print(f'skip {player}') continue teammates = session.query(func.unnest(Game.players))\ .filter(Game.players.any(player))\ .distinct()\