excess.org

Ian Ward

Software
CKAN contributor/tech lead
Urwid author
PyRF contributor
Speedometer author

Presentations
Contributing to Open Source
IASA E-Summit, 2014-05-16
Urwid Applications
2012-11-14
Urwid Intro
2012-01-22
Unfortunate Python
2011-12-19
Django 1.1
2009-05-16

Writing
Moving to Python 3
2011-02-17
Article Tags

Home

Ian Ward's email:
first name at this domain

wardi on OFTC, freenode and github

Locations of visitors to this page

Debugging the Django Database API

Posted on 2007-06-24, last modified 2008-05-14.

Django comes with an amazingly powerful database API that does a very good job of hiding database-specific SQL. Sometimes, though, the API doesn't do exactly what you expect. I ran into this when developing the code that displays the “Tags” that have stories posted.

Fortunately, there is a fairly easy way to get down to the SQL and see what is happening when something goes wrong.

My Article model has a ManyToMany field to my Tag model. So my first attempt at getting all the tags that had stories posted for the current site looked something like:

tags = Tag.objects.filter(articles__site=settings.SITE_ID)

Unfortunately, this returned all the tag objects instead of just the ones I wanted.
I didn't know why all the tags were being returned, so I modified the query to add an intentionally invalid item:

tags = Tag.objects.filter(articles__site=settings.SITE_ID)
tags = tags.extra(where=["break_my_sql_please"])

Then, when I executed the page I was given the usual Django traceback page, where I expanded the last “local vars” link to find that the SQL that was being executed included the following:

... "blag_tag" LEFT OUTER JOIN "blag_article_tags" ...

The “LEFT OUTER JOIN” meanes that tags would be returned even if the WHERE clause eliminates the articles that must be present. If there were no matching articles then the article would be NULL, so I worked around this problem by adding an extra filter:

tags = Tag.objects.filter(articles__site=settings.SITE_ID, 
       articles__isnull=False)

And everything started working as intended.

Tags: Django