Sqlalchemy: Produce OR-clause with multiple filter()-Calls

Question

hi,

I'm new to sqlalchemy and could use some help. I'm trying to write a small application for which i have to dynamically change a select-statement. So I do s = select([files]), and then i add filters by s = s.where(files.c.createtime.between(val1, val2)). This works great, but only with an AND-conjunction. So, when I want to have all entries with createtime (between 1.1.2009 and 1.2.2009) OR createtime == 5.2.2009, I got the problem that i don't know how to achieve this with different filter-calls. Because of the programs logic it's not possible to use s= s.where(_or(files.c.createtime.between(val1, val2), files.c.createtime == DateTime('2009-02-01')))

Thanks in advance, Christof

Answer 1

You can build or clauses dynamically from lists:

clauses = []
if cond1:
    clauses.append(files.c.createtime.between(val1, val2))
if cond2:
    clauses.append(files.c.createtime == DateTime('2009-02-01'))
if clauses:
    s = s.where(or_(*clauses))

Answer 2

If you're willing to "cheat" by making use of the undocumented _whereclause attribute on Select objects, you can incrementally specify a series of OR terms by building a new query each time based on the previous query's where clause:

s = select([files]).where(literal(False)) # Start with an empty query.
s = select(s.froms).where(or_(s._whereclause,
        files.c.createtime.between(val1, val2)))
s = select(s.froms).where(or_(s._whereclause,
        files.c.createtime == datetime(2009, 2, 1)))

Building up a union is another option. This is a bit clunkier, but doesn't rely on undocumented attributes:

s = select([files]).where(literal(False)) # Start with an empty query.
s = s.select().union(
        select([files]).where(files.c.createtime.between(val1, val2)))
s = s.select().union(
        select([files]).where(files.c.createtime == datetime(2009, 2, 1)))