SQLAlchemy MySQL IF Statement

Question

I'm in the middle of converting an old legacy PHP system to Flask + SQLAlchemy and was wondering how I would construct the following:

I have a model:

class Invoice(db.Model):
   paidtodate = db.Column(DECIMAL(10,2))
   fullinvoiceamount = db.Column(DECIMAL(10,2))
   invoiceamount = db.Column(DECIMAL(10,2))
   invoicetype = db.Column(db.String(10))
   acis_cost = db.Column(DECIMAL(10,2))

The query I need to run is:

SELECT COUNT(*) AS the_count, sum(if(paidtodate>0,paidtodate,if(invoicetype='CPCN' or invoicetype='CPON' or invoicetype='CBCN' or invoicetype='CBON' or invoicetype='CPUB' or invoicetype='CPGU' or invoicetype='CPSO',invoiceamount,
fullinvoiceamount))) AS amount,
SUM(acis_cost) AS cost, (SUM(if(paidtodate>0,paidtodate,invoiceamount))-SUM(acis_cost)) AS profit FROM tblclientinvoices

Is there an SQLAlchemyish way to construct this query? - I've tried googling for Mysql IF statments with SQlAlchemy but drew blanks.

Many thanks!

Answer 1

Use func(documentation) to generate SQL function expression:

qry = select([
        func.count().label("the_count"),

        func.sum(func.IF(
            Invoice.paidtodate>0,
            Invoice.paidtodate,
            # @note: I prefer using IN instead of multiple OR statements
            func.IF(Invoice.invoicetype.in_(
                    ("CPCN", "CPON", "CBCN", "CBON", "CPUB", "CPGU", "CPSO",)
                ),
                Invoice.invoiceamount,
                Invoice.fullinvoiceamount)
            )
        ).label("amount"),

        func.sum(Invoice.acis_cost).label("Cost"),

        (func.sum(func.IF(
            Invoice.paidtodate>0,
            Invoice.paidtodate,
            Invoice.invoiceamount
            ))
            - func.sum(Invoice.acis_cost)
        ).label("Profit"),
    ],
)

rows = session.query(qry).all()
for row in rows:
    print row