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?

SingleNegationElimination · Accepted Answer · 2014-03-28T12:32:32.167

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

one question please, is there a function that does the same but with `empty` values (value = '')? — salamey, Mar 28 '14 at 08:34

Daniel E. · Answer 2 · 2014-03-27T17:17:02.853

0

use IFNULL(Target,Actual) instead of Target

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

edited Mar 27 '14 at 17:17

answered Mar 27 '14 at 17:02

Daniel E.

2,029
3
22
28

ISNULL() takes only one parameter, did you IFNULL()? – salamey Mar 27 '14 at 17:09

score 0 · Answer 3 · answered Mar 27 '14 at 17:14

0

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

answered Mar 27 '14 at 17:14

haraprasadj

1,059
1
8
17

SQLAlchemy (sql) conditional query

3 Answers3

Linked