Souping Up My Craft Tracking

At the beginning of the year, I started my hand-made wardrobe challenge, attempting to wear at least one hand-made item every day, and logging my choice so I can analyse my hand-made wardrobe for trends and insights. Up to this point I’ve been using a spreadsheet to track my progress. It’s a bit unwieldy, though - I have to type in the right garment name every time, I’ve had to put together some truly heinous formulae to try to get to the information I need, and - not to put too fine a point on it - Numbers just kind of sucks.

It didn’t take long until I started thinking surely there’s a smarter, fancier way to do this?, and my mind went to code. A long time ago, I tinkered with using Python and MySQL to more efficiently generate the markdown files that power this site; specifically, I set up a semi-automated ‘gallery’ of my finished objects so I wouldn’t have to be faffing around with shortcodes or whatever.

However, I ran out of steam on this fairly quickly, as I was a mere hatchling of a Python programmer and it turns out that just typing a shortcode into a markdown file is in fact easier and faster than spending hours going through chains of exceptions in Python just to post something into the database… and have MySQL give me even more exceptions. And then I entirely rebuilt the site (again), ditching the gallery, and the whole concept sat gathering cobwebs in a neglected github repository.

Recently, though, while filling out my spreadsheet and fighting with my attempt at a heatmap (which would actually be easier to do with needle and thread than in Numbers 🙃), I remembered that old gallery project. And I thought; why not slap this in a database? Why not manage that database with Python? And why not put a webapp over the top of it to make it easy for me to log and view items?

Why not, indeed.

So. I remade my two tables in MySQL. One with garments, and one with logged days, linking the two on the garment id. I made a view that joins them together, aggregating some stats like the total number of wears for an item, and a couple more views summarising overall and monthly stats. So far so good.

Then I tackled the UI. I picked Flask for this, mostly because it was the first framework that showed up when I searched ‘python webapp easy’, and slapped basic bitch bootstrap on the top so it wouldn’t be entirely hideous. I connected back to the database, using MySQL Connector to interface with my underlying dataset. And I set up a few basic data visualisations with MatPlotLib, including a neat little timeline for each garment.

Then came the really tricky part: making it a webapp, rather than a singlelaptopapp. I stuck it on pythonanywhere, duplicating the database and the code and spending a not inconsiderable amount of time getting it working. Which it did! Finally! For five minutes. I reset the app, it worked for five minutes, and then died again. Eventually, after giving myself an eyestrain headache reading 4pt font server logs and deciphering a whole new set of exception messages, I figured out the problem. My code was crap.

Okay, not just that. The database server has a 5 minute timout on idle connections, so it was fine when I was there and logging, but as soon as I walked away from my machine, it would kill the connection… and my app. And that was because I was opening the connection in the main part of my app.py, and not the bits affected by the Flask decorators… and this is probably very tedious for people who read this blog for knitting. Sorry.

Anyway, I figured it out and almost completely rewrote it with this limitation in mind, shifting all my SQL queries and a lot of the app-specific processing into classes which, I just want to say, I’m very proud of myself for doing. I’ve had object-oriented programming patiently explained to me a number of times over the years (first attempt was 15 years ago, I think?) and every time I’ve sort-of understood the concept and even been able to summarise it to someone else, but never actually known how to use it. This time, I think I’ve cracked it, because as of this moment it’s been online for over 48 hours and is still working. Hurrah! It’s password protected because I don’t want anyone messing with my logs, but on the homepage of this very site (and below) you can see some of the fruits of my labour.

I have also made a very cool visualisation of colour distribution, but it’s still in the ‘serviceable but ugly’ phase, so I’m not going to share it yet. I want to see if I can make it (and the rest of the charts) somewhat interactive before I do, and then there’s all sorts of fun analysis I want to do. For some definition of fun, anyway…

(NB: Google Sheets and Looker Studio would work perfectly well for this, maybe even Excel and PowerBI. If I were doing this for a work project, I would absolutely use some form of templated solution like that. But this is for me, and if I want to overcomplicate things I will absolutely do that. 😂)