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
|
#!/usr/bin/env python3
import json
import operator
import sys
import time
import oursql
import requests
import db
def insert_kill(c, kill):
try:
db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)',
kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID'])
except oursql.IntegrityError as e:
if e.args[0] == oursql.errnos['ER_DUP_ENTRY']:
return False
raise
victim = kill['victim']
parambatch = [(
kill['killID'], 1, victim['characterID'], victim['characterName'], victim['shipTypeID'],
victim['allianceID'], victim['allianceName'], victim['corporationID'], victim['corporationName'], victim['factionID'], victim['factionName'],
victim['damageTaken'], None, None, None,
)]
for attacker in kill['attackers']:
parambatch.append((
kill['killID'], 0, attacker['characterID'], attacker['characterName'], attacker['shipTypeID'],
attacker['allianceID'], attacker['allianceName'], attacker['corporationID'], attacker['corporationName'], attacker['factionID'], attacker['factionName'],
attacker['damageDone'], attacker['finalBlow'], attacker['securityStatus'], attacker['weaponTypeID'],
))
c.executemany('''
INSERT INTO characters (
kill_id, victim, character_id, character_name, ship_type_id,
alliance_id, alliance_name, corporation_id, corporation_name, faction_id, faction_name,
damage, final_blow, security_status, weapon_type_id
) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', parambatch
)
parambatch = []
for item in kill['items']:
parambatch.append((kill['killID'], item['typeID'], item['flag'],
item['qtyDropped'], item['qtyDestroyed'], item['singleton']))
c.executemany('''
INSERT INTO items (
kill_id, type_id, flag, dropped, destroyed, singleton
) VALUES(?, ?, ?, ?, ?, ?)
''', parambatch
)
try:
result = db.get(c, 'SELECT cost FROM item_costs WHERE type_id = ?', (victim['shipTypeID']))
cost = result['cost']
except db.NoRowsException:
cost = 0
result = db.get(c, '''
SELECT SUM(cost * (dropped + destroyed)) AS item_cost
FROM items
JOIN item_costs ON items.type_id = item_costs.type_id
WHERE kill_id = ?
''', kill['killID'])
if result['item_cost'] is not None:
cost += result['item_cost']
db.execute(c, 'INSERT INTO kill_costs (kill_id, cost) VALUES(?, ?)', kill['killID'], cost)
return True
def main():
rs = requests.session()
with db.cursor() as c:
if len(sys.argv) == 2:
kill_id = sys.argv[1]
response = rs.get('http://api.whelp.gg/kill/' + kill_id)
character_id = response.json()['victim']['character_id']
url = 'https://zkillboard.com/api/losses/characterID/{}/beforeKillID/{}/limit/1'
response = rs.get(url.format(character_id, int(kill_id) + 1))
data = response.json()
if len(data) != 1:
raise Exception('got {} kills'.format(len(data)))
if insert_kill(c, data[0]):
print('inserted!')
else:
print('duplicate')
return
groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6)
groups = list(map(operator.itemgetter('groupID'), groups))
last_kill_ids = {}
for i in range(0, len(groups), 10):
query_groups = ','.join(map(str, groups[i:i+10]))
last_kill_ids[query_groups] = None
last_request_time = 0
while True:
for query_group in last_kill_ids:
path = '/api/losses/api-only/groupID/' + query_group
last_kill_id = last_kill_ids[query_group]
if last_kill_id is not None:
path += '/beforeKillID/' + str(last_kill_id)
now = time.time()
if now - last_request_time < 12:
sleep_secs = 12 - (now - last_request_time)
print('sleeping', sleep_secs)
time.sleep(sleep_secs)
last_request_time = time.time()
try:
r = rs.get('https://zkillboard.com' + path)
kills = r.json()
except Exception as e:
print(repr(e))
break
print('inserting', len(kills), 'kills', end='... ')
sys.stdout.flush()
inserted = 0
try:
for kill in kills:
if insert_kill(c, kill):
inserted += 1
except TypeError as e:
print(repr(e), kills)
break
db.conn.commit()
print(len(kills) - inserted, 'dupes')
last_kill_id = kills[-1]['killID']
last_kill_ids[query_group] = last_kill_id
if __name__ == '__main__':
main()
|
