CODEWITHSUNDEEP
pythonpyparsing
Hoopes
Hoopes

Reputation: 4155

pyparsing optional subfield

This is a continuation of pyparsing, Each, results name . I've been having great success with pyparsing, but I'm stuck when parsing SQL order by clauses. The problem is that any of the fields can be set to ascending or descending order.

So, a SQL query might look like:

SELECT a FROM x WHERE a = b ...
ORDER BY c, d
ORDER BY c asc, d
ORDER BY c asc, d desc
ORDER BY c, d asc

I've been fumbling around, and the best I've come up with is:

order_dir = oneOf('asc desc', caseless=True)
...
Optional( CaselessKeyword('order by') + columnNameList('order') + Optional(order_dir)('order_dir'))
...

For something like ORDER BY c asc, d desc, this matches that c is the first things i'm ordering by, and asc is my order dir, but doesn't get to the d.

I'd happily take the output as something like ['c asc', 'd desc'] as long as I can still get the columnNameList in there.

Is there any way to handle this with pyparsing? Was my question unclear?

Upvotes: 1

Views: 510

Answers (1)

PaulMcG
PaulMcG

Reputation: 63739

Each orderBy column has a column name, and an optional order direction. There can be multiple columns, separated by commas. You should group each column-direction pair so that the column name and order direction are kept together. Lists of things separated by commas are a natural for pyparsing's delimitedList.

Try changing your clause to:

Optional( CaselessKeyword('order by') + 
        delimitedList(Group(columnNameList('order') + 
                            Optional(order_dir, default="asc")('order_dir')))('orderByColumns')
        )

This will give you a field named 'orderByColumns', containing a sequence of pairs. Even if the asc/desc qualifier is omitted, the default field of the Optional class will insert 'asc'. Read these columns as:

if result.orderByColumns:
    for ocol in result.orderByColumns:
        print "Order by %(order)s (%(order_dir)s)" % ocol

(all untested)

Upvotes: 1

Related Questions