Database scheme for resin recipes

This document explores how an SQLite database could be used to store data for the resin-calculator. Normally, the recipes are a dictionary keyed to the name of the resin. The value of each recipe is a list of (component, parts-by-weight) tuples. Since a database only contains tables, where each row has the same form, how do we store such infomation in a database?

As preparation, create a database.

import sqlite3

connection = sqlite3.connect('resins.sqlite')
cursor = connection.cursor()

The first table in the database is the “recipes” table. This table has two colums; the implicit “rowid”, and the recipe name, which is text.

The second table contains the recipe items. The first column contains the row-id of recipe it belongs to. The second column is the name of the component and the third is the quantity.

Using the row-id of the recipe links the ingredients to the recipe.

def create_empty_db(path):
    connection = sqlite3.connect(path)
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE recipes(name TEXT)")
    cursor.execute("CREATE TABLE components(recipe INT, name TEXT, quantity REAL)")
    connection.commit()
    return connection

Let’s add recipes for Araldite 5052 and Synolite 1967 to the database.

ins_rec = "INSERT INTO recipes (name) VALUES(?)"
inc_comp = "INSERT INTO components (recipe, name, quantity) VALUES(?,?,?)"

cursor.execute(ins_rec, ("Araldite 5052",))
recipe = cursor.lastrowid
cursor.execute(ins_comp, (recipe, "araldite LY 5052", 100))
cursor.execute(ins_comp, (recipe, "aradur 5052", 38))
connection.commit()

cursor.execute(ins_rec, ("Synolite 1967-G-1",))
recipe = cursor.lastrowid
cursor.execute(ins_comp, (recipe, "synolite 1967-G-1", 1000))
cursor.execute(ins_comp, (recipe, "trigonox 249 VR", 20))
connection.commit()

The Python data is a dictionary which has the name of the mixture as the key and a list of (component, quantity) tuples as the value. Knowing this, we can write a function to write such recipies to the database.

def save_recipe(db, name, components):
    cursor = db.cursor()
    ins_rec = "INSERT INTO recipes (name) VALUES(?)"
    inc_comp = "INSERT INTO components (recipe, name, quantity) VALUES(?,?,?)"
    cursor.execute(ins_rec, (name,))
    recipe = cursor.lastrowid
    for component, quantity in components:
        cursor.execute(ins_comp, (recipe, component, quantity))
    db.commit()

Optionally, the database could have a table called “properties” which contains metadata about the database. The following function replaces all properties.

def set_db_properties(db, properties):
    db.execute('DROP TABLE IF EXISTS properties')
    db.execute("CREATE TABLE properties(key TEXT, value TEXT)")
    for key, value in properties:
        db.execute('INSERT INTO properties (key, value) VALUES(?,?)', (key, value))
    db.commit()

This could e.g. contain who compiled the database, when it was created, when it was last updated et cetera.

Now how would we construct the Python data from the recipe database? That is actually quite simple.

def recipes_from_db(db):
    sel_comp = "SELECT name, quantity FROM components WHERE recipe = ?"
    cur = db.cursor()
    cur.execute("SELECT name, rowid FROM recipes")
    recipes = {name: tuple(cur.execute(sel_comp, (rowid,)).fetchall())
        for name, rowid in cur.fetchall()}
    return recipes

In the end, I didn’t use this feature in resin-calculator, because I considered it best to have a human-readable data format.

Nevertheless, it was useful to learn about using sqlite from Python.

For comments, please send me an e-mail.

← Doing calculations with Python Using sqlite3 for time management →

Roland's homepage

Database scheme for resin recipes

Related articles