Tools to help track and measure household groceries.
|
10 months ago | |
---|---|---|
app | 9 months ago | |
archive | 3 years ago | |
db | 9 months ago | |
recipes | 10 months ago | |
sqlpage | 9 months ago | |
test | 1 year ago | |
.gitignore | 3 years ago | |
convert_to_mealie.py | 10 months ago | |
grocery_transactions.py | 1 year ago | |
grocery_transactions.sh | 2 years ago | |
maintenance.sql | 1 year ago | |
mealie_units.json | 10 months ago | |
mock.py | 3 years ago | |
price_check.py | 1 year ago | |
readme.md | 1 year ago | |
recipe.py | 1 year ago | |
reconcile.py | 1 year ago | |
requirements.txt | 1 year ago | |
scratch.sql | 3 years ago | |
units.sql | 1 year ago |
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 TUIprice_check.py
- show price history graph per product (TUI)rest
- basic website to view price trendsThe 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.
This is a python
+ urwid
TUI to help make data entry easy.
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):
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)
A small python
+ bottle
website for things that are better viewed on the web:
seaborn
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.
All the helper apps are written it python
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
.
postgreql
's query_to_xml_and_xmlschema
feature is used with an XSLT style sheet to generate the HTML pages.
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.
pip3 install -r requirements.txt
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