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