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

问题描述

这是我的（PostgreSQL）表 -

  test =>创建表人（name varchar主键，
 marriage_status varchar）; 
 
 test =>插入人的价值观（'肯'，'已婚'）; 
 test => insert into people values（'May'，'single'）; 
 test => insert into people values（'Joe'，NULL）; 
  

我想选择不

<$>

p $ p> test => select * from people where marriage_status！='married';
name | marriage_status
------ + -----------------
May | single
（1 row）


当然这样做 -

  test => select * from people where marriage_status！='married'
或marriage_status为NULL; 
 name | marriage_status 
 ------ + ----------------- 
 May | single 
 Joe | 
  

问题是我从SQLAlchemy访问它 -

  ...过滤器（or_（people.marriage_status！='married'，
 people.marriage_status是None））
  

可翻译为

  SELECT people.name as name，
 people.marriage_status as marriage_status 
 FROM people 
 WHERE people.marriage_status！=％（status_1）s或False 
 sqlalchemy.engine。 base.Engine .... {'status_1'：'married'} 
  



<不工作 -

  test => select * from people where marriage_status！='married'
或False; 
 name | marriage_status 
 ------ + ----------------- 
 May | single 
（1 row）
  




  test => select * from people where marriage_status！='married'
或NULL; 
 name | marriage_status 
 ------ + ----------------- 
 May |单个
（1行）
  

如何通过SQLAlchemy选择NULL值？

解决方案

（如 @augurar a>）：因为 sqlalchemy 使用魔术方法（运算符重载）创建 SQL 例如！= 或 == ，但不能使用 code>（这是一个非常有效的Python结构）。

因此，要使它与sqlalchemy一起工作，而不是：

  ...过滤器（or_（people.marriage_status！='married'，people.marriage_status是None））
  

您应该使用：

  ...过滤器（or_（people.marriage_status！='married'，people.marriage_status == 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 =http://www.sqlalchemy.org/docs/05/ormtutorial.html#common-filter-operators>文档。

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);

I want to select all people that are not known to be married, i.e., including those with NULL marriage_status.

This does not work --

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

Of course this does --

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

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

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

which gets translated to --

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'}

And does not work --

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

neither does --

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

How should I select NULL values through SQLAlchemy?

解决方案

(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).

Therefore, to make it work with sqlalchemy, instead of:

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

you should use:

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

, 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 != ?

See IS NULL in the documentation.

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