Peewee syntax for selecting on null field
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
Related videos on Youtube
Comments
-
thaavik about 4 years
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 == ""
andPeers.deleted == "NULL"
. The MySQL syntax should end inWHERE 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 thewhere
clauses chained together. So instead ofPeers.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.
-
Foo Bar User over 10 yearsmaybe
and not Peers.deleted
-
-
kev almost 8 yearsIs 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 :-( -
coleifer almost 8 yearsYes, 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 produceWHERE foo.something IS NOT NULL
.