Tools to help track and measure household groceries.

Daniel Sheffield ba403efe09 persist filter and implement other tabs 10 months ago
app 6e444566b4 Merge branch 'bugfix/totals-row-cell-missing' of gogsadmin/grocery-manager into master 9 months ago
archive 7c4d709cd0 add archived txn files 3 years ago
db a44beb4ee0 cron: take backup 9 months ago
recipes 691e65e454 add Mealie recipe converter 10 months ago
sqlpage ba403efe09 persist filter and implement other tabs 9 months ago
test dd76dc2e8a remove button-resize css class 1 year ago
.gitignore 5e5c797987 update .gitignore and move to psycopg3 (now just psycopg) 3 years ago
convert_to_mealie.py 691e65e454 add Mealie recipe converter 10 months ago
grocery_transactions.py fc4ef29cc7 create new txn file if doesn't exist 1 year ago
grocery_transactions.sh f7d36ed12c fix shell script to commit transactions to point to the new db name 2 years ago
maintenance.sql 467fe06bea add maintenance sql 1 year ago
mealie_units.json 691e65e454 add Mealie recipe converter 10 months ago
mock.py 5e5c797987 update .gitignore and move to psycopg3 (now just psycopg) 3 years ago
price_check.py 3b48369ddf fix some lint 1 year ago
readme.md b3d7d42a80 Update 'readme.md' 1 year ago
recipe.py 3b48369ddf fix some lint 1 year ago
reconcile.py 628d49e4a8 more store codes and better handling of them 1 year ago
requirements.txt 2049a1cd1b fix requirements.txt 1 year ago
scratch.sql b46ee7cd59 Update monthly report query with indentation and excluding the rollup portion 3 years ago
units.sql f0f834f3ea add unit type to new units 1 year ago

readme.md

Grocery Manager

This a database driven app - meaning it's all about the data and nothing but the data.

The database is assumed to be postgresql, but any modern database should work with some tweaks.

The following apps make the data useful:

  • grocery_transactions.py - facilitate data entry with a TUI
  • price_check.py - show price history graph per product (TUI)
  • rest - basic website to view price trends

Workflow

  1. Grocery purchases are tracked by entering reciepts into the database using grocery_transactions.py or otherwise
  2. That's it! So long as the data is in the database, you can do whatever you want with it.

The database itself is designed to be the first class UI.

This is because I wanted data integrity first and foremost, and I wanted to be able to start using the data without need for a UI (I hate UI development).

However, there are helper tools to ease the workflow.

grocery_transactions.py

This is a python + urwid TUI to help make data entry easy.

  • New products can be added to the db without manually modifying the database
  • Reciepts can be entered quickly using autocomplete based off existing data
  • Items can be tagged with any number of free-form tags

Once done, the app can be exited by hitting ESC on the keyboard.

The result is a .txn file that can be commited to the database using grocery_transactions.sh

Not all features of the database are supported including (needs to be done manually for now):

  • Creating new stores
  • Adding new units

price_check.py

This is another python + urwid TUI to look up products to see their price history.

It's designed to be usable on mobile (I use termux and have a shortcut to ssh into a jumphost)

rest

A small python + bottle website for things that are better viewed on the web:

  • trending price data over all products (can be filtered) rendered with seaborn
  • product listings (direct from database via xml + xsl)
  • tag listings and usage

Technology stack

postgresql

The schema is designed to protect data from human error.

Stored procedures are supplied where CHECK constraints can not be used to prevent all invalid usage.

python

All the helper apps are written it python

psycopg

psycopg is used heavily.

No ORM is used in any apps.

Remember the database is designed to keep queries simple.

Queries are built up from SQL fragments in app. While postgresql is assumed, it should be straight forward to replace the db connection with another RBDMS db connecion and adjust query syntax where needed.

Some window functions are used it the TUI apps but only because it was convienient at the time. This is the most significant thing that may need reworking to interact with other RBDMS.

Some client side data transforms are done with pandas.

HTML + XSLT

postgreql's query_to_xml_and_xmlschema feature is used with an XSLT style sheet to generate the HTML pages.

bottle

Thes web inerface uses bottle as a web server.

psycopg is used to get the XML data for the web page from the database.

An HTML form is built client side and added to the page using bottle's templating feature.

The form is submitted using URL params so filters can be written by hand and bookmarked.

Only CSS + HTML is used. No javascript.

This is for portability reasons.

Get Started

Install Requirements

pip3 install -r requirements.txt

Usage

Enter receipt from New World (TUI)

python3 grocery_transactions.py 2023-06-02-NW.txn

Commit when done

# check all is looking good first
./grocery_transactions.sh -d 2023-06-02 < 2023-06-02-NW.txn

# then commit (supply -c)
./grocery_transactions.sh -d 2023-06-02 -c < 2023-06-02-NW.txn

Check prices (TUI)

python3 price_check.py

Check the web inerface: https://shandan.one/grocery/trend?category=&group=Fish%2C+Meat%2C+Eggs&product=&tag=&unit=kg