Making a Database of Recipes

There are lots of apps around to do this, like krecipes, but if you are like me it’s a shame to waste lots of utilities that we have in Debian GNU/Linux.

  1. Scarf a bunch or recipes from Karl in Vancouver
  2. wget http://www.fortunecity.com/littleitaly/machievelli/97/download.html
  3. grep -o “http.*zip” download.html > list #gets a list of URIs for the zipped recipe files. You can cut the first line because of garbage.
  4. wget -w 5 -i list #gets the zipped files, with a polite pause of 5s between files…
  5. Here’s the booty:
    ls
    20indian.zip breakfst.zip cheese.zip diabeti1.zip meatloaf.zip afghan.zip brisket.zip chickenb.zip diabetic.zip mexican.zip am.zip cajun.zip chili15.zip dinner.zip mmgarlic.zip
    appetiz.zip cakes02.zip chilismm.zip dips.zip mmpotato.zip asparagu.zip cake.zip chili.zip download.html mm-trout.zip aussie.zip califfig.zip choclate.zip dressing.zip porkchop.zip babyfood.zip camp.zip chscake.zip easy.zip salads.zip barbeque.zip canada.zip c_hsugar.zip egg.zip shrimp.zip barley.zip canning.zip chutney.zip english.zip sidedish.zip
    bbqsauce.zip caribou.zip cookies.zip ethiopia.zip soups.zip beef.zip cass.zip cowboy.zip ethnic.zip stews.zip
    beverage.zip cburg2.zip crockpot.zip fish.zip welsh.zip blend1.zip ccakes.zip day.zip grbeef.zip breads1.zip cc-wok.zip dehyd.zip italian.zip bread.zip cheeseck.zip dessert.zip list
  6. Hungry yet? 😉

  7. am.zip is copyrighted by ArrowHead Mills and rather than ask permission, we delete it.
    rm am.zip
  8. Others have READ.ME files and such so we unzip them each in their own directories.
    for f in *.zip;do mkdir $f.d;cd $f.d;unzip ../$f ;cd ..;done
  9. cat *.d/FILE_ID.DIZ|less #will let you examine the sources.
  10. cat *.d/READ*|less#allows reading the READ.MEs. They mostly say OK with personal use and attribution. I will delete all of those that suggest it is not OK to put online without attribution.
    rm -fr barley* c_hsu* califfig*
  11. Notice some directories now have sub-directories and files with strange names…
    file dips.zip.d/DIPS.DOC
    dips.zip.d/DIPS.DOC: ISO-8859 English text, with CRLF line terminators
  12. Install Krecipes:
    apt-get install krecipes
    The following NEW packages will be installed:
    krecipes krecipes-data libkdewebkit5 libqimageblitz4 sqlite3
  13. cat */*TXT */*MMF */*MXP */*DOC */*mxp */*/*MXP > big.txt #rolls most of the recipes into one big file for krecipes.
  14. Krecipes choked on it so
    split -C 1000000 big.txt #krecipes can handle files of 860 recipes each,

Krecipes is still beta so there are some bugs. Sometimes the “advanced” search does not work for title and ingredients. It crashes if you import large files… I would think that if it can parse the files it would know where to pause to digest them but no, it wants to ask you whether to proceed after counting them… It’s not a bug. It’s a feature. 😉

Well, now I have a recipe for rye bread I want to try this evening…

UPDATE I found a huge page of additional recipes. Warning, it takes many hours for Krecipes to import the 66K recipes… 😉 Seems to import well without crashing if I have some free RAM laying around.

Krecipes is beta software at the moment so it has some weirdness like making you wait while typing in search terms. It updates the search window results in real time… That’s OK for 100 recipes but a dog for 78000…
select count(id) as c from recipes;
+——-+
| c |
+——-+
| 78174 |
+——-+
1 row in set (0.00 sec)

There are a few apparent duplicates…
mysql> select title from recipes where title like ‘%frog%’ order by title;
+—————————————————+
| title |
+—————————————————+
| BULL FROGS (SLUSH) |
| CRISPY FROG LEGS |
| CURRIED FROGS’ LEGS |
| CURRIED FROGS’ LEGS – ECH NAU CA-RI |
| ECH NAU CA-RI (CURRIED FROGS’ LEGS) |
| FAKED FROGLEGS |
| FRIED FROG LEGS |
| FRIED FROGS’ LEGS WITH GARLIC SAUCE |
| FROG EYE SALAD |
| FROG LEGS A LA SAUCE PIQUANTE |
| FROG LEGS SAUCE PICANTE |
| Frog Neck Crab Cakes |
| Frogmore Stew |
| FROGMORE STEW (2) |
| JOE FROGGERS |
| OLD-FASHIONED FROG’S EYE SALAD |
| QUICK & EASY FROG’S EYE SALAD |
| SNAILS WITH FROG LEGS |
| SNAILS WITH FROGS’ LEGS / ESCARGOTS AUX GRENO |
| SNAILS WITH FROGS’ LEGS / ESCARGOTS AUX GRENO (2) |
| SQUASHED FROGS FOR HALLOWEEN |
| Stuffed Frogs |
| STUFFED FROGS (2) |
| Tabasco Classic – Frog Legs Piquant |
+—————————————————+
24 rows in set (0.15 sec)

select count(title) from recipes where title like ‘%(2%’;
+————–+
| count(title) |
+————–+
| 9031 |
+————–+
1 row in set (0.14 sec)

but they are not real…
select title from recipes where title like ‘%(2%’ order by title;

| ZUCCHINI PANCAKES (2) |
| ZUCCHINI PUFFS (2) |
| ZUCCHINI RELISH (2) |
| ZUCCHINI SALAD (2) |
| Z

I really do have a huge mess of recipes in a database and can search them quickly. Now to make that search more convenient. I can just be patient with Krecipes or I can write my own software, much more fun… 😉

time searcher bison
id title
61290 CUBBISON’S CAESAR SALAD
79782 PAN-SEARED BISON TENDERLOIN

real 0m0.019s
user 0m0.004s
sys 0m0.012s

cat $HOME/bin/searcher
#!/bin/bash
echo “select id,title from recipes where title like ‘%”$1″%’ order by title”|mysql Krecipes


That’s a lot faster…
lister 79782
+—————————–+
| title |
+—————————–+
| PAN-SEARED BISON TENDERLOIN |
+—————————–+
+————————————+——–+——-+
| ingredient | amount | units |
+————————————+——–+——-+
| 6-7 oz Bison Filets | 4 | NULL |
| Smoked Elk or smoked Ham Julienned | 8 | oz |
| vegetable oil | 4 | tbsp |
| chili powder | 1 | tbsp |
| Cooked linguine | 12 | oz |
| Morel Mushrooms | 2 | oz |
| Whole butter | 2 | oz |
| heavy cream | 4 | oz |
| Misc baby field vegetables | 0 | NULL |
| Marinated in vinaigrette | 0 | NULL |
+————————————+——–+——-+
instructions
Mix oil and chili powder together and rub evenly over filets and let rest for 30 minutes. Heat saucepan and sear filets on all sided,lower heat and cook, turning often until desired doneness. (Medium Rare to Medium is recommended. Melt butter in pan and add elk and moresl. Saute 1 minute. Add linguine and heat, toss until hot. Add cream and stir until incorporated with pasta. Lightly charbroil vegetables. Place pasta on hot plate. Place Bison filet in center and surround with char-broiled vegetables.

cat $HOME/bin/lister
#!/bin/bash
echo “select title from recipes where id=’$1′; select iname as ingredient,amount,units.name_abbrev as units from units inner join(select ingredients.name as iname,amount,unit_id from ingredients inner join (select ingredient_list.ingredient_id,ingredient_list.amount,ingredient_list.unit_id,recipes.id as rid from ingredient_list left join recipes on ingredient_list.recipe_id=recipes.id where recipes.id=’$1′ ) as charlie on ingredients.id=charlie.ingredient_id)as mike on units.id=unit_id;”|mysql -t –raw Krecipes
echo “select instructions from recipes where id=’$1′”|mysql –raw Krecipes

There, we can easily port this to a web application in PHP or CGI and have a much faster search than Krecipes. I can add search for ingredients as well.

About Robert Pogson

I am a retired teacher in Canada. I taught in the subject areas where I have worked for almost forty years: maths, physics, chemistry and computers. I love hunting, fishing, picking berries and mushrooms, too.
This entry was posted in technology. Bookmark the permalink.

One Response to Making a Database of Recipes

  1. D-G says:

    Hey, Pog. Forgive for not writing about your god-like shell skills in this post. But I’m so bored by your recent outings that I now even personally see to it that you get fodder for good conspiracy posts. Therefore, without further ado:

    http://www.h-online.com/open/news/item/20-years-of-Microsoft-at-the-tender-free-European-Commission-1346121.html

    I expect an article soon.

Leave a Reply