How to do query with `WHERE value IN list` in the Python Peewee ORM?

Question

I'm using the (awesome) Python Peewee ORM for my Flask project, but I now got stuck trying to do a query with a where value in ['a', 'b', 'c']. I tried doing it as follows:

MyModel.select().where(MyModel.sell_currency in ['BTC', 'LTC'])

But unfortunately it returns all records in the DB. Any ideas how I could do this?

score 46 · Accepted Answer · edited Sep 04 '18 at 10:45

46

The docs has the answer: x << y will perform x IN y, where y is a list or query. So the final query will look like:

MyModel.select().where(MyModel.sell_currency << ['BTC', 'LTC'])

edited Sep 04 '18 at 10:45

RasmusWL

1,573
13
26

answered Aug 07 '14 at 09:41

ambi

1,256
11
16

1

Excuse me for my ignorance. I just found it after I posted this question.. :S Thanks a million anyway! – kramer65 Aug 07 '14 at 09:52
6

Python always coerces the return value of `x in y` to a boolean, necessitating the use of the `<<` operator. – coleifer Aug 07 '14 at 14:24
@coleifer - Is there no way around this? Although it works now and I am capable of remembering it for next time, using `x in y` is so much more intuitive, especially for newcomers. And intuitive DB-usage is one of the things that Peewee shines with.. (thanks for creating peewee by the way; I've been using it for half a year now and it's awesome!) – kramer65 Aug 11 '14 at 09:02
8

It's a python thing, believe me I'd much rather use ``in``. If you want, you can also write `MyModel.sell_currency.in_(['BTC', 'LTC'])` – coleifer Aug 12 '14 at 14:06
Could you have some sort of intermediate object that implements __in__ (handwaves) along the lines of django F and Q objects? – Stuart Axon Nov 03 '16 at 13:28

score 5 · Answer 2 · edited Aug 01 '18 at 14:13

5

You can also do "IN" expressions with a subquery. For example, to get users whose username starts with "a":

a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

The .in_() method signifies an "IN" query

a_user_tweets = Tweet.select().where(Tweet.user.in_(a_users))

See http://peewee.readthedocs.io/en/latest/peewee/query_operators.html

edited Aug 01 '18 at 14:13

coleifer

24,887
6
60
75

answered Jul 31 '18 at 17:46

Oscar Javier Jimenez

109
1
3

From peewee.py, "in_ = _e(OP.IN)", "__lshift__ = _e(OP.IN)", the method "in_()" makes more sense for me. Thanks for your answer. – MadHatter May 18 '22 at 10:46

How to do query with `WHERE value IN list` in the Python Peewee ORM?

2 Answers2

Linked