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()\
