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 supplied as the argument to the recipe’s setSort method:

['name', 'value DESC']

is equivalent to the SQL clause:

ORDER BY name, value DESC

The class handles multiple-key, multiple-direction sorts in the _ _cmp_ _ method. A list of attributes is built for each key, and individual items are swapped between the two objects if that particular key has a reversed sort order. Performance may not be great, but the idea is both simple and useful.

Here is the self-test code that would normally be placed at the end of the module, both to test functionality and to provide an example of use:

def testSqlSortable(  ):
    data = [('Premier', 'Stealth U-11'), ('Premier', 'Stealth U-10'),
            ('Premier', 'Stealth U-12'),
            ('Co-ed',   'Cyclones'),     ('Co-ed',   'Lightning'),
            ('Co-ed',   'Dolphins'),
            ('Girls',   'Dynamos'),      ('Girls',   'Tigers'),
            ('Girls',   'Dolphins')]

    testList = [sqlSortable(program=program, name=name) 
                for program, name in data]

    tests = [['program DESC', 'name'],
             ['name desc', 'program asc']]

    for sortBy in tests:
        print '#### Test basic sorting ###', sortBy
        for sortable in testList:
            sortable.setSort(sortBy)
        testList.sort(  )
        for item in testList:
            print item

    print '#### Test modification of attributes ###', sortBy
    assert testList[4].name == 'Lightning'
    testList[4].name = 'ZZ 1st name'
    testList.sort(  )
    for item in testList:
        print item

if _ _name_ _ == '_ _main_ _':
    testSqlSortable(  )

See Also

The O’Reilly Network, for an article about SQL ORDER BY (http://linux.oreillynet.com/pub/a/linux/2001/02/13/aboutSQL.html); your database’s reference for SQL.