grocery-manager

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