#!/usr/bin/env python3 """Build CutCal's food database (SQLite + full-text search) from the Open Food Facts CSV export. Usage: python3 tools/build_foods.py data/off-products.jsonl.gz data/foods.sqlite (The CSV export also works but leaves out many US products' calories, checked 2026-09-23.) Keeps UK and US products that have calories, cleans names, merges the same product sold in different packs, and ranks by how often people scan a product. Data licence: ODbL (Open Food Facts). """ import csv, gzip, json, math, os, re, sqlite3, sys, time SRC, OUT = sys.argv[1], sys.argv[2] csv.field_size_limit(10**8) # Same rules as app/search.js cleanName(): pack counts and sizes make one product look like several. PACK_OR_SIZE = re.compile(r'\b\d+\s*(pk|pack|packs)\b|\bx\s*\d+\b|\b\d+\s*x\b|\b\d+(\.\d+)?\s*(g|kg|ml|l)\b', re.I) KEEP_UPPER = {'UK', 'US', 'M&S', 'BBQ', 'XL', 'TTD'} JUNK = re.compile(r'\?{2,}|[_*#<>{}\[\]|\\]+') def clean_name(name): name = JUNK.sub(' ', PACK_OR_SIZE.sub(' ', name)) name = re.sub(r'\s+', ' ', name).strip(' -,.;:') if name.isupper() and len(name) > 3: # "WHITE CHOC BUTTONS" -> "White Choc Buttons" name = ' '.join(w if w in KEEP_UPPER else w.capitalize() for w in name.split()) return name def num(v, lo=0.0, hi=1e9): try: x = float(v) except (TypeError, ValueError): return None return x if lo <= x <= hi and not math.isnan(x) else None def dup_key(name, kcal, p, c, f): words = [w[:-1] if w.endswith('s') else w for w in re.sub(r'[^a-z0-9 ]', ' ', name.lower()).split()] return (' '.join(words), round(kcal), round(p * 10), round(c * 10), round(f * 10)) def read_products(path): """Yield one dict per product from the CSV export or the (complete) JSONL export.""" if path.endswith('.jsonl.gz'): with gzip.open(path, 'rb') as fh: for line in fh: if b'en:united-kingdom' not in line and b'en:united-states' not in line: continue # skip the JSON parse for the ~75% of products sold elsewhere o = json.loads(line) n = o.get('nutriments') or {} # Newer products (most UK ones, checked 2026-09-23) leave `nutriments` empty and use this instead. agg = (o.get('nutrition') or {}).get('aggregated_set') or {} an = (agg.get('nutrients') or {}) if agg.get('per') in ('100g', '100ml') else {} v = lambda k: n.get(f'{k}_100g', n.get(f'{k}_prepared_100g', (an.get(k) or {}).get('value'))) yield {'countries': ','.join(o.get('countries_tags') or []), 'code': o.get('code'), 'name': o.get('product_name_en') or o.get('product_name'), 'generic': o.get('generic_name_en') or o.get('generic_name'), 'brands': o.get('brands'), 'scans': o.get('unique_scans_n'), 'serving': o.get('serving_quantity'), 'kcal': v('energy-kcal'), 'kj': v('energy-kj') if v('energy-kj') is not None else v('energy'), 'p': v('proteins'), 'c': v('carbohydrates'), 'f': v('fat'), 'fib': v('fiber'), 'sug': v('sugars'), 'salt': v('salt'), 'na': v('sodium')} return with gzip.open(path, 'rt', encoding='utf-8', errors='replace', newline='') as fh: rows = csv.reader(fh, delimiter='\t', quoting=csv.QUOTE_NONE) head = next(rows) cols = {'countries': 'countries_tags', 'code': 'code', 'name': 'product_name', 'generic': 'generic_name', 'brands': 'brands', 'scans': 'unique_scans_n', 'serving': 'serving_quantity', 'kcal': 'energy-kcal_100g', 'kj': 'energy_100g', 'p': 'proteins_100g', 'c': 'carbohydrates_100g', 'f': 'fat_100g', 'fib': 'fiber_100g', 'sug': 'sugars_100g', 'salt': 'salt_100g', 'na': 'sodium_100g'} ix = {k: head.index(c) for k, c in cols.items()} for row in rows: yield {k: (row[i] if i < len(row) else '') for k, i in ix.items()} def main(): t0 = time.time() foods = {} # dup_key -> food dict seen = kept = 0 for r in read_products(SRC): seen += 1 countries = r['countries'] uk, us = 'en:united-kingdom' in countries, 'en:united-states' in countries if not (uk or us): continue kcal = num(r['kcal'], 0, 900) if kcal is None: kj = num(r['kj'], 0, 3800) kcal = kj / 4.184 if kj is not None else None if kcal is None: continue p, c, f = (num(r[k], 0, 100) or 0.0 for k in ('p', 'c', 'f')) # Fibre, sugar, salt: unknown stays None (not 0). Salt is sodium x 2.5 when only sodium is given. fib, sug = num(r['fib'], 0, 100), num(r['sug'], 0, 100) salt = num(r['salt'], 0, 100) if salt is None and num(r['na'], 0, 40) is not None: salt = num(r['na'], 0, 40) * 2.5 brands = [] for b in (r['brands'] or '').split(','): b = b.strip() if b and b.lower() not in (x.lower() for x in brands): brands.append(b) name = clean_name(r['name'] or '') or clean_name(r['generic'] or '') if brands and name.lower() == brands[0].lower(): # name is only the shop, e.g. "Waitrose" name = clean_name(r['generic'] or '') or name if len(name) < 2: continue code = re.sub(r'\D', '', str(r['code'] or '')) scans = int(num(r['scans']) or 0) serving = num(r['serving'], 1, 2000) key = dup_key(name, kcal, p, c, f) prev = foods.get(key) if prev is None: foods[key] = {'name': name, 'brands': brands[:3], 'uk': uk, 'us': us, 'kcal': kcal, 'p': p, 'c': c, 'f': f, 'fib': fib, 'sug': sug, 'salt': salt, 'serving': serving, 'scans': scans, 'codes': [code] if code else []} kept += 1 else: prev['uk'] |= uk; prev['us'] |= us prev['serving'] = prev['serving'] or serving for k, x in (('fib', fib), ('sug', sug), ('salt', salt)): if prev[k] is None: prev[k] = x prev['scans'] += scans if code: prev['codes'].append(code) for b in brands: if len(prev['brands']) < 3 and b.lower() not in (x.lower() for x in prev['brands']): prev['brands'].append(b) if seen % 500000 == 0: print(f' read {seen:,} rows, kept {kept:,}', flush=True) if os.path.exists(OUT): os.remove(OUT) db = sqlite3.connect(OUT) db.executescript(''' PRAGMA journal_mode = OFF; PRAGMA synchronous = OFF; CREATE TABLE foods (id INTEGER PRIMARY KEY, name TEXT NOT NULL, brands TEXT NOT NULL, uk INT, us INT, kcal REAL, p REAL, c REAL, f REAL, fib REAL, sug REAL, salt REAL, serving REAL, scans INT, boost REAL); CREATE TABLE codes (code TEXT PRIMARY KEY, food_id INT NOT NULL) WITHOUT ROWID; ''') r1 = lambda x: None if x is None else round(x, 1) for i, fd in enumerate(foods.values(), 1): db.execute('INSERT INTO foods VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', (i, fd['name'], ', '.join(fd['brands']), int(fd['uk']), int(fd['us']), r1(fd['kcal']), r1(fd['p']), r1(fd['c']), r1(fd['f']), r1(fd['fib']), r1(fd['sug']), None if fd['salt'] is None else round(fd['salt'], 2), r1(fd['serving']), fd['scans'], round(math.log1p(fd['scans']), 3))) db.executemany('INSERT OR IGNORE INTO codes VALUES (?,?)', [(c, i) for c in fd['codes']]) db.executescript(''' CREATE VIRTUAL TABLE foods_fts USING fts5(name, brands, content='foods', content_rowid='id', tokenize='unicode61 remove_diacritics 2', prefix='2 3'); INSERT INTO foods_fts(rowid, name, brands) SELECT id, name, brands FROM foods; INSERT INTO foods_fts(foods_fts) VALUES ('optimize'); ''') db.commit() db.execute('VACUUM') db.close() uk = sum(1 for f in foods.values() if f['uk']); us = sum(1 for f in foods.values() if f['us']) print(f'Done in {time.time() - t0:.0f}s: read {seen:,} rows, {len(foods):,} foods ({uk:,} UK, {us:,} US), ' f'{os.path.getsize(OUT) / 1e6:.0f} MB') if __name__ == '__main__': main()