[코딩] Riot API에서 정보 가져오기 2

API에서 가져온 정보를 DB에 저장하려고 한다.

 

이전에 Coursera에서 Python for Everyone 코스를 수강하면서 파이썬의 SQLite3 모듈과 DB Browser for SQLite 프로그램을 써본 적이 있어서 이번에도 같은 프로그램을 사용하려고 한다.

 

데이터베이스 구조는 밑의 그림과 같이 짰다.

한 사람이 여러 경기를 하는 경우도 있고, 여러 사람이 같은 게임을 하는 경우도 있었기 때문에 user 테이블과 match 테이블 사이에 user_idx와 match_id만 모아놓은 game_played 테이블을 추가했다.

 

sqlite3 패키지를 이용해 파이썬에서 다음과 같이 테이블을 만들어준다.

 

import sqlite3
from start import *
import time
from tqdm import tqdm
import json

conn = sqlite3.connect("gamedata.db") 

cur = conn.cursor()
cur.executescript('''
        CREATE TABLE IF NOT EXISTS user(
            idx INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
            nickname TEXT UNIQUE,
            nickname_nocase TEXT UNIQUE COLLATE NOCASE,
            puuid TEXT UNIQUE,
            id TEXT UNIQUE,
            account_id TEXT UNIQUE
        );

        CREATE TABLE IF NOT EXISTS game_played(
            user_idx INTEGER,
            match_id TEXT,
            PRIMARY KEY (user_idx, match_id)
        );

        CREATE TABLE IF NOT EXISTS match(
            match_id TEXT NOT NULL PRIMARY KEY UNIQUE, 
            data TEXT
        )
    ''')

 

<SQL 기억할 만한 특징 몇 가지>

-IF NOT EXISTS 를 이용해 스크립트를 실행할 때 테이블이 이미 있으면 건너 뛰고 없으면 새로 만들도록 했다

-AUTOINCREMENT 를 사용하면 따로 값을 추가해주지 않아도 자동으로 넘버링이 된다

-PRIMARY KEY 는 하나의 column key에도 적용할 수 있지만 여러 column key의 튜플로 지정하는 것도 가능하다

 

<대소문자, 띄어쓰기 관련 문제>

리그오브레전드의 소환사 닉네임은 대소문자 구분이 없고 띄어쓰기도 무시하기 때문에 Hide on bush, hidEOnbusH와 같이 다른 식으로 검색해도 같은 결과값이 나와야 한다. RIOT API에서는 각각 다른 url로 request를 보내도 자동으로 같은 결과값을 보내준다.

 

request를 통해 소환사 정보를 찾는 경우와 달리 DB에서는 정확한 소환사 닉네임을 적어야만 찾을 수 있는 문제가 있었다. 대소문자나 띄어쓰기가 달라도 닉네임을 찾을 수 있도록 만들기 위해서 nickname_nocase 라는 새로운 열에 nickname 의 값에서 COLLATE NOCASE를 통해 대/소문자를 무시하고, 띄어쓰기를 제거한 값을 저장했다. 아이디 검색을 할 때 띄어쓰기를 제거하고 그 값을 nickname_nocase와 비교하는 식으로 해결하려는 생각이다. 

 

nickname_list = ["hide on bush", "DK ShowMaker"]

for nickname in nickname_list:
    # 닉네임으로 id 검색
    data = search_summoner_by_name(nickname)
    id = data['id']
    account_id = data['accountId']
    puuid = data['puuid']
    nickname = data['name']
    nickname_nocase = nickname.lower()
    nickname_nocase = nickname_nocase.replace(' ', '')

    # DB 저장
    cur.execute('''INSERT OR IGNORE INTO user(nickname, nickname_nocase, puuid, id, account_id)
        VALUES ( ?, ?, ?, ?, ? )''', ( nickname, nickname_nocase, puuid, id, account_id ))
    cur.execute('''SELECT idx FROM User WHERE nickname_nocase = REPLACE( (?) , ' ','')''', (nickname, ))
    user_idx = cur.fetchone()[0]

    # puuid로 최근 6개월 게임 match_id를 저장
    epoch = int(time.time()) - 16070400
    now = int(time.time()) + 86400
    
    for epoch in tqdm(range(epoch, now, 86400), desc='collecting match_id'):
        
        #puuid로 경기 검색
        d = get_matchid_by_puuid(puuid, count=100, endTime=epoch)

        for match_id in d:
            cur.execute('''INSERT OR IGNORE INTO game_played (user_idx, match_id)
                VALUES ( ?, ? )''', ( user_idx, match_id ) )
        time.sleep(1.5)

cur.execute('SELECT DISTINCT match_id FROM game_played WHERE user_idx = ( ? )', (user_idx, ))
count = len(cur.fetchall())
print(f'finished saving match_id to db: {count} items found')

닉네임을 리스트로 주면 각 닉네임에 대해서 user 테이블과 game_played 테이블을 채워주도록 코드를 짰다.

 

API를 보니 최근 6개월까지의 match_id 밖에 반환해주지 않는 것 같다.

일단은 단순하게 6개월 전 시각에서부터 24시간 단위로 100개씩 검색하고, DB에 중복되지 않는 값만 저장하도록 했다.

 

24시간 단위로 반복문을 돌렸는데, 이건 한 번에 match_id가 최대 100개씩 검색 가능하고 한 판이 최소 15분이라고 생각했을 때 25시간이어서 정한 기준이기도 하고, 사람이 24시간에 100판 이상 하지 않을 것이라는 합리적인 기대에서 비롯된 단위이기도 하다.

다만 탈주자가 있는 경기는 15분보다 짧을 수도 있기 때문에 아주 드물게 하루에 100판 이상 하는 예외적인 유저가 있을 경우에 match_id가 누락될 가능성이 있다.

그리고 API request 횟수 제한이 있어서 match_id를 가져오는데 상당히 오래 걸린다는 문제점이 있다. 특히 게임을 거의 하지 않는 한 유저의 모든 match_id를 가져오는데 8분 정도 걸리는데 정작 저장되는 데이터 자체는 많지 않다는 것을 확인했다.

 

이 부분은 나중에 수정이 필요하다고 느꼈다.

 

match_rows = cur.execute('''SELECT DISTINCT match_id FROM game_played 
		WHERE NOT EXISTS(SELECT 1 FROM match WHERE match.match_id = game_played.match_id)''')
match_rows = match_rows.fetchall()
for match_row in tqdm(match_rows, desc='fetching match data'):
    match_id = match_row[0]

    match_data = get_match_data(match_id)
    if 'status' in data.keys():
        print(match_data['status'])
        break

    match_data = json.dumps(match_data)
    cur.execute('''INSERT OR IGNORE INTO match(match_id, data) 
        VALUES ( ?, ? )''', ( match_id, str(match_data) ) )
    
    time.sleep(1.5)
time.sleep(10)

print('[save match data complete]')

    
conn.commit()
conn.close()
print('[Done]')

그리고는 각각의 match_id에 대해 경기 정보를 받아와 json을 그대로 string(text)으로 저장했다. 

반환 받는 데이터의 형태가 dictionary 안의 dictionary 형태로 여러 층으로 되어있었기 때문에 바로 테이블 형태로 바꿔 저장하기가 힘들었고, 또 워낙 json 자체가 길어서 일단 그냥 통째로 저장해 놓고 나중에 필요한 정보를 각각 뽑아 쓰는 방식으로 방향을 정했다. 

 

받아온 json이 정상적인 경기 데이터가 아닌 경우 status가 key로 들어가기 때문에 status가 있는 경우 건너 뛰어주었다.

 

 

이렇게 API에서 원하는 정보를 받아와 DB에 저장을 하는 것까지 구현이 된 것 같다.

아직까지 데이터를 가져오는데 생각보다 너무 오래걸리기 때문에(내 아이디 1개에 1시간 정도 걸렸다) 더 최적화할 수 있을지 고민해봐야할 것 같다.

 

다음에는 이 저장된 정보에서 필요한 부분만 가져와 시각화해보는 것을 해보려고 한다.