SQLAlchemy (sql) conditional query

Question

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?

Answer 1

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

• salamey: one question please, is there a function that does the same but with empty values (value = '')?

Answer 2

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

Answer 3

use IFNULL(Target,Actual) instead of Target

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

• salamey: ISNULL() takes only one parameter, did you IFNULL()?