SQLAlchemy: Logical Operators with case statement

Here is my mssql code snippet

SELECT
Sum((Case  When me.status not in ('CLOSED','VOID') and me.pc_cd in ('IK','JM')
Then 1 else 0 end)) as current_cd
from
ccd_pvc me with(nolock)  

How would i use the and operator with case statement if i write the above statement in sqlalchemy.

I have tried doing this but did not work

case([and_((ccd_pvc.status.in_(['CLOSED', 'VOID']),ccd_pvc.pc_cd.in_(['IK','JM'])),
literal_column("'greaterthan100'"))])

I have searched through the sqlalchemy documentation but did not find the info on using logical operators with case statement.

The link has some info on this.

Answer

This should get you started:

ccd_pvc = aliased(CcdPvc, name="me")

expr = func.sum(
    case([(and_(
        ccd_pvc.status.in_(['CLOSED', 'VOID']),
        ccd_pvc.pc_cd.in_(['IK', 'JM'])
    ), 1)], else_=0)
).label("current_cd")

q = session.query(expr)