Python: Simple order by with Pony ORM

Question

I try to implement a simple select with an order_by aggregation in pony orm:

So, I tried to ways: The first way raise a error message:

sel = select((f.Name, f.id) for f in Firma).order_by(Firma.Name)

In this way, I get the error message from python: "NotImplementedError: Ordering by attributes is limited to queries which return simple list of objects. Try use other forms of ordering (by tuple element numbers or by full-blown lambda expr)"

The second way, were the selection in order works fine but the inserting in my dictionary destroy the disered selection order

            for f in Firma.select(lambda f: f.Name) \
                .order_by((Firma.Name)):
            dic[f.Name] = f.id
            print ("=== f.Name", f.Name)
            dic[f.Name] = f.id
            print(" === dic[f.Name]", dic[f.Name])

The result is the following:

    === f.Name A
 === dic[f.Name] 10
=== f.Name B
 === dic[f.Name] 11
=== f.Name C
 === dic[f.Name] 12
=== f.Name Neckermann
 === dic[f.Name] 7
=== f.Name Otto
 === dic[f.Name] 6
=== f.Name Quelle
 === dic[f.Name] 3
=== f.Name Testfirma
 === dic[f.Name] 9
=== f.Name The Pearlfactory
 === dic[f.Name] 8

But the dictionary is:

dic  {'Testfirma': 9, 'Quelle': 3, 'C': 12, 'The Pearlfactory': 8, 'B': 11, 'Otto': 6, 'A': 10, 'Neckermann': 7}

Now the questions: How I can use the first way of order_by aggregation whithout this error message. Or, How I can put the values of firm id and firm name in my dictionary in the sorted soccesion

I hope someone can understand my terrible english ;)

Greetings

niesel

score 7 · Answer 1 · edited Aug 06 '18 at 01:04

Regarding your first question, if a query select tuples you can order it by specifying the number of tuple item:

query = select((f.Name, f.id) for f in Firma).order_by(1)

Here, 1 means "sort by first tuple item (that is, by Name) in ascending order". If you specify, say, -2 instead, it will mean "sort by second tuple item (that is, by id) in descending order".

Another way to do the same is to specify lambda function in order_by section:

query = select((f.Name, f.id) for f in Firma).order_by(lambda: f.Name)

or, for example:

query = select((f.Name, f.id) for f in Firma).order_by(lambda: desc(f.id))

Note that I use lambda function without any argument. In that form, f inside lambda will refer to variable f inside generator expression. Be aware that IDE don't understand that syntax and may highlight it as an error, but the code is actually correct.

Your query with lambda function looks incorrect:

Firma.select(lambda f: f.Name).order_by((Firma.Name))

In that query, lambda inside select used for filtering, it just filter objects which name is not empty.

Regarding you second question, in Python dictionaries are unordered. If you want to keep order of items, you should not keep them in dictionary. Use list instead:

query = select((f.Name, f.id) for f in Firma).order_by(1)
name_id_pairs = query[:]  # retrieve result list from a query
# another form: name_id_pairs = list(query)

for name, id in name_id_pairs:
    print('Name:', name, ', id:', id)

If all your data processing take place inside db_session (which is recommended), you can retrieve objects instead of tuples from a query:

query = select(f for f in Firma).order_by(lambda: f.name)
firms = list(query)
for f in firms:
    print('Name:', f.name, ', id:', f.id)

Python: Simple order by with Pony ORM

1 Answers1