How to append an else case to a dynamically created case statement in sqlalchemy?

Question

Say I have a list of thresholds with corresponding classes like this:

classes = [{1, "1"},
           {2, "4"},
           {3, "7"},
           {4, "8"},
           {5, "9"}]

I want to use this list in a query by defining cases based on its elements. This is my query:

query = select([
            subquery.c.id,
            case([(subquery.c.some_value <= x, y) for x, y in classes
            ]).label("incidence_class")
        ])

This works fine but I have edge cases that I want to catch by using an else case. So what I essentially want to dynamically create is this:

query = select([
            subquery.c.id,
            case((subquery.c.some_value <= 1, "1"),
            case((subquery.c.some_value <= 2, "4"),
            case((subquery.c.some_value <= 3, "7"),
            case((subquery.c.some_value <= 4, "8"),
            case((subquery.c.some_value <= 5, "9"),
            else_=10
            ).label("incidence_class")
        ])

The line that I want to add to the list is "else_=10". I can't just add this to the list that I create with list comprehension because it is code, right?

Thanks in advance.

Answer 1

case() can be constructed as an (unpacked) series of tuples with else_ at the end:

query = select([
            subquery.c.id,
            case(*[(subquery.c.some_value <= x, y) for x, y in classes], 
            else_=10).label("incidence_class")
        ])