Python sqlalchemy.orm.lazyload() Examples

def test_refresh_with_lazy(self):
        """test that when a lazy loader is set as a trigger on an object's
        attribute (at the attribute level, not the class level), a refresh()
        operation doesn't fire the lazy loader or create any problems"""

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

        s = create_session()
        mapper(
            User,
            users,
            properties={"addresses": relationship(mapper(Address, addresses))},
        )
        q = s.query(User).options(sa.orm.lazyload("addresses"))
        u = q.filter(users.c.id == 8).first()

        def go():
            s.refresh(u)

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

def test_safe_bound_option_allows_bake(self):
        User, Address, Dingaling = self._o2m_twolevel_fixture(lazy="joined")

        lru = Address.dingalings.property._lazy_strategy._bakery(
            lambda q: None
        )._bakery
        l1 = len(lru)
        for i in range(5):
            sess = Session()
            u1 = (
                sess.query(User)
                .options(
                    Load(User)
                    .defaultload(User.addresses)
                    .lazyload(Address.dingalings)
                )
                .first()
            )
            for ad in u1.addresses:
                ad.dingalings
        l2 = len(lru)
        eq_(l1, 0)
        eq_(l2, 2) 

def test_safe_unbound_option_allows_bake(self):
        User, Address, Dingaling = self._o2m_twolevel_fixture(lazy="joined")

        lru = Address.dingalings.property._lazy_strategy._bakery(
            lambda q: None
        )._bakery
        l1 = len(lru)
        for i in range(5):
            sess = Session()
            u1 = (
                sess.query(User)
                .options(
                    defaultload(User.addresses).lazyload(Address.dingalings)
                )
                .first()
            )
            for ad in u1.addresses:
                ad.dingalings
        l2 = len(lru)
        eq_(l1, 0)
        eq_(l2, 2) 

def test_m2o_joinedload_not_others(self):
        self._eagerload_mappings(addresses_lazy="joined")
        Address = self.classes.Address
        sess = create_session()
        q = (
            sess.query(Address)
            .options(lazyload("*"), joinedload("user"))
            .yield_per(1)
            .filter_by(id=1)
        )

        def go():
            result = q.all()
            assert result[0].user

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

def test_instance_lazy_relation_loaders(self):
        users, addresses = (self.tables.users, self.tables.addresses)

        mapper(
            User,
            users,
            properties={"addresses": relationship(Address, lazy="noload")},
        )
        mapper(Address, addresses)

        sess = Session()
        u1 = User(name="ed", addresses=[Address(email_address="ed@bar.com")])

        sess.add(u1)
        sess.commit()
        sess.close()

        u1 = sess.query(User).options(lazyload(User.addresses)).first()
        u2 = pickle.loads(pickle.dumps(u1))

        sess = Session()
        sess.add(u2)
        assert u2.addresses 

def get_count(q):
    """
    Count the number of rows in a table. More efficient than count(*)
    :param q:
    :return:
    """
    disable_group_by = False
    if len(q._entities) > 1:
        # currently support only one entity
        raise Exception("only one entity is supported for get_count, got: %s" % q)
    entity = q._entities[0]
    if hasattr(entity, "column"):
        # _ColumnEntity has column attr - on case: query(Model.column)...
        col = entity.column
        if q._group_by and q._distinct:
            # which query can have both?
            raise NotImplementedError
        if q._group_by or q._distinct:
            col = distinct(col)
        if q._group_by:
            # need to disable group_by and enable distinct - we can do this because we have only 1 entity
            disable_group_by = True
        count_func = func.count(col)
    else:
        # _MapperEntity doesn't have column attr - on case: query(Model)...
        count_func = func.count()
    if q._group_by and not disable_group_by:
        count_func = count_func.over(None)
    count_q = (
        q.options(lazyload("*"))
        .statement.with_only_columns([count_func])
        .order_by(None)
    )
    if disable_group_by:
        count_q = count_q.group_by(None)
    count = q.session.execute(count_q).scalar()
    return count 

def update_image_status(session: Session, account: str, image_digest: str, old_statuses: list, new_status: str) -> str:
        current_record = session.query(ArchivedImage).filter_by(account=account, imageDigest=image_digest).options(lazyload(ArchivedImage._tags)).one_or_none()

        logger.debug('Updating archive image status from one of: {} to {} for {}/{} w/record: {}'.format(old_statuses, new_status, account, image_digest, current_record))
        if current_record:
            if current_record.status not in old_statuses:
                raise Exception("Status mismatch")
            else:
                current_record.status = new_status
        else:
            return None

        return new_status 

def test_simple_lazy_clause_no_race_on_generate(self):
        User, Address = self._o2m_fixture()

        (
            expr1,
            paramdict1,
        ) = User.addresses.property._lazy_strategy._simple_lazy_clause

        # delete the attr, as though a concurrent thread is also generating it
        del User.addresses.property._lazy_strategy._simple_lazy_clause
        (
            expr2,
            paramdict2,
        ) = User.addresses.property._lazy_strategy._simple_lazy_clause

        eq_(paramdict1, paramdict2)

    # additional tests:
    # 1. m2m w lazyload
    # 2. o2m lazyload where m2o backrefs have an eager load, test
    # that eager load is canceled out
    # 3. uselist = False, uselist=False assertion


# assert that the integration style illustrated in the dogpile.cache
# example works w/ baked 

def test_baked_lazy_loading_m2o(self):
        User, Address = self._m2o_fixture()

        base_bq = self.bakery(lambda s: s.query(Address))

        base_bq += lambda q: q.options(lazyload(Address.user))
        base_bq += lambda q: q.order_by(Address.id)

        assert_result = self.static.address_user_result

        for i in range(4):
            for cond1 in (False, True):
                bq = base_bq._clone()

                sess = Session()

                if cond1:
                    bq += lambda q: q.filter(
                        Address.email_address == "jack@bean.com"
                    )
                else:
                    bq += lambda q: q.filter(
                        Address.email_address.like("ed@%")
                    )

                if cond1:

                    def go():
                        result = bq(sess).all()
                        eq_(assert_result[0:1], result)

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

                    def go():
                        result = bq(sess).all()
                        eq_(assert_result[1:4], result)

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

                sess.close() 

def test_aliased_bound_are_now_safe_to_cache(self):
        User, Address, Dingaling = self._o2m_twolevel_fixture(lazy="joined")

        class SubDingaling(Dingaling):
            pass

        mapper(SubDingaling, None, inherits=Dingaling)

        lru = Address.dingalings.property._lazy_strategy._bakery(
            lambda q: None
        )._bakery
        l1 = len(lru)
        for i in range(5):
            sess = Session()
            u1 = (
                sess.query(User)
                .options(
                    Load(User)
                    .defaultload(User.addresses)
                    .lazyload(
                        Address.dingalings.of_type(aliased(SubDingaling))
                    )
                )
                .first()
            )
            for ad in u1.addresses:
                ad.dingalings
        l2 = len(lru)
        eq_(l1, 0)
        eq_(l2, 2) 

def test_aliased_unbound_are_now_safe_to_cache(self):
        User, Address, Dingaling = self._o2m_twolevel_fixture(lazy="joined")

        class SubDingaling(Dingaling):
            pass

        mapper(SubDingaling, None, inherits=Dingaling)

        lru = Address.dingalings.property._lazy_strategy._bakery(
            lambda q: None
        )._bakery
        l1 = len(lru)
        for i in range(5):
            sess = Session()
            u1 = (
                sess.query(User)
                .options(
                    defaultload(User.addresses).lazyload(
                        Address.dingalings.of_type(aliased(SubDingaling))
                    )
                )
                .first()
            )
            for ad in u1.addresses:
                ad.dingalings
        l2 = len(lru)
        eq_(l1, 0)
        eq_(l2, 2) 

def test_useget_cancels_eager(self):
        """test that a one to many lazyload cancels the unnecessary
        eager many-to-one join on the other side."""

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

        mapper(User, users)
        mapper(
            Address,
            addresses,
            properties={
                "user": relationship(User, lazy="joined", backref="addresses")
            },
        )

        sess = create_session()
        u1 = sess.query(User).filter(User.id == 8).one()

        def go():
            eq_(u1.addresses[0].user, u1)

        self.assert_sql_execution(
            testing.db,
            go,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.user_id AS "
                "addresses_user_id, addresses.email_address AS "
                "addresses_email_address FROM addresses WHERE :param_1 = "
                "addresses.user_id",
                {"param_1": 8},
            ),
        ) 

def find_document(path, compareto):
    query = session.query(Document)

    for i, match in enumerate(
        re.finditer(r"/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?", path)
    ):
        (token, attrname, attrvalue) = match.group(1, 2, 3)

        if not i:
            parent = Document
            target_node = aliased(_Node)

            query = query.join(parent._nodes.of_type(target_node)).filter(
                target_node.parent_id.is_(None)
            )
        else:
            parent = target_node
            target_node = aliased(_Node)

            query = query.join(parent.children.of_type(target_node))

        query = query.filter(target_node.tag == token)
        if attrname:
            attribute_entity = aliased(_Attribute)
            query = query.join(
                target_node.attributes.of_type(attribute_entity)
            )
            if attrvalue:
                query = query.filter(
                    and_(
                        attribute_entity.name == attrname,
                        attribute_entity.value == attrvalue,
                    )
                )
            else:
                query = query.filter(attribute_entity.name == attrname)
    return (
        query.options(lazyload(Document._nodes))
        .filter(target_node.text == compareto)
        .all()
    ) 

def find_document(path, compareto):
    query = session.query(Document)
    attribute = Document._root
    for i, match in enumerate(
        re.finditer(r"/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?", path)
    ):
        (token, attrname, attrvalue) = match.group(1, 2, 3)
        target_node = aliased(_Node)

        query = query.join(attribute.of_type(target_node)).filter(
            target_node.tag == token
        )

        attribute = target_node.children

        if attrname:
            attribute_entity = aliased(_Attribute)

            if attrvalue:
                query = query.join(
                    target_node.attributes.of_type(attribute_entity)
                ).filter(
                    and_(
                        attribute_entity.name == attrname,
                        attribute_entity.value == attrvalue,
                    )
                )
            else:
                query = query.join(
                    target_node.attributes.of_type(attribute_entity)
                ).filter(attribute_entity.name == attrname)
    return (
        query.options(lazyload(Document._root))
        .filter(target_node.text == compareto)
        .all()
    ) 

def make_query(self, model):
        cols = ['storage_backend', 'storage_file_id', 'md5']
        if model.add_file_date_column:
            cols.append('created_dt')
        opts = QUERY_OPTIONS.get(model)
        return (model.query
                .filter(model.storage_file_id.isnot(None), model.storage_backend.in_(self.source_backend_names))
                .filter_by(**SPECIAL_FILTERS.get(model, {}))
                .options(*((opts,) if opts else ()))
                .options(lazyload('*'), load_only(*cols))) 

def test_model_history__both_classes(self):
        ssn = self.Session()
        # Get a user from the DB
        user = ssn.query(models.User).options(
            lazyload('*')
        ).get(1)

        # Prepare two history objects
        old_user = ModelHistoryProxy(user)

        # Check `user` properties
        self.assertEqual(user.id, 1)
        self.assertEqual(user.name, 'a')
        self.assertEqual(user.age, 18)
        self.assertEqual(user.tags, ['1', 'a'])

        # === Test: columns
        # Check `old_user` properties
        # self.assertEqual(old_user.id, 1)
        self.assertEqual(old_user.name, 'a')
        self.assertEqual(old_user.age, 18)
        self.assertEqual(old_user.tags, ['1', 'a'])

        # Change `user`
        user.id = 1000
        user.name = 'aaaa'
        user.age = 1800
        user.tags = [1000,]

        # Check `old_user` retains properties
        self.assertEqual(old_user.id, 1)
        self.assertEqual(old_user.name, 'a')
        self.assertEqual(old_user.age, 18)
        self.assertEqual(old_user.tags, ['1', 'a'])

        # Undo
        ssn.close()

    # Older tests 

def test_refresh_maintains_deferred_options(self):
        # testing a behavior that may have changed with
        # [ticket:3822]
        User, Address, Dingaling = self.classes("User", "Address", "Dingaling")
        users, addresses, dingalings = self.tables(
            "users", "addresses", "dingalings"
        )

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

        mapper(
            Address,
            addresses,
            properties={"dingalings": relationship(Dingaling)},
        )

        mapper(Dingaling, dingalings)

        s = create_session()
        q = (
            s.query(User)
            .filter_by(name="fred")
            .options(sa.orm.lazyload("addresses").joinedload("dingalings"))
        )

        u1 = q.one()

        # "addresses" is not present on u1, but when u1.addresses
        # lazy loads, it should also joinedload dingalings.  This is
        # present in state.load_options and state.load_path.   The
        # refresh operation should not reset these attributes.
        s.refresh(u1)

        def go():
            eq_(
                u1.addresses,
                [
                    Address(
                        email_address="fred@fred.com",
                        dingalings=[Dingaling(data="ding 2/5")],
                    )
                ],
            )

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

def test_state_noload_to_lazy(self):
        """Behavioral test to verify the current activity of loader callables
        """

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

        mapper(
            User,
            users,
            properties={"addresses": relationship(Address, lazy="noload")},
        )
        mapper(Address, addresses)

        sess = create_session()
        u1 = sess.query(User).options(lazyload(User.addresses)).first()
        assert isinstance(
            attributes.instance_state(u1).callables["addresses"],
            strategies.LoadLazyAttribute,
        )
        # expire, it goes away from callables as of 1.4 and is considered
        # to be expired
        sess.expire(u1)

        assert "addresses" in attributes.instance_state(u1).expired_attributes
        assert "addresses" not in attributes.instance_state(u1).callables

        # load it
        sess.query(User).first()
        assert (
            "addresses" not in attributes.instance_state(u1).expired_attributes
        )
        assert "addresses" not in attributes.instance_state(u1).callables

        sess.expunge_all()
        u1 = sess.query(User).options(lazyload(User.addresses)).first()
        sess.expire(u1, ["addresses"])
        assert (
            "addresses" not in attributes.instance_state(u1).expired_attributes
        )
        assert isinstance(
            attributes.instance_state(u1).callables["addresses"],
            strategies.LoadLazyAttribute,
        )

        # load the attr, goes away
        u1.addresses
        assert (
            "addresses" not in attributes.instance_state(u1).expired_attributes
        )
        assert "addresses" not in attributes.instance_state(u1).callables 

def test_many_to_one_null(self):
        """test that a many-to-one eager load which loads None does
        not later trigger a lazy load.

        """

        Order, Address, addresses, orders = (
            self.classes.Order,
            self.classes.Address,
            self.tables.addresses,
            self.tables.orders,
        )

        # use a primaryjoin intended to defeat SA's usage of
        # query.get() for a many-to-one lazyload
        mapper(
            Order,
            orders,
            properties=dict(
                address=relationship(
                    mapper(Address, addresses),
                    primaryjoin=and_(
                        addresses.c.id == orders.c.address_id,
                        addresses.c.email_address != None,  # noqa
                    ),
                    lazy="joined",
                )
            ),
        )
        sess = create_session()

        def go():
            o1 = (
                sess.query(Order)
                .options(lazyload("address"))
                .filter(Order.id == 5)
                .one()
            )
            eq_(o1.address, None)

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

        sess.expunge_all()

        def go():
            o1 = sess.query(Order).filter(Order.id == 5).one()
            eq_(o1.address, None)

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

def test_useget_cancels_eager_propagated_present(self):
        """test that a one to many lazyload cancels the unnecessary
        eager many-to-one join on the other side, even when a propagated
        option is present."""

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

        mapper(User, users)
        mapper(
            Address,
            addresses,
            properties={
                "user": relationship(User, lazy="joined", backref="addresses")
            },
        )

        from sqlalchemy.orm.interfaces import MapperOption

        class MyBogusOption(MapperOption):
            propagate_to_loaders = True

        sess = create_session()
        u1 = (
            sess.query(User)
            .options(MyBogusOption())
            .filter(User.id == 8)
            .one()
        )

        def go():
            eq_(u1.addresses[0].user, u1)

        self.assert_sql_execution(
            testing.db,
            go,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.user_id AS "
                "addresses_user_id, addresses.email_address AS "
                "addresses_email_address FROM addresses WHERE :param_1 = "
                "addresses.user_id",
                {"param_1": 8},
            ),
        ) 

def test_useget_cancels_eager_propagated_present(self):
        """test that a one to many lazyload cancels the unnecessary
        eager many-to-one join on the other side, even when a propagated
        option is present."""

        User = self.classes.User
        Address = self.classes.Address

        mapper(User, self.tables.users)
        mapper(
            Address,
            self.tables.addresses,
            properties={
                "user": relationship(
                    User,
                    lazy="joined",
                    backref=backref("addresses", lazy="baked_select"),
                )
            },
        )

        from sqlalchemy.orm.interfaces import MapperOption

        class MyBogusOption(MapperOption):
            propagate_to_loaders = True

        sess = Session()
        u1 = (
            sess.query(User)
            .options(MyBogusOption())
            .filter(User.id == 8)
            .one()
        )

        def go():
            eq_(u1.addresses[0].user, u1)

        self.assert_sql_execution(
            testing.db,
            go,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.user_id AS "
                "addresses_user_id, addresses.email_address AS "
                "addresses_email_address FROM addresses WHERE :param_1 = "
                "addresses.user_id",
                {"param_1": 8},
            ),
        )