Python sqlalchemy.case方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
def test_session_query(session, table, session_using_test_dataset, table_using_test_dataset):
    for session, table in [(session, table), (session_using_test_dataset, table_using_test_dataset)]:
        col_concat = func.concat(table.c.string).label('concat')
        result = (
            session
            .query(
                table.c.string,
                col_concat,
                func.avg(table.c.integer),
                func.sum(case([(table.c.boolean == True, 1)], else_=0))
            )
            .group_by(table.c.string, col_concat)
            .having(func.avg(table.c.integer) > 10)

        ).all()
        assert len(result) > 0 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
def _add_order_by(self, fragment):
        if not len(self.order_by):
            return fragment

        clauses = []
        for expr in self.order_by:
            key = expr.op()
            sort_expr = key.expr

            # here we have to determine if key.expr is in the select set (as it
            # will be in the case of order_by fused with an aggregation
            if _can_lower_sort_column(self.table_set, sort_expr):
                arg = sort_expr.get_name()
            else:
                arg = self._translate(sort_expr)

            if not key.ascending:
                arg = sa.desc(arg)

            clauses.append(arg)

        return fragment.order_by(*clauses) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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"} 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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],
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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. 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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,
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
def _cardinality(array):
    return sa.case(
        [(array.is_(None), None)],  # noqa: E711
        else_=sa.func.coalesce(sa.func.array_length(array, 1), 0),
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
def _simple_case(t, expr):
    op = expr.op()

    cases = [op.base == case for case in op.cases]
    return _translate_case(t, cases, op.results, op.default) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import case [as 别名]
def _translate_case(t, cases, results, default):
    case_args = [t.translate(arg) for arg in cases]
    result_args = [t.translate(arg) for arg in results]

    whens = zip(case_args, result_args)
    default = t.translate(default)

    return sa.case(whens, else_=default)