SQLAlchemy text() with case() : Column expression expected, got sqlalchemy.sql.elements.TextClause

user_5180644 0
Viewed 481

I have a use case where I'm trying to generate a SQL Query using SQLALchemy's Expression language and part of the query is provided by user which is a condition. Example - 'rooms > 10' as a string, where 'room' is a column in a table. Example -

case(
    ('rooms > 10', 0),
    else_=1
).label(col_name)

Now, my first thought was to wrap this into a text() function by SQLAlchemy. Example -

case(
    (text('rooms > 10'), 0),
    else_=1
).label(col_name)

But as per documentation(https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-text-fragments-inside-bigger-statements) we can use that within select() and not with case().

Is there any way to accomplish this?

Thanks!

sqlalchemy python
1 Answers

One work around is to convert whole case statement into a string and use Text() on it.

text(f'CASE WHEN room > 10 THEN 0 ELSE 1 END AS {col_name}')

But this will obviously fail if some DB's SQL syntax is different for CASE.

user_51806440
Related