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% ';

What SQL backend are you using?

van
– van

2012-01-15 21:54:59 +00:00
Commented Jan 15, 2012 at 21:54 — van
– van, Commented Jan 15, 2012 at 21:54

van · Accepted Answer · 2012-01-15 21:55:01Z

8

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%')
            )
        )

answered Jan 15, 2012 at 21:55

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

leonciokof Over a year ago

Thank you. SQLSoup I'm working with, as I adapt this code to sqlsoup.

shaunc Over a year ago

hmm.. but what if you don't have an id field? how do you simply get "count(*)", possibly in the presence of other things in select list (so you cant use sqlalchemy .count())?

van Over a year ago

@shaunc: You can always do func.count('*') to get a COUNT(*) SQL expression.

Collectives™ on Stack Overflow

Filtering a sql count(*) with SQLAlchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related