Filtering or of multiple between in sqlalchemy

Question

I have a list of tuples like this:

l = [
     (100, 230),
     (10, 12),
     (7,1320),
     ...
    ]

I want to generate a query in SQLAlchemy that the value field of my table are between 100 , 230 or 10, 12or 7, 1320 and so on.

my table looks like this:

id  | value
----|------
1   | 120
2   | 2
3   | 9
4   | 1245
5   | 4512

in this case I want these ids: 1,3,4.
I did something like this but I cant do the or part so it just filter the rows that matches all criteria

 q = session.query(Table.value)
 for f in l:
     q = q.filter((Table.value.between(f[0], f[1])))

I'm using Python3.6.

Answer 1

SQLAlchemy provides or_ to combine filter clauses:

from sqlalchemy import or_
...
q = session.query(Table.id)
q = q.filter(or_(Table.value.between(100, 230), Table.value.between(10, 12)))

According to the documentation, or_ actually receives variable number of clauses, so you can use list comprehension + unpacking technique to pass your filter boundaries to it as following:

q = q.filter(or_(*[Table.value.between(left, right) for left, right in l]))

The * is the unpack operator, it will unpack the arguments out of a list or tuple and pass them as separate positional arguments to a function call.