[PYTHON] I want to get League of Legends data ③

Connect to database and store data

Last time, I was able to extract the data I wanted, so this time I would like to save that data in the database. (Click here for the previous article https://qiita.com/mattya_527/items/9b90451e94de246525a4)

environment

As of 2/8/2020 OS:windows10 Anaconda:4.8.1 python:3.7.6 MySQL:8.0.19

Database settings

I referred to here for the initial settings such as installation. (https://www.dbonline.jp/mysql/)

Creating a database

mysql -u root -p Enter password in to start mysql. create database loldb; Create a database named LOLdb in. show databases; You can check the list of databases at. OK with LOLdb

Connect to database

First, install the required modules. python -m pip mysql

I will write the Python code immediately.

#MySQLdb import
import MySQLdb 

#Connect to database and generate cursor
connection = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="{Password set by mysql}",
    db="LOLdb",
    charset="utf8"
    )
cursor = connection.cursor()

You can now connect to the database you just created. Then create the table.

#Table initialization
cursor.execute("DROP TABLE IF EXISTS match_data")

#Creating a table
cursor.execute("""CREATE TABLE match_data(
               id INT(10) AUTO_INCREMENT NOT NULL,
               sn VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               wol VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               kills INT(10) NOT NULL,
               deaths INT(10) NOT NULL,
               assists INT(10) NOT NULL,
               championId  INT(10) NOT NULL,
               roles VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               cs INT(10) NOT NULL,
               gold INT(10) NOT NULL,
               damage INT(10) NOT NULL,
               side VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               game_time INT(10) NOT NULL,

               PRIMARY KEY(id)
               )""")

In this script, if a table called ** match_data ** exists, it is deleted and recreated. (Comment out this work when you're done and don't process it.) The items in the table should be the ones you picked up last time.

#Add data(test)
cursor.execute("INSERT INTO match_data(sn,wol,kills,deaths,assists,championId,roles,cs,gold,damage,side,game_time) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)


#Displaying a list of data
cursor.execute("SELECT * FROM match_data")
for row in cursor:
    print(row)


#Execute save
connection.commit()
#Close connection
connection.close()

Changed to extract from the list ** data ** when adding to the table. Be sure to remember ** commit () **.

Now you can create, add, and even view the table. I will write the code of the data to be added. To do this, I'll improve the code I wrote last time.

###Same part as last time###
from riotwatcher import RiotWatcher

API_KEY = "{Obtained API KEY}"
watcher = RiotWatcher(API_KEY)
region = "jp1" #Server selection: Japanese mackerel is jp1
name = "Macha-kun" #Enter the summoner name
summoner = watcher.summoner.by_name(region,name) #Acquisition of player data
recentmatchlists = watcher.match.matchlist_by_account(region,summoner["accountId"]) #Get a list of data for the last 20 games
matches = recentmatchlists["matches"]
match_data = watcher.match.by_id(region,matches[0]["gameId"]) #Extract only the last one match

#The summoner name is"Macha-kun"Extracts the player and returns its partnersId
for i in range(10):
    match_data["participantIdentities"][i]["player"]
    if match_data["participantIdentities"][i]["player"]["summonerName"] == name: #Does the summoner name match?
        par_Id = match_data["participants"][i]["participantId"]

###Part up to the last time###

###The improved part this time###
data=[] #Preparing list data to add to the table
data.append(name) #Add name
# par_Output the data that matches Id
if match_data["participants"][par_Id-1]["stats"]["participantId"] == par_Id: #par_Id-1 matches the index
    if match_data["participants"][par_Id-1]["stats"]["win"] == True:#If the victory or defeat is True, win,If False, lose
        wol = "win"
    else:wol = "lose"
    data.append(wol) #Add wol
    kills = match_data["participants"][par_Id-1]["stats"]["kills"] #Number of kills
    data.append(kills) #Add kills
    deaths = match_data["participants"][par_Id-1]["stats"]["deaths"] #Number of deaths
    data.append(deaths) #Add deaths
    assists = match_data["participants"][par_Id-1]["stats"]["assists"] #Number of assists
    data.append(assists) #Add assists
    championId = match_data["participants"][par_Id-1]["championId"] #Champion used
    data.append(championId) #Add championId(Later, convert the champion ID to the champion name.)
    lane = match_data["participants"][par_Id-1]["timeline"]["lane"] #lane
    role = match_data["participants"][par_Id-1]["timeline"]["role"] #roll
    if role == "DUO_SUPPORT": #support
        roles = "SUP"
    elif role == "DUO_CARRY": #carry
        roles = "ADC"
    elif role == "SOLO": #Solo lane
        if lane == "TOP": #Top
            roles = "TOP"
        elif lane == "MIDDLE": #Mid
            roles = "MID"
    elif role == "NONE":
        roles = "JG" #jungle
    data.append(roles) #Add roles(First, you can judge whether it is SUP, ADC, SOLO lane, or jungle by looking at the role. When it is SOLO, determine TOP or MID.)
    cs = match_data["participants"][par_Id-1]["stats"]["totalMinionsKilled"] #CS **Is there anything other than minions and jungle creep that is necessary for CS because it is not enough for CS seen in OPGG?
    data.append(cs) #Add cs
    gold = match_data["participants"][par_Id-1]["stats"]["goldEarned"] #Earn gold
    data.append(gold) #Add gold
    damage = match_data["participants"][par_Id-1]["stats"]["magicDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["physicalDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["trueDamageDealtToChampions"] #Damage to the champion
    data.append(damage) #Add damage
    if match_data["participants"][par_Id-1]["teamId"] == 100: #100 is the blue side, 200 is the red side
        side = "RED"
    else: side = "BLUE"
    data.append(side) #Add side
    game_time = match_data["gameDuration"] #Game time(Seconds)
    data.append(game_time) #game_Add time(Later convert the game time to minutes.)

It can be added to the end of a list element by using ** append () **. Write this code before the location where you create the database.

Execution result

*** Check with MySQL ***

You have now created a table and saved the data.

Experiment to see if multiple data can be entered

With this, one piece of data could be stored, but it cannot function as a database unless multiple pieces are inserted. So, let's comment out and delete the code for initializing and creating the table in the previous code.

Execution result

I tried changing the index of the match and running it, but I was able to store it properly.

Summary

I enjoyed using MySQL this time and found that it was fairly easy to play with it on the Python side. I wanted to study databases, but I couldn't step on it, so it was a good opportunity. From now on, I would like to make an OPGG-like WEB application.