def write_new_match(match_attributes):
"""
Write new match (represented as a dictionary in input) in the DB
:param match_attributes:
:return:
"""
SQLLite.get_connection().insert("Match", match_attributes)
def write_new_player(player_name,
player_fifa_api_id,
birthday,
height,
weight,
player_api_id=None):
"""
Insert a new player in the DB
:param player_name:
:param player_fifa_api_id:
:param birthday:
:param height:
:param weight:
:param player_api_id:
:return:
"""
print("Inserting new player", player_name, player_api_id,
player_fifa_api_id)
player_diz = dict()
player_diz["player_name"] = player_name
if not util.is_None(player_fifa_api_id):
player_diz["player_fifa_api_id"] = player_fifa_api_id
if not util.is_None(birthday):
player_diz["birthday"] = birthday
if not util.is_None(height):
player_diz["height"] = height
if not util.is_None(weight):
player_diz["weight"] = weight
if not util.is_None(player_api_id):
player_diz["player_api_id"] = player_api_id
SQLLite.get_connection().insert("Player", player_diz)
return read_by_fifa_api_id(player_fifa_api_id)
def add_name(self, new_league_name):
"""
Add the input name to this league
:param new_league_name:
:return:
"""
names = self.name + "|" + new_league_name
update = "UPDATE League set name = '" + names + "' where id='" + str(
self.id) + "'"
SQLLite.get_connection().execute_update(update)
def update_match(match, match_attributes):
"""
Update the match
:param match:
:param match_attributes:
:return:
"""
for attribute, value in match_attributes.items():
match.__setattr__(attribute, value)
SQLLite.get_connection().update("Match", match)
def read_players_api_id_by_team_api_id(team_api_id, season=None):
"""
return a list of player_api_id
if season is set, consider only that list
:param team_api_id:
:param season:
:return:
"""
players_api_id = set()
filter = {}
if season:
filter["season"] = season
else:
season = ""
try:
return Cache.get_element(
str(team_api_id) + "_" + season,
"MATCH_GET_PLAYERS_BY_TEAM_API_ID")
except KeyError:
pass
filter["home_team_api_id"] = team_api_id
for sqllite_row in SQLLite.get_connection().select(
"Match",
column_filter="home_player_1, home_player_2, "
"home_player_3, home_player_4, "
"home_player_5, home_player_6, "
"home_player_7, home_player_8, "
"home_player_9, home_player_10, "
"home_player_11",
**filter):
for home_player_i, player_api_id in sqllite_row.items():
if player_api_id:
players_api_id.add(player_api_id)
del (filter["home_team_api_id"])
filter["away_team_api_id"] = team_api_id
for sqllite_row in SQLLite.get_connection().select(
"Match",
column_filter="away_player_1, away_player_2, "
"away_player_3, away_player_4, "
"away_player_5, away_player_6, "
"away_player_7, away_player_8, "
"away_player_9, away_player_10, "
"away_player_11",
**filter):
for away_player_i, player_api_id in sqllite_row.items():
if not util.is_None(player_api_id):
players_api_id.add(player_api_id)
Cache.add_element(
str(team_api_id) + "_" + season, players_api_id,
"MATCH_GET_PLAYERS_BY_TEAM_API_ID")
return players_api_id
def update(player):
"""
Update the player in the DB, and return the last version of it
:param player:
:return:
"""
SQLLite.get_connection().update("Player", player)
Cache.del_element(player.player_fifa_api_id, "PLAYER_BY_FIFA_API_ID")
Cache.del_element(player.player_api_id, "PLAYER_BY_API_ID")
Cache.del_element(player.player_name, "PLAYER_BY_NAME")
Cache.del_element(player.id, "PLAYER_BY_ID")
return read_by_id(player.id)
def read_matches_by_away_team(team_api_id, season=None):
"""
Read matches of the team identified by team_api_id, when it plays AWAY
:param team_api_id:
:param season:
:return:
"""
match_list = []
filter = {"away_team_api_id": team_api_id}
if season:
filter["season"] = season
else:
season = ""
try:
return Cache.get_element(str(team_api_id) + "_" + season, "MATCH_AWAY")
except KeyError:
pass
for sqllite_row in SQLLite.get_connection().select("Match", **filter):
match = Match(sqllite_row["id"])
for attribute, value in sqllite_row.items():
match.__setattr__(attribute, value)
match_list.append(match)
Cache.add_element(
str(team_api_id) + "_" + season, match_list, "MATCH_AWAY")
return match_list
def write_player_attributes(player, player_attributes, date=util.get_today_date()+" 00:00:00"):
"""
Write a new player attribute in the DB
:param player:
:param player_attributes:
:param date:
:return:
"""
log.debug("write_player_attributes of player_fifa_api_id = [" + str(player.player_fifa_api_id) + "]")
player_attributes["player_fifa_api_id"] = player.player_fifa_api_id
player_attributes["player_api_id"] = player.player_api_id
player_attributes["date"] = date
SQLLite.get_connection().insert("Player_Attributes", player_attributes)
Cache.del_element(player.player_fifa_api_id, "PLAYER_ATTRIBUTES")
def read_by_player_api_id(player_api_id, only_stages=True):
"""
Read the matches played by the input-player
:param player_api_id:
:param only_stages:
:return:
"""
try:
return Cache.get_element(player_api_id, "MATCH_BY_PLAYER_API_ID")
except KeyError:
pass
home_player_i = 'home_player_'
away_player_i = 'away_player_'
or_filter = {}
for i in range(11):
or_filter[home_player_i + str(i + 1)] = player_api_id
or_filter[away_player_i + str(i + 1)] = player_api_id
match_list = []
for sqllite_row in SQLLite.get_connection().select_or(
"Match", **or_filter):
match = Match(sqllite_row["id"])
for attribute, value in sqllite_row.items():
match.__setattr__(attribute, value)
if only_stages and type(match.stage) != int:
continue
match_list.append(match)
Cache.add_element(player_api_id, match_list, "MATCH_BY_PLAYER_API_ID")
return match_list
def read_matches_by_league(league_id, season=None, only_stages=True):
"""
return matches played in the league_id, in a specified season if required
Only stages allow to filter-out dirty data
:param league_id:
:param season:
:param only_stages:
:return:
"""
match_list = []
filter = {"league_id": league_id}
if season:
filter["season"] = season
else:
season = ""
try:
return Cache.get_element(
str(league_id) + "_" + season, "MATCH_BY_LEAGUE")
except KeyError:
pass
for sqllite_row in SQLLite.get_connection().select("Match", **filter):
match = Match(sqllite_row["id"])
for attribute, value in sqllite_row.items():
match.__setattr__(attribute, value)
if only_stages and type(match.stage) != int:
continue
match_list.append(match)
Cache.add_element(
str(league_id) + "_" + season, match_list, "MATCH_BY_LEAGUE")
return match_list
def read_by_fifa_api_id(player_fifa_api_id):
"""
Read a player by its team_fifa_api_id
:param player_fifa_api_id:
:return:
"""
try:
return Cache.get_element(player_fifa_api_id, "PLAYER_BY_FIFA_API_ID")
except KeyError:
pass
filter = {"player_fifa_api_id": player_fifa_api_id}
try:
sqllite_row = SQLLite.get_connection().select("Player", **filter)[0]
except IndexError:
return None
player = Player(sqllite_row["id"])
for attribute, value in sqllite_row.items():
player.__setattr__(attribute, value)
Cache.add_element(player.player_fifa_api_id, player,
"PLAYER_BY_FIFA_API_ID")
Cache.add_element(player.player_api_id, player, "PLAYER_BY_API_ID")
Cache.add_element(player.player_name, player, "PLAYER_BY_NAME")
Cache.add_element(player.id, player, "PLAYER_BY_ID")
return player
def write_team_attributes(team,
team_attributes,
date=util.get_today_date() + " 00:00:00"):
"""
Persist the team attributes of the team
:param team:
:param team_attributes:
:param date:
:return:
"""
log.debug("write_team_attributes of team_fifa_api_id = [" +
str(team.team_fifa_api_id) + "]")
team_attributes["team_fifa_api_id"] = team.team_fifa_api_id
team_attributes["team_api_id"] = team.team_api_id
team_attributes["date"] = date
SQLLite.get_connection().insert("Team_Attributes", team_attributes)
Cache.del_element(team.team_fifa_api_id, "TEAM_ATTRIBUTES")
def write_new_bet_event(match_event_id, event_name, bet_values):
'''
Insert a new bet event in the DB
:param match_event_id:
:param event_name:
:param bet_values:
:return:
'''
bet_values_str = json.dumps(bet_values)
# decode
#j = json.loads(l)
#print(j, type(j))
SQLLite.get_connection().insert(
"Bet_Event", {
'match_event_id': match_event_id,
'event_name': event_name,
'bet_value': bet_values_str,
'date': util.get_today_date(with_hours=True)
})
def read_all():
"""
Read all the team_attributes
:return:
"""
team_attributes_list = []
for p in SQLLite.read_all("Team_Attributes"):
team_attributes = Team_Attributes(p["id"])
for attribute, value in p.items():
team_attributes.__setattr__(attribute, value)
team_attributes_list.append(team_attributes)
return team_attributes_list
def read_all():
"""
Read all players
:return:
"""
players = []
for p in SQLLite.read_all("Player"):
player = Player(p["id"])
for attribute, value in p.items():
player.__setattr__(attribute, value)
players.append(player)
return players
def read_all():
"""
Read all the player attributes
:return:
"""
player_attibutes_list = []
for p in SQLLite.read_all("Player_Attributes"):
player_attributes = PlayerAttributes(p["id"])
for attribute, value in p.items():
player_attributes.__setattr__(attribute, value)
player_attibutes_list.append(player_attributes)
return player_attibutes_list
def read_by_name(name, like=False):
"""
Return the list of leagues with this name
:param name:
:param like:
:return:
"""
if like:
sqllite_row = SQLLite.get_connection().select_like(
"League", **{"name": name})
else:
sqllite_row = SQLLite.get_connection().select("League",
**{"name": name})
leagues = []
for p in sqllite_row:
league = League(p["id"])
for attribute, value in p.items():
league.__setattr__(attribute, value)
leagues.append(league)
return leagues
def read_by_name(player_name, like=False):
"""
Read a player by its name
:param player_name:
:param like:
:return:
"""
filter = {"player_name": player_name}
if like:
sqlrows = SQLLite.get_connection().select_like("Player", **filter)
else:
sqlrows = SQLLite.get_connection().select("Player", **filter)
players = []
for p in sqlrows:
player = Player(p["id"])
for attribute, value in p.items():
player.__setattr__(attribute, value)
players.append(player)
return players
def read_by_name(name, like=False):
"""
read a country by its name
:param name:
:param like:
:return:
"""
if like:
sqllite_rows = SQLLite.get_connection().select_like(
"Country", **{"name": name})
else:
sqllite_rows = SQLLite.get_connection().select("Country",
**{"name": name})
countries = []
for c in sqllite_rows:
country = Country(c["id"])
for attribute, value in c.items():
country.__setattr__(attribute, value)
countries.append(country)
return countries
def read_all(column_filter='*'):
"""
Reads all countries from the DB
:param column_filter:
:return:
"""
countries = []
for c in SQLLite.read_all("Country", column_filter):
country = Country(c["id"])
for attribute, value in c.items():
country.__setattr__(attribute, value)
countries.append(country)
return countries
def read_all(column_filter='*'):
"""
Read all the matches
:param column_filter:
:return:
"""
match_list = []
for p in SQLLite.read_all("Match", column_filter=column_filter):
match = Match(p["id"])
for attribute, value in p.items():
match.__setattr__(attribute, value)
match_list.append(match)
return match_list
def read_all():
"""
Return all the leagues
:return:
"""
league_list = []
for p in SQLLite.read_all("League"):
league = League(p["id"])
for attribute, value in p.items():
league.__setattr__(attribute, value)
league_list.append(league)
return league_list
def read_by_country(country_id):
"""
Return the league in the country identified by country_id
:param country_id:
:return:
"""
sqllite_rows = SQLLite.get_connection().select(
"League", **{"country_id": country_id})
league_list = []
for p in sqllite_rows:
league = League(p["id"])
for attribute, value in p.items():
league.__setattr__(attribute, value)
league_list.append(league)
return league_list
def read_by_team_api_id(team_api_id, season=None):
"""
Return list of players that play in the team identified my team_api_id
if season is set, consider only that season
:param team_api_id:
:param season:
:return:
"""
if not season:
season = ""
try:
return Cache.get_element(
str(team_api_id) + "_" + season, "PLAYER_BY_TEAM_API_ID")
except KeyError:
pass
players = []
players_api_id = Match.read_players_api_id_by_team_api_id(
team_api_id, season)
for player_api_id in players_api_id:
# if the player_api_id is not set --> continue
if util.is_None(player_api_id):
continue
try:
player = Cache.get_element(player_api_id, "PLAYER_BY_API_ID")
except KeyError:
filter = {"player_api_id": player_api_id}
try:
sqllite_row = SQLLite.get_connection().select(
"Player", **filter)[0]
except IndexError:
log.warning("Player api id not found in DB [" +
str(player_api_id) + "]")
continue
player = Player(sqllite_row["id"])
for attribute, value in sqllite_row.items():
player.__setattr__(attribute, value)
Cache.add_element(player_api_id, player, "PLAYER_BY_API_ID")
players.append(player)
Cache.add_element(
str(team_api_id) + "_" + season, players, "PLAYER_BY_TEAM_API_ID")
return players
def get_seasons(self):
"""
Return the stored seasons of this league
:return:
"""
try:
return Cache.get_element(self.id, "SEASONS_BY_LEAGUE")
except KeyError:
pass
seasons = []
query = "SELECT distinct(season) FROM Match WHERE league_id='" + str(
self.id) + "'"
for sqllite_row in SQLLite.get_connection().execute_select(query):
seasons.append(sqllite_row[0])
Cache.add_element(self.id, seasons, "SEASONS_BY_LEAGUE")
return seasons
def read_by_id(id):
"""
Return the league with this id
:param id:
:return:
"""
try:
return Cache.get_element(id, "LEAGUE_BY_ID")
except KeyError:
pass
sqllite_row = SQLLite.get_connection().select("League", **{"id": id})[0]
league = League(sqllite_row["id"])
for attribute, value in sqllite_row.items():
league.__setattr__(attribute, value)
Cache.add_element(league.id, league, "LEAGUE_BY_ID")
Cache.add_element(league.country_id, league, "LEAGUE_BY_COUNTRY")
return league
def read_by_player_fifa_api_id(player_fifa_api_id):
"""
return a player by its fifa api id
:param player_fifa_api_id:
:return:
"""
try:
return Cache.get_element(player_fifa_api_id, "PLAYER_ATTRIBUTES")
except KeyError:
pass
player_attributes_list = []
for sqllite_row in SQLLite.get_connection().\
select("Player_Attributes", **{"player_fifa_api_id": player_fifa_api_id}):
player_attributes = PlayerAttributes(sqllite_row["id"])
for attribute, value in sqllite_row.items():
player_attributes.__setattr__(attribute, value)
player_attributes_list.append(player_attributes)
Cache.add_element(player_fifa_api_id, player_attributes_list, "PLAYER_ATTRIBUTES")
return player_attributes_list
def read_by_team_fifa_api_id(team_fifa_api_id):
"""
:param team_fifa_api_id:
:return:
"""
try:
return Cache.get_element(team_fifa_api_id, "TEAM_ATTRIBUTES")
except KeyError:
pass
team_attributes_list = []
for sqllite_row in SQLLite.get_connection().select(
"Team_Attributes", **{"team_fifa_api_id": team_fifa_api_id}):
team_attributes = Team_Attributes(sqllite_row["id"])
for attribute, value in sqllite_row.items():
team_attributes.__setattr__(attribute, value)
team_attributes_list.append(team_attributes)
Cache.add_element(team_fifa_api_id, team_attributes_list,
"TEAM_ATTRIBUTES")
return team_attributes_list
def read_by_match_date(date_str, order_by_date=False):
"""
Return all the matches played in the input-date
:param date_str:
:return:
"""
if order_by_date:
columns_order = "DATE"
else:
columns_order = None
match_list = []
sqllite_rows = SQLLite.get_connection().select_like(
"Match", columns_order=columns_order, **{"date": str(date_str)})
for p in sqllite_rows:
match = Match(p["id"])
for attribute, value in p.items():
match.__setattr__(attribute, value)
match_list.append(match)
return match_list
def read_by_match_event_id_and_event_name(match_event_id, event_name=None):
'''
Return the list of bet_event of the input match
:param match_event_id:
:param event_name:
:return:
'''
filter = {}
filter["match_event_id"] = str(match_event_id)
if not util.is_None(event_name):
filter["event_name"] = event_name
bet_events = []
for sqllite_row in SQLLite.get_connection().select("Bet_Event", **filter):
be = BetEvent(sqllite_row["id"])
for attribute, value in sqllite_row.items():
be.__setattr__(attribute, value)
bet_events.append(be)
return bet_events