CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
salamey
salamey

Reputation: 3811

SQLAlchemy (sql) conditional query

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?

Upvotes: 0

Views: 3264

Answers (3)

SingleNegationElimination
SingleNegationElimination

Reputation: 156138

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

Upvotes: 3

Daniel E.
Daniel E.

Reputation: 2059

use IFNULL(Target,Actual) instead of Target

ISnull is SQL, ifnull is mysql, sorry about that.

Upvotes: 0

haraprasadj
haraprasadj

Reputation: 1087

SQL has a case statement. In this case it would be something like:

CASE MYTARGET WHEN TARGET IS NOT NULL THEN TARGET ELSE ACTUAL

I am not a db expert but I have used this construct. Mytarget is a new column

Upvotes: 0

Related Questions