SWDB/install_user.py

#!/usr/bin/python3

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

USER = "ivv0"

"""
Opens the database file and deletes from the user 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 openDatabase(name):
    global OPT_CREATE_DB
    print('Configuring database...')
    try:
        db = sqlite3.connect(name)
        cursor = db.cursor()
        cursor.execute('''DELETE FROM monster''')
        cursor.execute('''DELETE FROM monster_rune''')
        cursor.execute('''DELETE FROM rune''')
        #cursor.execute('''DELETE FROM player''')
        cursor.execute('''DELETE FROM building''')
        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;

"""
Parses building data (table building).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, building-instances.
"""
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"]:
            building_id = building["building"]
            level = building["level"]
            insert(db, "building", (building_id, level))

        # 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 rune data (table rune).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, rune-instances.
"""
def parseRunes(db, url):
    print("Parsing runes...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for rune in data["results"]:
            id = rune["id"]
            com2us_id = rune["com2us_id"]
            assigned_to = rune["assigned_to"]
            rtype = rune["type"]
            if rtype == 1:
                rtype = 'Energy'
            elif rtype == 2:
                rtype = 'Fatal'
            elif rtype == 3:
                rtype = 'Blade'
            elif rtype == 4:
                rtype = 'Rage'
            elif rtype == 5:
                rtype = 'Swift'
            elif rtype == 6:
                rtype = 'Focus'
            elif rtype == 7:
                rtype = 'Guard'
            elif rtype == 8:
                rtype = 'Endure'
            elif rtype == 9:
                rtype = 'Violent'
            elif rtype == 10:
                rtype = 'Will'
            elif rtype == 11:
                rtype = 'Nemesis'
            elif rtype == 12:
                rtype = 'Shield'
            elif rtype == 13:
                rtype = 'Revenge'
            elif rtype == 14:
                rtype = 'Despair'
            elif rtype == 15:
                rtype = 'Vampire'
            elif rtype == 16:
                rtype = 'Destroy'
            elif rtype == 17:
                rtype = 'Fight'
            elif rtype == 18:
                rtype = 'Determination'
            elif rtype == 19:
                rtype = 'Enhance'
            elif rtype == 20:
                rtype = 'Accuracy'
            elif rtype == 21:
                rtype = 'Tolerance'
            slot = rune["slot"]
            stars = rune["stars"]
            level = rune["level"]
            quality = rune["quality"]
            original_quality = rune["original_quality"]
            value = rune["value"]
            substat_upgrades_remaining = rune["substat_upgrades_remaining"]
            efficiency = rune["efficiency"]
            max_efficiency = rune["max_efficiency"]
            main_stat = rune["main_stat"]
            main_stat_value = rune["main_stat_value"]
            innate_stat = rune["innate_stat"]
            innate_stat_value = rune["innate_stat_value"]
            substat_1 = rune["substat_1"]
            substat_1_value = rune["substat_1_value"]
            substat_1_craft = rune["substat_1_craft"]
            substat_2 = rune["substat_2"]
            substat_2_value = rune["substat_2_value"]
            substat_2_craft = rune["substat_2_craft"]
            substat_3 = rune["substat_3"]
            substat_3_value = rune["substat_3_value"]
            substat_3_craft = rune["substat_3_craft"]
            substat_4 = rune["substat_4"]
            substat_4_value = rune["substat_4_value"]
            substat_4_craft = rune["substat_4_craft"]
            """
            Human readable stat names (as of 2019-08-16):
                STAT_HP = 1
                STAT_HP_PCT = 2
                STAT_ATK = 3
                STAT_ATK_PCT = 4
                STAT_DEF = 5
                STAT_DEF_PCT = 6
                STAT_SPD = 7
                STAT_CRIT_RATE_PCT = 8
                STAT_CRIT_DMG_PCT = 9
                STAT_RESIST_PCT = 10
                STAT_ACCURACY_PCT = 11
            """
            if (main_stat == 1):
                main_stat = "HP"
            elif (main_stat == 2):
                main_stat = "HP%"
            elif (main_stat == 3):
                main_stat = "ATK"
            elif (main_stat == 4):
                main_stat = "ATK%"
            elif (main_stat == 5):
                main_stat = "DEF"
            elif (main_stat == 6):
                main_stat = "DEF%"
            elif (main_stat == 7):
                main_stat = "SPD"
            elif (main_stat == 8):
                main_stat = "CRR"
            elif (main_stat == 9):
                main_stat = "CRD"
            elif (main_stat == 10):
                main_stat = "RES"
            elif (main_stat == 11):
                main_stat = "ACC"
            if (innate_stat == 1):
                innate_stat = "HP"
            elif (innate_stat == 2):
                innate_stat = "HP%"
            elif (innate_stat == 3):
                innate_stat = "ATK"
            elif (innate_stat == 4):
                innate_stat = "ATK%"
            elif (innate_stat == 5):
                innate_stat = "DEF"
            elif (innate_stat == 6):
                innate_stat = "DEF%"
            elif (innate_stat == 7):
                innate_stat = "SPD"
            elif (innate_stat == 8):
                innate_stat = "CRR"
            elif (innate_stat == 9):
                innate_stat = "CRD"
            elif (innate_stat == 10):
                innate_stat = "RES"
            elif (innate_stat == 11):
                innate_stat = "ACC"
            if (substat_1 == 1):
                substat_1 = "HP"
            elif (substat_1 == 2):
                substat_1 = "HP%"
            elif (substat_1 == 3):
                substat_1 = "ATK"
            elif (substat_1 == 4):
                substat_1 = "ATK%"
            elif (substat_1 == 5):
                substat_1 = "DEF"
            elif (substat_1 == 6):
                substat_1 = "DEF%"
            elif (substat_1 == 7):
                substat_1 = "SPD"
            elif (substat_1 == 8):
                substat_1 = "CRR"
            elif (substat_1 == 9):
                substat_1 = "CRD"
            elif (substat_1 == 10):
                substat_1 = "RES"
            elif (substat_1 == 11):
                substat_1 = "ACC"
            if (substat_2 == 1):
                substat_2 = "HP"
            elif (substat_2 == 2):
                substat_2 = "HP%"
            elif (substat_2 == 3):
                substat_2 = "ATK"
            elif (substat_2 == 4):
                substat_2 = "ATK%"
            elif (substat_2 == 5):
                substat_2 = "DEF"
            elif (substat_2 == 6):
                substat_2 = "DEF%"
            elif (substat_2 == 7):
                substat_2 = "SPD"
            elif (substat_2 == 8):
                substat_2 = "CRR"
            elif (substat_2 == 9):
                substat_2 = "CRD"
            elif (substat_2 == 10):
                substat_2 = "RES"
            elif (substat_2 == 11):
                substat_2 = "ACC"
            if (substat_3 == 1):
                substat_3 = "HP"
            elif (substat_3 == 2):
                substat_3 = "HP%"
            elif (substat_3 == 3):
                substat_3 = "ATK"
            elif (substat_3 == 4):
                substat_3 = "ATK%"
            elif (substat_3 == 5):
                substat_3 = "DEF"
            elif (substat_3 == 6):
                substat_3 = "DEF%"
            elif (substat_3 == 7):
                substat_3 = "SPD"
            elif (substat_3 == 8):
                substat_3 = "CRR"
            elif (substat_3 == 9):
                substat_3 = "CRD"
            elif (substat_3 == 10):
                substat_3 = "RES"
            elif (substat_3 == 11):
                substat_3 = "ACC"
            if (substat_4 == 1):
                substat_4 = "HP"
            elif (substat_4 == 2):
                substat_4 = "HP%"
            elif (substat_4 == 3):
                substat_4 = "ATK"
            elif (substat_4 == 4):
                substat_4 = "ATK%"
            elif (substat_4 == 5):
                substat_4 = "DEF"
            elif (substat_4 == 6):
                substat_4 = "DEF%"
            elif (substat_4 == 7):
                substat_4 = "SPD"
            elif (substat_4 == 8):
                substat_4 = "CRR"
            elif (substat_4 == 9):
                substat_4 = "CRD"
            elif (substat_4 == 10):
                substat_4 = "RES"
            elif (substat_4 == 11):
                substat_4 = "ACC"
            insert(db, "rune", (id, com2us_id, assigned_to, rtype, slot, stars, level, quality, original_quality, value, substat_upgrades_remaining, efficiency, max_efficiency, main_stat, main_stat_value, innate_stat, innate_stat_value, substat_1, substat_1_value, substat_1_craft, substat_2, substat_2_value, substat_2_craft, substat_3, substat_3_value, substat_3_craft, substat_4, substat_4_value, substat_4_craft))

        # 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 monster, monster_rune).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, monster-instances.
"""
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"]
            com2us_id = mon["com2us_id"]
            monster = mon["monster"]
            stars = mon["stars"]
            level = mon["level"]
            skill_1_level = mon["skill_1_level"]
            skill_2_level = mon["skill_2_level"]
            skill_3_level = mon["skill_3_level"]
            skill_4_level = mon["skill_4_level"]
            base_hp = mon["base_hp"]
            base_attack = mon["base_attack"]
            base_defense = mon["base_defense"]
            base_speed = mon["base_speed"]
            base_crit_rate = mon["base_crit_rate"]
            base_crit_damage = mon["base_crit_damage"]
            base_resistance = mon["base_resistance"]
            base_accuracy = mon["base_accuracy"]
            rune_hp = mon["rune_hp"]
            rune_attack = mon["rune_attack"]
            rune_defense = mon["rune_defense"]
            rune_speed = mon["rune_speed"]
            rune_crit_rate = mon["rune_crit_rate"]
            rune_crit_damage = mon["rune_crit_damage"]
            rune_resistance = mon["rune_resistance"]
            rune_accuracy = mon["rune_accuracy"]
            avg_rune_efficiency = mon["avg_rune_efficiency"]
            fodder = mon["fodder"]
            in_storage = mon["in_storage"]
            ignore_for_fusion = mon["ignore_for_fusion"]
            priority = mon["priority"]
            notes = mon["notes"]
            insert(db, "monster", (id, com2us_id, monster, stars, level, skill_1_level, skill_2_level, skill_3_level, skill_4_level, base_hp, base_attack, base_defense, base_speed, base_crit_rate, base_crit_damage, base_resistance, base_accuracy, rune_hp, rune_attack, rune_defense, rune_speed, rune_crit_rate, rune_crit_damage, rune_resistance, rune_accuracy, avg_rune_efficiency, fodder, in_storage, ignore_for_fusion, priority, notes))
            for rune in mon["runes"]:
                insert(db, "monster_rune", (id, rune["id"]))

        # 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 = openDatabase('application/sw.sqlite')
parseBuildings(db, 'https://swarfarm.com/api/v2/profiles/' + USER +  '/buildings/')
parseRunes(db, 'https://swarfarm.com/api/v2/profiles/' + USER +  '/runes/')
parseMonsters(db, 'https://swarfarm.com/api/v2/profiles/' + USER +  '/monsters/')
db.commit()