CODEWITHSUNDEEP
postgresqlsqlalchemy
l0b0
l0b0

Reputation: 58848

How to add a CASE column to SQLAlchemy output?

So far I've got basically the following:

MyTable.query
    .join(…)
    .filter(…)

The filter has a complicated case insensitive prefix check:

or_(*[MyTable.name.ilike("{}%".format(prefix)) for prefix in prefixes])

Now I have to retrieve the matching prefix in the result. In pseudo-SQL:

SELECT CASE
           WHEN strpos(lower(my_table.foo), 'prefix1') = 0 THEN 'prefix1'
           WHEN …
       END AS prefix
 WHERE prefix IS NOT NULL;

The SQLAlchemy documentation demonstrates how to use CASE within WHERE, which seems like a strange edge case, and not how to use it to get a new column based on an existing one.

The goal is to avoid duplicating the logic and prefix names anywhere.

Upvotes: 3

Views: 1806

Answers (1)

Giedrius.S
Giedrius.S

Reputation: 80

You know that case is an expression. You can use it in any place where SQL allows it, and link to docs you have provided show how to construct the case expression.

from sqlalchemy import case, literal
from sqlalchemy.orm import Query

q = Query(case([
    (literal(1) == literal(1), True),
], else_ = False))

print(q)

SELECT CASE WHEN (:param_1 = :param_2) THEN :param_3 ELSE :param_4 END AS anon_1

Upvotes: 4

Related Questions