在SQLAlchemy中选择NULL值

Question

这是我的（PostgreSQL）表-

test=> create table people (name varchar primary key,
                            marriage_status varchar) ;

test=> insert into people values ('Ken', 'married');
test=> insert into people values ('May', 'single');
test=> insert into people values ('Joe', NULL);

我想选择所有 不 知道要结婚的人，即包括那些拥有NULL wedding_status的人。

这并 不能 正常工作-

test=> select * from people where marriage_status != 'married' ; 
 name | marriage_status 
------+-----------------
 May  | single
(1 row)

当然可以-

test=> select * from people where marriage_status != 'married'
       or marriage_status is NULL ; 
 name | marriage_status 
------+-----------------
 May  | single
 Joe  |

问题是我正在使用-从SQLAlchemy访问它

...filter(or_(people.marriage_status!='married',
              people.marriage_status is None))

转换为-

SELECT people.name as name,
       people.marriage_status as marriage_status
FROM people 
WHERE people.marriage_status != %(status_1)s OR False
sqlalchemy.engine.base.Engine.... {'status_1': 'married'}

而且也 没有 工作-

test=> select * from people where marriage_status != 'married'
       or False; 
 name | marriage_status 
------+-----------------
 May  | single
(1 row)

也不-

test=> select * from people where marriage_status != 'married'
       or NULL; 
 name | marriage_status 
------+-----------------
 May  | single
(1 row)

如何通过SQLAlchemy选择NULL值？

Answer 1

（由指示@augurar)）：因为
SQLAlchemy的 使用 魔术方法（操作符重载）
创建SQL结构，但它只能处理操作，如!=或==，但不能与工作is（这是一种非常有效的Python结构）。

因此，要使其与sqlalchemy一起使用，应使用：

...filter(or_(people.marriage_status!='married', people.marriage_status == None))

，基本上取代了is None用== None。在这种情况下，您的查询将被正确转换为以下SQL：

SELECT people.name AS people_name, people.marriage_status AS people_marriage_status 
FROM people 
WHERE people.marriage_status IS NULL OR people.marriage_status != ?

请参阅IS NULL中的文档。