I had big plans for today – I was going to get caught up on Quicken, wine bookkeeping, refill the wine refrigerator, and work on the High Holidays honors for Shir Hadash.
I got caught up on Quicken and put the wines we bought yesterday into CellarTracker, but the rest of the plans will have to wait.
I wrote a program a while ago to help me pick which wines to move into the wine refrigerator (based on how far along in the drinking window a wine is and how many bottles I have of it), but I noticed that the list showed the same wine in several places. I’d reworked the program a few months ago to take the inventory information I get from CellarTracker and put it into a simple sqlite3 database to figure out which bottles to move. It seemed to work well, but today I realized that CellarTracker returns the information in order of acquisition, so that if I bought the same wine a few times, it wouldn’t be consolidated.
It should be easy to consolidate the data in the database – all it would need is a few
GROUP BY clauses in my query. I looked at the statement I was using to query the database:
select type, ready, whereitis, b.label, beginconsume, endconsume from ( select min(n, n * (now - bc) / (ec - bc)) as ready, label, beginconsume, endconsume from ( select count(*) as n, julianday(beginconsume || '-01-01') as bc, julianday(date(endconsume || '-12-31')) as ec, beginconsume, endconsume, julianday('now') as now, vintage || ' ' || wine as label, type from wines group by label ) order by ready ) r inner join ( select type, vintage || ' ' || wine as label, location, location || ' ' || bin as whereitis from wines ) b on b.label = r.label where ready > 0 order by ready desc;
and I realized that it would be a whole lot easier to rewrite the logic in simple Python than it would be to figure out how to fix the query.
So I did.
Doing the work in Python may mean that the program runs slower than doing it in the database – on the other hand, I only run the program once a month or so, and it only takes a couple of seconds when I do run it. And next time, I might be able to figure out what I’m doing!