Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Peewee syntax for selecting on null field

Tags:

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.

like image 931
thaavik Avatar asked Oct 08 '13 22:10

thaavik


1 Answers

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

like image 107
coleifer Avatar answered Oct 10 '22 10:10

coleifer