Peewee syntax for selecting on null field

Question

I have researched this everywhere and can't seem to find an answer. I hope I haven't duplicated this (as it's my first question on SO).

I am trying to write a select query with Peewee that would normally go ... WHERE foo = NULL; in SQL world.

MySQL looks like this:

+-----------+-------------+------+-----+---------+----------------+  
| Field     | Type        | Null | Key | Default | Extra          |  
+-----------+-------------+------+-----+---------+----------------+  
| id        | bigint(20)  | NO   | PRI | NULL    | auto_increment |  
| user      | varchar(30) | NO   |     | NULL    |                |  
| peer      | varchar(30) | NO   |     | NULL    |                |  
| deleted   | date        | YES  |     | NULL    |                |  
| confirmed | date        | YES  |     | NULL    |                |  
+-----------+-------------+------+-----+---------+----------------+  

My select query looks like this:

Peers.select().where(Peers.user == 'foo' and Peers.deleted is None)

But it doesn't work! I've tried Peers.deleted == "" and Peers.deleted == "NULL". The MySQL syntax should end in WHERE deleted is NULL; but nothing in Peewee seems to be doing that.

Can anyone help? What am I missing from the docs?

Updated from Foo Bar User's comment: and not Peers.deleted didn't work, but it led me to more information. It seems that peewee wants the where clauses chained together. So instead of

Peers.select().where(Peers.user == 'foo' and Peers.deleted is None)

it should be:

Peers.select().where(Peers.user == 'foo').where(Peers.deleted is None)

Sadly, that still doesn't yield the right syntax to select on null rows in deleted.

Anlage

maybe and not Peers.deleted – Mythomania

Answer 1

First off you must use the bitwise operands for "and" and "or". Then for is null, use >>:

Peers.select().where((Peers.user == 'foo') & Peers.deleted.is_null())

For not null you would negate it:

Peers.select().where(Peers.deleted.is_null(False))

It is documented: https://peewee.readthedocs.io/en/latest/peewee/query_operators.html#query-operators

Demonstrable

Is there an alternative to >> None? It's not intuitive at all and as I don't use peewee every day I have to look it up every time :-( – Auer

Yes, two ways. If you do .where(Foo.something == None) Peewee will turn it into "IS NULL". Alternatively, you can do .where(Foo.something.is_null(True)). Similarly, .where(Foo.something.is_null(False)) will produce WHERE foo.something IS NOT NULL. – Demonstrable