def new_message():
def format_text(project_name, message):
cases = {
"error": "\u26D4New error from %s:\n"
"%s",
"warning": "⚠️New warning from %s:\n"
"%s",
"log": "\U0001F4ACNew log from %s:\n"
"%s",
}
return cases[message["type"]] % (project_name,
log["message"]["content"])
log = request.json
session = Session()
project = session.query(Project).filter_by(token=log["token"]).first()
if not project:
return {
"ok": False,
"error": "Project not found! Please check your token"
}
text = format_text(project.name, log["message"])
bot.sendMessage(chat_id=project.user_id, text=text)
return jsonify({"ok": True})
async def upload_file(file_path: str, league_name: str, stat_type: str,
last_5: bool) -> bool:
session: SQLSession = Session()
try:
with open(file_path, 'rb') as file:
msg = await bot.send_photo(admins[0],
file,
disable_notification=True)
file_id = msg.photo[-1].file_id
session.query(MediaIds).filter(
and_(MediaIds.league == league_name,
MediaIds.stat_type == stat_type,
MediaIds.last_5 == last_5)).delete()
session.add(
MediaIds(league_name, stat_type, last_5, file_id, file_path))
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.commit()
session.close()
def __init__(self, url: str):
"""
make sure site is in db
check if site is up
if it is, update last_up in db
"""
# this should be statically defined in child implementations of the class
assert self.actor != ""
self.current_victims = []
self.new_victims = []
self.url = url
self.session = Session()
q = self.session.query(Site).filter_by(actor=self.actor)
if q.count() == 0:
# site is new, init obj
self.site = Site(actor=self.actor, url=self.url)
self.session.add(self.site)
self.session.commit()
self.first_run = True
else:
# site exists, set obj
self.site = q.first()
# if we haven't successfully scraped the site before, consider this the first run
if self.site.last_scraped is None:
self.first_run = True
# check if site is up
self.is_up = self.is_site_up()
def __update_xg(self, league_name: str) -> bool:
logging.info(f"Start update xg for league={league_name}")
session: SQLSession = Session()
try:
for player_stat in self.fbref.get_xg_stats(league_name):
cur_player: PlayerStats = self.get_player(player_stat)
if cur_player:
last3_xg_per_game, last3_npxg_per_game, last3_xa_per_game, last5_xg_per_game, \
last5_npxg_per_game, last5_xa_per_game = self.__compute_xg(player_stat, cur_player)
session.query(PlayerStats).filter(
PlayerStats.id == cur_player.id).update({
'r5_xg':
player_stat["xg"],
'r5_npxg':
player_stat["npxg"],
'r5_xa':
player_stat["xa"],
'last3_xg_per_game':
last3_xg_per_game,
'last3_npxg_per_game':
last3_npxg_per_game,
'last3_xa_per_game':
last3_xa_per_game,
'last5_xg_per_game':
last5_xg_per_game,
'last5_npxg_per_game':
last5_npxg_per_game,
'last5_xa_per_game':
last5_xa_per_game
})
session.commit()
else: # if new player
session.add(
PlayerStats(player_stat['name'], league_name,
player_stat['team'],
player_stat['position']))
session.commit()
cur_player = self.get_player(player_stat)
if cur_player:
session.query(PlayerStats).filter(
PlayerStats.id == cur_player.id).update({
'r5_xg':
player_stat["xg"],
'r5_npxg':
player_stat["npxg"],
'r5_xa':
player_stat["xa"]
})
session.commit()
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.commit()
session.close()
def is_new_round(self, league_name: str) -> bool:
session: SQLSession = Session()
league_info = session.query(League_info).filter(
League_info.league == league_name).first()
session.close()
if not league_info:
return False
return datetime.now() > league_info.deadline
def get_player(self, player_info: dict) -> PlayerStats:
session: SQLSession = Session()
result = session.query(PlayerStats).filter(
and_(PlayerStats.name == player_info['name'],
PlayerStats.league == player_info['league'],
PlayerStats.team == player_info['team'])).first()
session.close()
return result
def get_coeffs(self, league_name: str, cur_round: bool) -> str:
session: SQLSession = Session()
coeffs = session.query(Coeff).filter(
and_(Coeff.league == league_name, Coeff.is_cur_round == cur_round))
if not coeffs:
return ""
coeffs_list: List[Coeff] = [cf for cf in coeffs]
return self.__transform_coeffs(coeffs_list, cur_round)
async def update_coeffs(self, league_name: str) -> bool:
# delete last round
db_session: SQLSession = Session()
db_session.query(Coeff).filter(Coeff.league == league_name).delete()
db_session.commit()
db_session.close()
return await self.xbet.update_league(league_name)
def is_password_correct(password: str) -> bool:
"""Returns whether a user email exists in the database.
Args:
email: An email that may belong to a user.
Returns:
Returns true if the email exists, false otherwise.
"""
session = Session()
return session.query(User).filter_by(password=password).first() != None
def get_role_groups(role_id: int) -> List[Group]:
"""Returns a list of groups with the given role.
Args:
role_id: An role's role_id.
Returns:
Returns a list of groups if an role has the role_id, otherwise returns None.
"""
session = Session()
return session.query(Group).join(RoleToGroup).filter(
RoleToGroup.role_id == role_id).all()
def get_employee_roles(email: str) -> List[EmployeeToRole]:
"""Returns a list of roles with the given email.
Args:
email: An employee's email.
Returns:
Returns a list of roles if an employee has the email, otherwise returns None.
"""
session = Session()
return session.query(EmployeeToRole).filter(
EmployeeToRole.email == email).all()
def get_sources(self, league_name: str) -> List[Source]:
session: SQLSession = Session()
try:
sources = session.query(Source).filter(
Source.league == league_name).all()
return sources
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return []
finally:
session.close()
def get_slack_groups() -> List[Group]:
"""Returns a list of groups that belong to Slack.
Returns:
Returns a list containing all the groups from Slack.
"""
groups = None
session = Session()
try:
groups = session.query(Group).filter(Group.app_id == 1).all()
except Exception as e:
print(e)
return groups
def save(update, context):
session = Session()
new_name = context.user_data["new_name"]
project = context.user_data["current_project"]
session.query(Project).filter(Project.id == project.id).update(
{"name": new_name})
session.commit()
project.name = new_name
context.user_data["current_project"] = project
return edit(update, context)
def add_source(self, name: str, league_name: str, url: str,
description: str) -> bool:
logging.info(f"Add source {name} for league={league_name}")
session: SQLSession = Session()
try:
session.add(Source(name, league_name, url, description))
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.commit()
session.close()
def delete_source_by_id(self, source_id: int) -> str:
logging.info(f"Delete source with id={source_id}")
session: SQLSession = Session()
try:
league_name = session.query(Source).filter(
and_(Source.id == source_id)).first().league
session.query(Source).filter(and_(Source.id == source_id)).delete()
return league_name
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return ""
finally:
session.commit()
session.close()
def delete_source(self, name: str, league_name: str, url: str) -> bool:
logging.info(f"Delete source {name} for league={league_name}")
session: SQLSession = Session()
try:
session.query(Source).filter(
and_(Source.league == league_name, Source.name == name,
Source.url == url)).delete()
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.commit()
session.close()
def get_players(self, league_name: str) -> str:
xbet = XBet()
if league_name not in xbet.leagues:
return "Ошибка в названии лиги"
session: SQLSession = Session()
best_players = session.query(Player).filter(Player.league == league_name) \
.order_by(Player.dif_popularity.desc(), Player.old_popularity.desc()).limit(10).all()
worst_players = session.query(Player).filter(Player.league == league_name) \
.order_by(Player.dif_popularity, Player.old_popularity.desc()).limit(10).all()
session.close()
result = ["\U0001F4C8 Популярные игроки:\n"]
result += self.__transform_players(best_players)
result += ["\n\U0001F4C9 Непопулярные игроки:\n"]
result += self.__transform_players(worst_players)
return '\n'.join(result)
def typing_name(update, context):
try:
session = Session()
token = uuid.uuid1()
project = Project(token=token,
name=update.message.text,
user_id=update.message.chat.id)
session.add(project)
session.commit()
update.message.reply_text(text=str(token))
return main(update, context)
except Exception as e:
print(str(e))
def get_roles_with_ids(ids: List[int]) -> List[Role]:
"""Returns a list of roles that match the specified ids.
Args:
ids: The ids being used to query roles.
Returns:
Returns a list of roles.
"""
session = Session()
roles = []
try:
roles = session.query(Role).filter(Role.id.in_(ids)).all()
except Exception as e:
print(e)
return roles
def get_employees_with_ids(ids: List[int]) -> List[Employee]:
"""Returns a list of employees that match the specified ids.
Args:
ids: The ids being used to query employees.
Returns:
Returns a list of employess.
"""
session = Session()
employees = []
try:
employees = session.query(Employee).filter(Employee.id.in_(ids)).all()
except Exception as e:
print(e)
return employees
def get_employees_by_role(role: Role) -> List[Employee]:
"""Returns a list of employees with the given role.
Args:
role: An instance of Role.
Returns:
Returns a list of employees.
"""
employees = []
session = Session()
try:
employees = session.query(Employee).join(EmployeeToRole).filter(
EmployeeToRole.role_id == role.id).all()
except Exception as e:
print(e)
return employees
def show_projects(update, context):
chat_id = update.callback_query.message.chat.id
try:
session = Session()
projects = session.query(Project).filter_by(user_id=chat_id)
text = "Your projects"
buttons = []
count = projects.count()
context.user_data["projects"] = projects
for i in range(0, count, 2):
row = [
InlineKeyboardButton(text=projects[i].name,
callback_data=str(EDITING) +
str(projects[i].name)),
]
if i + 1 < count:
row.append(
InlineKeyboardButton(text=projects[i + 1].name,
callback_data=str(EDITING) +
str(projects[i + 1].name)))
buttons.append(row)
buttons.append([
InlineKeyboardButton(text='Return to main',
callback_data=str(MAIN))
])
keyboard = InlineKeyboardMarkup(buttons)
update.callback_query.answer()
update.callback_query.edit_message_text(text=text,
reply_markup=keyboard)
return SELECTING_ACTION
except Exception as e:
print(str(e))
async def __update_shoots_files(self, league_name: str) -> bool:
try:
logging.info(f"Start update shoots files for league={league_name}")
# get best players
session: SQLSession = Session()
best_players_last_3 = session.query(PlayerStats).filter(PlayerStats.league == league_name) \
.order_by(PlayerStats.last3_shoots_per_game.desc(), PlayerStats.last3_on_target_per_shoot.desc()) \
.limit(20).all()
best_players_last_5 = session.query(PlayerStats).filter(PlayerStats.league == league_name) \
.order_by(PlayerStats.last5_shoots_per_game.desc(), PlayerStats.last5_on_target_per_shoot.desc()) \
.limit(20).all()
# create pd pataframes
last_3_info = [[
round(player.last3_shoots_per_game, 2),
int(player.last3_on_target_per_shoot * 100), player.name,
player.team
] for player in best_players_last_3]
last_5_info = [[
round(player.last5_shoots_per_game, 2),
int(player.last5_on_target_per_shoot * 100), player.name,
player.team
] for player in best_players_last_5]
last_3_df = pd.DataFrame(
last_3_info, columns=['Уд/И', 'УдС/Уд(%)', 'Игрок', 'Команда'])
last_3_df_styled = last_3_df.style.background_gradient()
last_5_df = pd.DataFrame(
last_5_info, columns=['Уд/И', 'УдС/Уд(%)', 'Игрок', 'Команда'])
last_5_df_styled = last_5_df.style.background_gradient()
result = self.__update_files(league_name, 'shoots',
last_3_df_styled, last_5_df_styled)
result *= await upload_files(league_name, 'shoots')
return result
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.close()
def update_deadline(self, league_name: str) -> bool:
"""
Update deadline for league
"""
logging.info(f"Update deadline for league={league_name}")
new_deadline = self.sports.get_deadline(league_name)
if not new_deadline:
return False
session: SQLSession = Session()
league = session.query(League_info).filter(
League_info.league == league_name).first()
if league:
session.query(League_info).filter(League_info.id == league.id) \
.update({League_info.deadline: new_deadline})
else:
session.add(League_info(league_name, new_deadline))
session.commit()
session.close()
return True
def start(update, context):
session = Session()
try:
u = User(chat_id=update.message.chat.id)
session.add(u)
session.commit()
except IntegrityError:
print("User already exists")
buttons = [[
InlineKeyboardButton(text='To main menu', callback_data=str(MAIN)),
]]
keyboard = InlineKeyboardMarkup(buttons)
update.message.reply_text("Choose the menu", reply_markup=keyboard)
return SELECTING_ACTION
def get_gca_id(self, league_name: str, last_5: bool = False) -> str:
if league_name not in self.fbref.shoots_creation_leagues:
return "Для данной лиги нет данных"
try:
session: SQLSession = Session()
image = session.query(MediaIds).filter(
and_(MediaIds.league == league_name,
MediaIds.stat_type == 'gca',
MediaIds.last_5 == last_5)).first()
if image is not None:
return image.file_id
else:
return ""
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return ""
finally:
session.close()
def update_league(self, league_name: str, new_round: bool = False) -> bool:
"""
Updates players popularity for league
"""
logging.info(
f"Start update players for league={league_name}, new_round={new_round}"
)
session: SQLSession = Session()
try:
if new_round:
session.query(Player).filter(
Player.league == league_name).update({'dif_popularity': 0})
for player in self.sports.get_league_players(league_name):
cur_player = self.get_player(player)
if cur_player:
if new_round:
session.query(Player).filter(
Player.id == cur_player.id).update(
{'old_popularity': player['popularity']})
else:
session.query(Player).filter(
Player.id == cur_player.id).update({
'dif_popularity':
player['popularity'] - Player.old_popularity
})
else: # if new player
session.add(
Player(player['name'], player['league'],
player['team'], player['amplua'],
player['popularity'], 0))
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.commit()
session.close()
async def __update_match(self, league_name: str, match_info: dict, cur_round: bool = False) -> bool:
db_session: SQLSession = Session()
html_session = AsyncHTMLSession()
try:
home_team = match_info['homeTeam']['name']
away_team = match_info['awayTeam']['name']
match_url = match_info['url']
logging.info(f"Update coeffs in match {home_team} vs {away_team}")
# not match
if "голы" in home_team or "специальное" in home_team or "Хозяева" in home_team:
return False
n_att = 0
while n_att < 3:
try:
r = await html_session.get(match_url)
break
except requests.exceptions.ConnectionError:
n_att += 1
sleep(5)
continue
if n_att == 3:
return False
# render JS
await r.html.arender(retries=3, wait=2.5, timeout=30)
game_html = r.html.find("#allBetsTable", first=True)
# 4 - list index of block with only needed koeffs
total_1_all = game_html.find(containing="Индивидуальный тотал 1-го")[4]
total_1_more_1_5 = float(total_1_all.find("div", containing="1.5 Б", first=True)
.find("i", first=True).text)
total_1_less_0_5 = float(total_1_all.find("div", containing="0.5 М", first=True)
.find("i", first=True).text)
total_2_all = game_html.find(containing="Индивидуальный тотал 2-го")[4]
total_2_more_1_5 = float(total_2_all.find("div", containing="1.5 Б", first=True)
.find("i", first=True).text)
total_2_less_0_5 = float(total_2_all.find("div", containing="0.5 М", first=True)
.find("i", first=True).text)
# update db
home_coeff = Coeff(
home_team, away_team, league_name,
total_1_more_1_5, total_2_less_0_5, True, cur_round
)
away_coeff = Coeff(
away_team, home_team, league_name,
total_2_more_1_5, total_1_less_0_5, False, cur_round
)
db_session.add_all([home_coeff, away_coeff])
return True
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
await html_session.close()
db_session.commit()
db_session.close()
async def __update_xg_files(self, league_name: str) -> bool:
try:
logging.info(f"Start update xg files for league={league_name}")
# get best players
session: SQLSession = Session()
best_xg_last_3 = session.query(PlayerStats).filter(PlayerStats.league == league_name) \
.order_by(PlayerStats.last3_xg_per_game.desc(), PlayerStats.last3_npxg_per_game.desc()) \
.limit(20).all()
best_xg_last_5 = session.query(PlayerStats).filter(PlayerStats.league == league_name) \
.order_by(PlayerStats.last5_xg_per_game.desc(), PlayerStats.last5_npxg_per_game.desc()) \
.limit(20).all()
best_xg_xa_last_3 = session.query(PlayerStats, (PlayerStats.last3_xg_per_game +
PlayerStats.last3_xa_per_game).label('xg_xa')) \
.filter(PlayerStats.league == league_name).order_by(desc('xg_xa')).limit(20).all()
best_xg_xa_last_5 = session.query(PlayerStats, (PlayerStats.last5_xg_per_game +
PlayerStats.last5_xa_per_game).label('xg_xa')) \
.filter(PlayerStats.league == league_name).order_by(desc('xg_xa')).limit(20).all()
# create pd pataframes
last_3_xg = [[
round(player.last3_xg_per_game, 2),
round(player.last3_npxg_per_game, 2),
round(player.last3_xa_per_game, 2), player.name, player.team
] for player in best_xg_last_3]
last_5_xg = [[
round(player.last5_xg_per_game, 2),
round(player.last5_npxg_per_game, 2),
round(player.last5_xa_per_game, 2), player.name, player.team
] for player in best_xg_last_5]
last_3_xg_xa = [[
round(player[1], 2),
round(player[0].last3_npxg_per_game, 2), player[0].name,
player[0].team
] for player in best_xg_xa_last_3]
last_5_xg_xa = [[
round(player[1], 2),
round(player[0].last5_npxg_per_game, 2), player[0].name,
player[0].team
] for player in best_xg_xa_last_5]
last_3_xg_df = pd.DataFrame(
last_3_xg,
columns=['xG/И', 'npxG/И', 'xA/И', 'Игрок', 'Команда'])
last_3_xg_df_styled = last_3_xg_df.style.background_gradient()
last_5_xg_df = pd.DataFrame(
last_5_xg,
columns=['xG/И', 'npxG/И', 'xA/И', 'Игрок', 'Команда'])
last_5_xg_df_styled = last_5_xg_df.style.background_gradient()
last_3_xg_xa_df = pd.DataFrame(
last_3_xg_xa,
columns=['xG+xA/И', 'npxG/И', 'Игрок', 'Команда'])
last_3_xg_xa_df_styled = last_3_xg_xa_df.style.background_gradient(
)
last_5_xg_xa_df = pd.DataFrame(
last_5_xg_xa,
columns=['xG+xA/И', 'npxG/И', 'Игрок', 'Команда'])
last_5_xg_xa_df_styled = last_5_xg_xa_df.style.background_gradient(
)
result = all([
self.__update_files(league_name, 'xg', last_3_xg_df_styled,
last_5_xg_df_styled),
self.__update_files(league_name, 'xg_xa',
last_3_xg_xa_df_styled,
last_5_xg_xa_df_styled)
])
result *= await upload_files(league_name, 'xg')
result *= await upload_files(league_name, 'xg_xa')
return result
except Exception as ex:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logging.warning(f"Ex={ex} in file={fname} line={exc_tb.tb_lineno}")
return False
finally:
session.close()
