SQLAlchemy cast boolean column to int

Question

I have a table of user records and I wish to see the average when some condition is met. the condition column is a boolean. In Postgresql I could cast it easily:

select id, avg(cond::INT) from table group by id;

But in SQLAlchemy I couldn't find anything equivalent to ::INT.
How do i deal with the type conversion? I have

orm_query(Table.id, func.avg(Table.cond))

which, of course, returns an error.

Answer 1

The <expr>::<type> is Postgresql specific syntax, CAST(<expr> AS <type>) the standard SQL equivalent. In SQLAlchemy you can produce a type cast expression with a variety of ways. In this case you can simply:

orm_query(Table.id, func.avg(Table.cond.cast(Integer)))

Another option is to use the cast() construct.

Answer 2

You can use cast fuction to convert boolean to integer:

from sqlalchemy.sql.expression import cast
import sqlalchemy

orm_query(Table.id, func.avg(cast(Table.cond, sqlalchemy.Integer)))