-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmysql_player_match_history_censored.py
160 lines (122 loc) · 6.42 KB
/
mysql_player_match_history_censored.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# -*- coding: utf-8 -*-
"""mysql_player_match_history.py
Automatically generated by Colaboratory.
## **This is a Python script that extracts match data using the Riot API, stores the necessary information to a pandas dataframe, and loads the table into an Amazon RDS database**
"""
#import libraries
import requests
import pandas as pd
import time
import pymysql
import numpy as np
from sqlalchemy import create_engine
"""## **Function to create a table based on the player name in AWS RDS it does not exist**"""
#Create sql table in RDS if it doesn't exist
def create_table(cursor):
sql = ('''CREATE TABLE IF NOT EXISTS %s_match_info (
player_id VARCHAR(50), player_name VARCHAR(16), match_id VARCHAR(50) primary key,
date_played int, game_length int, game_mode TEXT, queue_id int, lane text,
champion_played VARCHAR(30), total_dmg int, kills int, deaths int, assists int,
vision_score int, creep_score int, win boolean)''' % player_name)
cursor.execute(sql)
"""## **Function to retrieve player id from the player in-game name using Riot's API**"""
def get_player_id(player_name): #retrieve player id
response = requests.get(
'https://na1.api.riotgames.com/lol/summoner/v4/summoners/by-name/'
+player_name+'?api_key=%s' % API_KEY).json()
time.sleep(1)
#save player id
player_id = response['puuid']
return player_id
"""## **Function to output dataframe with non-duplicate match_ids**"""
def get_new_matches(player_id):
temp_df = pd.DataFrame(columns = ["match_id"])
#make API call to retreive matchlist
match_url = 'https://americas.api.riotgames.com/lol/match/v5/matches/by-puuid/'+player_id+'/ids?start=0&count='+match_count+'&api_key=%s' % API_KEY
match_list = pd.DataFrame(requests.get(match_url).json()).set_axis(['match_id'], axis = 1)
#list of matches currently in RDS database
db_matches = pd.read_sql_query('select match_id from '+player_name+'_match_info order by date_played desc limit '+match_count+'', engine)
#check if database is empty or not
if db_matches.empty == 0:
#determine duplicate matches
db_matches_merge = pd.merge(match_list, db_matches, on = ['match_id'], how = 'left', indicator = 'Exist')
db_matches_merge = np.where(db_matches_merge.Exist == 'both', True, False)
else:
print('database table empty')
return match_list
#iterate through matches in data and appending only unique match_id's
y = 0
for match in range(len(db_matches_merge)):
if db_matches_merge[y] == 0:
temp_df = temp_df.append({'match_id':match_list['match_id'][y]}, ignore_index = True)
y += 1
return temp_df
"""## **Function that takes the player id and calls a second API to obtain a list of match ids and loops through the list of matches and obtain match details for the specified player**"""
def get_player_match_information(df):
#make first API call to retrieve player information
player_id = get_player_id(player_name)
#get matchlist
match_list = get_new_matches(player_id)
#make API call to retrieve match information while looping through recent matches
if match_list.empty == 0:
y = 0
for match in range(len(match_list)):
match_id = match_list['match_id'][y]
match_detail = requests.get('https://americas.api.riotgames.com/lol/match/v5/matches/'+match_id+'?api_key=%s' % API_KEY).json()
time.sleep(.7)
x = 0
for player in match_detail['info']['participants']:
#only record information for the specified player
if player_id == match_detail['info']['participants'][x]['puuid']:
#collecting match information for dataframe
date_played = match_detail['info']['gameCreation']
game_length = match_detail['info']['gameDuration']
game_mode = match_detail['info']['gameMode']
queue_id = match_detail['info']['queueId']
lane = match_detail['info']['participants'][x]['individualPosition']
total_dmg = match_detail['info']['participants'][x]['totalDamageDealtToChampions']
kills = match_detail['info']['participants'][x]['kills']
deaths = match_detail['info']['participants'][x]['deaths']
assists = match_detail['info']['participants'][x]['assists']
vision_score = match_detail['info']['participants'][x]['visionScore']
champion_played = match_detail['info']['participants'][x]['championName']
creep_score = match_detail['info']['participants'][x]['totalMinionsKilled']
outcome = match_detail['info']['participants'][x]['win']
#save data in df
df = df.append({'player_id': player_id, 'player_name': player_name,'match_id': match_id, 'date_played': date_played,
'game_length': game_length, 'game_mode': game_mode, 'queue_id': queue_id, 'lane': lane, 'champion_played': champion_played,
'total_dmg': total_dmg, 'kills': kills, 'deaths': deaths, 'assists': assists,
'vision_score': vision_score, 'creep_score': creep_score, 'win': outcome }, ignore_index = True)
x += 1
y += 1
return df
"""## **Initialize API and Amazon RDS information**"""
#API Key - permanent Riot API key approved for 'Match Data Analysis'
API_KEY = 'XXX'
#Specify player to retrieve and number of matches to retrieve(max 100 based on API)
player_name = 'MiniSoloCup'
match_count = '100'
#Amazon RDS information
dbname = 'XXX'
user = 'XXX'
password = 'XXX'
host = 'XXX'
port = 3306
#create sqlalchemy engine
db_data = 'mysql+pymysql://'+user+':'+password+'@'+host+':'+str(port)+'/'+dbname
engine = create_engine(db_data)
#connect to database
db = pymysql.connect(user = user, password = password,
host = host, database = dbname, port = port)
cursor = db.cursor()
#initialize our dataframe
df = pd.DataFrame(columns = ["player_id","player_name","match_id","date_played","game_length","game_mode", "queue_id","lane",
"champion_played","total_dmg","kills","deaths","assists","vision_score","creep_score","win"])
"""## **Create table, Populate and load dataframe using the 'get_player_match_information' function**"""
#main
#create table if it has not been created
create_table(cursor)
#call function to populate dataframe
df = get_player_match_information(df)
#load completed dataframe into RDS table 'player_match_info'
df.to_sql('%s_match_info' % player_name, engine, if_exists = 'append', index = False)