Python sqlalchemy.orm.column_property() Examples

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.MapperProperty`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def test_column_property_correlated(self):
        A = self.classes.A
        b_table, a_table = self.tables.b, self.tables.a
        cp = (
            select([func.sum(b_table.c.value)])
            .where(b_table.c.a_id == a_table.c.id)
            .correlate(a_table)
        )

        self._fixture({"summation": column_property(cp.scalar_subquery())})
        self.assert_compile(
            create_session()
            .query(A)
            .options(joinedload("bs"))
            .order_by(A.summation)
            .limit(50),
            "SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.a_id "
            "AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS "
            "b_1_a_id, b_1.value AS b_1_value FROM (SELECT "
            "(SELECT sum(b.value) AS sum_1 FROM b WHERE b.a_id = a.id) "
            "AS anon_2, a.id AS a_id FROM a ORDER BY anon_2 "
            "LIMIT :param_1) AS anon_1 LEFT OUTER JOIN b AS b_1 ON "
            "anon_1.a_id = b_1.a_id ORDER BY anon_1.anon_2",
        ) 

def test_column_property_desc(self):
        A = self.classes.A
        b_table, a_table = self.tables.b, self.tables.a
        cp = select([func.sum(b_table.c.value)]).where(
            b_table.c.a_id == a_table.c.id
        )

        self._fixture({"summation": column_property(cp.scalar_subquery())})
        self.assert_compile(
            create_session()
            .query(A)
            .options(joinedload("bs"))
            .order_by(A.summation.desc())
            .limit(50),
            "SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.a_id "
            "AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS "
            "b_1_a_id, b_1.value AS b_1_value FROM (SELECT "
            "(SELECT sum(b.value) AS sum_1 FROM b WHERE b.a_id = a.id) "
            "AS anon_2, a.id AS a_id FROM a ORDER BY anon_2 DESC "
            "LIMIT :param_1) AS anon_1 LEFT OUTER JOIN b AS b_1 ON "
            "anon_1.a_id = b_1.a_id ORDER BY anon_1.anon_2 DESC",
        ) 

def test_column_property(self):
        A = self.classes.A
        b_table, a_table = self.tables.b, self.tables.a
        cp = select([func.sum(b_table.c.value)]).where(
            b_table.c.a_id == a_table.c.id
        )

        self._fixture({"summation": column_property(cp.scalar_subquery())})
        self.assert_compile(
            create_session()
            .query(A)
            .options(joinedload("bs"))
            .order_by(A.summation)
            .limit(50),
            "SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.a_id "
            "AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS "
            "b_1_a_id, b_1.value AS b_1_value FROM (SELECT "
            "(SELECT sum(b.value) AS sum_1 FROM b WHERE b.a_id = a.id) "
            "AS anon_2, a.id AS a_id FROM a ORDER BY anon_2 "
            "LIMIT :param_1) AS anon_1 LEFT OUTER JOIN b AS b_1 ON "
            "anon_1.a_id = b_1.a_id ORDER BY anon_1.anon_2",
        ) 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`_orm.relationship`, or
        :func:`.composite`
        functions.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def test_info(self):
        users = self.tables.users
        Address = self.classes.Address

        class MyComposite(object):
            pass

        for constructor, args in [
            (column_property, (users.c.name,)),
            (relationship, (Address,)),
            (composite, (MyComposite, "id", "name")),
            (synonym, "foo"),
        ]:
            obj = constructor(info={"x": "y"}, *args)
            eq_(obj.info, {"x": "y"})
            obj.info["q"] = "p"
            eq_(obj.info, {"x": "y", "q": "p"})

            obj = constructor(*args)
            eq_(obj.info, {})
            obj.info["q"] = "p"
            eq_(obj.info, {"q": "p"}) 

def test_column_prop_deannotate(self):
        """test that column property deannotates,
        bringing expressions down to the original mapped columns.
        """
        User, users = self.classes.User, self.tables.users
        m = mapper(User, users)
        assert User.id.property.columns[0] is users.c.id
        assert User.name.property.columns[0] is users.c.name
        expr = User.name + "name"
        expr2 = sa.select([User.name, users.c.id])
        m.add_property("x", column_property(expr))
        m.add_property("y", column_property(expr2.scalar_subquery()))

        assert User.x.property.columns[0] is not expr
        assert User.x.property.columns[0].element.left is users.c.name
        # a deannotate needs to clone the base, in case
        # the original one referenced annotated elements.
        assert User.x.property.columns[0].element.right is not expr.right

        assert User.y.property.columns[0] is not expr2
        assert (
            User.y.property.columns[0].element._raw_columns[0] is users.c.name
        )
        assert User.y.property.columns[0].element._raw_columns[1] is users.c.id 

def test_synonym_nonexistent_attr(self):
        # test [ticket:4767].
        # synonym points to non-existent attrbute that hasn't been mapped yet.
        users = self.tables.users

        class User(object):
            def _x(self):
                return self.id

            x = property(_x)

        m = mapper(
            User,
            users,
            properties={"x": synonym("some_attr", descriptor=User.x)},
        )

        # object gracefully handles this condition
        assert not hasattr(User.x, "__name__")
        assert not hasattr(User.x, "comparator")

        m.add_property("some_attr", column_property(users.c.name))

        assert not hasattr(User.x, "__name__")
        assert hasattr(User.x, "comparator") 

def _fixture(self, label=True, polymorphic=False):
        User, Address = self.classes("User", "Address")
        users, addresses = self.tables("users", "addresses")
        stmt = (
            select([func.max(addresses.c.email_address)])
            .where(addresses.c.user_id == users.c.id)
            .correlate(users)
        )
        if label:
            stmt = stmt.label("email_ad")
        else:
            stmt = stmt.scalar_subquery()

        mapper(
            User,
            users,
            properties={"ead": column_property(stmt)},
            with_polymorphic="*" if polymorphic else None,
        )
        mapper(Address, addresses) 

def get_inherited_denormalized_columns(self, table):
        parent_models = list(versioned_parents(self.manager, self.model))
        mapper = sa.inspect(self.model)
        args = {}

        if parent_models and not (mapper.single or mapper.concrete):
            columns = [
                self.manager.option(self.model, 'operation_type_column_name'),
                self.manager.option(self.model, 'transaction_column_name')
            ]
            if self.manager.option(self.model, 'strategy') == 'validity':
                columns.append(
                    self.manager.option(
                        self.model,
                        'end_transaction_column_name'
                    )
                )

            for column in columns:
                args[column] = column_property(
                    table.c[column],
                    *[m.__table__.c[column] for m in parent_models]
                )
        return args 

def info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.MapperProperty`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def _memoized_attr_info(self):
        """Info dictionary associated with the object, allowing user-defined
        data to be associated with this :class:`.InspectionAttr`.

        The dictionary is generated when first accessed.  Alternatively,
        it can be specified as a constructor argument to the
        :func:`.column_property`, :func:`.relationship`, or :func:`.composite`
        functions.

        .. versionadded:: 0.8  Added support for .info to all
           :class:`.MapperProperty` subclasses.

        .. versionchanged:: 1.0.0 :attr:`.MapperProperty.info` is also
           available on extension types via the
           :attr:`.InspectionAttrInfo.info` attribute, so that it can apply
           to a wider variety of ORM and extension constructs.

        .. seealso::

            :attr:`.QueryableAttribute.info`

            :attr:`.SchemaItem.info`

        """
        return {} 

def test_nice_dependency_error(self):
        class User(Base):

            __tablename__ = "users"
            id = Column("id", Integer, primary_key=True)
            addresses = relationship("Address")

        class Address(Base):

            __tablename__ = "addresses"
            id = Column(Integer, primary_key=True)
            foo = sa.orm.column_property(User.id == 5)

        # this used to raise an error when accessing User.id but that's
        # no longer the case since we got rid of _CompileOnAttr.

        assert_raises(sa.exc.ArgumentError, configure_mappers) 

def test_property_cascade_abstract(self):
        counter = mock.Mock()

        class Abs(Base):
            __abstract__ = True

            @declared_attr.cascading
            def my_prop(cls):
                counter(cls)
                return column_property(cls.x + 2)

        class A(Abs):
            __tablename__ = "a"

            id = Column(Integer, primary_key=True)
            x = Column(Integer)

        class B(A):
            pass

        eq_(counter.mock_calls, [mock.call(A), mock.call(B)]) 

def test_property_cascade_mixin_override(self):
        counter = mock.Mock()

        class Mixin(object):
            @declared_attr.cascading
            def my_prop(cls):
                counter(cls)
                return column_property(cls.x + 2)

        class A(Base, Mixin):
            __tablename__ = "a"

            id = Column(Integer, primary_key=True)
            x = Column(Integer)

        with expect_warnings(
            "Attribute 'my_prop' on class .*B.* "
            "cannot be processed due to @declared_attr.cascading; "
            "skipping"
        ):

            class B(A):
                my_prop = Column("foobar", Integer)

        eq_(counter.mock_calls, [mock.call(A), mock.call(B)]) 

def test_property_cascade_mixin(self):
        counter = mock.Mock()

        class Mixin(object):
            @declared_attr.cascading
            def my_prop(cls):
                counter(cls)
                return column_property(cls.x + 2)

        class A(Base, Mixin):
            __tablename__ = "a"

            id = Column(Integer, primary_key=True)
            x = Column(Integer)

        class B(A):
            pass

        eq_(counter.mock_calls, [mock.call(A), mock.call(B)]) 

def test_property_noncascade(self):
        counter = mock.Mock()

        class Mixin(object):
            @declared_attr
            def my_prop(cls):
                counter(cls)
                return column_property(cls.x + 2)

        class A(Base, Mixin):
            __tablename__ = "a"

            id = Column(Integer, primary_key=True)
            x = Column(Integer)

        class B(A):
            pass

        eq_(counter.mock_calls, [mock.call(A)]) 

def _fixture(self):
        User, Address = self.classes.User, self.classes.Address
        Dingaling = self.classes.Dingaling
        address_table = self.tables.addresses

        class SubAddr(Address):
            pass

        mapper(
            SubAddr,
            inherits=Address,
            properties={
                "sub_attr": column_property(address_table.c.email_address),
                "dings": relationship(Dingaling, viewonly=True),
            },
        )

        return User, Address, SubAddr 

def test_column_property_select(self):
        User = self.classes.User
        Address = self.classes.Address

        mapper = inspect(User)
        mapper.add_property(
            "score",
            column_property(
                select([func.sum(Address.id)])
                .where(Address.user_id == User.id)
                .scalar_subquery()
            ),
        )
        session = Session()

        with self._assert_bind_args(session):
            session.query(func.max(User.score)).scalar() 

def test_kwarg_accepted(self):
        users, Address = self.tables.users, self.classes.Address

        class DummyComposite(object):
            def __init__(self, x, y):
                pass

        from sqlalchemy.orm.interfaces import PropComparator

        class MyFactory(PropComparator):
            pass

        for args in (
            (column_property, users.c.name),
            (deferred, users.c.name),
            (synonym, "name"),
            (composite, DummyComposite, users.c.id, users.c.name),
            (relationship, Address),
            (backref, "address"),
            (dynamic_loader, Address),
        ):
            fn = args[0]
            args = args[1:]
            fn(comparator_factory=MyFactory, *args) 

def test_column_property_flag(self):
        User, users = self.classes.User, self.tables.users

        mapper(
            User,
            users,
            properties={
                "name": column_property(users.c.name, active_history=True)
            },
        )
        u1 = User(name="jack")
        self._test_attribute(u1, "name", "ed") 

def define_tables(cls, metadata):
        import json
        from sqlalchemy.ext.declarative import declarative_base

        class JSONEncodedDict(TypeDecorator):
            impl = VARCHAR(50)

            def process_bind_param(self, value, dialect):
                if value is not None:
                    value = json.dumps(value)

                return value

            def process_result_value(self, value, dialect):
                if value is not None:
                    value = json.loads(value)
                return value

        MutableDict = cls._type_fixture()

        Base = declarative_base(metadata=metadata)

        class AbstractFoo(Base):
            __abstract__ = True

            id = Column(
                Integer, primary_key=True, test_needs_autoincrement=True
            )
            data = Column(MutableDict.as_mutable(JSONEncodedDict))
            non_mutable_data = Column(JSONEncodedDict)
            unrelated_data = Column(String(50))

        class Foo(AbstractFoo):
            __tablename__ = "foo"
            column_prop = column_property(
                func.lower(AbstractFoo.unrelated_data)
            )

        assert Foo.data.property.columns[0].type is not AbstractFoo.data.type 

def column_property_fixture(self):
        users, Address, addresses, User = (
            self.tables.users,
            self.classes.Address,
            self.tables.addresses,
            self.classes.User,
        )

        mapper(
            User,
            users,
            properties=util.OrderedDict(
                [
                    ("concat", column_property((users.c.id * 2))),
                    (
                        "count",
                        column_property(
                            select(func.count(addresses.c.id))
                            .where(users.c.id == addresses.c.user_id,)
                            .correlate(users)
                            .scalar_subquery()
                        ),
                    ),
                ]
            ),
        )

        mapper(Address, addresses, properties={"user": relationship(User,)})

        return User, Address 

def test_mixin_attr_refers_to_column_copies(self):
        # this @declared_attr can refer to User.id
        # freely because we now do the "copy column" operation
        # before the declared_attr is invoked.

        counter = mock.Mock()

        class HasAddressCount(object):
            id = Column(Integer, primary_key=True)

            @declared_attr
            def address_count(cls):
                counter(cls.id)
                return column_property(
                    select([func.count(Address.id)])
                    .where(Address.user_id == cls.id)
                    .scalar_subquery()
                )

        class Address(Base):
            __tablename__ = "address"
            id = Column(Integer, primary_key=True)
            user_id = Column(ForeignKey("user.id"))

        class User(Base, HasAddressCount):
            __tablename__ = "user"

        eq_(counter.mock_calls, [mock.call(User.id)])

        sess = Session()
        self.assert_compile(
            sess.query(User).having(User.address_count > 5),
            "SELECT (SELECT count(address.id) AS "
            'count_1 FROM address WHERE address.user_id = "user".id) '
            'AS anon_1, "user".id AS user_id FROM "user" '
            "HAVING (SELECT count(address.id) AS "
            'count_1 FROM address WHERE address.user_id = "user".id) '
            "> :param_1",
        ) 

def test_col_prop_builtin_function(self):
        class Foo(object):
            pass

        mapper(
            Foo,
            self.tables.users,
            properties={
                "foob": column_property(
                    func.coalesce(self.tables.users.c.name)
                )
            },
        )

        stmt1 = select(Foo).where(Foo.foob == "somename").order_by(Foo.foob)
        stmt2 = (
            Session()
            .query(Foo)
            .filter(Foo.foob == "somename")
            .order_by(Foo.foob)
            ._final_statement(legacy_query_style=False)
        )

        expected = (
            "SELECT coalesce(users.name) AS coalesce_1, "
            "users.id, users.name FROM users "
            "WHERE coalesce(users.name) = :param_1 "
            "ORDER BY coalesce_1"
        )
        self.assert_compile(stmt1, expected)
        self.assert_compile(stmt2, expected)