SWDB/install_base.py

#!/usr/bin/python3

import urllib3
import sys
import os
import requests
import urllib.request
import sqlite3
import wget
import json

OPT_DOWNLOAD = False; # TODO
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 TEXT,
                com2us_id TEXT,
                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 TEXT,
                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 TEXT,
                level INT,
                description TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_skill(
                id TEXT,
                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 TEXT,
                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_unit(
                id TEXT,
                com2us_id TEXT,
                family 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,
                hp INT,
                attack INT,
                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_unit_skill(
                unit TEXT,
                skill TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_unit_source(
                unit TEXT,
                source INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_unit_essence(
                unit TEXT,
                element TEXT,
                grade TEXT,
                amount TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_fusion(
                id INT,
                product TEXT,
                stars INT,
                cost INT,
                ingredient TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_craft(
                id INT,
                name INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_experience(
                stars INT,
                level INT,
                exp INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS player(
                uid TEXT,
                name TEXT,
                mana INT,
                crystal INT,
                country TEXT,
                lang TEXT,
                level INT,
                experience INT,
                energy INT,
                energy_max INT,
                energy_per_min INT,
                arena_energy INT,
                arena_energy_max INT,
                rep TEXT,
                social_point INT,
                honor_point INT,
                guild_point INT,
                darkportal_energy INT,
                darkportal_energy_max INT,
                costume_point INT,
                costume_point_max INT,
                honor_medal INT,
                honor_mark INT,
                event_coin INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS scenario(
                uid TEXT,
                region INT,
                difficulty INT,
                cleared INT,
                max_cleared INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS defense(
                uid TEXT,
                unit TEXT,
                position INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS defense(
                uid TEXT,
                building INT,
                level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS unit(
                uid TEXT,
                id TEXT,
                building TEXT,
                unit TEXT,
                level INT,
                stars INT,
                hp INT,
                attack INT,
                defense INT,
                speed INT,
                crit_rate INT,
                crit_damage INT,
                resistance INT,
                accuracy INT,
                experience INT,
                exp_gained INT,
                exp_gain_rate INT,
                costume INT,
                attribute INT,
                source INT,
                create_time DATETIME,
                homunculus INT,
                homunculus_name TEXT,
                lock INT,
                notes TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS unit_skill(
                uid TEXT,
                unit TEXT,
                skill TEXT,
                level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rune(
                uid TEXT,
                id TEXT,
                assigned_to TEXT,
                type TEXT,
                slot INT,
                stars INT,
                level INT,
                quality INT,
                original_quality INT,
                value 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_enchant INT,
                substat_1_grind INT,
                substat_2 TEXT,
                substat_2_value INT,
                substat_2_enchant INT,
                substat_2_grind INT,
                substat_3 TEXT,
                substat_3_value INT,
                substat_3_enchant INT,
                substat_3_grind INT,
                substat_4 TEXT,
                substat_4_value INT,
                substat_4_enchant INT,
                substat_4_grind INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS building(
                uid TEXT,
                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;

"""
Dowwnloads a file.

If OPT_DOWNLOAD is set to false, it will do nothing (but it will still return
true).
If OPT_DOWNLOAD_OVERWRITE it will only download the file if the target doesn't
exist (but it will still return true if it does).

:param url: File url.
:param target: Full or relative path (whit filename to save the url).
:returns: True on success, false on error.
"""
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

"""
Parses building date (tables k_building, k_building_level).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, buildings.
"""
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

"""
Parses skill data (tables k_skill, k_skill_level, k_skill_effect).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, skills.
"""
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

"""
Parses leader skills (table k_leader_skill).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, leader-skills.
"""
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

"""
Parses skill effects (table k_effect).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, skill-efects.
"""
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

"""
Parses monster sources (table k_source).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, craft-materials.
"""
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

"""
Parses monster data (tables k_unit, k_unit_essence, k_unit_source,
k_monsster_skill).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, monsters.
"""
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"]
            hp = mon["base_hp"]
            attack = mon["base_attack"]
            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_unit", (id, com2us_id, family_id, name, element, archetype, base_stars, natural_stars, obtainable, can_awaken, awaken_bonus, skill_ups_to_max, leader_skill, hp, attack, 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_unit_essence", (id, "Fire", "Low", awaken_mats_fire_low))
            if awaken_mats_fire_mid > 0:
                insert(db, "k_unit_essence", (id, "Fire", "Mid", awaken_mats_fire_mid))
            if awaken_mats_fire_high > 0:
                insert(db, "k_unit_essence", (id, "Fire", "High", awaken_mats_fire_high))
            if awaken_mats_water_low > 0:
                insert(db, "k_unit_essence", (id, "Water", "Low", awaken_mats_water_low))
            if awaken_mats_water_mid > 0:
                insert(db, "k_unit_essence", (id, "Water", "Mid", awaken_mats_water_mid))
            if awaken_mats_water_high > 0:
                insert(db, "k_unit_essence", (id, "Water", "High", awaken_mats_water_high))
            if awaken_mats_wind_low > 0:
                insert(db, "k_unit_essence", (id, "Wind", "Low", awaken_mats_wind_low))
            if awaken_mats_wind_mid > 0:
                insert(db, "k_unit_essence", (id, "Wind", "Mid", awaken_mats_wind_mid))
            if awaken_mats_wind_high > 0:
                insert(db, "k_unit_essence", (id, "Wind", "High", awaken_mats_wind_high))
            if awaken_mats_dark_low > 0:
                insert(db, "k_unit_essence", (id, "Dark", "Low", awaken_mats_dark_low))
            if awaken_mats_dark_mid > 0:
                insert(db, "k_unit_essence", (id, "Dark", "Mid", awaken_mats_dark_mid))
            if awaken_mats_dark_high > 0:
                insert(db, "k_unit_essence", (id, "Dark", "High", awaken_mats_dark_high))
            if awaken_mats_light_low > 0:
                insert(db, "k_unit_essence", (id, "Light", "Low", awaken_mats_light_low))
            if awaken_mats_light_mid > 0:
                insert(db, "k_unit_essence", (id, "Light", "Mid", awaken_mats_light_mid))
            if awaken_mats_light_high > 0:
                insert(db, "k_unit_essence", (id, "Light", "High", awaken_mats_light_high))
            if awaken_mats_magic_low > 0:
                insert(db, "k_unit_essence", (id, "Magic", "Low", awaken_mats_magic_low))
            if awaken_mats_magic_mid > 0:
                insert(db, "k_unit_essence", (id, "Magic", "Mid", awaken_mats_magic_mid))
            if awaken_mats_magic_high > 0:
                insert(db, "k_unit_essence", (id, "Magic", "High", awaken_mats_magic_high))
            for skill in mon["skills"]:
                insert(db, "k_unit_skill", (id, skill))
            for source in mon["source"]:
                insert(db, "k_unit_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')

        # 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

"""
Parses fusion data (table k_fusion).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, fusions.
"""
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

"""
Parses crafting materials (table k_craft).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, craft-materials.
"""
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

"""
Removes references to imperfect monsters.

Imperfect monsters are only available in Japanese servers due to legal reasons,
they are not needed for this app. They wont be deleted, but awakening chains
wil be cut.

:param db: Sqlite database connection.
"""
def removeImperfect(db):
    print("Unlinking inperfetc units...")
    cursor = db.cursor()
    cursor.execute('''
        UPDATE k_unit
        SET awakens_from = null
        WHERE awakens_from IN (SELECT id FROM k_unit WHERE name LIKE 'Imperfect %')
    ''');
    db.commit()
    cursor.close()

"""
Changes IDS from swarfarm formats to swex formats.

:param db: Sqlite database connection.
"""
def useSwexIds(db):
    print("Updating IDs...")
    cursor = db.cursor()
    cursor.execute('UPDATE k_building_level SET building = (SELECT com2us_id FROM k_building WHERE building = id)');
    #db.commit()
    #cursor.execute('ALTER TABLE k_building_level DROP COLUMN com2us_id')
    cursor.execute('UPDATE k_building SET id = com2us_id');
    #db.commit()
    #cursor.execute('ALTER TABLE k_building_level DROP COLUMN com2us_id')
    cursor.execute('UPDATE k_skill_level SET skill = (SELECT com2us_id FROM k_skill WHERE skill = id)');
    cursor.execute('UPDATE k_skill_effect SET skill = (SELECT com2us_id FROM k_skill WHERE skill = id)');
    cursor.execute('UPDATE k_unit_skill SET skill = (SELECT com2us_id FROM k_skill WHERE skill = id)');
    cursor.execute('UPDATE k_skill SET id = com2us_id');
    #db.commit()
    #cursor.execute('ALTER TABLE k_skill DROP COLUMN com2us_id')
    cursor.execute('UPDATE k_unit_skill SET unit = (SELECT com2us_id FROM k_unit WHERE unit = k_unit.id)');
    cursor.execute('UPDATE k_unit_source SET unit = (SELECT com2us_id FROM k_unit WHERE unit = k_unit.id)');
    cursor.execute('UPDATE k_unit_essence SET unit = (SELECT com2us_id FROM k_unit WHERE unit = k_unit.id)');
    cursor.execute('UPDATE k_fusion SET product = (SELECT com2us_id FROM k_unit WHERE product = k_unit.id)');
    cursor.execute('UPDATE k_fusion SET ingredient = (SELECT com2us_id FROM k_unit WHERE ingredient = k_unit.id)');
    cursor.execute('UPDATE k_unit SET awakens_from = (SELECT com2us_id FROM k_unit un WHERE k_unit.awakens_from = un.id)');
    cursor.execute('UPDATE k_unit SET awakens_to = (SELECT com2us_id FROM k_unit un WHERE k_unit.awakens_to = un.id)');
    cursor.execute('UPDATE k_unit SET id = com2us_id');
    db.commit()
    #cursor.execute('ALTER TABLE k_unit DROP COLUMN com2us_id')
    cursor.close()

def staticData(db):
    print("Procesing data...")
    cursor = db.cursor()
    cursor.execute('INSERT INTO k_experience VALUES (1, 1, 460)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 1, 552)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 1, 662)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 1, 796)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 1, 952)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 1, 1150)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 2, 516)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 2, 619)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 2, 743)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 2, 892)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 2, 1068)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 2, 1290)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 3, 579)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 3, 695)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 3, 834)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 3, 1002)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 3, 1199)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 3, 1447)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 4, 650)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 4, 779)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 4, 936)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 4, 1124)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 4, 1344)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 4, 1624)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 5, 728)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 5, 875)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 5, 1049)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 5, 1261)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 5, 1509)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 5, 1823)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 6, 818)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 6, 981)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 6, 1178)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 6, 1415)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 6, 1693)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 6, 2044)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 7, 918)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 7, 1102)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 7, 1321)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 7, 1587)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 7, 1899)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 7, 2294)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 8, 1029)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 8, 1235)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 8, 1483)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 8, 1781)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 8, 2131)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 8, 2574)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 9, 1155)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 9, 1386)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 9, 1663)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 9, 1998)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 9, 2392)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 9, 2888)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 10, 1296)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 10, 1555)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 10, 1866)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 10, 2243)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 10, 2682)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 10, 3240)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 11, 1455)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 11, 1745)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 11, 2094)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 11, 2515)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 11, 3010)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 11, 3635)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 12, 1631)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 12, 1958)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 12, 2350)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 12, 2823)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 12, 3378)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 12, 4079)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 13, 1831)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 13, 2197)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 13, 2636)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 13, 3167)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 13, 3789)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 13, 4576)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 14, 2054)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 14, 2465)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 14, 2957)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 14, 3553)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 14, 4252)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 14, 5135)')
    cursor.execute('INSERT INTO k_experience VALUES (1, 15, 0)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 15, 2765)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 15, 3319)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 15, 3987)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 15, 4770)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 15, 5762)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 16, 3103)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 16, 3723)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 16, 4473)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 16, 5352)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 16, 6464)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 17, 3481)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 17, 4178)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 17, 5019)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 17, 6006)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 17, 7252)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 18, 3906)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 18, 4687)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 18, 5631)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 18, 6738)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 18, 8138)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 19, 4423)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 19, 5307)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 19, 6376)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 19, 7628)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 19, 9214)')
    cursor.execute('INSERT INTO k_experience VALUES (2, 20, 0)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 20, 6009)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 20, 7219)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 20, 8638)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 20, 10431)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 21, 6802)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 21, 8172)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 21, 9779)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 21, 11811)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 22, 7703)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 22, 9254)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 22, 11072)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 22, 13371)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 23, 8720)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 23, 10476)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 23, 12535)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 23, 15140)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 24, 9962)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 24, 11969)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 24, 14321)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 24, 17296)')
    cursor.execute('INSERT INTO k_experience VALUES (3, 25, 0)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 25, 13673)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 25, 16360)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 25, 19758)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 26, 15619)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 26, 18690)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 26, 22572)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 27, 17844)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 27, 21350)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 27, 25786)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 28, 20386)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 28, 24392)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 28, 29458)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 29, 23495)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 29, 28113)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 29, 33954)')
    cursor.execute('INSERT INTO k_experience VALUES (4, 30, 0)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 30, 32404)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 30, 39134)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 31, 37348)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 31, 45107)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 32, 43048)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 32, 51990)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 33, 49617)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 33, 59924)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 34, 57188)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 34, 69068)')
    cursor.execute('INSERT INTO k_experience VALUES (5, 35, 0)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 35, 76085)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 36, 83816)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 37, 92332)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 38, 101712)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 39, 112046)')
    cursor.execute('INSERT INTO k_experience VALUES (6, 40, 0)')
    db.commit()
    #cursor.execute('ALTER TABLE k_unit DROP COLUMN com2us_id')
    cursor.close()

"""
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/')
removeImperfect(db)
useSwexIds(db)
staticData(db)