Python sqlalchemy.case() Examples

def collate(expression, collation):
    """Return the clause ``expression COLLATE collation``.

    e.g.::

        collate(mycolumn, 'utf8_bin')

    produces::

        mycolumn COLLATE utf8_bin

    The collation expression is also quoted if it is a case sensitive
    identifier, e.g. contains uppercase characters.

    .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
       expressions if they are case sensitive.

    """

    expr = _literal_as_binds(expression)
    return BinaryExpression(
        expr,
        CollationClause(collation),
        operators.collate, type_=expr.type) 

def collate(expression, collation):
    """Return the clause ``expression COLLATE collation``.

    e.g.::

        collate(mycolumn, 'utf8_bin')

    produces::

        mycolumn COLLATE utf8_bin

    The collation expression is also quoted if it is a case sensitive
    identifier, e.g. contains uppercase characters.

    .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
       expressions if they are case sensitive.

    """

    expr = _literal_as_binds(expression)
    return BinaryExpression(
        expr,
        ColumnClause(collation),
        operators.collate, type_=expr.type) 

def test_persistent_access_two(self):
        User, Address = self.classes.User, self.classes.Address
        s = Session()

        def go():
            u1 = User(id=1, addresses=[Address(id=1), Address(id=2)])
            u2 = s.merge(u1)
            a1 = u2.addresses[0]
            assert a1.user is u2
            a2 = u2.addresses[1]
            assert a2.user is u2

        self.assert_sql_count(testing.db, go, 4)

    # "pending" - we get at an Address that is new- user_id should be
    # None.  But in this case the set attribute on the forward side
    # already sets the backref.  commenting out the "skip bidirectional"
    # check emits SQL again for the other two Address objects already
    # persistent. 

def setup_classes(cls):
        Base = cls.DeclarativeBasic

        class A(fixtures.ComparableEntity, Base):
            __tablename__ = "a"

            id = Column(
                Integer, primary_key=True, test_needs_autoincrement=True
            )
            discriminator = Column(String(50), nullable=False)
            child_id = Column(Integer, ForeignKey("a.id"))
            child = relationship("A")

            p_a = case([(discriminator == "a", "a")], else_="b")

            __mapper_args__ = {
                "polymorphic_identity": "a",
                "polymorphic_on": p_a,
            }

        class B(A):
            __mapper_args__ = {"polymorphic_identity": "b"} 

def collate(expression, collation):
    """Return the clause ``expression COLLATE collation``.

    e.g.::

        collate(mycolumn, 'utf8_bin')

    produces::

        mycolumn COLLATE utf8_bin

    The collation expression is also quoted if it is a case sensitive
    identifier, e.g. contains uppercase characters.

    .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
       expressions if they are case sensitive.

    """

    expr = coercions.expect(roles.ExpressionElementRole, expression)
    return BinaryExpression(
        expr, CollationClause(collation), operators.collate, type_=expr.type
    ) 

def test_related_eagerload_against_text(self, add_columns, loader_option):
        # new in 1.4.   textual selects have columns so subqueryloaders
        # and selectinloaders can join onto them.   we add columns
        # automatiacally to TextClause as well, however subqueryloader
        # is not working at the moment due to execution model refactor,
        # it creates a subquery w/ adapter before those columns are
        # available.  this is a super edge case and as we want to rewrite
        # the loaders to use select(), maybe we can get it then.
        User = self.classes.User

        text_clause = text("select * from users")
        if add_columns:
            text_clause = text_clause.columns(User.id, User.name)

        s = create_session()
        q = (
            s.query(User)
            .from_statement(text_clause)
            .options(loader_option(User.addresses))
        )

        def go():
            eq_(set(q.all()), set(self.static.user_address_result))

        self.assert_sql_count(testing.db, go, 2) 

def test_plain(self):
        # control case
        class Base(object):
            pass

        class Sub(Base):
            pass

        mapper(Base, base)
        mapper(Sub, subtable, inherits=Base)

        # Sub gets a "base_id" property using the "base_id"
        # column of both tables.
        eq_(
            class_mapper(Sub).get_property("base_id").columns,
            [subtable.c.base_id, base.c.base_id],
        ) 

def test_table_to_alias_8(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(
                case([(5, t1.c.col2)], value=t1.c.col1, else_=t1.c.col1)
            ),
            "CASE t1alias.col1 WHEN :param_1 THEN "
            "t1alias.col2 ELSE t1alias.col1 END",
        ) 

def test_table_to_alias_7(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(case([(t1.c.col1 == 5, t1.c.col2)], else_=t1.c.col1)),
            "CASE WHEN (t1alias.col1 = :col1_1) THEN "
            "t1alias.col2 ELSE t1alias.col1 END",
        ) 

def apply_ca_filters(query, filters, user_join_condition):

    # get all custom attributes and create pivot table
    new_cs = [CustomAttributeUserStorage.user_id]
    for value in db.session.query(CustomAttributeUserStorage.name).distinct():
        value = value[0]
        new_cs.append(
             func.max(case(
                [(CustomAttributeUserStorage.name == value, CustomAttributeUserStorage.value)],
                else_=None
            )).label(value)
        )

    # join pivot table of custom attributes
    pivot = db.session.query(*new_cs).group_by(CustomAttributeUserStorage.user_id).subquery()
    query = query.outerjoin(pivot, user_join_condition == pivot.c.user_id)
    
    for batches in filters:
        to_batch = []
        for _filt in batches:
            column = _filt[0]
            comparator = _filt[1]
            val = _filt[2]

            if comparator == 'EQ':
                val = val if isinstance(val, list) else [val]
                val = [f'\"{element}\"' for element in val] # needs ot be in form '"{item}"' for json string match
                to_batch.append(pivot.c[column].in_(val))
            elif comparator == 'GT':
               to_batch.append(pivot.c[column] > val)
            elif comparator == "LT":
                to_batch.append(pivot.c[column] < val)
        query = query.filter(or_(*to_batch))

    return query 

def test_compare_tables(self):
        is_true(table_a.compare(table_a_2))

        # the "proxy" version compares schema tables on metadata identity
        is_false(table_a.compare(table_a_2, use_proxies=True))

        # same for lower case tables since it compares lower case columns
        # using proxies, which makes it very unlikely to have multiple
        # table() objects with columns that compare equally
        is_false(
            table("a", column("x", Integer), column("q", String)).compare(
                table("a", column("x", Integer), column("q", String)),
                use_proxies=True,
            )
        ) 

def test_case(self):
        c = case([(self.criterion, self.column)], else_=self.column)
        self._do_test(c) 

def test_polymorphic_on_expr_explicit_map(self):
        t2, t1 = self.tables.t2, self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case([(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")])
        mapper(
            Parent,
            t1,
            properties={"discriminator": column_property(expr)},
            polymorphic_identity="parent",
            polymorphic_on=expr,
        )
        mapper(Child, t2, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_polymorphic_on_expr_implicit_map_no_label_joined(self):
        t2, t1 = self.tables.t2, self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case([(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")])
        mapper(Parent, t1, polymorphic_identity="parent", polymorphic_on=expr)
        mapper(Child, t2, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_polymorphic_on_expr_implicit_map_w_label_joined(self):
        t2, t1 = self.tables.t2, self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case(
            [(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")]
        ).label(None)
        mapper(Parent, t1, polymorphic_identity="parent", polymorphic_on=expr)
        mapper(Child, t2, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_polymorphic_on_expr_implicit_map_w_label_single(self):
        """test that single_table_criterion is propagated
        with a standalone expr"""
        t1 = self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case(
            [(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")]
        ).label(None)
        mapper(Parent, t1, polymorphic_identity="parent", polymorphic_on=expr)
        mapper(Child, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_polymorphic_on_column_prop(self):
        t2, t1 = self.tables.t2, self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case([(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")])
        cprop = column_property(expr)
        mapper(
            Parent,
            t1,
            properties={"discriminator": cprop},
            polymorphic_identity="parent",
            polymorphic_on=cprop,
        )
        mapper(Child, t2, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_polymorphic_on_column_str_prop(self):
        t2, t1 = self.tables.t2, self.tables.t1
        Parent, Child = self.classes.Parent, self.classes.Child
        expr = case([(t1.c.x == "p", "parent"), (t1.c.x == "c", "child")])
        cprop = column_property(expr)
        mapper(
            Parent,
            t1,
            properties={"discriminator": cprop},
            polymorphic_identity="parent",
            polymorphic_on="discriminator",
        )
        mapper(Child, t2, inherits=Parent, polymorphic_identity="child")

        self._roundtrip(parent_ident="p", child_ident="c") 

def test_update_using_case(self):
        Document = self.classes.Document
        s = Session()

        subq = (
            s.query(func.max(Document.title).label("title"))
            .group_by(Document.user_id)
            .scalar_subquery()
        )

        # this would work with Firebird if you do literal_column('1')
        # instead
        case_stmt = case([(Document.title.in_(subq), True)], else_=False)
        s.query(Document).update(
            {"flag": case_stmt}, synchronize_session=False
        )

        eq_(
            set(s.query(Document.id, Document.flag)),
            set(
                [
                    (1, True),
                    (2, False),
                    (3, False),
                    (4, True),
                    (5, True),
                    (6, False),
                ]
            ),
        ) 

def test_columns_augmented_sql_four(self):
        User, Address = self.classes.User, self.classes.Address

        sess = create_session()

        q = (
            sess.query(User)
            .join("addresses")
            .distinct(Address.email_address)
            .options(joinedload("addresses"))
            .order_by(desc(Address.email_address))
            .limit(2)
        )

        # but for the subquery / eager load case, we still need to make
        # the inner columns available for the ORDER BY even though its
        # a DISTINCT ON
        self.assert_compile(
            q,
            "SELECT anon_1.users_id AS anon_1_users_id, "
            "anon_1.users_name AS anon_1_users_name, "
            "anon_1.addresses_email_address AS "
            "anon_1_addresses_email_address, "
            "addresses_1.id AS addresses_1_id, "
            "addresses_1.user_id AS addresses_1_user_id, "
            "addresses_1.email_address AS addresses_1_email_address "
            "FROM (SELECT DISTINCT ON (addresses.email_address) "
            "users.id AS users_id, users.name AS users_name, "
            "addresses.email_address AS addresses_email_address "
            "FROM users JOIN addresses ON users.id = addresses.user_id "
            "ORDER BY addresses.email_address DESC  "
            "LIMIT %(param_1)s) AS anon_1 "
            "LEFT OUTER JOIN addresses AS addresses_1 "
            "ON anon_1.users_id = addresses_1.user_id "
            "ORDER BY anon_1.addresses_email_address DESC, addresses_1.id",
            dialect="postgresql",
        ) 

def test_filter_with_detached_non_pk_col_is_default_null(self):
        self._fixture1()
        Dingaling, HasDingaling = (
            self.classes.Dingaling,
            self.classes.HasDingaling,
        )
        s = Session()
        d = Dingaling()
        s.add(d)
        s.flush()
        s.commit()
        d.id
        s.expire(d, ["data"])
        s.expunge(d)
        assert "data" not in d.__dict__
        assert "id" in d.__dict__

        q = s.query(HasDingaling).filter_by(dingaling=d)

        # this case we still can't handle, object is detached so we assume
        # nothing

        assert_raises_message(
            sa_exc.StatementError,
            r"Can't resolve value for column dingalings.data on "
            r"object .*Dingaling.* the object is detached and "
            r"the value was expired",
            q.all,
        ) 

def test_case(self):
        User = self.classes.User
        session = Session()
        with self._assert_bind_args(session, expect_mapped_bind=True):
            session.query(case([(User.name == "x", "C")], else_="W")).all() 

def receberConta(self):

        try:

            # Abrindo Sessao
            conecta = Conexao()
            sessao = conecta.Session()

            # Selecionando ID
            row = sessao.query(ContaAReceber).get(self.id)

            # Update Status se valor recebido igual ou maior que valor parcela
            status = case([
                (ContaAReceber.valor_recebido >= row.valor, '1')
            ], else_='2'
            )

            # Query
            row.forma_pagamento = self.formaPagamento
            row.data_recebimento = self.dataRecebimento
            row.valor_recebido = ContaAReceber.valor_recebido + self.valorRecebido
            row.pagamento = status

            # Executando a query
            sessao.commit()

            # Fechando a Conexao
            sessao.close()

        except IntegrityError as err:
            print(err) 

def _setup_polymorphic_on_mappers(self):
        employee_mapper = mapper(
            self.classes.Employee,
            self.tables.employees,
            polymorphic_on=case(
                value=self.tables.employees.c.type,
                whens={
                    "E": "employee",
                    "M": "manager",
                    "G": "engineer",
                    "R": "engineer",
                },
            ),
            polymorphic_identity="employee",
        )
        mapper(
            self.classes.Manager,
            inherits=employee_mapper,
            polymorphic_identity="manager",
        )
        mapper(
            self.classes.Engineer,
            inherits=employee_mapper,
            polymorphic_identity="engineer",
        )
        self.sess = sessionmaker()() 

def apply_map(self, map_):
        return self

# for backwards compatibility in case
# someone is re-implementing the
# _truncated_identifier() sequence in a custom
# compiler 

def Pagar(self):

        try:

            # Abrindo Sessao
            conecta = Conexao()
            sessao = conecta.Session()

            # Selecionando Id
            row = sessao.query(Compra).get(self.id)

            # Update status Pagamento
            status = case([
                (Compra.valor_pago >= Compra.valor_total, '1')
            ], else_='2'
            )

            row.valor_pago = Compra.valor_pago + self.valorPago
            row.pagamento = status

            # Executando a Query
            sessao.commit()

            # Fechando a Conexao
            sessao.close()

        except IntegrityError as err:
            print(err)

    # Lista de Pedidos a receber hoje 

def _make_proxy(
        self,
        selectable,
        name=None,
        name_is_truncatable=False,
        disallow_is_literal=False,
        **kw
    ):
        # the "is_literal" flag normally should never be propagated; a proxied
        # column is always a SQL identifier and never the actual expression
        # being evaluated. however, there is a case where the "is_literal" flag
        # might be used to allow the given identifier to have a fixed quoting
        # pattern already, so maintain the flag for the proxy unless a
        # :class:`.Label` object is creating the proxy.  See [ticket:4730].
        is_literal = (
            not disallow_is_literal
            and self.is_literal
            and (
                # note this does not accommodate for quoted_name differences
                # right now
                name is None
                or name == self.name
            )
        )
        c = self._constructor(
            coercions.expect(roles.TruncatedLabelRole, name or self.name)
            if name_is_truncatable
            else (name or self.name),
            type_=self.type,
            _selectable=selectable,
            is_literal=is_literal,
        )
        c._propagate_attrs = selectable._propagate_attrs
        if name is None:
            c.key = self.key
        c._proxies = [self]
        if selectable._is_clone_of is not None:
            c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
        return c.key, c 

def _typeof(t, expr):
    (arg,) = expr.op().args
    sa_arg = t.translate(arg)
    typ = sa.cast(sa.func.pg_typeof(sa_arg), sa.TEXT)

    # select pg_typeof('asdf') returns unknown so we have to check the child's
    # type for nullness
    return sa.case(
        [
            ((typ == 'unknown') & (arg.type() != dt.null), 'text'),
            ((typ == 'unknown') & (arg.type() == dt.null), 'null'),
        ],
        else_=typ,
    ) 

def _regex_extract(t, expr):
    string, pattern, index = map(t.translate, expr.op().args)
    result = sa.case(
        [
            (
                sa.func.textregexeq(string, pattern),
                sa.func.regex_extract(string, pattern, index + 1),
            )
        ],
        else_='',
    )
    return result 

def _cardinality(array):
    return sa.case(
        [(array.is_(None), None)],  # noqa: E711
        else_=sa.func.coalesce(sa.func.array_length(array, 1), 0),
    )