def get_starting_squad(fpl_team_id=None):
"""
use the transactions table in the db
"""
if not fpl_team_id:
# use the most recent transaction in the table
most_recent = (session.query(Transaction).order_by(
Transaction.id.desc()).filter_by(free_hit=0).first())
fpl_team_id = most_recent.fpl_team_id
print("Getting starting squad for {}".format(fpl_team_id))
s = Squad()
# Don't include free hit transfers as they only apply for the week the
# chip is activated
transactions = (session.query(Transaction).order_by(
Transaction.gameweek, Transaction.id).filter_by(
fpl_team_id=fpl_team_id).filter_by(free_hit=0).all())
for trans in transactions:
if trans.bought_or_sold == -1:
s.remove_player(trans.player_id, price=trans.price)
else:
# within an individual transfer we can violate the budget and squad
# constraints, as long as the final squad for that gameweek obeys them
s.add_player(
trans.player_id,
price=trans.price,
season=trans.season,
gameweek=trans.gameweek,
check_budget=False,
check_team=False,
)
return s
def fixture_num_assists(seasons=CHECK_SEASONS, session=session):
"""Check number of assists is less than or equal to number of goals
for home and away team in each fixture.
Less than or equal to as some goals do not result in an assist being
awarded.
Keyword Arguments:
seasons {[type]} -- seasons to check (default: {CHECK_SEASONS})
session {SQLAlchemy session} -- DB session (default:
airsenal.framework.schema.session)
"""
print("Checking no. assists less than or equal to no. goals...\n")
n_error = 0
for season in seasons:
fixtures = get_fixtures_for_season(season=season)
for fixture in fixtures:
result = get_result_for_fixture(fixture)
if result:
result = result[0]
home_scores = (
session.query(PlayerScore)
.filter_by(fixture=fixture, player_team=fixture.home_team)
.all()
)
away_scores = (
session.query(PlayerScore)
.filter_by(fixture=fixture, player_team=fixture.away_team)
.all()
)
home_assists = sum([score.assists for score in home_scores])
away_assists = sum([score.assists for score in away_scores])
if home_assists > result.home_score:
n_error += 1
print(
"{}: Player assists sum to {} but {} goals in result for home team".format(
fixture_string(fixture, result),
home_assists,
result.home_score,
)
)
if away_assists > result.away_score:
n_error += 1
print(
"{}: Player assists sum to {} but {} goals in result for away team".format(
fixture_string(fixture, result),
away_assists,
result.away_score,
)
)
print("\n", result_string(n_error))
return n_error
def fixture_num_conceded(seasons=CHECK_SEASONS, session=session):
"""Check number of goals concdeded equals goals scored by opposition if
player played whole match (90 minutes).
NB: only checks max of player conceded values to avoid potential issues
with substitutes and goals in stoppage time.
Keyword Arguments:
seasons {[type]} -- seasons to check (default: {CHECK_SEASONS})
session {SQLAlchemy session} -- DB session (default:
airsenal.framework.schema.session)
"""
print("Checking no. goals conceded matches goals scored by opponent...\n")
n_error = 0
for season in seasons:
fixtures = get_fixtures_for_season(season=season)
for fixture in fixtures:
result = get_result_for_fixture(fixture)
if result:
result = result[0]
home_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.home_team,
minutes=90).all())
away_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.away_team,
minutes=90).all())
home_conceded = max([score.conceded for score in home_scores])
away_conceded = max([score.conceded for score in away_scores])
if home_conceded != result.away_score:
n_error += 1
print(
"{}: Player conceded {} but {} goals in result for home team"
.format(
fixture_string(fixture, result),
home_conceded,
result.away_score,
))
if away_conceded != result.home_score:
n_error += 1
print(
"{}: Player conceded {} but {} goals in result for away team"
.format(
fixture_string(fixture, result),
away_conceded,
result.home_score,
))
print("\n", result_string(n_error))
return n_error
def get_starting_squad(fpl_team_id=None, use_api=False, apifetcher=None):
"""
use the transactions table in the db, or the API if requested
"""
if use_api:
if not fpl_team_id:
raise RuntimeError(
"Please specify fpl_team_id to get current squad from API")
players_prices = get_current_squad_from_api(fpl_team_id,
apifetcher=apifetcher)
s = Squad(season=CURRENT_SEASON)
for pp in players_prices:
s.add_player(
pp[0],
price=pp[1],
gameweek=NEXT_GAMEWEEK - 1,
check_budget=False,
check_team=False,
)
s.budget = get_bank(fpl_team_id, season=CURRENT_SEASON)
return s
# otherwise, we use the Transaction table in the DB
if not fpl_team_id:
# use the most recent transaction in the table
most_recent = (session.query(Transaction).order_by(
Transaction.id.desc()).filter_by(free_hit=0).first())
if most_recent is None:
raise ValueError("No transactions in database.")
fpl_team_id = most_recent.fpl_team_id
print("Getting starting squad for {}".format(fpl_team_id))
# Don't include free hit transfers as they only apply for the week the
# chip is activated
transactions = (session.query(Transaction).order_by(
Transaction.gameweek, Transaction.id).filter_by(
fpl_team_id=fpl_team_id).filter_by(free_hit=0).all())
if len(transactions) == 0:
raise ValueError(
f"No transactions in database for team ID {fpl_team_id}")
s = Squad(season=transactions[0].season)
for trans in transactions:
if trans.bought_or_sold == -1:
s.remove_player(trans.player_id, price=trans.price)
else:
# within an individual transfer we can violate the budget and squad
# constraints, as long as the final squad for that gameweek obeys them
s.add_player(
trans.player_id,
price=trans.price,
gameweek=trans.gameweek,
check_budget=False,
check_team=False,
)
return s
def fixture_num_goals(seasons=CHECK_SEASONS, session=session):
"""Check individual player goals sum to match result for each fixture.
Keyword Arguments:
seasons {[type]} -- seasons to check (default: {CHECK_SEASONS})
session {SQLAlchemy session} -- DB session (default:
airsenal.framework.schema.session)
"""
print("Checking sum of player goals equals match results...\n")
n_error = 0
for season in seasons:
fixtures = get_fixtures_for_season(season=season)
for fixture in fixtures:
result = fixture.result
if result:
home_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.home_team).all())
away_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.away_team).all())
home_goals = sum(score.goals for score in home_scores) + sum(
score.own_goals for score in away_scores)
away_goals = sum(score.goals for score in away_scores) + sum(
score.own_goals for score in home_scores)
if home_goals != result.home_score:
n_error += 1
msg = (
"{}: Player scores sum to {} but {} goals in result "
"for home team").format(
result,
home_goals,
result.home_score,
)
print(msg)
if away_goals != result.away_score:
n_error += 1
msg = (
"{}: Player scores sum to {} but {} goals in result "
"for away team").format(
result,
away_goals,
result.away_score,
)
print(msg)
print("\n", result_string(n_error))
return n_error
def fixture_num_players(seasons=CHECK_SEASONS, session=session):
"""Check each fixture has between 11 and 14 players with at least 1 minute
in player_scores.
Keyword Arguments:
seasons {[type]} -- seasons to check (default: {CHECK_SEASONS})
session {SQLAlchemy session} -- DB session (default:
airsenal.framework.schema.session)
"""
print("Checking 11 to 14 players play per team in each fixture...\n")
n_error = 0
for season in seasons:
fixtures = get_fixtures_for_season(season=season)
for fixture in fixtures:
result = get_result_for_fixture(fixture)
if result:
result = result[0]
home_scores = (
session.query(PlayerScore)
.filter_by(fixture=fixture, player_team=fixture.home_team)
.filter(PlayerScore.minutes > 0)
.all()
)
away_scores = (
session.query(PlayerScore)
.filter_by(fixture=fixture, player_team=fixture.away_team)
.filter(PlayerScore.minutes > 0)
.all()
)
if not ((len(home_scores) > 10) and (len(home_scores) < 15)):
n_error += 1
print(
"{}: {} players with minutes > 0 for home team.".format(
fixture_string(fixture, result), len(home_scores)
)
)
if not ((len(away_scores) > 10) and (len(away_scores) < 15)):
n_error += 1
print(
"{}: {} players with minutes > 0 for away team.".format(
fixture_string(fixture, result), len(away_scores)
)
)
print("\n", result_string(n_error))
return n_error
def fixture_num_players(seasons=CHECK_SEASONS, session=session):
"""Check each fixture has between 11 and 14 players with at least 1 minute
in player_scores. For season 19/20 it can be up to 16 players.
Keyword Arguments:
seasons {[type]} -- seasons to check (default: {CHECK_SEASONS})
session {SQLAlchemy session} -- DB session (default:
airsenal.framework.schema.session)
"""
print(
"Checking 11 to 14 players play per team in each fixture...\n"
"Note:\n"
"- 2019/20: 5 subs allowed after Covid-19 lockdown (accounted for in checks)\n"
"- From 2020/21: Concussion subs allowed (may cause false errors)\n")
n_error = 0
for season in seasons:
fixtures = get_fixtures_for_season(season=season)
for fixture in fixtures:
result = fixture.result
if result:
home_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.home_team).filter(
PlayerScore.minutes > 0).all())
away_scores = (session.query(PlayerScore).filter_by(
fixture=fixture, player_team=fixture.away_team).filter(
PlayerScore.minutes > 0).all())
# Rule change due to shorter season
if fixture.season == "1920" and int(fixture.gameweek) >= 39:
upper_team_limit = 16
else:
upper_team_limit = 14
if not ((len(home_scores) > 10) and
(len(home_scores) <= upper_team_limit)):
n_error += 1
print("{}: {} players with minutes > 0 for home team.".
format(result, len(home_scores)))
if not ((len(away_scores) > 10) and
(len(away_scores) <= upper_team_limit)):
n_error += 1
print("{}: {} players with minutes > 0 for away team.".
format(result, len(away_scores)))
print("\n", result_string(n_error))
return n_error
def get_transfer_suggestions(currentsession, Suggestions):
"""
query the transfer_suggestion table. Each row of the table
will be in individual player in-or-out in a gameweek - we
therefore need to group together all the rows that correspond
to the same transfer strategy. We do this using the "timestamp".
"""
all_rows = currentsession.query(Suggestions).all()
last_timestamp = all_rows[-1].timestamp
rows = (session.query(TransferSuggestion).filter_by(
timestamp=last_timestamp).order_by(TransferSuggestion.gameweek).all())
return rows
def get_player_history_table(position="all"):
"""
Query the player_score table.
"""
with open("player_history_{}.csv".format(position), "w") as output_file:
output_file.write(
"player_id,player_name,match_id,goals,assists,minutes,team_goals\n"
)
player_ids = list_players(position)
for pid in player_ids:
player_name = get_player_name(pid)
results = session.query(PlayerScore).filter_by(player_id=pid).all()
row_count = 0
for row in results:
minutes = row.minutes
match_id = row.match_id
goals = row.goals
assists = row.assists
# find the match, in order to get team goals
Match = None # TODO: Placeholder for missing (deprecated?) Match class
match = session.query(Match).filter_by(
match_id=row.match_id).first()
if match.home_team == row.opponent:
team_goals = match.away_score
elif match.away_team == row.opponent:
team_goals = match.home_score
else:
print("Unknown opponent!")
team_goals = -1
output_file.write("{},{},{},{},{},{},{}\n".format(
pid, player_name, match_id, goals, assists, minutes,
team_goals))
row_count += 1
if row_count < 38 * 3:
for _ in range(row_count, 38 * 3):
output_file.write("{},{},0,0,0,0,0\n".format(
pid, player_name))
def get_starting_squad():
"""
use the transactions table in the db
"""
s = Squad()
# Don't include free hit transfers as they only apply for the week the chip is activated
transactions = (session.query(Transaction).order_by(
Transaction.id).filter_by(free_hit=0).all())
for trans in transactions:
if trans.bought_or_sold == -1:
s.remove_player(trans.player_id, price=trans.price)
else:
## within an individual transfer we can violate the budget and team constraints,
## as long as the final team for that gameweek obeys them
s.add_player(
trans.player_id,
price=trans.price,
season=trans.season,
gameweek=trans.gameweek,
check_budget=False,
check_team=False,
)
return s
#!/usr/bin/env python
"""
query the transfer_suggestion table. Each row of the table
will be in individual player in-or-out in a gameweek - we
therefore need to group together all the rows that correspond
to the same transfer strategy. We do this using the "timestamp".
"""
from airsenal.framework.schema import TransferSuggestion
from airsenal.framework.utils import session, get_player_name
if __name__ == "__main__":
all_rows = session.query(TransferSuggestion).all()
last_timestamp = all_rows[-1].timestamp
rows = (session.query(TransferSuggestion).filter_by(
timestamp=last_timestamp).order_by(TransferSuggestion.gameweek).all())
output_string = "Suggested transfer strategy: \n"
current_gw = 0
for row in rows:
if row.gameweek != current_gw:
output_string += " gameweek {}: ".format(row.gameweek)
current_gw = row.gameweek
if row.in_or_out < 0:
output_string += " sell "
else:
output_string += " buy "
output_string += get_player_name(row.player_id) + ","
output_string += " for a total gain of {} points.".format(
rows[0].points_gain)
print(output_string)
def main():
# Dump Player database
player_fieldnames = ["player_id", "name"]
output_path = os.path.join(os.path.dirname(__file__),
"../data/players.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=player_fieldnames)
writer.writeheader()
for player in session.query(Player).all():
player = vars(player)
row = {}
for field in player:
if (isinstance(player[field], str)
or isinstance(player[field], int)
or isinstance(player[field], float)):
row[field] = player[field]
writer.writerow(row)
print(" ==== dumped Player database === ")
# Dump PlayerAttributes database
player_attributes_fieldnames = [
"id",
"player_id",
"season",
"gameweek",
"price",
"team",
"position",
"transfers_balance",
"selected",
"transfers_in",
"transfers_out",
]
output_path = os.path.join(os.path.dirname(__file__),
"../data/player_attributes.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile,
fieldnames=player_attributes_fieldnames)
writer.writeheader()
for player_attributes in session.query(PlayerAttributes).all():
player_attributes = vars(player_attributes)
row = {}
for field in player_attributes:
if (isinstance(player_attributes[field], str)
or isinstance(player_attributes[field], int)
or isinstance(player_attributes[field], float)):
row[field] = player_attributes[field]
writer.writerow(row)
print(" ==== dumped PlayerAttributes database === ")
# Dump Fixture database
fixture_fieldnames = [
"fixture_id",
"date",
"gameweek",
"home_team",
"away_team",
"season",
"tag",
"player_id",
]
output_path = os.path.join(os.path.dirname(__file__),
"../data/fixtures.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=fixture_fieldnames)
writer.writeheader()
for fixture in session.query(Fixture).all():
fixture = vars(fixture)
row = {}
for field in fixture:
if (isinstance(fixture[field], str)
or isinstance(fixture[field], int)
or isinstance(fixture[field], float)):
row[field] = fixture[field]
writer.writerow(row)
print(" ==== dumped Fixture database === ")
# Dump Result database
result_fieldnames = [
"result_id",
"fixture_id",
"home_score",
"away_score",
"player_id",
]
output_path = os.path.join(os.path.dirname(__file__),
"../data/results.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=result_fieldnames)
writer.writeheader()
for result in session.query(Result).all():
result = vars(result)
row = {}
for field in result:
if (isinstance(result[field], str)
or isinstance(result[field], int)
or isinstance(result[field], float)):
row[field] = result[field]
writer.writerow(row)
print(" ==== dumped Result database === ")
# Dump Team database
team_fieldnames = ["id", "name", "full_name", "season", "team_id"]
output_path = os.path.join(os.path.dirname(__file__), "../data/teams.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=team_fieldnames)
writer.writeheader()
for team in session.query(Team).all():
team = vars(team)
row = {}
for field in team:
if (isinstance(team[field], str)
or isinstance(team[field], int)
or isinstance(team[field], float)):
row[field] = team[field]
writer.writerow(row)
print(" ==== dumped Team database === ")
# Dump FifaTeamRating database
# Add season to the fieldnames once the table creation is updated
fifa_team_rating_fieldnames = ["team", "att", "defn", "mid", "ovr"]
output_path = os.path.join(os.path.dirname(__file__),
"../data/fifa_team_ratings.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile,
fieldnames=fifa_team_rating_fieldnames)
writer.writeheader()
for fifa_team_rating in session.query(FifaTeamRating).all():
fifa_team_rating = vars(fifa_team_rating)
row = {}
for field in fifa_team_rating:
if (isinstance(fifa_team_rating[field], str)
or isinstance(fifa_team_rating[field], int)
or isinstance(fifa_team_rating[field], float)):
row[field] = fifa_team_rating[field]
writer.writerow(row)
print(" ==== dumped FifaTeamRating database === ")
# Dump Transaction database
transaction_fieldnames = [
"id",
"player_id",
"gameweek",
"bought_or_sold",
"season",
"tag",
"price",
]
output_path = os.path.join(os.path.dirname(__file__),
"../data/transactions.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=transaction_fieldnames)
writer.writeheader()
for transaction in session.query(Transaction).all():
transaction = vars(transaction)
row = {}
for field in transaction:
if (isinstance(transaction[field], str)
or isinstance(transaction[field], int)
or isinstance(transaction[field], float)):
row[field] = transaction[field]
writer.writerow(row)
print(" ==== dumped Transaction database === ")
# Dump PlayerScore database
player_score_fieldnames = [
"id",
"player_team",
"opponent",
"points",
"goals",
"assists",
"bonus",
"conceded",
"minutes",
"player_id",
"result_id",
"fixture_id",
"clean_sheets",
"own_goals",
"penalties_saved",
"penalties_missed",
"yellow_cards",
"red_cards",
"saves",
"bps",
"influence",
"creativity",
"threat",
"ict_index",
"value",
"transfers_balance",
"selected",
"transfers_in",
"transfers_out",
]
output_path = os.path.join(os.path.dirname(__file__),
"../data/player_scores.csv")
with open(output_path, "w") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=player_score_fieldnames)
writer.writeheader()
for player_score in session.query(PlayerScore).all():
player_score = vars(player_score)
row = {}
for field in player_score:
if (isinstance(player_score[field], str)
or isinstance(player_score[field], int)
or isinstance(player_score[field], float)):
row[field] = player_score[field]
writer.writerow(row)
print(" ==== dumped PlayerScore database === ")
