Credit: Andrew M. Henshaw
You need to sort by multiple keys, with
each key independently ascending or descending, mimicking the
functionality of the SQL ORDER BY
clause.
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)
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( )
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.