CASE WHEN with ORM (SQLalchemy)

Question

I am using SQLAlchemy with the ORM paragdim. I don't manage to find a way to do a CASE WHEN instruction. I don't find info about this on the web.

Is it possible ?

Answer 1

Update: In the latest sqlalchemy, no [] is needed in case statement

case(
    [
      (orderline.c.qty > 100, item.c.specialprice),
      (orderline.c.qty > 10, item.c.bulkprice),
    ], 
   else_=item.c.regularprice,
)

should be changed to

case(
    (orderline.c.qty > 100, item.c.specialprice),
    (orderline.c.qty > 10, item.c.bulkprice),
    else_=item.c.regularprice,
)

otherwise, you may have this error: sqlalchemy.exc.ArgumentError: The "whens" argument to case(), when referring to a sequence of items, is now passed as a series of positional elements, rather than as a list.

Answer 2

I got this to work with an aggregate function, in this case func.sum

My Example Code

from sqlalchemy import func, case

my_case_stmt = case(
    [
        (MyTable.hit_type.in_(['easy', 'medium']), 1),
        (MyTable.hit_type == 'hard', 3)
    ]
)

score = db.session.query(
    func.sum(my_case_stmt)
).filter(
    MyTable.success == 1
)

return score.scalar()

My Use Case

MyTable looks like this:

|   hit_type     |  success |  
-----------------------------  
|   easy         |   1      |  
|   medium       |   1      |  
|   easy         |   0      |  
|   hard         |   1      |  
|   easy         |   0      |
|   easy         |   1      |  
|   medium       |   1      |  
|   hard         |   1      |

score is computed as such: score = num_easy_hits + num_medium_hits + (3 * num_hard_hits)

4 successful easy/medium hits and 2 successful hard hits gives you (4 + (2*3)) = 10

Answer 3

Here is the link in the doc: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.Case

but it confused me to see those examples, and there is no runnable code. I have try many times, and I have met many kinds of problem.

Finally, I found two ways to implement "Case when" within sqlalchemy.

The first way:

By the way, my occasion is I need to mask the phone field depending on if the user has logged in.

    @staticmethod
    def requirement_list_common_query(user=None):
      `enter code here`  phone_mask = case(
            [
                (db.true() if user else db.false(), Requirement.temp_phone),
            ],
            else_=func.concat(func.left(Requirement.temp_phone, 3), '****', func.right(Requirement.temp_phone, 4))
        ).label('temp_phone')
        query = db.session.query(Requirement.company_id,
                                 Company.uuid.label('company_uuid'),
                                 Company.name.label('company_name'),
                                 Requirement.uuid,
                                 Requirement.title,
                                 Requirement.content,
                                 Requirement.level,
                                 Requirement.created_at,
                                 Requirement.published_at,
                                 Requirement.end_at,
                                 Requirement.status,
                                 # Requirement.temp_phone,
                                 phone_mask,
                                 User.name.label('user_name'),
                                 User.uuid.label('user_uuid')
                                 )
        query = query.join(Company, Company.id == Requirement.company_id) \
            .join(User, User.id == Requirement.user_id)
        return query

Requirement is my one of my models. the user argument in the method 'requirement_list_common_query' is the logged-in user if the user has logged in.

the second way: the occasion here is I want to classify the employees depend on their income.

the models are:

class Dept(Base):
    __tablename__ = 'dept'
    deptno = Column(Integer, primary_key=True)
    dname = Column(String(14))
    loc = Column(String(13))

    def __repr__(self):
        return str({
            'deptno': self.deptno,
            'dname': self.dname,
            'loc': self.loc
        })


class Emp(Base):
    __tablename__ = 'emp'
    empno = Column(Integer, primary_key=True)
    ename = Column(String(10))
    job = Column(String(9))
    mgr = Column(Integer)
    hiredate = Column(Date)
    sal = Column(DECIMAL(7, 2))
    comm = Column(DECIMAL(7, 2))
    deptno = Column(Integer, ForeignKey('dept.deptno'))

    def __repr__(self):
        return str({
            'empno': self.empno,
            'ename': self.ename,
            'job': self.job,
            'deptno': self.deptno,
            'comm': self.comm
        })

Here is the code:

from sqlalchemy import text
income_level = case(
    [
        (text('(emp.sal + ifnull(emp.comm,0))<1500'), 'LOW_INCOME'),
        (text('1500<=(emp.sal + ifnull(emp.comm,0))<3500'), 'MIDDLE_INCOME'),
        (text('(emp.sal + ifnull(emp.comm,0))>=3500'), 'HIGH_INCOME'),
    ], else_='UNKNOWN'
).label('income_level')
emps = sess.query(Emp.ename, label('income', Emp.sal + func.ifnull(Emp.comm, 0)),
                  income_level).all()
for item in emps:
    print(item.ename, item.income, item.income_level)

why did I use "text"? Because code like this in SQLAlchemy 1.2.8 can't be implemented. I have tried so long and I can't find way like this, as @van has said:

case([(orderline.c.qty > 100, item.c.specialprice),
      (orderline.c.qty > 10, item.c.bulkprice)
    ], else_=item.c.regularprice)
case(value=emp.c.type, whens={
        'engineer': emp.c.salary * 1.1,
        'manager':  emp.c.salary * 3,
    })

hopes it will help!

Answer 4

See sqlalchemy.sql.expression.case function and more examples on the documentation page. But it would look like this (verbatim from the documentation linked to):

case([(orderline.c.qty > 100, item.c.specialprice),
      (orderline.c.qty > 10, item.c.bulkprice)
    ], else_=item.c.regularprice)
case(value=emp.c.type, whens={
        'engineer': emp.c.salary * 1.1,
        'manager':  emp.c.salary * 3,
    })

edit-1: (answering the comment) Sure you can, see example below:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    first_name = Column(String)
    last_name = Column(String)

xpr = case([(User.first_name != None, User.first_name + " " + User.last_name),],
        else_ = User.last_name).label("full_name")

qry = session.query(User.id, xpr)
for _usr in qry:
    print _usr.fullname

Also see Using a hybrid for an example of case used in the hybrid properties.