def get_dba():
"""Connect to db before tests, disconnect after."""
# Setup : start db
dba.open_connect()
yield dba.get_connect() # this is where the testing happens
# Teardown : stop db
dba.close_connect()
def _initialize_sex(sex):
quals_from_tid = __wta_quals_from_tid if sex == "wta" else __atp_quals_from_tid
sql = """select ID_T_S, ID_P_S
from Seed_{0}
where SEEDING Like '%q%' or SEEDING Like '%LL%'
order by ID_T_S;""".format(
sex
)
with closing(dba.get_connect().cursor()) as cursor:
for (tour_id, player_id) in cursor.execute(sql):
quals_from_tid[tour_id].append(player_id)
def read_birth_date(self, sex):
if not self.ident or self.birth_date is not None:
return
sql = """SELECT DATE_P
FROM Players_{}
WHERE ID_P = {};""".format(sex, self.ident)
with closing(dba.get_connect().cursor()) as cursor:
cursor.execute(sql)
row = cursor.fetchone()
if row:
birth = row[0]
if birth:
self.birth_date = birth.date() if birth is not None else None
def get_presence_results(
idents: Set[int],
sex: str,
min_date: datetime.date,
max_date: datetime.date,
with_retired=False,
) -> List[PlayerResult]:
"""[min_date, max_date) is semiclose range
:return list where date is latest for [min_date,...,max_date)
"""
present_pids = set()
present_results = []
date_now = datetime.date.today()
if not dba.initialized():
dba.open_connect()
sql = """select games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%') """.format(sex)
sql += dba.sql_dates_condition(min_date, max_date, dator="games.DATE_G")
sql += " order by games.DATE_G desc;"
with closing(dba.get_connect().cursor()) as cursor:
for (match_dt, score_txt, fst_id, snd_id) in cursor.execute(sql):
match_date = match_dt.date() if match_dt else None
if not score_txt:
continue # may be it is scheduled match
date = match_date
if date is None:
log.error(
f"empty date in {sex} pid1{fst_id} pid2 {snd_id} score:{score_txt}"
)
continue
if with_retired and not sc.Score(score_txt).retired:
continue
if not with_retired and fst_id in idents and fst_id not in present_pids:
present_pids.add(fst_id)
present_results.append(
PlayerResult(id=fst_id, days_ago=(date_now - date).days))
if snd_id in idents and snd_id not in present_pids:
present_pids.add(snd_id)
present_results.append(
PlayerResult(id=snd_id, days_ago=(date_now - date).days))
return present_results
def read_draw_statuses_from_db(self, sex, tour_id):
def seed_sql(player_id):
return """select SEEDING
from Seed_{0}
where ID_T_S = {1}
and ID_P_S = {2};""".format(sex, tour_id, player_id)
def fetch_draw_status(player_id):
cursor.execute(seed_sql(player_id))
row = cursor.fetchone()
if row is not None and len(row) == 1:
seed = row[0].strip().lower()
if "q" in seed or "ll" in seed:
seed = "qual"
return seed
def first_rnd_bye_sql(player_id):
"""we supposed today mode is sufficient"""
return """SELECT today.TOUR
FROM today_{0} AS today
WHERE today.TOUR = {1} and
today.ROUND = {2} and
today.ID1 = {3} and
today.RESULT = 'bye';""".format(
sex, tour_id,
Round("First").oncourt_id(), player_id)
def fetch_first_rnd_bye(player_id):
cursor.execute(first_rnd_bye_sql(player_id))
return cursor.fetchone() is not None
if self.rnd is None or self.rnd.qualification():
return
with closing(dba.get_connect().cursor()) as cursor:
self.first_draw_status = fetch_draw_status(self.first_player.ident)
self.second_draw_status = fetch_draw_status(
self.second_player.ident)
if self.rnd == "Second":
if self.first_draw_status != "qual":
if fetch_first_rnd_bye(self.first_player.ident):
self.first_draw_status = "bye"
if self.second_draw_status != "qual":
if fetch_first_rnd_bye(self.second_player.ident):
self.second_draw_status = "bye"
def _initialize_results_sex(sex,
max_rating,
max_rating_dif,
with_bo5=False,
min_date=None,
max_date=None):
tmp_recov_dct = defaultdict(list) # date -> list of (plr_id, set2win)
tmp_keep_dct = defaultdict(list) # date -> list of (plr_id, set2win)
sql = """select tours.DATE_T, games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%')""".format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " ;"
with closing(dba.get_connect().cursor()) as cursor:
for (tour_dt, match_dt, score_txt, fst_id,
snd_id) in cursor.execute(sql):
tdate = tour_dt.date() if tour_dt else None
mdate = match_dt.date() if match_dt else None
if not score_txt:
continue
scr = sc.Score(score_txt)
sets_count = scr.sets_count(full=True)
if sets_count < 2:
continue
date = mdate if mdate else tdate
if date is None:
continue
recov, keep = _get_match_data(sex, date, fst_id, snd_id, scr,
max_rating, max_rating_dif, with_bo5)
if recov is not None:
tmp_recov_dct[date].append((recov.player_id, recov.set2_win))
if keep is not None:
tmp_keep_dct[date].append((keep.player_id, keep.set2_win))
recov_dates = list(tmp_recov_dct.keys())
recov_dates.sort(reverse=True)
for date in recov_dates:
recovery_dict[sex][date] = tmp_recov_dct[date]
keep_dates = list(tmp_keep_dct.keys())
keep_dates.sort(reverse=True)
for date in keep_dates:
keep_dict[sex][date] = tmp_keep_dct[date]
def read_players_from_db(sex, players_set, min_date, players_ext_dct):
"""fill players_set"""
def add_player(pid, name, country):
ext_dct = players_ext_dct.get(pid)
if ext_dct is not None:
players_set.add(
tennis.Player(
pid,
name,
cou=country,
lefty=ext_dct.get("lefty"),
disp_names=ext_dct.get("disp_names"),
))
else:
players_set.add(tennis.Player(pid, name, country))
sql = """
select plr_left.ID_P, plr_left.NAME_P, plr_left.COUNTRY_P,
plr_right.ID_P, plr_right.NAME_P, plr_right.COUNTRY_P
from Games_{0} AS games, Tours_{0} AS tours,
Players_{0} AS plr_left, Players_{0} AS plr_right
where (games.ID_T_G = tours.ID_T)
and games.ID1_G = plr_left.ID_P
and games.ID2_G = plr_right.ID_P
and games.RESULT_G IS NOT NULL
and (tours.NAME_T Not Like '%juniors%')
and (tours.NAME_T Not Like '%Wildcard%')
and (plr_left.NAME_P Not Like '%/%')
and (plr_right.NAME_P Not Like '%/%')
and tours.DATE_T >= {1};""".format(sex, dba.msaccess_date(min_date))
with closing(dba.get_connect().cursor()) as cursor:
for (
plr_left_id,
plr_left_name,
plr_left_country,
plr_right_id,
plr_right_name,
plr_right_country,
) in cursor.execute(sql):
add_player(plr_left_id, plr_left_name, plr_left_country)
add_player(plr_right_id, plr_right_name, plr_right_country)
def _initialize_results_sex(sex, min_date=None, max_date=None):
tmp_dct = defaultdict(list) # date -> list of match_results
sql = """select tours.DATE_T, games.DATE_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%')""".format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " ;"
with closing(dba.get_connect().cursor()) as cursor:
for (tour_dt, match_dt, score_txt, fst_id,
snd_id) in cursor.execute(sql):
tdate = tour_dt.date() if tour_dt else None
mdate = match_dt.date() if match_dt else None
if not score_txt:
continue
scr = sc.Score(score_txt)
if scr.retired:
continue
sets_cnt = scr.sets_count(full=True)
if sets_cnt not in (3, 5):
continue
if sets_cnt == 3:
sets_sc = scr.sets_score()
if sets_sc != (2, 1):
continue
indec_sc = scr[sets_cnt - 1]
games_dif = indec_sc[0] - indec_sc[1]
if games_dif <= 0:
log.error(
"strange decided score {} in {} 1id {} 2id {}".format(
scr, sex, fst_id, snd_id))
continue
date = mdate if mdate else tdate
tmp_dct[date].append(MatchResult(fst_id, snd_id, games_dif))
dates = list(tmp_dct.keys())
dates.sort()
for date in dates:
results_dict[sex][date] = tmp_dct[date]
def _read_match_score(sex: str, pid1: int, pid2: int, rec_date: date):
from score import Score
min_date = rec_date - timedelta(days=1)
max_date = rec_date + timedelta(days=2)
sql = """select games.ID1_G, games.ID2_G, games.RESULT_G
from games_{0} AS games
where games.RESULT_G IS NOT NULL
and games.DATE_G IS NOT NULL
and (
(games.ID1_G = {1} and games.ID2_G = {2}) or
(games.ID1_G = {2} and games.ID2_G = {1})
)
""".format(sex, pid1, pid2)
sql += dba.sql_dates_condition(min_date, max_date, dator='games.DATE_G')
sql += ";"
with closing(dba.get_connect().cursor()) as cursor:
cursor.execute(sql)
row = cursor.fetchone()
if row:
winner_id = row[0]
loser_id = row[1]
res_score = Score(text=row[2])
return winner_id, loser_id, res_score
def _make(self, sex, match, completed_only):
from tournament import Tournament
if (not match.first_player or not match.second_player
or not match.first_player.ident
or not match.second_player.ident or not match.date):
return
tour_date_max = tt.past_monday_date(match.date)
sql = """select Rounds.NAME_R, Courts.NAME_C, tours.ID_T, tours.NAME_T,
tours.RANK_T, tours.DATE_T, tours.PRIZE_T, tours.COUNTRY_T,
games.ID1_G, games.ID2_G, games.RESULT_G, games.DATE_G
from Tours_{0} AS tours, Games_{0} AS games, Rounds, Courts
where games.ID_T_G = tours.ID_T
and Rounds.ID_R = games.ID_R_G
and tours.ID_C_T = Courts.ID_C
and tours.DATE_T < {1}
and ((games.ID1_G = {2} and games.ID2_G = {3}) or
(games.ID2_G = {2} and games.ID1_G = {3}) )
and (tours.NAME_T Not Like '%juniors%')
;""".format(
sex,
dba.msaccess_date(tour_date_max),
match.first_player.ident,
match.second_player.ident,
)
with closing(dba.get_connect().cursor()) as cursor:
for (
rnd_name,
surf_name,
tour_id,
tour_name,
tour_rank,
tour_time,
money,
cou,
p1st_id,
p2nd_id,
score_name,
match_dtime,
) in cursor.execute(sql):
score = sc.Score(score_name)
if score.retired and completed_only:
continue
tour = Tournament(
ident=tour_id,
name=tour_name,
sex=sex,
surface=Surface(surf_name),
rank=tour_rank,
date=tour_time.date() if tour_time else None,
money=money,
cou=cou,
)
if tour.level == "future":
continue
match_date = match_dtime.date(
) if match_dtime is not None else None
if (p1st_id == match.first_player.ident
and p2nd_id == match.second_player.ident):
m = Match(
match.first_player,
match.second_player,
score=score,
rnd=Round(rnd_name),
date=match_date,
)
else:
m = Match(
match.second_player,
match.first_player,
score=score,
rnd=Round(rnd_name),
date=match_date,
)
avgset = self._get_avgset(tour, m)
self.tour_match_aset.append((tour, m, avgset))
self.tour_match_aset.sort(key=lambda i: i[0].date, reverse=True)
self._remove_current_match(match.date)
def rows(self):
with closing(dba.get_connect().cursor()) as cursor:
cursor.execute(self.sql)
for row in dba.result_iter(cursor):
yield SqlBuilder.Row(*row)
def _initialize_results_sex(sex,
max_rating,
max_rating_dif,
min_date=None,
max_date=None):
sql = """select tours.DATE_T, tours.NAME_T, tours.RANK_T, tours.PRIZE_T,
games.ID_R_G, games.RESULT_G, games.ID1_G, games.ID2_G
from Tours_{0} AS tours, games_{0} AS games, Players_{0} AS fst_plr
where games.ID_T_G = tours.ID_T
and games.ID1_G = fst_plr.ID_P
and (tours.NAME_T Not Like '%juniors%')
and (fst_plr.NAME_P Not Like '%/%') """.format(sex)
sql += dba.sql_dates_condition(min_date, max_date)
sql += " order by tours.DATE_T;"
with closing(dba.get_connect().cursor()) as cursor:
for (
tour_dt,
tour_name,
db_rank,
db_money,
rnd_id,
score_txt,
fst_id,
snd_id,
) in cursor.execute(sql):
date = tour_dt.date() if tour_dt else None
if date is None:
raise co.TennisScoreError("none date {}".format(tour_name))
if not score_txt:
continue
scr = sc.Score(score_txt)
if scr.retired:
continue
sets_count = scr.sets_count(full=True)
if sets_count != 3 or scr.best_of_five():
continue
set3_score = scr[2]
if set3_score[0] < set3_score[1]:
raise co.TennisScoreError(
"right winner unexpected {}".format(scr))
money = oncourt_db.get_money(db_money)
rank = None if db_rank is None else int(db_rank)
if rank is None:
log.error("none rank date: {} scr: {} name: {}".format(
date, scr, tour_name))
if not isinstance(rank, int):
raise co.TennisError(
"not int rank '{}' date: {} scr: {} name: {}".format(
rank, date, scr, tour_name))
rawname, level = oncourt_db.get_name_level(sex, tour_name.strip(),
rank, money, date)
if level in DISABLE_LEVELS:
continue
if level is None:
raise co.TennisError(
"none level date: {} scr: {} name: {}".format(
date, scr, tour_name))
rnd = tennis.Round.from_oncourt_id(rnd_id)
soft_level = tennis.soft_level(level, rnd)
if soft_level is None:
raise co.TennisError(
"none soft_level date: {} scr: {} name: {}".format(
date, scr, tour_name))
mdata = _get_match_data(sex, date, fst_id, snd_id, scr, max_rating,
max_rating_dif)
if mdata is not None:
past_monday = tt.past_monday_date(date)
ywn = tt.get_year_weeknum(past_monday)
data_dict[(sex, soft_level)][ywn][(mdata.set1_score,
mdata.set2_score)].hit(
mdata.decided_win)