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)