Operator NOT IN with Peewee

Question

The documentation shows here how to use the IN operator, but I couldn't find how to use the NOT IN operator.

If I put a not << I get a syntax error.

If I put a not <FieldName> << there is a WHERE False instead of a subquery like WHERE (<FieldName> NOT IN (SELECT ....

Here is the output with the documentation examples. The first one is correct, the second and third are wrong.

>>> Tweet.select().where(Tweet.user << a_users).sql()
('SELECT t1."id", t1."user_id", t1."message", t1."created_date", t1."is_published" FROM "tweet" AS t1 WHERE (t1."user_id" IN (SELECT t2."id" FROM "user" AS t2 WHERE (Lower(Substr(t2."username", ?, ?)) = ?)))', [1, 1, 'a'])
>>> Tweet.select().where(not Tweet.user << a_users).sql()
('SELECT t1."id", t1."user_id", t1."message", t1."created_date", t1."is_published" FROM "tweet" AS t1 WHERE ?', [False])
>>> Tweet.select().where(Tweet.user not << a_users).sql()
SyntaxError: invalid syntax

Determined

not in is literally a single operator named not in, it doesn't mean you can put not before any other operator. The fact that Peewee reinterprets << to mean a SQL IN doesn't mean it can change Python syntax. – Affiliate

@Affiliate I know... hence my question – Determined

Answer 1

Simple:

Tweet.select().where(Tweet.user.not_in(a_users))

For slightly different semantics (NOT (x in y)) as opposed to (x NOT IN y):

Tweet.select().where(~(Tweet.user << a_users))

Pinnate

Yeah, that makes sense, ~ is overrideable and doesn't need to return a Boolean. It doesn't seem to be documented anywhere though. – Uhlan

Kindall, can you kindly update or remove you answer? It is not correct and may cause confusion to other readers. – Pinnate

Is there an exists operator? I find it weird that you support window functions but not not and exists – Nineteenth

fn.EXISTS() is how you would accomplish that. – Pinnate

You cannot override not and in because the Python interpreter coerces the return value to a boolean. Obviously I would have preferred to do it that way. – Pinnate

I did think I would need an unary negation operator, and I tried !. I didn't think of ~. Thanks – Determined

Answer 2

I know that this is a "necro-posting", but this question is first hit in Google for peewee not in query, so I would like to add it here:

You can also use not_in method which is described in the doc:

Tweet.select().where(Tweet.user.not_in(a_users))

As for me, it looks much more readable than ~ ... << construct.

Expendable

Hey, thanks for mentioning this! I've updated my answer to include your edits so hopefully more folks will see it. – Pinnate

Answer 3

This has nothing to do with Peewee, really. Peewee is using some Python operators for its own purposes. << is a numeric operator normally, and it doesn't make any sense to take its logical negation. Thus not << is never valid Python syntax.

Your second example is close, but not applies only to Tweet.user (not having higher precedence than <<). Add some parentheses and you get:

Tweet.select().where(not (Tweet.user << a_users)).sql()

Now this still isn't right, as you've discovered (readers: see the comments for some discussion on this). not returns a Boolean value, which is not what is wanted and won't work. Peewee repurposes the ~ operator for this; take a look at @coleifer's answer.

Uhlan

In case you're wondering why Peewee would use << instead of just using in (in which case you could use not in here); the in operator has to return a bool, not a query object or anything else. (What happens if it doesn't return a bool depends on your Python implementation and version; it may be an error, or it may get converted to bool, but either way, it's not very useful…) – Affiliate

I don't understand your answer. The way I understand it, those operators are used by Peewee to create an SQL command, they are not interpreted by Python. Infact if you try your expression you get a ... WHERE False, as in my second case without the parenthesis. – Determined

They can't not be interpreted by Python. Peewee provides special Python objects that override operators such as << to construct a SQL query. In other words, there is nothing in Peewee that ever sees Tweet.user << a_users and does something with it, that's all done by methods on Tweet.user. The result is a kind of domain-specific language (DSL) that works seamlessly with Python because it is Python. Unfortunately, I guessed wrong about a solution to your problem. – Uhlan

Looking at Query operators in the Peewee docs, it doesn't seem to support not, or ~, or a method or function named not_ or unary_not… that can't be true, can it? Well, if it is, Adding user-defined operators shows how you can do it. – Affiliate

@stenci: Also, I'd file a bug against Peewee pointing out that either it has no way to write NOT queries, or (if it does) that it's too hard to figure out what that way is from the docs… – Affiliate