def test_lineup_designate_starter(session, match_data, person_data, position_data): starter_indx = 0 lineups = [ mcm.MatchLineups( match=mcm.Matches(**match_data), player=mcp.Players(**plyr), position=pos, is_starting=(j == starter_indx)) for j, (plyr, pos) in enumerate(zip(person_data['player'], position_data)) ] session.add_all(lineups) starter_position = position_data[starter_indx] lineup_from_db = session.query(mcm.MatchLineups).join(mcp.Positions).filter( mcp.Positions.name == starter_position.name).all() assert len(lineup_from_db) == 1 assert lineup_from_db[0].is_starting is True assert lineup_from_db[0].is_captain is False other_lineup_from_db = session.query(mcm.MatchLineups).join(mcp.Positions).filter( mcp.Positions.name != starter_position.name).all() for others in other_lineup_from_db: assert others.is_starting is False assert others.is_captain is False
def test_club_goal_insert(session, club_data, person_data, position_data): match = mc.ClubLeagueMatches(matchday=15, **club_data) session.add(match) player = mcp.Players(position=position_data[0], **person_data['player'][0]) session.add(player) session.commit() club_from_db = session.query( mc.Clubs).filter(mc.Clubs.name == u"Arsenal FC").one() lineup = mc.ClubMatchLineups(match_id=match.id, team_id=club_from_db.id, player_id=player.id, position_id=player.position_id) session.add(lineup) session.commit() goal = mc.ClubGoals(lineup_id=lineup.id, team_id=club_from_db.id, bodypart=enums.BodypartType.head, event=enums.ShotEventType.cross_ck, time=70) session.add(goal) goals_from_db = session.query(mc.ClubGoals).join(mc.ClubMatchLineups).join( mc.ClubLeagueMatches).filter(mc.ClubLeagueMatches.id == match.id) assert goals_from_db.count() == 1 assert goals_from_db[0].team.name == u"Arsenal FC" assert goals_from_db[0].lineup.full_name == u"Miguel Ángel Ponce" assert goals_from_db[0].bodypart.value == "Head" assert goals_from_db[0].event.value == "Cross from corner kick" assert goals_from_db[0].time == 70
def test_natl_match_lineup_insert(session, national_data, person_data, position_data): match = mn.NationalGroupMatches( group_round=enums.GroupRoundType.first_round, group='C', matchday=2, **national_data ) session.add(match) session.commit() nation_from_db = session.query(mco.Countries).filter(mco.Countries.name == u"Mexico").one() player_data = person_data['player'][0] del player_data['country'] player_data['country_id'] = nation_from_db.id player = mcp.Players(position=position_data[0], **player_data) session.add(player) session.commit() lineup = mn.NationalMatchLineups( match_id=match.id, team_id=nation_from_db.id, player_id=player.id, position_id=player.position_id ) session.add(lineup) lineup_from_db = session.query(mn.NationalMatchLineups).join(mn.NationalGroupMatches).\ filter(mn.NationalGroupMatches.id == match.id) assert lineup_from_db.count() == 1 assert unicode(lineup_from_db[0]) == u"<NationalMatchLineup(match={}, player=Miguel Ángel Ponce, team=Mexico, " \ u"position=Left back, starter=False, captain=False)>".format(match.id)
def test_substitutions_insert(session, match_lineup, person_data): match_from_db, lineup_from_db = match_lineup bench_lineup = mcm.MatchLineups( match_id=match_from_db.id, player=mcp.Players(**person_data['generic']), position=mcp.Positions(name=u"Center back", type=enums.PositionType.defender), is_starting=False, is_captain=False) session.add(bench_lineup) session.commit() substitution = mce.Substitutions(lineup_in_id=bench_lineup.id, lineup_out_id=lineup_from_db.id, time=67) session.add(substitution) lineup_alias = aliased(mcm.MatchLineups) substitution_from_db = session.query(mce.Substitutions)\ .join(mcm.MatchLineups, mcm.MatchLineups.id == mce.Substitutions.lineup_in_id)\ .join(lineup_alias, lineup_alias.id == mce.Substitutions.lineup_out_id)\ .join(mcm.Matches).filter(mcm.Matches.id == match_from_db.id) assert substitution_from_db.count() == 1 assert substitution_from_db[0].lineup_out.full_name == u"Cristiano Ronaldo" assert substitution_from_db[0].lineup_in.full_name == u"John Doe" assert substitution_from_db[0].time == 67
def test_player_insert(session, person_data, position_data): player_data = [ data for key, records in person_data.items() for data in records if key in ['player'] ] for player, position in zip(player_data, position_data): player['position'] = position players = [mcp.Players(**data) for data in player_data] session.add_all(players) players_from_db = session.query(mcp.Players) assert players_from_db.count() == len(players)
def test_player_history_insert(session, person_data, player_history_data): player_data = dict(position=mcp.Positions( name=u'Central Midfielder', type=enums.PositionType.midfielder), **person_data['generic']) generic_player = mcp.Players(**player_data) player_history = [ mcp.PlayerHistory(**dict(player=generic_player, **data)) for data in player_history_data ] session.add_all(player_history) history_from_db = session.query(mcp.PlayerHistory).join(mcp.Players).\ filter(mcp.Players.last_name == u"Doe") assert history_from_db.count() == len(player_history)
def test_match_lineup_generic_insert(session, match_data, person_data, position_data): lineup = mcm.MatchLineups( match=mcm.Matches(**match_data), player=mcp.Players(**person_data['player'][1]), position=position_data[1] ) session.add(lineup) lineup_from_db = session.query(mcm.MatchLineups).one() match_from_db = session.query(mcm.Matches).one() player_from_db = session.query(mcp.Players).one() assert lineup_from_db.is_starting is False assert lineup_from_db.is_captain is False assert lineup_from_db.match_id == match_from_db.id assert lineup_from_db.player_id == player_from_db.id
def test_player_history_representation(session, person_data, player_history_data): player_data = dict(position=mcp.Positions( name=u'Central Midfielder', type=enums.PositionType.midfielder), **person_data['generic']) generic_player = mcp.Players(**player_data) player_history = [ mcp.PlayerHistory(**dict(player=generic_player, **data)) for data in player_history_data ] session.add_all(player_history) history_from_db = session.query(mcp.PlayerHistory).join(mcp.Players).\ filter(mcp.Players.last_name == u"Doe", mcp.PlayerHistory.date == date(1998, 7, 15)).one() assert repr( history_from_db ) == u"<PlayerHistory(name=John Doe, date=1998-07-15, height=1.74, weight=76)>"
def test_natl_goal_insert(session, national_data, person_data, position_data): match = mn.NationalKnockoutMatches( ko_round=enums.KnockoutRoundType.round_16, **national_data ) session.add(match) session.commit() nation_from_db = session.query(mco.Countries).filter(mco.Countries.name == u"Mexico").one() player_data = person_data['player'][0] del player_data['country'] player_data['country_id'] = nation_from_db.id player = mcp.Players(position=position_data[0], **player_data) session.add(player) session.commit() lineup = mn.NationalMatchLineups( match_id=match.id, team_id=nation_from_db.id, player_id=player.id, position_id=player.position_id ) session.add(lineup) session.commit() goal = mn.NationalGoals( lineup_id=lineup.id, team_id=nation_from_db.id, bodypart=enums.BodypartType.head, event=enums.ShotEventType.cross_ck, time=70 ) session.add(goal) goals_from_db = session.query(mn.NationalGoals).join(mn.NationalMatchLineups)\ .join(mn.NationalKnockoutMatches).filter(mn.NationalKnockoutMatches.id == match.id) assert goals_from_db.count() == 1 assert goals_from_db[0].team.name == u"Mexico" assert goals_from_db[0].lineup.full_name == u"Miguel Ángel Ponce" assert goals_from_db[0].bodypart.value == "Head" assert goals_from_db[0].event.value == "Cross from corner kick" assert goals_from_db[0].time == 70
def test_club_match_lineup_insert(session, club_data, person_data, position_data): match = mc.ClubLeagueMatches(matchday=15, **club_data) session.add(match) player = mcp.Players(position=position_data[0], **person_data['player'][0]) session.add(player) session.commit() club_from_db = session.query( mc.Clubs).filter(mc.Clubs.name == u"Arsenal FC").one() lineup = mc.ClubMatchLineups(match_id=match.id, team_id=club_from_db.id, player_id=player.id, position_id=player.position_id) session.add(lineup) lineup_from_db = session.query(mc.ClubMatchLineups).join(mc.ClubLeagueMatches)\ .filter(mc.ClubLeagueMatches.id == match.id) assert lineup_from_db.count() == 1 assert unicode(lineup_from_db[0]) == u"<ClubMatchLineup(match={}, player=Miguel Ángel Ponce, team=Arsenal FC, " \ u"position=Left back, starter=False, captain=False)>".format(match.id)
def match_lineup(session, match_data, person_data, position_data): match = mcm.Matches(**match_data) session.add(match) match_from_db = session.query(mcm.Matches).one() lineups = [ mcm.MatchLineups(match_id=match_from_db.id, player=mcp.Players(**plyr), position=pos, is_starting=True, is_captain=False) for j, (plyr, pos) in enumerate(zip(person_data['player'], position_data)) ] session.add_all(lineups) scorer_indx = 1 lineup_from_db = session.query(mcm.MatchLineups).join(mcp.Players).\ filter(mcp.Players.last_name == person_data['player'][scorer_indx]['last_name']).one() return match_from_db, lineup_from_db
def test_player_representation(session, person_data, position_data): player_data = [ data for key, records in person_data.items() for data in records if key in ['player'] ] for player, position in zip(player_data, position_data): player['position'] = position players = [mcp.Players(**data) for data in player_data] session.add_all(players) korean_player = session.query(mcp.Players).join(mco.Countries).\ filter(mco.Countries.name == u"Korea Republic").one() assert repr(korean_player) == u"<Player(name=Son Heung-Min, DOB=1992-07-08, " \ u"country=Korea Republic, position=Second striker)>" ronaldo = session.query(mcp.Players).filter( mcp.Players.nick_name == u"Cristiano Ronaldo").one() assert repr(ronaldo) == u"<Player(name=Cristiano Ronaldo, DOB=1985-02-05, " \ u"country=Portugal, position=Forward)>" mexican_player = session.query(mcp.Players).join( mco.Countries).filter(mco.Countries.name == u"Mexico").one() assert unicode(mexican_player) == u"<Player(name=Miguel Ángel Ponce, DOB=1989-04-12, " \ u"country=Mexico, position=Left back)>"
def players(self, data_frame): player_set = set() player_records = [] remote_countryids = [] remote_ids = [] fields = [ 'known_first_name', 'first_name', 'middle_name', 'last_name', 'second_last_name', 'nick_name', 'birth_date', 'order', 'country_id', 'position_id', 'remote_id', 'remote_country_id' ] for _, row in data_frame.iterrows(): player_set.add( tuple([(field, row[field]) for field in fields if field in row and row[field] is not None])) logger.info("{} players in data feed".format(len(player_set))) for elements in player_set: player_dict = dict(elements) remote_id = player_dict.pop('remote_id') remote_country_id = player_dict.pop('remote_country_id', None) if not self.record_exists(mcs.PlayerMap, remote_id=remote_id): if not self.record_exists(mcp.Players, **player_dict): player_records.append(mcp.Players(**player_dict)) remote_ids.append(remote_id) remote_countryids.append(remote_country_id) else: player_id = self.session.query( mcp.Players).filter_by(**player_dict).one().id map_record = mcs.PlayerMap(id=player_id, remote_id=remote_id, supplier_id=self.supplier_id) self.session.add(map_record) else: player_id = self.session.query( mcs.PlayerMap).filter_by(remote_id=remote_id).one().id if not self.record_exists(mcp.Players, **player_dict): updated_records = self.session.query(mcp.Players).\ filter(mcp.Players.person_id == mcp.Persons.person_id).\ filter(mcp.Players.id == player_id) for rec in updated_records: for field, value in player_dict.items(): setattr(rec, field, value) if self.session.dirty: self.session.commit() logger.info("{} player records ingested".format(len(player_records))) self.session.add_all(player_records) self.session.commit() map_records = [ mcs.PlayerMap(id=player_record.id, remote_id=remote_id, supplier_id=self.supplier_id) for remote_id, player_record in zip(remote_ids, player_records) if remote_id ] self.session.add_all(map_records) self.session.commit() for remote_id, player_record in zip(remote_countryids, player_records): if remote_id and not self.record_exists( mcs.CountryMap, remote_id=remote_id, supplier_id=self.supplier_id): self.session.add( mcs.CountryMap(id=player_record.country_id, remote_id=remote_id, supplier_id=self.supplier_id)) self.session.commit()