Peewee syntax for selecting on null field
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.
Answer
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: http://peewee.readthedocs.org/en/latest/peewee/querying.html#query-operators