From b5e9beaa31e9d2a7f54909a1467eda7171ed3229 Mon Sep 17 00:00:00 2001 From: raylu Date: Sun, 20 Oct 2013 03:34:03 -0700 Subject: fix collation for search and importer api-only also, importer whitespace --- db/importer.py | 42 ------------------------- db/schema.sql | 10 +++--- importer.py | 98 +++++++++++++++++++++++++++++----------------------------- 3 files changed, 54 insertions(+), 96 deletions(-) delete mode 100755 db/importer.py diff --git a/db/importer.py b/db/importer.py deleted file mode 100755 index 4f8f4b4..0000000 --- a/db/importer.py +++ /dev/null @@ -1,42 +0,0 @@ -#!/usr/bin/env python3 - -import json -from pprint import pprint - -import __init__ as db - -with db.cursor() as c: - db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)', - kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID']) - - 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 - ) diff --git a/db/schema.sql b/db/schema.sql index 7c601ce..3c632a9 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -10,7 +10,7 @@ CREATE TABLE `kills` ( `kill_time` datetime NOT NULL, `moon_id` int unsigned NOT NULL, PRIMARY KEY (`kill_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `characters` ( `id` int unsigned NOT NULL AUTO_INCREMENT, @@ -31,7 +31,7 @@ CREATE TABLE `characters` ( `weapon_type_id` int DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_char_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `items` ( `id` int unsigned NOT NULL AUTO_INCREMENT, @@ -43,17 +43,17 @@ CREATE TABLE `items` ( `singleton` tinyint(4) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_item_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `item_costs` ( `type_id` int NOT NULL UNIQUE, `cost` bigint unsigned NOT NULL, PRIMARY KEY (`type_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `kill_costs` ( `kill_id` int unsigned NOT NULL UNIQUE, `cost` bigint unsigned NOT NULL, PRIMARY KEY (`kill_id`), CONSTRAINT `fk_kill_cost_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; diff --git a/importer.py b/importer.py index 44c95dc..a6d6232 100755 --- a/importer.py +++ b/importer.py @@ -8,59 +8,59 @@ from pprint import pprint import db def insert_kill(c, kill): - db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)', - kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID']) + db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)', + kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID']) - 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 - ) + 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 - ) + 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 + ) - db.conn.commit() + db.conn.commit() def main(): - conn = HTTPSConnection('zkillboard.com', timeout=10) - with db.ykill_cursor() as c: - groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6) - groups = list(map(operator.itemgetter('groupID'), groups)) - for i in range(0, len(groups), 10): - query_groups = map(str, groups[i:i+10]) - conn.request('GET', '/api/groupID/{}/api-only'.format(','.join(query_groups))) - response = conn.getresponse() - if response.status != 200: - raise Exception('got {} {} from zkb'.format(response.status, response.reason)) - kills = json.loads(response.read().decode('utf-8')) - response.close() - print('inserting', len(kills), 'kills') - for kill in kills: - insert_kill(c, kill) + conn = HTTPSConnection('zkillboard.com', timeout=10) + with db.cursor() as c: + groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6) + groups = list(map(operator.itemgetter('groupID'), groups)) + for i in range(0, len(groups), 10): + query_groups = map(str, groups[i:i+10]) + conn.request('GET', '/api/losses/api-only/groupID/{}'.format(','.join(query_groups))) + response = conn.getresponse() + if response.status != 200: + raise Exception('got {} {} from zkb'.format(response.status, response.reason)) + kills = json.loads(response.read().decode('utf-8')) + response.close() + print('inserting', len(kills), 'kills') + for kill in kills: + insert_kill(c, kill) if __name__ == '__main__': - main() + main() -- cgit v1.2.3