Vegetarian Protein Source Analysis

Lentils: 7g per 125ml, 0.4g fat, 14g carbs
Firm tofu: 14g per 85g, 7g fat, 3g carbs
Yves “the good” burger: 13g per 75g, 2g fat, 7g carbs

import re

def split_at_unit(arg_str: str) -> tuple[float, str]:
    """
    Split str into a float and a unit string
    e.g. '125ml' becomes (125.0, 'ml')
    """
    split_re = re.compile(r"(\d+\.?\d*)\s*(\w+)")
    match = split_re.search(arg_str)
    if match:
        groups = match.groups()
        return (float(groups[0]), groups[1])
    print(f"string '{arg_str}' is not a number")
    return (-1, "")


def parse(txt_file) -> list:
    """
    Parse the specific text file in question into a list of its entries
    The line structure of the test file:
    <name>: <protein_val> per <ref_amount>, <fat_val> fat, <carb_val> carbs
    name is the name of a food item
    ref_amount is the amount of that food the nutrition values are based on
    protein_val, fat_val, and carb_val are the number values of those components of the food
    ref_amount may be a volume in millilitres or a mass in grams
    The nutrition values are masses in grams
    """
    result = []
    for line in txt_file.readlines():
        name = line[: (col_idx := line.find(":"))]
        food_values = []
        # parse the numbers
        for val in line[col_idx + 1 :].split(","):
            # parse the reference amount
            if (per_idx := val.find("per")) >= 0:
                ref_split = split_at_unit(val[per_idx + 4 :])
                food_values.append(ref_split[0])
                food_values.append(ref_split[1])
                protein_split = split_at_unit(val[:per_idx])
                food_values.append(protein_split[0])
                food_values.append(protein_split[1])
            else:
                # get the food values
                val_split = split_at_unit(val)
                food_values.append(val_split[0])
                food_values.append(val_split[1])
        result.append((name, *food_values))

    return result

CREATE TABLE vegprotein(
  vegprotein_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  ref_num FLOAT NOT NULL,
  ref_unit VARCHAR(255) NOT NULL DEFAULT 'g',
  protein_num FLOAT NOT NULL,
  protein_unit VARCHAR(255) NOT NULL DEFAULT 'g',
  fat_num FLOAT NOT NULL,
  fat_unit VARCHAR(255) NOT NULL DEFAULT 'g',
  carb_num FLOAT NOT NULL,
  carb_unit VARCHAR(255) NOT NULL DEFAULT 'g',
  CONSTRAINT gt0 CHECK (ref_num > 0 AND protein_num > 0 AND fat_num > 0 AND carb_num > 0)
)

import mysql.connector
import os
from dotenv import load_dotenv, dotenv_values
from parse_txt_data import parse

PROTEIN_COLUMNS = [
    "vegprotein_id",
    "name",
    "ref_num",
    "ref_unit",
    "protein_num",
    "protein_unit",
    "fat_num",
    "fat_unit",
    "carb_num",
    "carb_unit",
]

if __name__ == "__main__":

  with open("./pyvegprotein/data.txt", "r", encoding="utf-8") as f:
      food_data = parse(f)

  load_dotenv()
  DB_HOST = os.getenv("db_host")
  DB_USER = os.getenv("db_user")
  DB_PASSWORD = os.getenv("db_password")
  DB_NAME = os.getenv("db_name")

  proteindb = mysql.connector.connect(
      host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
  )

  cursor = proteindb.cursor()

  col_str = "".join([s + ", " for s in PROTEIN_COLUMNS[1:]])
  col_str = col_str[:-2]

  val_str = "%s, " * (len(PROTEIN_COLUMNS) - 2) + "%s"

  sql_str = f"INSERT INTO vegprotein ({col_str}) VALUES ({val_str})"

  cursor.executemany(sql_str, food_data)

  proteindb.commit()

SELECT name, ref_num, ref_unit, density
FROM vegprotein INNER JOIN fooddata
ON vegprotein.vegprotein_id = fooddata.vegprotein_id
WHERE ref_unit = 'ml'

The result:

+-----------------------------+---------+----------+---------+
| name                        | ref_num | ref_unit | density |
+-----------------------------+---------+----------+---------+
| Lentils                     |     125 | ml       |    0.85 |
| “Dark red” kidney beans     |     125 | ml       |    0.79 |
| Organic chickpeas           |     125 | ml       |    0.71 |
+-----------------------------+---------+----------+---------+
3 rows in set (0.00 sec)

CREATE PROCEDURE GetFoodsWithVolumes ()
BEGIN
  SELECT name, ref_num, ref_unit, density
  FROM vegprotein INNER JOIN fooddata
  ON vegprotein.vegprotein_id = fooddata.vegprotein_id
  WHERE ref_unit = 'ml';
END

CREATE FUNCTION getMass (vegid INT(11))
RETURNS FLOAT DETERMINISTIC
BEGIN
  SET @ml := 0;
  SET @gml := 0;
  SELECT ref_num, density
  INTO @ml, @gml
  FROM vegprotein INNER JOIN fooddata
  ON vegprotein.vegprotein_id = fooddata.vegprotein_id
  WHERE vegprotein.vegprotein_id = vegid;
  RETURN @ml * @gml;
END

SELECT name, getMass(vegprotein_id) AS 'mass(grams)'
FROM vegprotein
WHERE ref_unit = 'ml'

The result:

+-----------------------------+------------------------+
| name                        | mass(grams)            |
+-----------------------------+------------------------+
| Lentils                     |                 106.25 |
| “Dark red” kidney beans     |                  98.75 |
| Organic chickpeas           |                  88.75 |
+-----------------------------+------------------------+
3 rows in set (0.00 sec)

def insert_food(db, food: tuple):
  """
    insert a food row into db
  """
  cursor = db.cursor()

  cursor.execute(f"INSERT INTO vegprotein ({"".join([ s + ", " for s in PROTEIN_COLUMNS[1:] ])[:-2]}) VALUES ({"".join([ "'" + val + "', " if type(val) == str else str(val) + ", " for val in food])[:-2]})")

  db.commit()

def insert_density(db, density: float, like_str: str):
  """
    insert a food density value into db
    density: the value to insert in g/ml
    like_str: the string that should match the corresponding food item. Supplied to the SQL LIKE operator
  """
  cursor = db.cursor()

  cursor.execute(f"SELECT vegprotein_id FROM vegprotein WHERE name LIKE '{like_str}'")
  sql_str = f"INSERT INTO fooddata (density, vegprotein_id) VALUES ({str(density)}, {str(cursor.fetchone()[0])})"
  cursor.execute(sql_str)

  db.commit()

I can see modifying insert_density to do some of the work of producing a string for the LIKE operator in the future. It does the job well enough for now though.

SELECT
  name,
  CAST(FORMAT(((protein_num / mass) * 100), 0) AS UNSIGNED) AS protein_percent,
  protein_num,
  mass
FROM vegprotein
ORDER BY protein_percent DESC

The result:

+------------------------------------+-----------------+-------------+--------+
| name                               | protein_percent | protein_num | mass   |
+------------------------------------+-----------------+-------------+--------+
| “Only peanuts” peanut butter       |              27 |           4 |     15 |
| Yves Canadian bacon                |              26 |          10 |     38 |
| Field roast classic smoked sausage |              26 |          22 |     85 |
| Tofurky Italian sausages           |              25 |          23 |     92 |
| PC Tempeh                          |              20 |          17 |     85 |
| Mixed nuts                         |              18 |           9 |     50 |
| Yves “the good” burger             |              17 |          13 |     75 |
| Firm tofu                          |              16 |          14 |     85 |
| Eggs                               |              12 |          13 |    105 |
| Organic chickpeas                  |              10 |           9 |  88.75 |
| Soft tofu                          |               8 |           7 |     85 |
| Lentils                            |               7 |           7 | 106.25 |
| “Dark red” kidney beans            |               7 |           7 |  98.75 |
+------------------------------------+-----------------+-------------+--------+
13 rows in set (0.00 sec)

CREATE TABLE calculations(
  calc_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  vegprotein_id INT(11),
  protein_mass_ratio FLOAT,
  carb_protein_ratio FLOAT,
  fat_protein_ratio FLOAT,
  health_ratio FLOAT,
  FOREIGN KEY(vegprotein_id) REFERENCES vegprotein(vegprotein_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)

INSERT INTO calculations(
  vegprotein_id,
  protein_ratio,
  carb_ratio,
  fat_ratio,
  health_ratio
)
SELECT
  vegprotein_id,
  protein_num / mass,
  carb_num / mass,
  fat_num / mass,
  (protein_num - carb_num - fat_num) / mass
FROM vegprotein

DELIMITER //

CREATE FUNCTION percentage(float_num FLOAT, dec_num INT)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
	DECLARE float_str VARCHAR(255);
	DECLARE dec_length INT;
	DECLARE _dec INT;
	SET float_str := CAST(float_num AS CHAR);
	SET dec_length := CHAR_LENGTH(MID(float_str, (LOCATE('.', float_str) + 1)));
	SET _dec := IF(dec_num > dec_length, dec_length, dec_num);
	SET float_str := CONCAT(FORMAT((float_num * 100), _dec), '%');
	RETURN float_str;
END//

DELIMITER ;

SELECT
  name,
  percentage(protein_ratio, 2) AS protein,
  percentage(carb_ratio, 2) AS carbs,
  percentage(fat_ratio, 2) AS fat,
FORMAT(health_ratio * 100, 2) AS health
FROM calculations AS calcs
JOIN vegprotein AS vp
ON calcs.vegprotein_id = vp.vegprotein_id
ORDER BY health_ratio DESC