CODEWITHSUNDEEP
pythonsqlalchemypsycopg2
Denis Steinman
Denis Steinman

Reputation: 7799

Psycopg2 can't adapt type 'BinaryExpression' on using CASE statement in query

I have the next query:

sub_qry = self.session.query(
    TableA.currency,
    func.sum(func.case([(TableB.status_id == EStatus.REPORTED.value, 1)], else_=0)).label('status')
) \
    .filter_by(period_id=period_id) \
    .outerjoin(TableB) \
    .group_by(TableA.currency) \
    .subquery()
result = self.session.query(func.count(sub_qry.c.status)).filter(sub_qry.c.status != 1).first()

But it crashes with the error:

(psycopg2.ProgrammingError) can't adapt type 'BinaryExpression'
[SQL: SELECT count(anon_1.status) AS count_1 
FROM (SELECT table_a.currency AS currency, sum(case(%(case_1)s)) AS status 
FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.table_a_id 
WHERE table_a.period_id = %(period_id_1)s GROUP BY table_a.currency) AS anon_1 
WHERE anon_1.status != %(status_1)s 
 LIMIT %(param_1)s]
[parameters: {'case_1': [(<sqlalchemy.sql.elements.BinaryExpression object at 0x0000022FC0F42700>, 1)], 'period_id_1': 4, 'status_1': 1, 'param_1': 1}]
(Background on this error at: http://sqlalche.me/e/13/f405)

What am I doing wrong? I've checked many samples online and seems all must be okay.

ADDED:
SQL version that works:

select count(r.status)
    from (
        select a.currency, SUM(CASE WHEN b.status_id = 2 THEN 1 else 0 END) as status from table_b b
            right outer join table_a a on a.id = b.table_a_id
            where a.period_id = 4
            group a.currency
    ) r
    where r.status != 1

Upvotes: 3

Views: 998

Answers (1)

Denis Steinman
Denis Steinman

Reputation: 7799

I found my mistake: call of sqlalchemy.func.case() instead of sqlalchemy.case().
It's important! SQLAlchemy will generate a correct SQL-query only in the second case.

P.S. It's a trivial error but I decided to save it here if someone will face same problem.

Upvotes: 8

Related Questions