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?

Bridal

Answer 1

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'])

Pearson

Excuse me for my ignorance. I just found it after I posted this question.. :S Thanks a million anyway! – Bridal

Python always coerces the return value of x in y to a boolean, necessitating the use of the << operator. – Tenderize

@Tenderize - 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!) – Bridal

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']) – Tenderize

Could you have some sort of intermediate object that implements in (handwaves) along the lines of django F and Q objects? – Huggins

Answer 2

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

Tutti

From peewee.py, "in_ = _e(OP.IN)", "lshift = _e(OP.IN)", the method "in_()" makes more sense for me. Thanks for your answer. – Peggiepeggir