def update_match_start(match: pd.Series, tournament_id: int): query = "SELECT start_time_utc FROM matches WHERE tournament_id=%s AND home=%s AND away=%s" params = [tournament_id, match.home, match.away] db_returned = execute_sql_postgres(query, params) if match.start_time_utc == db_returned[0]: pass else: logging.debug( f'Updating starting time. Original time {db_returned[0]}. New time {match.start_time_utc}. Match: {match}' ) query = "UPDATE matches SET start_time_utc = %s WHERE tournament_id=%s AND home=%s AND away=%s" time_params = [match.start_time_utc] time_params.extend(params) execute_sql_postgres(query, time_params, True) pass
def save_bet(book_id: int, bookmaker_matchid: str, bet_type: str, match_part: str, odd: float, probability: float): query = "INSERT INTO bet (bookmaker_id, match_bookmaker_id, bet_type, match_part, odd, probability, utc_time_recorded) \ VALUES (%s, %s, %s, %s, %s, %s, %s)" db_returned = execute_sql_postgres(query, [book_id, bookmaker_matchid, bet_type, match_part, odd, probability, datetime.datetime.now()], True) if "success" not in db_returned: logging.error(f"Imposible to save bet. Matchid: {bookmaker_matchid}. Query: {query}. Error: {db_returned}") pass
def get_tournament_id(book_id: int, tournament_bookmaker_id: str, tournament_bookmaker_year_id: str) -> tuple: query = "SELECT tournament_id FROM tournament_bookmaker WHERE bookmaker_id=%s AND tournament_bookmaker_id=%s AND \ tournament_bookmaker_extra_id=%s" return execute_sql_postgres( query, [book_id, tournament_bookmaker_id, tournament_bookmaker_year_id])
def evaluate_bet_on_set(book_id: int, bookmaker_matchid: str, set_number: int, home_won: bool): query = "SELECT * FROM bet WHERE bookmaker_id='%s' AND match_bookmaker_id=%s AND match_part=%s" match_part = "".join(['set', str(set_number)]) placed_bet = execute_sql_postgres(query, [book_id, bookmaker_matchid, match_part]) if placed_bet is not None: if placed_bet[7] is not None: raise Exception(f"Evaluating bet that is already evaluated: {[book_id, bookmaker_matchid, match_part]}") if (home_won and placed_bet[3] == 'home') or (not home_won and placed_bet[3] == 'away'): won = True else: won = False query = "UPDATE bet SET result = %s, result_corrected = %s WHERE bookmaker_id='%s' " \ "AND match_bookmaker_id=%s AND match_part=%s" db_returned = execute_sql_postgres(query, [won, won, book_id, bookmaker_matchid, match_part], True) if "success" not in db_returned: logging.error( f"Imposible to save bet result. Matchid: {bookmaker_matchid}. Query: {query}. Error: {db_returned}") pass
def save_set_odds(odds: tuple, bookmaker_id: int, bookmaker_matchid: str, set_number: int): utc_time_recorded = datetime.datetime.now() query = "INSERT INTO odds (bookmaker_id, match_bookmaker_id, odds_type, match_part, odd1, odd2, utc_time_recorded) \ VALUES (%s, %s, %s, %s, %s, %s, %s)" set_to_save = f"set{set_number}" params = [bookmaker_id, bookmaker_matchid, 'home_away', set_to_save, odds[0], odds[1], utc_time_recorded] db_returned = execute_sql_postgres(query, params, True) if type(db_returned) == IntegrityError or type(db_returned) == psycopg2.errors.UniqueViolation: if 'duplicate key value violates unique constraint' in db_returned.args[0]: query = "UPDATE odds SET odd1 = %s, odd2 = %s, utc_time_recorded = %s WHERE \ bookmaker_id = %s AND match_bookmaker_id = %s AND odds_type = %s AND match_part = %s" params = [odds[0], odds[1], utc_time_recorded, bookmaker_id, bookmaker_matchid, 'home_away', set_to_save] execute_sql_postgres(query, params, True) else: raise db_returned elif 'success' in db_returned: pass else: raise db_returned pass
def get_all_bets() -> pd.DataFrame: tournament = 'US Open' sex = 'men' match_type = 'singles' params = [tournament, sex, match_type] query = "SELECT home, away, start_time_utc, bet_type, match_part, odd, probability, result, utc_time_recorded " \ "FROM matches " \ "JOIN tournament t ON matches.tournament_id = t.id " \ "JOIN matches_bookmaker mb ON matches.id = mb.match_id " \ "JOIN bet b ON mb.bookmaker_id = b.bookmaker_id AND mb.match_bookmaker_id = b.match_bookmaker_id " \ "WHERE name = %s AND sex = %s AND type = %s AND result NOTNULL " \ "ORDER BY utc_time_recorded;" return pd.DataFrame(execute_sql_postgres(query, params, False, True), columns=BET_COLUMN_NAMES)
def home_won_set(current_set_score: tuple, last_set_score: tuple, set_number: int, match_id: int) -> bool: query = "INSERT INTO match_course (match_id, set_number, result, utc_time_recorded) VALUES (%s, %s, %s, %s)" if current_set_score[0] == last_set_score[0] + 1 and current_set_score[1] == last_set_score[1]: home_won = True result = "home" elif current_set_score[1] == last_set_score[1] + 1 and current_set_score[0] == last_set_score[0]: home_won = False result = "away" else: raise Exception(f"Unexpected scores: {last_set_score}, {current_set_score}") db_returned = execute_sql_postgres(query, [match_id, set_number, result, datetime.datetime.now()], True) if "success" not in db_returned: logging.error(f"Imposible to save result. Matchid: {match_id}. Query: {query}. Error: {db_returned}") return home_won
def get_starting_matches() -> List[tuple]: utc_time = pytz.utc.localize(datetime.datetime.utcnow()) limit_start_time = utc_time + datetime.timedelta( minutes=TIME_TO_MATCHSTART_MINUTES) query = "SELECT match_bookmaker_id FROM \ (SELECT * FROM matches WHERE start_time_utc > %s AND start_time_utc < %s) AS matches \ JOIN \ matches_bookmaker ON matches.id = match_id \ JOIN \ tournament ON matches.tournament_id = tournament.id \ WHERE NOT (name IN ('ATP Australian Open','ATP US Open', 'ATP French Open', 'ATP Wimbledon') AND " \ "sex = 'men' AND type = 'singles') \ EXCEPT \ SELECT match_bookmaker_id FROM inplay" params = [utc_time, limit_start_time] return execute_sql_postgres(query, params, False, True)
def get_first_set_data(start_date: str, end_date: str) -> pd.DataFrame: query = "SELECT matchid, home, away, set_number, odd1, odd2, " \ "CASE WHEN result = 'home' THEN 1 ELSE 0 END AS result, start_time_utc FROM ( " \ "SELECT *, CASE " \ "WHEN match_part = 'set1' THEN 1 " \ "WHEN match_part = 'set2' THEN 2 " \ "WHEN match_part = 'set3' THEN 3 " \ "WHEN match_part = 'set4' THEN 4 " \ "WHEN match_part = 'set5' THEN 5 " \ "END AS set_number_odds FROM odds) AS odds_enhanced " \ "INNER JOIN (SELECT *, ma.id AS matchid FROM matches_bookmaker mb " \ "JOIN matches ma ON mb.match_id = ma.id " \ "JOIN match_course mc ON mb.match_id = mc.match_id JOIN tournament t ON ma.tournament_id = t.id) " \ "AS match_course_enhanced ON odds_enhanced.match_bookmaker_id = match_course_enhanced.match_bookmaker_id " \ "AND odds_enhanced.bookmaker_id = match_course_enhanced.bookmaker_id " \ "AND odds_enhanced.set_number_odds = match_course_enhanced.set_number " \ "WHERE start_time_utc >= %s AND " \ "start_time_utc < %s AND set_number = 1 " \ "ORDER BY start_time_utc, match_course_enhanced.matchid, match_course_enhanced.set_number" # and sex='women' and type='singles' return pd.DataFrame(execute_sql_postgres(query, [start_date, end_date], False, True), columns=COLUMN_NAMES)
def clear_inplay(): query = "DELETE FROM inplay" execute_sql_postgres(query, None, True) logger.info("Table inplay cleared.") pass
def remove_inplay(bookmaker_matchid: str, book_id: int): query = "DELETE FROM inplay WHERE bookmaker_id=%s AND match_bookmaker_id=%s" execute_sql_postgres(query, [str(book_id), str(bookmaker_matchid)], True) pass
def insert_inplay(bookmaker_matchid: str, book_id: int): query = "INSERT INTO inplay (bookmaker_id, match_bookmaker_id, utc_time_recorded) VALUES (%s, %s, %s)" execute_sql_postgres(query, [book_id, bookmaker_matchid, datetime.datetime.now()], True) pass
def save_tournament(params: list) -> tuple: query = f"INSERT INTO tournament (name, sex, type, surface, year) VALUES ({', '.join(['%s'] * 5)}) RETURNING id" return execute_sql_postgres(query, params, True)
def get_matchid(bookmaker_matchid, bookmaker_id): query = "SELECT match_id FROM matches_bookmaker WHERE bookmaker_id = %s AND match_bookmaker_id = %s" return execute_sql_postgres(query, [bookmaker_id, bookmaker_matchid])
def save_match(params: list) -> tuple: query = f"INSERT INTO matches (home, away, start_time_utc, tournament_id) VALUES ({', '.join(['%s'] * 4)}) \ RETURNING id" return execute_sql_postgres(query, params, True)
def set_inplay(inplay_available: bool, match_bookmaker_id: str) -> tuple: query = f"UPDATE matches_bookmaker SET inplay_available = %s where match_bookmaker_id = %s" return execute_sql_postgres(query, [inplay_available, match_bookmaker_id], True)
def save_match_bookmaker(params: list) -> tuple: query = f"INSERT INTO matches_bookmaker (match_id, bookmaker_id, match_bookmaker_id) VALUES\ ({', '.join(['%s'] * 3)})" return execute_sql_postgres(query, params, True)
def save_tournament_bookmaker(params: list) -> tuple: query = f"INSERT INTO tournament_bookmaker (tournament_id, bookmaker_id, tournament_bookmaker_id, \ tournament_bookmaker_extra_id) VALUES ({', '.join(['%s'] * 4)})" return execute_sql_postgres(query, params, True)