在 SQLAlchemy 中选择 NULL 值 [英] Select NULL Values in SQLAlchemy

问题描述

这是我的 (PostgreSQL) 表 --

Here's my (PostgreSQL) table --

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 婚姻状态的人.

I want to select all people that are not known to be married, i.e., including those with NULL marriage_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 访问它 --

The problem is that I'm accessing it from SQLAlchemy with --

...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 值?

How should I select NULL values through SQLAlchemy?

推荐答案

(如 @augurar 所示):因为sqlalchemy 使用魔术方法(操作符重载)来创建SQL结构，它只能处理!=等操作符或 ==，但不能使用 is(这是一个非常有效的 Python 结构).

(as indicated by @augurar): Because sqlalchemy uses magic methods (operator overloading) to create SQL constructs, it can only handle operator such as != or ==, but is not able to work with is (which is a very valid Python construct).

因此，要使其与 sqlalchemy 一起使用，您应该使用:

Therefore, to make it work with sqlalchemy, you should use:

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

，基本上用== None替换is None.在这种情况下，您的查询将正确转换为以下 SQL:

, basically replace the is None with == None. In this case your query will be translated properly to the following 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">文档.

See IS NULL in the documentation.

这篇关于在 SQLAlchemy 中选择 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！