1. 使用sql的 ORDER BY 语法来为对象排序

2.13 Sorting Objects Using SQL's ORDER BY Syntax

使用sql的 ORDER BY 语法来为对象排序

Credit: Andrew M. Henshaw

1.1. 问题 Problem

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

你需要通过多个键值去排序，每个键值独立的升序或者降序，模仿sql的 ORDER BY 子句

1.2. 解决 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:

有时候，你从一个数据库中得到数据并且需要数据根据接连好几种方法来排序。相当于在数据库用不同的 ORDER BY 子句做多个select，你能够在你的python代码里模拟 ORDER BY的排序功能，并且一次就得到数据

   1 class sqlSortable:
   2     def _ _init_ _(self, **args):
   3         self._ _dict_ _.update(args)
   4 
   5     def setSort(self, sortOrder):
   6         self.sortFields = []
   7         for text in sortOrder:
   8             sortBy, direction = (text+' ').split(' ', 1)
   9             self.sortFields.append((sortBy, direction[0:4].lower(  ) == 'desc'))
  10 
  11     def _ _repr_ _(self):
  12         return repr([getattr(self, x) for x, reverse in self.sortFields])
  13 
  14     def _ _cmp_ _(self, other):
  15         myFields    = []
  16         otherFields = []
  17         for sortBy, reverse in self.sortFields:
  18             myField, otherField = getattr(self, sortBy), getattr(other, sortBy)
  19             if reverse:
  20                 myField, otherField = otherField, myField
  21             myFields.append(myField)
  22             otherFields.append(otherField)
  23         return cmp(myFields, otherFields)

1.3. 讨论 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:

有时候，我需要做比sql框架能处理的更复杂的数据库处理。使用这个类，我能抽取数据库的行并?br>椅恳恍惺道喽韵蟆Ｎ矣τ门判蛱跫团判虻膌ist。例如，这个查找描述，当提供作为参数?br>脚浞降膕etSort方法：

['name', 'value DESC']

is equivalent to the SQL clause: 是相当于sql子句：

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.

在cmp方里，类处理多个键，多个方向的排序。属性的一个list是为每个键内建的，并且，如果那个特别的键有一个逆向排次序，单独的条目在两个对象之间被交换。性能不是很好，但是想法是简单和有用的：

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:

这里是通常在模块尾部被替换自测的代码，两个测试功能和提供一个例子的用法：

   1 def testSqlSortable(  ):
   2     data = [('Premier', 'Stealth U-11'), ('Premier', 'Stealth U-10'),
   3             ('Premier', 'Stealth U-12'),
   4             ('Co-ed',   'Cyclones'),     ('Co-ed',   'Lightning'),
   5             ('Co-ed',   'Dolphins'),
   6             ('Girls',   'Dynamos'),      ('Girls',   'Tigers'),
   7             ('Girls',   'Dolphins')]
   8 
   9     testList = [sqlSortable(program=program, name=name)
  10                 for program, name in data]
  11 
  12     tests = [['program DESC', 'name'],
  13              ['name desc', 'program asc']]
  14 
  15     for sortBy in tests:
  16         print '#### Test basic sorting ###', sortBy
  17         for sortable in testList:
  18             sortable.setSort(sortBy)
  19         testList.sort(  )
  20         for item in testList:
  21             print item
  22 
  23     print '#### Test modification of attributes ###', sortBy
  24     assert testList[4].name == 'Lightning'
  25     testList[4].name = 'ZZ 1st name'
  26     testList.sort(  )
  27     for item in testList:
  28         print item
  29 
  30 if _ _name_ _ == '_ _main_ _':
  31     testSqlSortable(  )

1.4. 参考 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.