dlamotte

Custom SQL with Django ORM

2013-05-23T00:00:00-07:00

When faced with the need to do more complex things with the ORM in Django, it can be pretty frustrating. I happened upon a pretty interesting solution I’d like to share.

I needed to do some simple GROUP BY, but this trick can apply to all kinds of complicated SQL. The beauty of the ORM is that you can effectively pass around a SQL query and extend it. It encapsulates building a SQL String and saving associated params with that query. So, now, you want to do something more complicated, you need to use the “Escape Hatch” and use custom SQL, but you still need to begin with a QuerySet as a base. Here’s how:

from django.db import connections, router
from myproject.models import MyModel

qs = MyModel.objects.filter(...) # feel free to use the QuerySet API however

def do_custom_sql(qs):
    conn = connections[router.db_for_read(qs.model)]
    cursor = conn.cursor()

    # build the SQL and extract the params
    sql, params = qs.query.get_compiler(router.db_for_read(qs.model)).as_sql()

    # extract the WHERE clause
    where = sql[sql.index('WHERE') + 5:]

    cursor.execute('''
        SELECT
            column1
            , count(column2) as hits
        FROM
            %(table)s
        WHERE
            %(where)s
        GROUP BY
            column1
        HAVING
            hits > 100
    ''' % {
        'table': conn.ops.quote_name(qs.model._meta.db_table),
        'where': where,
    }, params)

    for column1, hits in cursor.fetchall():
        ...

Now you can use the power of the ORM with the power of custom SQL. Of course there are caveats, but at least this is in your toolbox now and you’ll probably be able to figure it out from there.

javascript promises (taming async)

2012-11-14T00:00:00-08:00

I recently discovered the need for a real promise library (jQuery Deferreds needed a little extra). I found a library called q.js which I’ve spent the last few days attempting to wrap my head around. This all started culminating after reading this excellent post about Missing the Point of Promises.

Indeed, I was missing the point.

After dealing with jQuery deferreds inside of more jQuery deferreds, I began looking around for a different way. I thought back to the above post and decided to start looking into using it.

I started by reading the main tutorial and found myself confused about why all these methods were necessary AND how they all worked together. I happened to stumble upon a file in the repository called README.js. I suggest going through and reading that for anyone that:

wonders why they would want/need promises
is confused after reading the main tutorial for the library
wants a good understanding of the source of the library

I noticed quite quickly that not all the methods are documented in the documentation and that you will need to peek at the source to find/understand some (which is unfortunate).

Here’s a quick demo of something you can do that helped me make sense of promises:

Q.when($.get('/data/'))  // convert jQuery Deferred "thenable" into Q promise
.then(function(data) {
  console.log('success', data);
  data.key = 'something else';
  return Q.when($.post('/data/', data)); // convert to Q promise
})
.then(function(postdata) {
  // do something with postdata
})
.fail(function() {
  // handles failure of all above jQuery Deferred's (get/post)
  console.log('failed', arguments);
})
.done();

Notice how the flow is linear down the page instead of nested inside of lower scopes. jQuery Deferreds get you pretty far, but it doesn’t chain like Q promises do which make things feel very linear instead of async (which is the point of promises).

Another piece of magic above is the .done() method which can be demonstrated by the following.

Q.fcall(function() {
  var obj = {};
  if (obj.attr.oops) {
    // the above blows up because "attr" doesnt exist and "oops"
    // is not a method of "undefined"
  }
})
.done();

In this case, the uncaught Exception is caught inside of the promise and reraised if not handled by fail handler if .done() is at the end of the chain. It means its very easy to construct something like the following python coders would be familiar with:

try:
    something()
except Exception, e:
    handle_exception(e)
finally:
    cleanup()

With q.js, it looks like this:

Q.fcall(function() {
  throw new Error('boom');
})
.fail(function(e) {
  // e is Error('boom')
})
.fin(function() {
  // here is your cleanup() method
})
.done();

Python has it as part of the syntax, but promises make the same function possible but also adding handling of async easily.

The q.js library also has a deferred similar in function to the jQuery Deferred but with all the guarantees/features of Q.

var defer = Q.defer();
var promise = defer.promise; // principle of least authority

// a promise here is exactly the same as what was accomplished above
promise.then(function(value) {
  console.log(value);
}, function(fail_value) {
  console.log(fail_value);
})
.done();

// in this case, console.log(value) will print 10
setTimeout(function() {
  defer.resolve(10);
}, 5000); // wait 5 seconds, or any async method

// OR

// in this case, console.log(fail_value) will print Error('boom')
setTimeout(function() {
  defer.reject(new Error('boom'));
}, 5000);

Also with deferreds, progress bars become trivial:

var defer = Q.defer();
var promise = defer.promise;

promise.progress(function(mesg) {
  console.log(mesg);
})
.then(function(value) {
  console.log(value);
})
.done();

setTimeout(function() {
  defer.notify('25% complete');
}, 1000);

setTimeout(function() {
  defer.notify('50% complete');
}, 2000);

setTimeout(function() {
  defer.notify('75% complete');
}, 3000);

setTimeout(function() {
  defer.notify('100% complete');
  defer.resolve('done');
}, 4000);

// the above prints:
// > 25% complete
// > 50% complete
// > 75% complete
// > 100% complete
// > done

There are plenty of other interesting and exciting ways to use the library. The above got me started to the point where I could start exploring other uses and I wouldn’t get bogged down in details and overly confused.

introducing statsd-ostools

2012-10-05T00:00:00-07:00

I recently deployed statsd and graphite after reading through etsy’s blog post measure anything, measure everything. Finally, I found django-statsd (pypi package is actually django-statsd-mozilla) for some quick and easy integration with Django. However, I was unable to find a way to easily monitor common unix OS information like iostat, mpstat and vmstat for instance.

So I set out to write something simple to do it and created statsd-ostools as a result. At the moment, it really only supports linux’s iostat, mpstat, and vmstat, but I plan to add support for other platforms in the future.

So take a look at statsd-ostools and let me know what you think. And/or install it via pip or easy_install:

easy_install http://pypi.python.org/pypi/statsd-ostools
pip install http://pypi.python.org/pypi/statsd-ostools

statsd-ostools is written in python and only depends on setproctitle and statsd so use it on any of your linux servers to monitor them.

postgres performance

2012-10-02T00:00:00-07:00

Lately, I’ve been very interested in tuning and understanding database performance. Specifically, I use postgres and find that it has a number of ways to track how it’s performing and finding exactly what you’re looking for is complicated.

I follow @craigkerstiens on twitter who recently posted this excellent gist on github. It’s a few queries which help you better understand how well you’re using your indexes and caches.

I also use pgbadger to track many things from the postgres logs. Things like: what queries are the slowest, what queries are taking up the most time, what queries are executed the most, … the list goes on and pgbadger is a great tool you should definitely check out. I’d used pgfouine in the past, but pgbadger has won me over now as it’s csvlog parsing actually works and it has many more reports.

djtemplate an opinionated django template

2012-09-22T00:00:00-07:00

I’ve created a number of Django projects now and the most frustrating thing jumping between projects is inconsistencies in the project layout. So, finally I set out to solve this problem.

It’s called djtemplate and its located here:

https://github.com/dlamotte/djtemplate

It’s README contains instructions on how to use it, but basically, you do something like this (make sure you’re using Django 1.4+):

django-admin.py startproject --template=https://github.com/dlamotte/djtemplate/tarball/master myprojectname

I call it a very opinionated template because it’s aimed at sites with heavier javascript and chooses Backbone.js with Rivets.js among other small libraries I’ve found useful. The layout is also derived from David Cramer’s Settings in Django which I’ve found very useful. The basic design is a typical Twitter Bootstrap site along with HTML5 Boilerplate baked in.

I didn’t suspect it’d take me very long to setup this template honestly, but after doing it and spending the better part of a day plus a few hours, I noticed that even a basic setup like this actually takes quite a bit of time. I’m hoping this template makes my projects much more consistent along with saving me such time in setting up new projects. I have to say, I find putting the pieces together for a new project is very tedious. Now, it’ll be a breeze.

Maybe others will find it useful (or at least as a base for your own personal template). Let me know if you do!

uploaded my dotfiles again

2012-09-09T00:00:00-07:00

I’ve played this game before. I uploaded my dotfiles to github and failed.

This time is different. You can find them here. This time, instead of a horribly hacked Makefile installing the files, I’ve gone with symlinks which seems that others have been doing successfully.

But I am not using lndir because it doesn’t quite work the way I’d like. In some cases, I want the whole directory to be symlinked. In others, I want subfiles symlinked and I want fine grained control of that (but I want good defaults so adding new files isn’t a hassle). On top of this, I hate the idea of symlinking the dotfiles straight across. How ugly. I don’t want to have to ls -a in my sandbox of dotfiles all the time. The “install script” should be smart enough to prefix symlinked files with a . so that I can look at the files in the dotfiles dir “normally”.

These two factors made me decide to write the script you’ll find in there called lndotfiles.py. This script has some global variables that act like configuration and also does the magic explained above.

I also have the distinct problem of having separate dotfiles between my work computer and my home computer. I’ve (hopefully) solved this problem this time around by adding a ~/.home/ and ~/.work/ directories which will contain location specific config files that will get sourced by the associated config file via ~/.current/... where ~/.current is a symlink to the appropriate directory.

Another interesting extension is the alias I’ve added to my ~/.current/zshrc. If I’m editting dotfiles in my home directory, I shouldn’t need to cd into my dotfiles sandbox. Instead, I should have direct access to the dotfiles sandbox. The alias looks like this:

alias gitdot="git --git-dir=$HOME/sandbox/dotfiles/.git --work-tree=$HOME/sandbox/dotfiles/"

Now I can just use gitdot in place of git and work on my dotfiles from wherever I like.

jekyll blogging first glance

2012-09-08T00:00:00-07:00

I finally sat down to try out blogging with Github and Jekyll. I have to say, I’m very happy with it so far. I stripped down the Jekyll-Bootstrap template to my liking and have ended up with a very simple blogging system based on Git and Jekyll (which is pretty convenient for this type of thing).

I get to use my favorite editor (vim) and don’t have to deal with a web interface to post.

Best of all, the hosting is free and relatively still “branded” for my username.

Can’t believe I didn’t do this sooner. If you’re considering blogging and want something lightweight with minimal setup, this is it.