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 image.png

*** Check with MySQL *** image.png

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 image.png

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.

Recommended Posts

I want to get League of Legends data ③
I want to get League of Legends data ②
I want to get League of Legends data ①
I want to create a web application that uses League of Legends data ①
I want to get the operation information of yahoo route
Keras I want to get the output of any layer !!
I want to get custom data attributes of html as elements using Python Selenium
[Note] I want to completely preprocess the data of the Titanic issue-Age version-
Anyway, I want to check JSON data easily
I want to knock 100 data sciences with Colaboratory
I want to customize the appearance of zabbix
I want to get the path of the directory where the running file is stored.
I want to visualize the transfer status of the 2020 J League, what should I do?
I want to get / execute variables / functions / classes of external files from Python
Library for "I want to do that" of data science on Jupyter Notebook
I want to detect images of cats from Instagram
I want to give a group_id to a pandas data frame
I want to grep the execution result of strace
I want to fully understand the basics of Bokeh
I want to install a package of Php Redis
I tried to analyze J League data with Python
[Python] I tried to get Json of squid ring 2
I want to say that there is data preprocessing ~
I want to increase the security of ssh connections
I want to solve Sudoku (Sudoku)
I want to use a python data source in Re: Dash to get query results
I want to be able to analyze data with Python (Part 3)
I tried to get the location information of Odakyu Bus
I want to specify another version of Python with pyvenv
I want to be able to analyze data with Python (Part 1)
[Python] I want to get a common set between numpy
I want to start a lot of processes from python
I want to be able to analyze data with Python (Part 4)
I want to color black-and-white photos of memories with GAN
I want to be able to analyze data with Python (Part 2)
I want to judge the authenticity of the elements of numpy array
I want to know the features of Python and pip
I want to know the legend of the IT technology world
How to get an overview of your data in Pandas
I tried to get data from AS / 400 quickly using pypyodbc
I sent the data of Raspberry Pi to GCP (free)
I want to get rid of import warnings from Pyright and pylint in VS Code
I want to understand systemd roughly
I want to scrape images to learn
I want to do ○○ with Pandas
I want to copy yolo annotations
I want to debug with Python
I want to manually assign the training parameters of the [Pytorch] model
I tried to get a database of horse racing using Pandas
I tried to get the index of the list using the enumerate function
I want to read the html version of "OpenCV-Python Tutorials" OpenCV 3.1 version
I want to output the beginning of the next month with Python
Comparison of GCP computing services [I want to use it serverless]
I want to use both key and value of Python iterator
I tried to get a list of AMI Names using Boto3
I want to acquire and list Japanese stock data without scraping
I want to check the position of my face with OpenCV!
Python techniques for those who want to get rid of beginners
I want to know the population of each country in the world.
[Python] I tried to get various information using YouTube Data API!
I want to convert vertically held data (long type) to horizontally held data (wide type)