#!/usr/bin/python3 import urllib3 import sys import os import requests import urllib.request import sqlite3 import wget import json OPT_DOWNLOAD = True; OPT_DOWNLOAD_OVERWRITE = False; """ Initializes the database file and creates all the tables. :param name: The path to the sqlite database. :returns: Connection to the database. :raises IntegrityError: The queryes couldn't bre executed. :raises IOError: The sqlite file couldn't be created. """ def createDatabase(name): global OPT_CREATE_DB print('Configuring database...') try: db = sqlite3.connect(name) cursor = db.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS k_building( id INT, com2us_id INT, area TEXT, affected_stat TEXT, element TEXT, name TEXT, description TEXT, max_level INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_building_level( building INT, level INT, bonus INT, cost INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_effect( id INT, name TEXT, is_buff INT, description TEXT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_skill_level( skill INT, level INT, description TEXT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_skill( id INT, com2us_id TEXT, name TEXT, description TEXT, slot INT, cooltime INT, hits INT, passive INT, aoe INT, max_level INT, multiplier_formula TEXT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_skill_effect( skill INT, effect INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_leader_skill( id INT, attribute TEXT, amount INT, area TEXT, element TEXT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_source( id INT, name TEXT, description TEXT, farmable_source INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_monster( id INT, com2us_id TEXT, family_id TEXT, name TEXT, element TEXT, archetype TEXT, base_stars INT, natural_stars INT, obtainable INT, can_awaken INT, awaken_bonus TEXT, skill_ups_to_max INT, leader_skill INT, base_hp INT, base_attack INT, base_defense INT, speed INT, crit_rate INT, crit_damage INT, resistance INT, accuracy INT, raw_hp INT, raw_attack INT, raw_defense INT, max_lvl_hp INT, max_lvl_attack INT, max_lvl_defense INT, awakens_from INT, awakens_to INT, fusion_food INT, homunculus INT, craft_cost INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_monster_skill( monster INT, skill INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_monster_source( monster INT, source INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_monster_essence( monster INT, element TEXT, grade TEXT, amount TEXT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_fusion( id INT, product INT, stars INT, cost INT, ingredient INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS k_craft( id INT, name INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS monster( id TEXT, com2us_id TEXT, monster INT, stars INT, level INT, skill_1_level INT, skill_2_level INT, skill_3_level INT, skill_4_level INT, base_hp INT, base_attack INT, base_defense INT, base_speed INT, base_crit_rate INT, base_crit_damage INT, base_resistance INT, base_accuracy INT, rune_hp INT, rune_attack INT, rune_defense INT, rune_speed INT, rune_crit_rate INT, rune_crit_damage INT, rune_resistance INT, rune_accuracy INT, avg_rune_efficiency INT, fodder INT, in_storage INT, ignore_for_fusion INT, priority INT, notes INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS rune( id TEXT, com2us_id TEXT, assigned_to TEXT, type TEXT, slot INT, stars INT, level INT, quality INT, original_quality INT, value INT, substat_upgrades_remaining INT, efficiency INT, max_efficiency INT, main_stat TEXT, main_stat_value INT, innate_stat TEXT, innate_stat_value INT, substat_1 TEXT, substat_1_value INT, substat_1_craft INT, substat_2 TEXT, substat_2_value INT, substat_2_craft INT, substat_3 TEXT, substat_3_value INT, substat_3_craft INT, substat_4 TEXT, substat_4_value INT, substat_4_craft INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS monster_rune( monster TEXT, rune INT ); ''') cursor.execute(''' CREATE TABLE IF NOT EXISTS building( building INT, level INT ); ''') db.commit() cursor.close() return db except sqlite3.IntegrityError as e: print("Error executing statement: " + str(e)) raise except IOError as e: print("I/O Error creating database " + name + ": " + str(e)) raise """ Inserts a row into the database. :param db: Connection to the database. :param table: Name of the table to insert into. :param values: List of values to insert. :raises IntegrityError: The insert query was unsuccesfull. """ def insert(db, table, values): cursor = db.cursor() placeholders = '' for x in range(0, len(values)): placeholders = placeholders + '?, ' placeholders = placeholders[:len(placeholders) - 2] query = 'INSERT INTO ' + table + ' VALUES (' + placeholders + ')' try: cursor.execute(query, values) except sqlite3.IntegrityError as e: print('Error Inserting into ' + table + ' with values (' + str(values) + '): ' + str(e)) raise cursor.close; def download(url, target): if OPT_DOWNLOAD == False: return True if OPT_DOWNLOAD_OVERWRITE == False and os.path.isfile(target): return True try: urllib.request.urlretrieve(url, target) return True except: return False def parseBuildings(db, url): print("Parsing buildings...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for building in data["results"]: id = building["id"] com2us_id = building["com2us_id"] area = building["area"] affected_stat = building["affected_stat"] element = building["element"] name = building["name"] description = building["description"] max_level = building["max_level"] insert(db, "k_building", (id, com2us_id, area, affected_stat, element, name, description, max_level)) lv = 0 for bonus in building["stat_bonus"]: insert(db, "k_building_level", (id, lv, bonus, building["upgrade_cost"][lv])) lv = lv + 1 # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseSkills(db, url): print("Parsing skills...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for skill in data["results"]: id = skill["id"] com2us_id = skill["com2us_id"] name = skill["name"] description = skill["description"] slot = skill["slot"] cooltime = skill["cooltime"] hits = skill["hits"] passive = skill["passive"] aoe = skill["aoe"] max_level = skill["max_level"] multiplier_formula = skill["multiplier_formula"] insert(db, "k_skill", (id, com2us_id, name, description, slot, cooltime, hits, passive, aoe, max_level, multiplier_formula)) insert(db, "k_skill_level", (id, 1, "")) if (max_level > 1): lv = 2 for level in skill["level_progress_description"]: insert(db, "k_skill_level", (id, str(lv), level)) lv = lv + 1 for effect in skill["effects"]: insert(db, "k_skill_effect", (id, effect["effect"]["id"])) image = "https://swarfarm.com/static/herders/images/skills/" + skill["icon_filename"] download(image, 'public/img/content/skill/' + str(id).zfill(4) + '.png') # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseLeaderSkills(db, url): print("Parsing leader skills...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for skill in data["results"]: id = skill["id"] attribute = skill["attribute"] amount = skill["amount"] area = skill["area"] element = skill["element"] insert(db, "k_leader_skill", (id, attribute, amount, area, element)) # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseEffects(db, url): print("Parsing skill effects...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for effect in data["results"]: id = effect["id"] name = effect["name"] is_buff = effect["is_buff"] description = effect["description"] insert(db, "k_effect", (id, name, is_buff, description)) # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseSources(db, url): print("Parsing sources...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for source in data["results"]: id = source["id"] name = source["name"] description = source["description"] farmable_source = source["farmable_source"] insert(db, "k_source", (id, name, description, farmable_source)) # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseMonsters(db, url): print("Parsing monsters...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for mon in data["results"]: id = mon["id"] url = mon["url"] com2us_id = mon["com2us_id"] family_id = mon["family_id"] name = mon["name"] image_filename = mon["image_filename"] element = mon["element"] archetype = mon["archetype"] base_stars = mon["base_stars"] natural_stars = mon["natural_stars"] obtainable = mon["obtainable"] can_awaken = mon["can_awaken"] awaken_level = mon["awaken_level"] awaken_bonus = mon["awaken_bonus"] skill_ups_to_max = mon["skill_ups_to_max"] if mon["leader_skill"] != None: leader_skill = mon["leader_skill"]["id"] else: leader_skill = None homunculus_skills = mon["homunculus_skills"] base_hp = mon["base_hp"] base_attack = mon["base_attack"] base_defense = mon["base_defense"] speed = mon["speed"] crit_rate = mon["crit_rate"] crit_damage = mon["crit_damage"] resistance = mon["resistance"] accuracy = mon["accuracy"] raw_hp = mon["raw_hp"] raw_attack = mon["raw_attack"] raw_defense = mon["raw_defense"] max_lvl_hp = mon["max_lvl_hp"] max_lvl_attack = mon["max_lvl_attack"] max_lvl_defense = mon["max_lvl_defense"] awakens_from = mon["awakens_from"] awakens_to = mon["awakens_to"] awaken_mats_fire_low = mon["awaken_mats_fire_low"] awaken_mats_fire_mid = mon["awaken_mats_fire_mid"] awaken_mats_fire_high = mon["awaken_mats_fire_high"] awaken_mats_water_low = mon["awaken_mats_water_low"] awaken_mats_water_mid = mon["awaken_mats_water_mid"] awaken_mats_water_high = mon["awaken_mats_water_high"] awaken_mats_wind_low = mon["awaken_mats_wind_low"] awaken_mats_wind_mid = mon["awaken_mats_wind_mid"] awaken_mats_wind_high = mon["awaken_mats_wind_high"] awaken_mats_light_low = mon["awaken_mats_light_low"] awaken_mats_light_mid = mon["awaken_mats_light_mid"] awaken_mats_light_high = mon["awaken_mats_light_high"] awaken_mats_dark_low = mon["awaken_mats_dark_low"] awaken_mats_dark_mid = mon["awaken_mats_dark_mid"] awaken_mats_dark_high = mon["awaken_mats_dark_high"] awaken_mats_magic_low = mon["awaken_mats_magic_low"] awaken_mats_magic_mid = mon["awaken_mats_magic_mid"] awaken_mats_magic_high = mon["awaken_mats_magic_high"] fusion_food = mon["fusion_food"] homunculus = mon["homunculus"] craft_cost = mon["craft_cost"] insert(db, "k_monster", (id, com2us_id, family_id, name, element, archetype, base_stars, natural_stars, obtainable, can_awaken, awaken_bonus, skill_ups_to_max, leader_skill, base_hp, base_attack, base_defense, speed, crit_rate, crit_damage, resistance, accuracy, raw_hp, raw_attack, raw_defense, max_lvl_hp, max_lvl_attack, max_lvl_defense, awakens_from, awakens_to, fusion_food, homunculus, craft_cost)) if awaken_mats_fire_low > 0: insert(db, "k_monster_essence", (id, "Fire", "Low", awaken_mats_fire_low)) if awaken_mats_fire_mid > 0: insert(db, "k_monster_essence", (id, "Fire", "Mid", awaken_mats_fire_mid)) if awaken_mats_fire_high > 0: insert(db, "k_monster_essence", (id, "Fire", "High", awaken_mats_fire_high)) if awaken_mats_water_low > 0: insert(db, "k_monster_essence", (id, "Water", "Low", awaken_mats_water_low)) if awaken_mats_water_mid > 0: insert(db, "k_monster_essence", (id, "Water", "Mid", awaken_mats_water_mid)) if awaken_mats_water_high > 0: insert(db, "k_monster_essence", (id, "Water", "High", awaken_mats_water_high)) if awaken_mats_wind_low > 0: insert(db, "k_monster_essence", (id, "Wind", "Low", awaken_mats_wind_low)) if awaken_mats_wind_mid > 0: insert(db, "k_monster_essence", (id, "Wind", "Mid", awaken_mats_wind_mid)) if awaken_mats_wind_high > 0: insert(db, "k_monster_essence", (id, "Wind", "High", awaken_mats_wind_high)) if awaken_mats_dark_low > 0: insert(db, "k_monster_essence", (id, "Dark", "Low", awaken_mats_dark_low)) if awaken_mats_dark_mid > 0: insert(db, "k_monster_essence", (id, "Dark", "Mid", awaken_mats_dark_mid)) if awaken_mats_dark_high > 0: insert(db, "k_monster_essence", (id, "Dark", "High", awaken_mats_dark_high)) if awaken_mats_light_low > 0: insert(db, "k_monster_essence", (id, "Light", "Low", awaken_mats_light_low)) if awaken_mats_light_mid > 0: insert(db, "k_monster_essence", (id, "Light", "Mid", awaken_mats_light_mid)) if awaken_mats_light_high > 0: insert(db, "k_monster_essence", (id, "Light", "High", awaken_mats_light_high)) if awaken_mats_magic_low > 0: insert(db, "k_monster_essence", (id, "Magic", "Low", awaken_mats_magic_low)) if awaken_mats_magic_mid > 0: insert(db, "k_monster_essence", (id, "Magic", "Mid", awaken_mats_magic_mid)) if awaken_mats_magic_high > 0: insert(db, "k_monster_essence", (id, "Magic", "High", awaken_mats_magic_high)) for skill in mon["skills"]: insert(db, "k_monster_skill", (id, skill)) for source in mon["source"]: insert(db, "k_monster_source", (id, source["id"])) # TODO: craft_materials image = "https://swarfarm.com/static/herders/images/monsters/" + mon["image_filename"] #download(image, 'public/img/content/monster/' + str(id).zfill(4) + '.png') if id > 1215: download(image, 'public/img/content/monster/' + str(id).zfill(4) + '.png') # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseFusions(db, url): print("Parsing fusions...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: for fusion in data["results"]: id = fusion["id"] product = fusion["product"] stars = fusion["stars"] cost = fusion["cost"] for ingredient in fusion["ingredients"]: insert(db, "k_fusion", (id, product, stars, cost, ingredient)) # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break def parseCraft(db, url): print("Parsing crafting materials...") http = urllib3.PoolManager() mlist = str(http.request('GET', url).data.decode('utf-8')) data = json.loads(mlist) while True: first = False for crat in data["results"]: id = crat["id"] name = crat["name"] insert(db, "k_craft", (id, name)) # Next page db.commit() if data["next"] != None: mlist = str(http.request('GET', data["next"]).data.decode('utf-8')) data = json.loads(mlist) else: break """ Begin script """ db = createDatabase('application/sw.sqlite') parseBuildings(db, 'https://swarfarm.com/api/v2/buildings/') parseEffects(db, 'https://swarfarm.com/api/v2/skill-effects/') parseSkills(db, 'https://swarfarm.com/api/v2/skills/') parseLeaderSkills(db, 'https://swarfarm.com/api/v2/leader-skills/') parseSources(db, 'https://swarfarm.com/api/v2/monster-sources/') parseMonsters(db, 'https://swarfarm.com/api/v2/monsters/') parseFusions(db, 'https://swarfarm.com/api/v2/fusions/') parseCraft(db, 'https://swarfarm.com/api/v2/craft-materials/')