CODEWITHSUNDEEP
sqlalchemy
phihag
phihag

Reputation: 287835

Query for multiple values at once

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

Upvotes: 19

Views: 26445

Answers (3)

enomad
enomad

Reputation: 1145

If you dont know PK columns best solution is to use get but its for one row.

For a non-composite PK

pk = model.__mapper__.primary_key[0]
items = model.query.filter(pk.in_(ids)).all()

Upvotes: 2

waitingkuo
waitingkuo

Reputation: 93784

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.

Upvotes: 38

phihag
phihag

Reputation: 287835

Use in_, like this:

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

Upvotes: 6

Related Questions