On 16/10/2013 04:36, Michael Bayer wrote:
On Oct 15, 2013, at 3:22 PM, Chris Withers <[email protected]> wrote:
Hi All,
How would I express the following postgresql query in SQLAlchemy?
select * from observation where '[2013-01-01,2013-01-03)'::tsrange @> period;
I know how to do the right operand:
session.query(Observation).filter(Observation.period.contains(Range(...)))
...but not the other way round.
What am I missing?
well you'd use literal() around it, though I guess we don't coerce that type
which would give you the operators, so maybe type_coerce:
type_coerce(some_range(...), NUMRANGE).contains(other)
eh maybe literal() , actually:
literal(some_range(...), type_=NUMRANGE).contains(other)
They both work:
from sqlalchemy.dialects.postgresql import TSRANGE
from sqlalchemy.sql.expression import type_coerce
session.query(Observation).filter(
type_coerce(Range(date(2013, 1, 1), date(2013, 1, 3)),
TSRANGE).contains(Observation.period)
).count()
from sqlalchemy.sql.expression import literal
session.query(Observation).filter(
literal(Range(date(2013, 1, 1), date(2013, 1, 3)),
type_=TSRANGE).contains(Observation.period)
).count()
I think I prefer the type_coerce version. Thankfully, there are
symmetrical operators in Postgres, and I mapped them all when I did the
SQLAlchemy bits, so the one I really like it:
session.query(Observation).filter(
Observation.period.contained_by(Range(date(2013, 1, 1), date(2013,
1, 3)))
).count()
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
