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
| import pymysql
import requests
import requests
import time
from lxml import etree
conn = pymysql.connect(host="localhost", port=3306, user="root", password="qaz781212", database="lol")
furl = "https://open.tjstats.com/match-auth-app/open/v1/compound/matchDetail?matchId=%d"
headers = {
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36",
'Authorization': '7935be4c41d8760a28c05581a7b1f570'
}
match_id = 0
team_id = 0
summoner_id = 0
rune_list_id = 0
item_list_id = 0
with conn.cursor() as cursor:
for i in range(11038, 11062):
resp = requests.get(furl % i,headers=headers)
resp.encoding = resp.apparent_encoding
matchInfos = resp.json()["data"]["matchInfos"]
for matchInfo in matchInfos:
match_id += 1
start_time = matchInfo["matchStartTime"]
duration = matchInfo["gameTime"]
is_rank = False
win_team_id = matchInfo["matchWin"]
sql = "insert into `match` (id, is_rank, start_time, duration) values (%s, %s, %s, %s)"
cursor.execute(sql, (match_id, is_rank, start_time, duration))
conn.commit()
teamInfos = matchInfo["teamInfos"]
for teamInfo in teamInfos:
team_id += 1
is_win = teamInfo["teamId"] == win_team_id
economy = teamInfo["golds"]
dragons = teamInfo["dragonAmount"]
kills = teamInfo["kills"]
sql = "insert into team (id, match_id, is_win, economy, dragons, kills) values (%s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (team_id, match_id, is_win, economy, dragons, kills))
conn.commit()
playerInfos = teamInfo["playerInfos"]
for playerInfo in playerInfos:
summoner_id += 1
battleDetail = playerInfo["battleDetail"]
assist = battleDetail["assist"]
death = battleDetail["death"]
kill = battleDetail["kills"]
otherDetail = playerInfo["otherDetail"]
economy = otherDetail["golds"]
last_hit = playerInfo["minionKilled"]
position = playerInfo["playerLocation"]
hero_name = playerInfo["heroTitle"]
query = "select id from hero where name = %s"
cursor.execute(query, hero_name)
hero_id = cursor.fetchone()[0]
player_name = playerInfo["playerName"]
query = "select id from player where name = %s"
cursor.execute(query, player_name)
if cursor.fetchone() == None:
sql = "insert into player(name) values(%s)"
cursor.execute(sql, player_name)
conn.commit()
query = "select id from player where name = %s"
cursor.execute(query,player_name)
player_id = cursor.fetchone()[0]
sql = "insert into summoner (id, assist, death, economy, last_hit, `kill`, position, hero_id, player_id, team_id) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql,(str(summoner_id), assist, death, economy, last_hit, kill, position, hero_id, player_id, team_id))
conn.commit()
items = playerInfo["items"]
item_names = [item["itemName"] for item in items]
item_ids = []
for item_name in item_names:
query = "select id from item where name = %s"
cursor.execute(query, item_name)
item_id = cursor.fetchone()
if item_id == None:
item_id = 1
else:
item_id = item_id[0]
item_ids.append(item_id)
item_ids = [str(id) for id in item_ids]
for item_id in item_ids:
item_list_id += 1
sql = "insert into item_list (id, item_id, summoner_id) values (%s, %s, %s)"
cursor.execute(sql,(str(item_list_id), str(item_id), str(summoner_id)))
conn.commit()
runes = playerInfo["perkRunes"]
rune_names = [rune["runeName"] for rune in runes]
rune_ids = []
for rune_name in rune_names:
if rune_name == "吸收生命力":
rune_name = "过量治疗"
if rune_name == "三重补药":
rune_name = "完美时机"
query = "select id from rune where name = %s"
cursor.execute(query, rune_name)
rune_id = cursor.fetchone()
if rune_id == None:
rune_id = 1
else:
rune_id = rune_id[0]
rune_ids.append(rune_id)
rune_ids = [str(id) for id in rune_ids]
for rune_id in rune_ids:
rune_list_id += 1
sql = "insert into rune_list (id, rune_id, summoner_id) values (%s, %s, %s)"
cursor.execute(sql,(str(rune_list_id), str(rune_id), str(summoner_id)))
conn.commit()
conn.close()
|
