SQLAlchemy (sql) 条件查询 [英] SQLAlchemy (sql) conditional query
问题描述
我有这个问题,我有一个像这样的表 product
有两列 target
和 actual
,我如何构建查询和告诉它:if target == '' then select from actual
.更清楚地说,这是我的表 product
与两列:
实际 |目标---------------p12 |<空>p14 |小时20p16 |<空>p16 |<空>p16 |<空>p16 |<空>p16 |<空>
所以我想选择例如一个值xx",我如何在 SQLAlchemy(或 sql)中建立这个条件,告诉它在 target
列中查找它,但如果单元格是空(为空)然后在 actual
列单元格中查找它.
这可能吗?
听起来像你想要的 COALESCE()
,它接受多个参数并返回第一个不为 null(如果所有参数都为 null,则返回 null)为空)
假设一个合理的设置:
import sqlalchemy as sa从 sqlalchemy.ext.declarative 导入 declarative_baseBase = declarative_base()类产品(基础):__tablename__ = "产品"id = sa.Column(sa.Integer, primary_key=True)实际 = sa.Column(sa.String)目标 = sa.Column(sa.String)
使用sqlalchemy.func.coalesce()
:
<小时>
如果您的缺失值不是空值,而是其他值,您应该使用 CASE
表达式.
I have this problem, I have a table product
like this with two columns target
and actual
, how can I build a query and tell it : if target == '' then select from actual
. To be clearer, this is my table product
with the two columns :
actual | target
---------------
p12 | <null>
p14 | h20
p16 | <null>
p16 | <null>
p16 | <null>
p16 | <null>
p16 | <null>
So I would like to select for example a value 'xx', how can I build this condition in SQLAlchemy (or sql) telling it to look for it in column target
but if the cell is empty (is null) then look for it inside the actual
column cell.
Is this possible?
sounds like you want COALESCE()
, which takes several arguments and returns the first which is not null (or null if all args are null)
Presuming a reasonable setup:
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Product(Base):
__tablename__ = "product"
id = sa.Column(sa.Integer, primary_key=True)
actual = sa.Column(sa.String)
target = sa.Column(sa.String)
use sqlalchemy.func.coalesce()
:
>>> print session.query(sa.func.coalesce(Product.target, Product.actual).label('x'))
SELECT coalesce(product.target, product.actual) AS x
FROM product
>>> session.query(sa.func.coalesce(Product.target, Product.actual).label('x')).all()
[(u'p12'), (u'h20'), (u'p16'), (u'p16'), (u'p16'), (u'p16'), (u'p16')]
edit: if your missing values are not null, but some other value, you should use a CASE
expression.
>>> print session.query(sa.case([(Product.target == '', Product.actual)], else_=Product.target))
SELECT CASE WHEN (product.target = :target_1) THEN product.actual ELSE product.target END AS anon_1
FROM product
这篇关于SQLAlchemy (sql) 条件查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!