Elite Dangerous Recipe Database

README.md

Basis for data: https://forums.frontier.co.uk/showthread.php?t=248275

crafting.csv extracted from (hidden) sheet Data. materials.csv extracted from (hidden) sheet Inventory Management (WIP).

import-materials.py

from model import Engineer, Material, Recipe, RecipeEffect, Ingredient, Base
import os
import csv
from pprint import pprint
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker


dbpath = "/home/user/eng/data.db"
engine = create_engine("sqlite:///"+dbpath)
Session = sessionmaker(bind=engine)


fields = (
    "title",
    "type",
    "description",
    "rarity",
)


def material_factory(db, row):
    inst = db.query(Material).filter(
        func.lower(Material.title)==func.lower(row["title"])
    ).first()
    if inst:
        inst.type = row["type"].lower()
        inst.description = row["description"]
        inst.rarity = row["rarity"].lower()
    else:
        inst = Material(
            title = row["title"],
            type = row["type"].lower(),
            description = row["description"],
            rarity = row["rarity"].lower()
        )
    db.add(inst)
    return inst


if __name__ == "__main__":
    session = Session()
    with open("materials.csv", "rb") as fp:
        reader = csv.DictReader(fp, fields, restkey="extra", delimiter=";", quotechar='"')
        for row in reader:
            material_factory(session, row)
    session.commit()

import-recipes.py

from model import Engineer, Material, Recipe, RecipeEffect, Ingredient, Base
import os
import csv
from pprint import pprint
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker

dbpath = "/home/user/eng/data.db"
engine = create_engine("sqlite:///"+dbpath)
Session = sessionmaker(bind=engine)

def chunks(l, n):
    """Yield successive n-sized chunks from l."""
    for i in xrange(0, len(l), n):
        yield l[i:i+n]


def dicted(fields, values):
    return dict(zip(fields, values))


def filter_row(row):
    return row["recipe_title"] and row["recipe_title"] != "0"


fields = (
    "eng_firstname",
    "eng_lastname",
    "recipe_type",
    "recipe_id",
    "recipe_number",
    "recipe_title",
    "recipe_level",
)
num_effects = 7
effect_fields = ("title", "influence", "min", "max")
num_ingredients = 5
ingredient_fields = ("title", "quantity")


def norm_row(row):
    row["effects"] = []
    row["ingredients"] = []
    row["recipe_level"] = row["recipe_level"]
    end_effects = num_effects*len(effect_fields)
    end_ingredients = end_effects + num_ingredients*len(ingredient_fields)
    effect_data = row["extra"][0:end_effects]
    ingredient_data = row["extra"][end_effects : end_ingredients]
    for chunk in chunks(effect_data,len(effect_fields)):
        effect = dicted(effect_fields, chunk)
        if effect["title"] and effect["title"] != "0":
            row["effects"].append(effect)
    for chunk in chunks(ingredient_data,len(ingredient_fields)):
        ingredient = dicted(ingredient_fields, chunk)
        if ingredient["title"] and ingredient["title"] != "0":
            row["ingredients"].append(ingredient)


def engineer_factory(db, row):
    name = row["eng_firstname"] + " " + row["eng_lastname"]
    eng = session.query(Engineer).filter(
        func.lower(Engineer.name) == func.lower(name)
    ).first()
    if not eng:
        eng = Engineer(name=name)
        db.add(eng)
    return eng


def recipe_factory(db, eng, row):
    r = session.query(Recipe).filter(
        func.lower(Recipe.title) == func.lower(row["recipe_title"]),
        func.lower(Recipe.type) == func.lower(row["recipe_type"]),
        Recipe.engineer==eng,
        Recipe.level==row["recipe_level"],
    ).first()
    if not r:
        r = Recipe(
            engineer=eng,
            title=row["recipe_title"],
            level=row["recipe_level"],
            type=row["recipe_type"]
        )
        db.add(r)
    return r


def effects_factory(db, rec, row):
    for item in row["effects"]:
        inf = {"G": "GAIN", "L": "LOSS"}
        effect = RecipeEffect(
            recipe=rec,
            title=item["title"],
            influence=inf[item["influence"]],
            min=item["min"] or "0",
            max=item["max"] or "0",
        )
        db.add(effect)


def material_factory(db, title):
    inst = db.query(Material).filter(
        func.lower(Material.title) == func.lower(title)
    ).first()
    if not inst:
        inst = Material(title=title)
        db.add(inst)
    return inst


def ingredients_factory(db, rec, row):
    for ingr in row["ingredients"]:
        ingredient = Ingredient(
            recipe=rec,
            material=material_factory(db, ingr["title"]),
            quantity=ingr["quantity"] or "0"
        )
        db.add(ingredient)


if __name__ == "__main__":
    try: os.unlink(dbpath)
    except: pass
    Base.metadata.create_all(engine)
    session = Session()
    with open("crafting.csv", "rb") as fp:
        fp.readline()  # skip header
        reader = csv.DictReader(fp, fields, restkey="extra", delimiter=";", quotechar='"')
        for row in filter(filter_row, reader):
            norm_row(row)
            # pprint(row)
            eng = engineer_factory(session, row)
            rec = recipe_factory(session, eng, row)
            effects_factory(session, rec, row)
            ingredients_factory(session, rec, row)
    session.commit()

models.py

from sqlalchemy import (
    Column,
    Integer,
    String,
    Enum,
    Numeric,
    ForeignKey
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref


Base = declarative_base()


class Engineer(Base):
    __tablename__ = "engineer"
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Material(Base):
    __tablename__ = "material"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    description = Column(String)
    rarity = Column(String)
    type = Column(String)


class Recipe(Base):
    __tablename__ = "recipe"
    id = Column(Integer, primary_key=True)
    engineer_id = Column(Integer, ForeignKey("engineer.id"))
    title = Column(String)
    type = Column(String)
    level = Column(Integer)
    engineer = relationship(Engineer, backref=backref("recipes"))


class RecipeEffect(Base):
    __tablename__ = "recipe_effect"
    id = Column(Integer, primary_key=True)
    recipe_id = Column(Integer, ForeignKey("recipe.id"))
    title = Column(String)
    influence = Column(Enum("GAIN", "LOSS"))
    min = Column(Numeric(10, 2, asdecimal=False))
    max = Column(Numeric(10, 2, asdecimal=False))
    recipe = relationship(Recipe, backref=backref("effects"))


class Ingredient(Base):
    __tablename__ = "ingredient"
    id = Column(Integer, primary_key=True)
    material_id = Column(Integer, ForeignKey("material.id"))
    recipe_id = Column(Integer, ForeignKey("recipe.id"))
    quantity = Column(Integer)
    material = relationship(Material, backref=backref("ingredients"))
    recipe = relationship(Recipe, backref=backref("ingredients"))