0

I need to create a calculated column in my SQL query using SQLAlchemy. The query i would like to get will look like this:

SELECT *, CASE WHEN (age < 18) THEN 1 ELSE 0 END AS kid 
FROM (SELECT TEST_TABLE.index AS index, TEST_TABLE.age AS age, TEST_TABLE.letter AS letter 
FROM TEST_TABLE) AS dataset

Now, I managed to do so with SQLAlchemy like this:

query = select(['*', case([(Column('age').op('<')(18), 1)], else_=0)])
query.select_from(alias(select([*]), name='dataset'))   # I actually pass different params here, but this is an example

But I didn't manage to put another alias() function to make the calculated column that i added be called "kid" like I want. SQLAlchemy generates its own alias for it instead.

I tried:

query = select(['*', case([(alias(Column('age').op('<')(18), name='kid'), 1)], else_=0)])

and:

query = select(['*', alias(case([(Column('age').op('<')(18), 1)], else_=0), name='kid')])

No success there. Any ideas?

NotSoShabby
  • 3,316
  • 9
  • 32
  • 56
  • Case _expression_. – jarlh Oct 24 '18 at 07:22
  • @jarlh please elaborate – NotSoShabby Oct 24 '18 at 07:23
  • A case expression returns a value, just like you use it. A case statement is used to do conditional execution of code, for example in stored procedures - and returns no value. expression <> statement – jarlh Oct 24 '18 at 07:25
  • How does this helps me alias that column? – NotSoShabby Oct 24 '18 at 07:30
  • 2
    I'm not into sqlalchemy but maybe you can use `label()` for the the column: https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.label `alias()` is used in the From Clause e.g. for aliasing a Table – Nebi Oct 24 '18 at 09:35

1 Answers1

1

Like I said in the comment I'm not familiar with sqlalchemy. But maybe this will help you:

query = select(['*', case([(Column('age').op('<')(18), 1)], else_=0).label("kid")])
query.select_from(alias(select([*]), name='dataset'))

But I'm really not sure if the syntax is right here. Maybe also look here: Using alias() for 'select as' in SQLAlchemy

Nebi
  • 306
  • 2
  • 10