Python Cookbook

Sorting Objects Using SQL’s ORDER BY Syntax

Credit: Andrew M. Henshaw

Problem

You need to sort by multiple keys, with each key independently ascending or descending, mimicking the functionality of the SQL ORDER BY clause.

Solution

Sometimes you get data from a database and need the data ordered in several ways in succession. Rather than doing multiple SELECT queries on the database with different ORDER BY clauses, you can emulate the sorting flexibility of ORDER BY in your Python code and get the data just once:

class sqlSortable:
    def _ _init_ _(self, **args):
        self._ _dict_ _.update(args)

    def setSort(self, sortOrder):
        self.sortFields = []
        for text in sortOrder:
            sortBy, direction = (text+' ').split(' ', 1)
            self.sortFields.append((sortBy, direction[0:4].lower(  ) == 'desc'))

    def _ _repr_ _(self):
        return repr([getattr(self, x) for x, reverse in self.sortFields])

    def _ _cmp_ _(self, other):
        myFields    = []
        otherFields = []
        for sortBy, reverse in self.sortFields:
            myField, otherField = getattr(self, sortBy), getattr(other, sortBy)
            if reverse:
                myField, otherField = otherField, myField
            myFields.append(myField)
            otherFields.append(otherField)
        return cmp(myFields, otherFields)

Discussion

Occasionally, I need to do database processing that is more complex than the SQL framework can handle. With this class, I can extract the database rows and instantiate the class object for each row. After massaging the objects, I apply a list of sort conditions and sort. For example, this search description, when ...