def insert_match_list(params):
db = dbConn.DbConn()
match_list = []
# puuid 리스트 만들어서
sql = "SELECT s_puuid FROM summoner where s_puuid IS not null"
puuid_list = db.selectdict(sql)
# puuid_list = db.select(sql)
# print(type(puuid_list), puuid_list)
# get_match_list 를 통해 api 호출
i = 1
j = len(puuid_list)
for pl in puuid_list:
puuid = pl['s_puuid']
get_match = get_match_list(params, puuid, count=30)
# match_list.append(get_match)
match_list.extend(get_match)
print(i, '/', j, '-----진행중 !!')
i += 1
sleep(1)
# 중복을 제거 한 후
print('전체 리스트:', len(match_list))
match_list = list(set(match_list))
print('중복 제거 리스트:', len(match_list))
# 인서트
sql = "insert into match_list(ml_match) values(%s) on duplicate key update timestamp = CURRENT_TIMESTAMP();"
res = db.executemany(sql, match_list)
print('db insert:', res)
def get_top_tier_info(params):
# tier_dict = {'challenger' : [], 'grandmaster' : [], 'master' : []}
tier_list = ['challenger', 'grandmaster', 'master']
tier_url = 'https://kr.api.riotgames.com/tft/league/v1/%s'
res_list = []
for tier in tier_list:
res_tier = req.get(tier_url % tier, params=params)
if res_tier.status_code == 200:
tier_summ_list = res_tier.json()['entries']
insert_data_list = []
for tier_info in tier_summ_list: # 각 티어별 소환사 정보
insert_data_list.append([
tier_info['summonerId'], tier_info['summonerName'],
tier_info['leaguePoints'], tier
])
db = dbConn.DbConn()
sql = "insert into summoner(s_id, s_name, s_points, s_tier) values(%s, %s, %s, %s) on duplicate key update s_name = values(s_name), s_points = values(s_points), s_tier = values(s_tier);"
res = db.executemany(sql, insert_data_list)
res_list.append(res)
return res_list
def insert_puuid(params):
db = dbConn.DbConn()
sql = "SELECT * FROM summoner WHERE s_puuid IS null"
puuid_is_null = db.selectdict(sql)
for i in puuid_is_null:
puuid = get_puuid(params, summoner_id=i['s_id'])
sql = f"UPDATE summoner SET s_puuid = '{puuid}' WHERE s_no={i['s_no']}"
db.execute(sql)
sleep(1)
def insert_match_info(params):
db = dbConn.DbConn()
# 1. match_list 테이블에서 ml_check 가 0인것들 리스트 가져오기
# sql = "SELECT * FROM match_list WHERE ml_check = 0"
# 뒤에꺼
# sql = "SELECT * FROM match_list WHERE ml_check = 0 AND ml_id >= 5231 order by ml_id"
# 앞에꺼
sql = "SELECT * FROM match_list WHERE ml_check = 0 AND ml_id < 5231 order by ml_id"
match_list = db.selectdict(sql)
# print(len(match_list), match_list)
totallen = len(match_list)
cnt = 0
# 2. api호출 상세정보 가져오기
for match in match_list:
ml_id = match['ml_id']
match_id = match['ml_match']
match_info = get_match_info(params, match_id)
# print(match_info)
# 3. match_metadata 게임정보 테이블 채우기
mm_version = match_info['info']['game_version']
mm_datetime = match_info['info']['game_datetime']
mm_length = match_info['info']['game_length']
meta_sql = f"INSERT INTO match_metadata (match_id, mm_version, mm_datetime, mm_length) values ('{match_id}','{mm_version}','{mm_datetime}','{mm_length}') on duplicate key update mm_version = '{mm_version}', mm_datetime = '{mm_datetime}', mm_length = {mm_length};"
# print(meta_sql)
db.execute(meta_sql)
# 4. participant 게임유저정보 테이블 채우기
for participant in match_info['info']['participants']:
p_puuid = participant['puuid']
p_gold_left = participant['gold_left']
p_last_round = participant['last_round']
p_level = participant['level']
p_placement = participant['placement']
p_time_eliminated = participant['time_eliminated']
p_total_damage = participant['total_damage_to_players']
participant_sql = f"""INSERT INTO participant (match_id, p_puuid, p_gold_left, p_last_round, p_level, p_placement, p_time_eliminated, p_total_damage)
VALUES ('{match_id}', '{p_puuid}', {p_gold_left}, {p_last_round}, {p_level}, {p_placement}, {p_time_eliminated}, {p_total_damage})
on duplicate key update p_gold_left = {p_gold_left}, p_last_round = {p_last_round}, p_level = {p_level}, p_placement = {p_placement}, p_time_eliminated = {p_time_eliminated}, p_total_damage = {p_total_damage}
"""
# print(participant_sql)
p_no = db.execute(participant_sql)
if p_no > 0:
# 6. trait 게임시너지 상세정보 채우기
for trait in participant['traits']:
t_name = trait['name']
t_num_units = trait['num_units']
t_style = trait['style']
t_tier_current = trait['tier_current']
t_tier_total = trait['tier_total']
trait_sql = f"""INSERT INTO trait (p_no, t_name, t_num_units, t_style, t_tier_current, t_tier_total)
VALUES ({p_no}, '{t_name}', {t_num_units}, {t_style}, {t_tier_current}, {t_tier_total})
on duplicate key update t_num_units = {t_num_units}, t_style = {t_style}, t_tier_current = {t_tier_current}, t_tier_total = {t_tier_total}
;
"""
db.execute(trait_sql)
# 7. unit 게임기물상세정보 채우기
for unit in participant['units']:
u_character_id = unit['character_id']
u_items_1 = unit['items'][0] if len(
unit['items']) > 0 else 0
u_items_2 = unit['items'][1] if len(
unit['items']) > 1 else 0
u_items_3 = unit['items'][2] if len(
unit['items']) > 2 else 0
u_rarity = unit['rarity']
u_tier = unit['tier']
unit_sql = f"""INSERT INTO unit (p_no, u_character_id, u_items_1, u_items_2, u_items_3, u_rarity, u_tier)
VALUES ({p_no}, '{u_character_id}', {u_items_1}, {u_items_2}, {u_items_3}, {u_rarity}, {u_tier});
"""
db.execute(unit_sql)
# 8. 위에것들 순차적으로 다 채워졌으면 match_list ml_check 값 1로 변경
update_sql = f"UPDATE match_list set ml_check = 1 where ml_match = '{match_id}';"
db.execute(update_sql)
cnt += 1
print(cnt, '/', totallen)
print(match_id, "데이터 입력 완료 !!")