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 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 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_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_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 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 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 should_be_insert(exists): if exists is not None: sdate = utils.date2sdate(datetime.now()) # 今天没有处理过的记录,无论是什么状态的 not_todays_record = True # 没有待处理的TODO数据 not_in_todo_list = True for exist in exists: # sdate if (exist[0] == sdate): not_todays_record = False print('fetch url has been processed today , no need insert') return False # status if (exist[1] == status_TODO): not_in_todo_list = False print('fetch url has already into TODO list , no need insert') return False # come to here should be True return not_todays_record and not_in_todo_list return True