Just when I started blogging again, I found myself sidetracked.
I decided to revisit my finance project. I had abandoned it to the pragmatic approach of using GnuCash. While I still think GnuCash is a great piece of software, it had a couple of limitations. I wanted more flexibility in reporting and I also wanted to be able to access my data from all of my devices, from PC to phone.
I tried reverting to a spreadsheet, but that didn’t really work. Data entry was more cumbersome and I managed to end up with a spreadsheet so complicated that Excel was taking several seconds to recalculate every time I added a row. I had to switch to manual calculation even on my main PC, which is no slouch.
So I went back to my Django project. When I started the project I intended to write up every step of my progress, showing the code I was writing.
Now I’m not so sure. Perhaps I was atop Mount Stupid when I so confidently started blogging about Django.
Getting back in to the development quickly sent me over the peak and down into the Valley of Despair.
Django makes some things very easy. A basic CRUD – Create, Read, Update, Delete – application can be put together with very little code. The problem is that real world applications are rarely that simple.
It’s not a problem specific to Django, but it illustrates a general point about frameworks. The things they’re designed to do, they do very well and make very easy, which is fine when you’re developing an application that fits within the framework.
The trouble is that there will always come a point where you want to step outside of those boundaries. At that point you have to understand what the framework is actually doing under the hood.
Even within the limits of what the framework is designed to do, it can be easy to get tripped up if you start developing based on reading a couple of tutorials…
For example, I had a page on my site that was taking an age to load. It turns out I was naively following foreign key relationships on an object – something the model and the template language make easy. I hadn’t realised that the SQL generated wasn’t creating a foreign key link to the related table.
I’ll save the details for another time, but in essence my code was generating the SQL as:
select id, name, table_b_id from table_a
And then using the table_b_id to query table_b separately for every record in table_a – literally hundreds of
select name from table_b where id = xxxx
Clearly, it should have been generating:
select table_a.id, table_a.name. table_b.name from table_a inner join table_b on table_b.id = table_a.table_b_id
It will immediately be obvious to any experienced Django developer that I’d written:
results = TableA.objects.all()
And then referred to a foreign key via:
When I should have written:
results = TableA.objects.all().select_related('table_b')
The trouble is, you don’t need the select_related option to access the related fields – Django will happily allow you to access them and then run the inefficient SQL to follow the relationships.
My real world example was following two separate parent foreign key relationships across multiple levels and then following one of the parent keys back down to get sibling child records together with their parent records on the other parent key…
My first pass generated 830 separate SQL queries but once I discovered select_related and prefetch_related I was able to reduce it to just two queries.
Of course that’s my fault – I didn’t understand Django well enough to know how it was translating operations on the objects into SQL. It serves, though, as an illustration of how a framework can lull you into a false sense of simplicity.
Django’s object model abstraction looks, at first glance, like a good way to develop database applications without having to know SQL. Make no mistake though – that’s a false assumption.
There have been times when I considered switching to a simpler framework, such as Flask, where I could work directly with SQL to extract data.
I’ve read through class source code on CCBV, adapted guidance on blog posts and tried to sort the wheat from the chaff on Stackoverflow.
It’s been a lot of work just to keep track of my finances, but it’s been absorbing.Finance Project