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

<预><代码>>>>打印 session.query(sa.func.coalesce(Product.target, Product.actual).label('x'))SELECT 合并(product.target, product.actual) AS x来自产品>>>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')]

<小时>

如果您的缺失值不是空值，而是其他值，您应该使用 CASE 表达式.

<预><代码>>>>打印 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来自产品

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屋！