24

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.

smci
  • 32,567
  • 20
  • 113
  • 146
thaavik
  • 3,257
  • 2
  • 18
  • 25

1 Answers1

39

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

smartexpert
  • 2,625
  • 3
  • 24
  • 41
coleifer
  • 24,887
  • 6
  • 60
  • 75
  • 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 :-( – kev Aug 01 '16 at 07:02
  • 7
    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`. – coleifer Aug 02 '16 at 07:40