Filtering a sql count(*) with SQLAlchemy

Question

How can I do the following SQL query using SQLAlchemy?

SELECT COUNT (*)
FROM det_factura
WHERE company = 31
  AND date::text LIKE '2011-05% ';

Answer 1

If det_factura is also a mapped object, then this query should do it:

qry = (session.query(func.count(det_factura.id))
        .filter(det_factura.company==31)
        .filter(det_factura.date.like('2010-05%'))
        )

If it is a table instance, the one below should work:

qry = select([func.count(det_factura.id)],
        and_(det_factura.company==31,
             det_factura.date.like('2010-05%')
            )
        )