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