Query for multiple values at once

Question

In SQL, I can get a set of rows like this:

SELECT * FROM table WHERE id in (2,3,5,7,11);

How does the equivalent sqlalchemy query look? cls.id in [2,3,5,7,11] evaluates to False, so this code:

q = meta.Session.query(cls)
q = q.filter(cls.id in [2,3,5,7,11])
return q.all()

fails with an exception:

  File "foo.py", line 1234, in findall_by_ids
    q = q.filter(cls.id in [2,3,5,7,11])
  File "<string>", line 1, in <lambda>
  File "/eggs/sqlalchemy/orm/query.py", line 50, in generate
    fn(self, *args[1:], **kw)
  File "/eggs/sqlalchemy/orm/query.py", line 1177, in filter
    "filter() argument must be of type "
ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement
               or string

Answer 1

Use in_

q.filter(cls.id.in_([2, 3, 5, 7, 11]))

Your interpreter said:

ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement
               or string

Those operators have been overwritten and will return what filter need, you can take a look at sqlalchemy.sql.operators. The reason that cls.id in [2, 3, 5, 7, 1] doesn't work is because that in is the operator of [2, 3, 5, 7, 1], it'll return True or False.

Answer 2

Use in_, like this:

q = meta.Session.query(cls)
q = q.filter(cls.id.in_([2,3,5,7,11]))
return q.all()