def list_all_tournament_matches_since_qdate(tournament_name, query_date):
list_all_tournament_matches_since_qdate = "\
SELECT match_id, \
tournament,\
date,\
home_team_id,\
home_team_name,\
away_team_id,\
away_team_name,\
full_time_home_goals,\
full_time_away_goals,\
result,\
sdate\
FROM match_fixtures\
WHERE \
tournament = %s \
and date >= %s \
order by date desc"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(list_all_tournament_matches_since_qdate,
(tournament_name, query_date))
return cursor.fetchall()
# xs = list_all_tournament_matches_since_qdate('Serie A', utils.beforeXmonth(6))
# for x in xs:
# print(x)
def list_team_squad_statistics_latest_sepcial_date(tournament, team_id, atype,
view, sepcial_date):
sepcial_sdate = query_team_squad_last_record_sdate(tournament, team_id,
atype, view,
sepcial_date)
query_datas = "SELECT\
`player_id`,\
`player_name`,\
`date`,\
`rating`,\
`cm`,\
`apps`,\
`mins`,\
`goals`,\
`assists`,\
`shots_pg`,\
`pass`,\
`aerials_won`,\
`man_ot_match` \
FROM `squad_statistics` where tournament = %s and team_id = %s and type = %s and view = %s and sdate = %s order by rating desc"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_datas,
(tournament, team_id, atype, view, sepcial_sdate))
return cursor.fetchall()
# sdate = query_team_squad_last_record_sdate('La Liga',62,team_statistics_repository.type_Summary,team_statistics_repository.view_Overall,utils.beforeXmonth(4))
# print(sdate)
# ts = list_team_squad_statistics_latest_sepcial_date('La Liga',62,team_statistics_repository.type_Summary,team_statistics_repository.view_Overall,utils.beforeXmonth(4))
# for t in ts:
# print(t)
def list_latest_special_date_tournament_teams(tournament_name, special_date):
query_date = find_latest_date_before_date(tournament_name, special_date)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
list_tournament_teams_sql = "SELECT \
`tournament`,\
`date`,\
`no`,\
`team_name`,\
`team_link`,\
`team_id`,\
`played`,\
`win`,\
`draw`,\
`loss`,\
`goals_for`,\
`goals_against`,\
`goals_difference`,\
`points`,\
`sdate`\
FROM `tournament_teams` where tournament = %s and date = %s order by points desc "
ps = (tournament_name, query_date)
cursor.execute(list_tournament_teams_sql, ps)
return cursor.fetchall()
def insert_fetch_url(url, atype, params, priority=priority_Normal):
sdate = utils.date2sdate(datetime.now())
if not should_be_insert(query_fetch_url_all_records(url)):
print(atype + "_" + url + " fetch url already exist, no need insert")
return
insert_sql = "\
INSERT INTO `fetch_url` (\
`url`,\
`type`,\
`date`,\
`status`,\
`params_json`,\
`sdate`,\
`priority`)\
VALUES \
(%s,%s,%s,%s,%s,%s,%s) "
values = (url, atype, datetime.now(), status_TODO, json.dumps(params),
sdate, priority)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def query_fetch_url_all_records(url):
query_last_date = "SELECT sdate,status,priority,id FROM `fetch_url` where url = %s "
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_date, (url, ))
results = cursor.fetchall()
return results
def update_last_record_of_url_status(url, errors):
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
nid = 0
try:
sdate = query_fetch_url_last_TODO_record_sdate(url)
if sdate is None:
# may be from last error fix process
sdate = query_fetch_url_last_Error_record_sdate(url)[0]
status = status_Done
if len(errors) > 0:
status = status_SomethingBlankOrIssue
update_sql = " UPDATE fetch_url SET status = %s , error_records = %s , date = %s , sdate = %s WHERE url = %s and sdate = %s "
values = (status, json.dumps(errors), datetime.now(),
utils.date2sdate(datetime.now()), url, sdate)
cursor.execute(update_sql, values)
nid = cursor.lastrowid
cnx.commit()
except:
cnx.rollback()
finally:
cursor.close()
cnx.close()
return nid
def find_max_team_number_within_x_month(tournament_name, x=3):
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
max_team_no = "SELECT no FROM `tournament_teams` where tournament = %s and date >= %s order by no desc limit 1"
ps = (tournament_name, utils.beforeXmonth(x))
cursor.execute(max_team_no, ps)
return cursor.fetchone()[0]
def find_latest_date_before_date(tournament_name, query_date):
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
list_tournament_teams_sql = "SELECT date FROM `tournament_teams` where tournament = %s and date <= %s order by date desc , no asc limit 1"
ps = (tournament_name, query_date)
cursor.execute(list_tournament_teams_sql, ps)
return cursor.fetchone()
def insert_team_statistics_defensive(tournament, team_id, team_name, view,
rating, apps, shots_conceded_pg,
tackles_pg, interceptions_pg, fouls_pg,
offsides_pg):
exist = query_team_statistics_last_record_data(tournament, team_id,
type_Defensive, view)
if apps is not None:
iapps = int(apps)
else:
iapps = None
if rating is not None:
frating = float(rating)
else:
frating = None
insert_values = (iapps, frating)
if exist is not None \
and insert_values == exist:
# already exist this record, do not insert again
print(tournament + "_" + str(team_id) + "_" + team_name + "_" +
type_Defensive + "_" + view + " nothing change, no need insert")
return
sdate = utils.date2sdate(datetime.now())
insert_sql = "\
INSERT INTO `team_statistics` (\
`team_id`,\
`team_name`,\
`date`,\
`type`,\
`view`,\
`tournament`,\
`rating`,\
`apps`,\
`shots_conceded_pg`,\
`tackles_pg`,\
`interceptions_pg`,\
`fouls_pg`,\
`offsides_pg`,\
`sdate`)\
VALUES \
( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
values = (team_id, team_name, datetime.now(), type_Defensive, view,
tournament, rating, apps, shots_conceded_pg, tackles_pg,
interceptions_pg, fouls_pg, offsides_pg, sdate)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def insert_squad_statistics_summary(tournament, team_id, view, player_id,
player_name, rating, cm, apps, mins, goals,
assists, shots_pg, apass, aerials_won,
man_ot_match):
sdate = utils.date2sdate(datetime.now())
exist = query_squad_statistics_last_record_sdate(
tournament, team_id, player_id,
team_statistics_repository.type_Summary, view)
if exist is not None \
and sdate == exist:
# already exist this record, do not insert again
print(sdate + "_" + tournament + "_" + team_id + "_" + player_id +
"_" + player_name + "_" +
team_statistics_repository.type_Summary + "_" + view +
" squad has been inserted today, no need insert")
return
insert_sql = "\
INSERT INTO `squad_statistics` (\
`tournament`,\
`team_id`,\
`type`,\
`view`,\
`player_id`,\
`player_name`,\
`date`,\
`rating`,\
`cm`,\
`apps`,\
`mins`,\
`goals`,\
`assists`,\
`shots_pg`,\
`pass`,\
`aerials_won`,\
`man_ot_match`,\
`sdate`) \
VALUES \
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
values = (tournament, team_id, team_statistics_repository.type_Summary,
view, player_id, player_name, datetime.now(), rating, cm, apps,
mins, goals, assists, shots_pg, apass, aerials_won, man_ot_match,
sdate)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def insert_team_statistics_summary(tournament, team_id, team_name, view,
rating, apps, goals, shots_pg, possession,
apass, aerials_won):
exist = query_team_statistics_last_record_data(tournament, team_id,
type_Summary, view)
if apps is not None:
iapps = int(apps)
else:
iapps = None
if rating is not None:
frating = float(rating)
else:
frating = None
insert_values = (iapps, frating)
if exist is not None \
and insert_values == exist:
# already exist this record, do not insert again
print(tournament + "_" + str(team_id) + "_" + team_name + "_" +
type_Summary + "_" + view +
" team stat nothing change, no need insert")
return
sdate = utils.date2sdate(datetime.now())
insert_sql = "\
INSERT INTO `team_statistics` (\
`team_id`,\
`team_name`,\
`date`,\
`type`,\
`view`,\
`tournament`,\
`rating`,\
`apps`,\
`goals`,\
`shots_pg`,\
`possession`,\
`pass`,\
`aerials_won`,\
`sdate`)\
VALUES \
( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
values = (team_id, team_name, datetime.now(), type_Summary, view,
tournament, rating, apps, goals, shots_pg, possession, apass,
aerials_won, sdate)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def query_team_statistics_last_record_data(tournament, team_id, atype, view):
query_last_data = "SELECT apps,rating FROM `team_statistics` where tournament = %s and team_id = %s and type = %s and view = %s order by date desc limit 1"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_data, (tournament, team_id, atype, view))
last_data = cursor.fetchone()
if last_data is not None:
return last_data
else:
return None
def query_tournament_last_record_sdate(tournament, team_id):
query_last_data = "SELECT sdate FROM `tournament_teams` where tournament = %s and team_id = %s order by date desc limit 1"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_data, (tournament, team_id))
last_data = cursor.fetchone()
if last_data is not None:
return last_data
else:
return None
def list_latest_tournament_teams(tournament_name, query_date):
limit = find_max_team_number_within_x_month(tournament_name)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
list_tournament_teams_sql = "SELECT * FROM `tournament_teams` where tournament = %s and date <= %s order by date desc , no asc limit " + str(
limit)
ps = (tournament_name, query_date)
cursor.execute(list_tournament_teams_sql, ps)
return cursor.fetchall()
def query_fetch_url_last_Error_record_sdate(url):
query_last_date = "SELECT sdate , id FROM `fetch_url` where url = %s and status = 'Error' order by date desc limit 1"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_date, (url, ))
sdates = cursor.fetchone()
if sdates is not None:
return sdates
else:
return None
def query_error_fetch_urls():
print('query_error_fetch_urls from DB ')
query_todo_sql = "SELECT type, params_json,priority, id FROM fetch_url WHERE status = 'Error' order by priority"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_todo_sql)
fetches = cursor.fetchall()
if fetches is not None:
return fetches
else:
return None
def query_team_statistic_last_record_data_before_date(tournament, team_id,
atype, view,
before_date):
if (atype == type_Summary):
query_last_data = "SELECT \
`rating`,\
`apps`,\
`goals`,\
`shots_pg`,\
`possession`,\
`pass`,\
`aerials_won`,\
FROM `team_statistics` where tournament = %s and team_id = %s and type = %s and view = %s and date <= %s order by date desc limit 1"
elif (atype == type_Offensive):
query_last_data = "SELECT \
`rating`,\
`apps`,\
`shots_pg`,\
`shots_ot_pg`,\
`dribbles_pg`,\
`fouled_pg`,\
FROM `team_statistics` where tournament = %s and team_id = %s and type = %s and view = %s and date <= %s order by date desc limit 1"
elif (atype == type_Defensive):
query_last_data = "SELECT \
`rating`,\
`apps`,\
`shots_conceded_pg`,\
`tackles_pg`,\
`interceptions_pg`,\
`fouls_pg`,\
`offsides_pg`,\
FROM `team_statistics` where tournament = %s and team_id = %s and type = %s and view = %s and date <= %s order by date desc limit 1"
else:
print("atype never be seen before.")
return
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_data,
(tournament, team_id, atype, view, before_date))
last_data = cursor.fetchone()
if last_data is not None:
return last_data
else:
return None
# x = query_team_statistics_last_record_data('League Cup', 560, 'Summary', 'Overall')
# print(x)
# y = (8,None)
# print(x==y)
# print(insert_team_statistics_summary('League Cup', 560,'abc', 'Overall', None, 8, 2.32, 2.65, None,None,None))
def query_match_id(home_team_id, away_team_id, sdate):
query_one_match_id = "\
SELECT match_id \
FROM match_fixtures\
WHERE \
home_team_id = %s and away_team_id = %s and sdate= %s"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_one_match_id, (home_team_id, away_team_id, sdate))
one_record = cursor.fetchone()
if one_record is not None:
return one_record[0]
else:
return None
def query_team_squad_last_record_sdate(tournament,
team_id,
atype,
view,
before_date=datetime.now()):
query_last_data = "SELECT sdate FROM `squad_statistics` where tournament = %s and team_id = %s and type = %s and view = %s and date <= %s order by date desc limit 1"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_last_data,
(tournament, team_id, atype, view, before_date))
last_date = cursor.fetchone()
if last_date is not None:
return last_date[0]
else:
return None
def insert_tournament_team(tournament, no, team_name, team_link, team_id,
played, win, draw, loss, goals_for, goals_against,
goals_difference, points):
sdate = utils.date2sdate(datetime.now())
exist = query_tournament_last_record_sdate(tournament, team_id)
if exist is not None \
and exist[0] == sdate:
# already exist this record, do not insert again
print(tournament + " : " + team_id +
" tournament has been inserted today, no need insert")
return
insert_sql = "\
INSERT INTO `tournament_teams`(\
`tournament`,\
`date`,\
`no`,\
`team_name`,\
`team_link`,\
`team_id`,\
`played`,\
`win`,\
`draw`,\
`loss`,\
`goals_for`,\
`goals_against`,\
`goals_difference`,\
`points`,\
`sdate`)\
VALUES \
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
values = (tournament, datetime.now(), no, team_name, team_link, team_id,
played, win, draw, loss, goals_for, goals_against,
goals_difference, points, sdate)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def insert_match_fixture(match_fixture_entity):
exist = query_one_match_fixture_entity(match_fixture_entity.match_id)
if exist is not None \
and exist == match_fixture_entity:
print("match " + match_fixture_entity.match_id +
" fixture already exist, no need insert")
return
insert_sql = "\
INSERT INTO match_fixtures (\
match_id,\
tournament,\
date,\
home_team_id,\
home_team_name,\
away_team_id,\
away_team_name,\
full_time_home_goals,\
full_time_away_goals,\
result,\
sdate)\
VALUES \
(%(match_id)s,\
%(tournament)s,\
%(date)s,\
%(home_team_id)s,\
%(home_team_name)s,\
%(away_team_id)s,\
%(away_team_name)s,\
%(full_time_home_goals)s,\
%(full_time_away_goals)s,\
%(result)s,\
%(sdate)s )"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, match_fixture_entity.__dict__)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def mark_url_errors(url, errors):
sdate = query_fetch_url_last_TODO_record_sdate(url)
status = status_Error
update_sql = " UPDATE fetch_url SET status = %s , error_records = %s , date = %s , sdate = %s WHERE url = %s and sdate = %s "
values = (status, json.dumps(errors), datetime.now(),
utils.date2sdate(datetime.now()), url, sdate)
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(update_sql, values)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
def query_one_match_fixture_entity(match_id):
query_one_match_id = "\
SELECT match_id, \
tournament,\
date,\
home_team_id,\
home_team_name,\
away_team_id,\
away_team_name,\
full_time_home_goals,\
full_time_away_goals,\
half_time_home_goals,\
half_time_away_goals,\
result,\
sdate\
FROM match_fixtures\
WHERE \
match_id = %s"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_one_match_id, (match_id, ))
one_record = cursor.fetchone()
if one_record is not None:
match_fixture = match_fixture_entity()
match_fixture.match_id = one_record[0]
match_fixture.tournament = one_record[1]
match_fixture.date = one_record[2]
match_fixture.home_team_id = one_record[3]
match_fixture.home_team_name = one_record[4]
match_fixture.away_team_id = one_record[5]
match_fixture.away_team_name = one_record[6]
match_fixture.full_time_home_goals = one_record[7]
match_fixture.full_time_away_goals = one_record[8]
match_fixture.half_time_home_goals = one_record[9]
match_fixture.half_time_away_goals = one_record[10]
match_fixture.result = one_record[11]
match_fixture.sdate = one_record[12]
return match_fixture
else:
return None
def query_one_match_preview(match_id):
query_one_sql = " SELECT * FROM match_previews WHERE match_id = %s"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(query_one_sql, (match_id, ))
one_record = cursor.fetchone()
if one_record is not None:
return one_record
else:
return None
# print(query_one_match_preview(1281313)[21])
# print(query_one_match_preview(1281313)[20])
# h = json.loads(query_one_match_preview(1281313)[20])
# print(type(h))
# for o in h.keys():
# print(o)
# print(h[o])
# print("----------------")
def update_url_priority(url, priority):
exists = query_fetch_url_all_records(url)
if exists is not None:
for exist in exists:
if (exist[1] == status_TODO):
#priority
if (exist[2] > priority):
update_sql = " UPDATE fetch_url SET priority = %s WHERE id = %s "
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
#id
cursor.execute(update_sql, (priority, exist[3]))
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
print('url priority has been updated:' + url)
return nid
else:
print('fetch url priority is higher , no need update')
return -1
return -1
def delete_id(id):
print('delete Error record , id :' + str(id))
delete_sql = "DELETE FROM fetch_url WHERE id = %s"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(delete_sql, (id, ))
cnx.commit()
cursor.close()
cnx.close()
# print(delete_id(2))
# insert_fetch_url("12345", type_TeamHome, "goodluck")
# print(query_fetch_url_last_TODO_record_sdate("12345"))
# print(query_fetch_url_last_Error_record_sdate("ttyuu"))
# errors = []
# print(update_last_record_of_url_status("12345", errors))
# xs = query_todo_fetch_urls()
# for i in range(0,20):
# print(xs[i])
# print(query_todo_fetch_urls())
# print(query_fetch_url_all_records('https://www.whoscored.com/Teams/9649/Show/Greece-Apollon-Smirnis'))
def insert_match_preview(match_preview_entity):
exist = query_one_match_preview(match_preview_entity.match_id)
if exist is not None \
and exist == int(match_preview_entity.match_id):
print(match_preview_entity.match_id +
" preview already exist, no need insert")
return
insert_sql = "\
INSERT INTO match_previews ( \
`match_id`,\
`date`,\
`home_team_id`,\
`home_team_name`,\
`home_goals`,\
`home_assists`,\
`home_average_ratings`,\
`home_shots_pg`,\
`home_aerial_duel_success`,\
`home_dribbles_pg`,\
`home_tackles_pg`,\
`away_team_id`,\
`away_team_name`,\
`away_goals`,\
`away_assists`,\
`away_average_ratings`,\
`away_shots_pg`,\
`away_aerial_duel_success`,\
`away_dribbles_pg`,\
`away_tackles_pg`,\
`home_missing_players`,\
`away_missing_players`,\
`home_players`,\
`away_players`)\
VALUES\
(%(match_id)s,\
%(date)s,\
%(home_team_id)s,\
%(home_team_name)s,\
%(home_goals)s,\
%(home_assists)s,\
%(home_average_ratings)s,\
%(home_shots_pg)s,\
%(home_aerial_duel_success)s,\
%(home_dribbles_pg)s,\
%(home_tackles_pg)s,\
%(away_team_id)s,\
%(away_team_name)s,\
%(away_goals)s,\
%(away_assists)s,\
%(away_average_ratings)s,\
%(away_shots_pg)s,\
%(away_aerial_duel_success)s,\
%(away_dribbles_pg)s,\
%(away_tackles_pg)s,\
%(home_missing_players)s,\
%(away_missing_players)s,\
%(home_players)s,\
%(away_players)s )"
cnx = utils.get_mysql_connector()
cursor = cnx.cursor()
cursor.execute(insert_sql, match_preview_entity.__dict__)
nid = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
return nid
