CODEWITHSUNDEEP
pythonsqlalchemy
Romeo M.
Romeo M.

Reputation: 3278

Sqlalchemy select condition

I have a mapper on a table and I want to define a column_property which should select True or False whether the entity has some propertie or not:

mapper( Person, persons_table, properties = {
    'administrator': column_property( 
        select( 
            [True if roles_table.c.is_admin or roles_table.c.id == 1 else False],
            roles_table.c.id == persons_table.c.role_id
        ).label( 'administrator' )
        )
} )

Is this something I can do? I'm more interested in this part: [True if roles_table.c.is_admin or roles_table.c.id == 1 else False], which lets me set a value to the column based on the condition.

Upvotes: 5

Views: 4471

Answers (1)

Ferdinand Beyer
Ferdinand Beyer

Reputation: 67217

Your SELECT-expression is actually a Python expression:

select([True if roles_table.c.is_admin or roles_table.c.id == 1 else False],
       roles_table.c.id == persons_table.c.role_id)

sqlalchemy.select() will see this as:

select([True], some_expression_object)

since the column object roles_table.c.is_admin will evaluate to True in Boolean context. I don't know from the top of my head how SQLAlchemy will interpret this, but it will surely not work as you intended.

You will have to rewrite this expression so that it will correspond to plain SQL, using sqlalchemy.sql.expression.case() instead of if ... else ...:

column_property(
    select([case([(roles_table.c.is_admin, 1),
                  (roles_table.c.id == 1, 1)], else_=0)],
           roles_table.c.id == persons_table.c.role_id))

In your case, however, there might be a much simpler solution. Person and Role seem to have a N:1 relation (one person has exactly one role). I assume there is a orm.relationship Person.role to get a person's role.

Why don't you just add a plain Python property:

class Person:
    # ...

    @property
    def administrator(self):
        return self.role and (self.role.is_admin or self.role.id == 1)

Upvotes: 5

Related Questions