Python sqlalchemy.orm.undefer() Examples

def get_packages(self, collection, only_new=False):
        """
        Get packages eligible for resolution in new repo for given collection.

        :param: collection collection for which packages are requested
        :param: only_new whether to consider only packages that weren't
                         resolved yet
        """
        query = (
            self.db.query(Package)
            .filter(~Package.blocked)
            .filter(Package.tracked)
            .filter(~Package.skip_resolution)
            .filter(Package.collection_id == collection.id)
            .filter(Package.last_complete_build_id != None)
            .options(joinedload(Package.last_build))
            .options(undefer('last_build.dependency_keys'))
        )
        if only_new:
            query = query.filter(Package.resolved == None)
        return query.all() 

def test_defer_on_wildcard_subclass(self):
        # pretty much the same as load_only except doesn't
        # exclude the primary key

        # TODO: what is ".*"?  this is not documented anywhere, how did this
        # get implemented without docs ?  see #4390
        s = Session()
        q = (
            s.query(Manager)
            .order_by(Person.person_id)
            .options(defer(".*"), undefer("status"))
        )
        self.assert_compile(
            q,
            "SELECT managers.status AS managers_status "
            "FROM people JOIN managers ON "
            "people.person_id = managers.person_id ORDER BY people.person_id",
        )

        # note this doesn't apply to "bound" loaders since they don't seem
        # to have this ".*" featue. 

def __call__(self):
        lecture = self.db.query(models.Lecture).options(undefer('tutorials.student_count')).get(self.lecture_id)
        names = self.request.config['lecture_types'][lecture.type]
        pref_subjects = lecture.pref_subjects()
        pref_count = sum([pref[0] for pref in pref_subjects])
        subjects = lecture.subjects()
        student_count = sum([subj[0] for subj in subjects])
        times = lecture.prepareTimePreferences(user=None)
        times = sorted([t for t in times], key=lambda s:s['time'].value)
        #print(times)
        return {'lecture': lecture,
                'names': names,
                'pref_subjects': pref_subjects,
                'pref_count': pref_count,
                'subjects': subjects,
                'times': times,
                'student_count': student_count,
                'categories': utils.categories,
                'exams': dict([[cat['id'], lecture.exams.filter(models.Exam.category==cat['id'])] for cat in utils.categories]),
                } 

def refresh_profile(orcid_id, high_priority=False):
    print u"refreshing {}".format(orcid_id)

    my_person = Person.query.options(orm.undefer('*')).filter_by(orcid_id=orcid_id).first()

    # for testing on jason's local, so it doesn't have to do a real refresh
    # sleep(5)
    # return my_person

    my_person.refresh(high_priority=high_priority)
    db.session.merge(my_person)

    commit_success = safe_commit(db)
    if commit_success:
        print u"committed {}".format(orcid_id)
    else:
        print u"COMMIT fail on {}".format(orcid_id)

    return my_person 

def test_load_only_w_deferred(self):
        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties={"description": deferred(orders.c.description)},
        )

        sess = create_session()
        q = sess.query(Order).options(
            load_only("isopen", "description"), undefer("user_id")
        )
        self.assert_compile(
            q,
            "SELECT orders.description AS orders_description, "
            "orders.id AS orders_id, "
            "orders.user_id AS orders_user_id, "
            "orders.isopen AS orders_isopen FROM orders",
        ) 

def test_undefer_star(self):
        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties=util.OrderedDict(
                [
                    ("userident", deferred(orders.c.user_id)),
                    ("description", deferred(orders.c.description)),
                    ("opened", deferred(orders.c.isopen)),
                ]
            ),
        )

        sess = create_session()
        q = sess.query(Order).options(Load(Order).undefer("*"))
        self.assert_compile(
            q,
            "SELECT orders.user_id AS orders_user_id, "
            "orders.description AS orders_description, "
            "orders.isopen AS orders_isopen, "
            "orders.id AS orders_id, "
            "orders.address_id AS orders_address_id "
            "FROM orders",
        ) 

def get_prev_build_for_comparison(self, build):
        """
        Finds a preceding build of the same package that is suitable to be
        compared against.
        """
        return (
            self.db.query(Build)
            .filter_by(package_id=build.package_id)
            .filter(Build.started < build.started)
            .filter(Build.deps_resolved == True)
            .order_by(Build.started.desc())
            .options(undefer('dependency_keys'))
            .first()
        ) 

def test_deferred_cols_missing_in_load_state_reset(self):
        Data = self.classes.DataDefer

        sess = create_session()

        d1 = Data(data="d1")
        sess.add(d1)
        sess.flush()
        sess.close()

        sess = create_session()
        d1 = (
            sess.query(Data)
            .from_statement(select([Data.id]))
            .options(undefer(Data.data))
            .first()
        )
        d1.data = "d2"

        # the deferred loader has to clear out any state
        # on the col, including that 'd2' here
        d1 = sess.query(Data).populate_existing().first()

        def go():
            eq_(d1.data, "d1")

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

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

        users, User = self.tables.users, self.classes.User

        mapper(User, users, properties={"name": deferred(users.c.name)})

        sess = create_session()
        u1 = sess.query(User).options(undefer(User.name)).first()
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, the attribute was loaded,
        # the attribute gets the callable
        sess.expire(u1)
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # load it
        u1.name
        assert "name" not in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, attribute was loaded but then deleted,
        # the callable goes away - the state wants to flip
        # it back to its "deferred" loader.
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        del u1.name
        sess.expire(u1)
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # single attribute expire, the attribute gets the callable
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        sess.expire(u1, ["name"])

        # the expire cancels the undefer
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables 

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

        mapper(Address, addresses)
        mapper(
            User,
            users,
            properties={
                "addresses": relationship(
                    Address, lazy="selectin", order_by=addresses.c.id
                )
            },
        )

        sess = create_session()

        with testing.expect_deprecated(
            r"The \*addl_attrs on orm.defer is deprecated.  "
            "Please use method chaining"
        ):
            sess.query(User).options(defer("addresses", "email_address"))

        with testing.expect_deprecated(
            r"The \*addl_attrs on orm.undefer is deprecated.  "
            "Please use method chaining"
        ):
            sess.query(User).options(undefer("addresses", "email_address")) 

def fetch_queue_chunk(self, chunk_size):
        logger.info(u"looking for new jobs")

        text_query_pattern = """
                        with update_chunk as (
                            select url
                            from {queue_table}
                            where started is null
                            order by retry_at nulls first, finished nulls first, started, rand
                            limit {chunk_size}
                            for update skip locked
                        )
                        update {queue_table} queue_rows_to_update
                        set started=now()
                        from update_chunk
                        where update_chunk.url = queue_rows_to_update.url
                        returning update_chunk.url;
                    """
        text_query = text_query_pattern.format(
            chunk_size=chunk_size,
            queue_table=self.table_name(None)
        )

        logger.info(u"the queue query is:\n{}".format(text_query))

        job_time = time()
        row_list = db.engine.execute(text(text_query).execution_options(autocommit=True)).fetchall()
        object_ids = [row[0] for row in row_list]
        logger.info(u"got ids, took {} seconds".format(elapsed(job_time)))

        job_time = time()
        q = db.session.query(PdfUrl).options(orm.undefer('*')).filter(PdfUrl.url.in_(object_ids))
        objects = q.all()
        logger.info(u"got pdf_url objects in {} seconds".format(elapsed(job_time)))

        random.shuffle(objects)
        return objects 

def __call__(self):
        lecture = self.db.query(models.Lecture).options(undefer('tutorials.student_count')).get(self.lecture_id)
        form = LectureEdit(self.request, lecture)
        assistants = self.db.query(models.User).filter(models.User.is_assistant==1).order_by(models.User.last_name).all()
        if self.request.method == 'POST' and form.processPostData(self.request.POST):
            form.saveValues()
            self.request.db.commit()
        names = self.request.config['lecture_types'][lecture.type]
        pref_subjects = lecture.pref_subjects()
        pref_count = sum([pref[0] for pref in pref_subjects])
        subjects = lecture.subjects()
        student_count = sum([subj[0] for subj in subjects])
        # Query results are already lexicographically sorted.
        # Sort again using length as key so we get length lexicographical sorting
        # https://github.com/muesli-hd/muesli/issues/28
        exams = dict([[cat['id'], sorted(list(lecture.exams.filter(models.Exam.category==cat['id'])),
                              key=lambda x:len(x.name))]
                      for cat in utils.categories])
        return {'lecture': lecture,
                'names': names,
                'pref_count': pref_count,
                'subjects': subjects,
                'student_count': student_count,
                'categories': utils.categories,
                'exams': exams,
                'assistants': assistants,
                'form': form,
                'tooltips': lecture_edit_tooltips} 

def __call__(self):
        lecture = self.db.query(models.Lecture).options(undefer('tutorials.student_count')).get(self.lecture_id)
        times = lecture.prepareTimePreferences(user=self.request.user)
        subscribed_tutorial = self.request.user.tutorials.filter(Tutorial.lecture_id == self.lecture_id).first()
        return {'lecture': lecture,
                'subscribed_tutorial': subscribed_tutorial,
                'times': times,
                'prefs': utils.preferences} 

def get(self):
        """
        ---
        get:
          security:
            - Bearer: [read]
            - Basic: [read]
          tags:
            - "v1"
          summary: "return a specific lecture"
          description: ""
          operationId: "lecture_get"
          produces:
            - "application/json"
          responses:
            200:
              description: successfull return of the lecture
              schema:
                $ref: "#/definitions/Lecture"
        """
        lecture_id = self.request.matchdict['lecture_id']
        lecture = self.db.query(models.Lecture).options(undefer('tutorials.student_count'), joinedload(models.Lecture.assistants), joinedload(models.Lecture.tutorials)).get(lecture_id)
        subscribed = self.request.user.id in [s.id for s in lecture.students]
        allowed_attr = allowed_attributes.lecture()
        if lecture.mode == 'off':
            allowed_attr = allowed_attributes.lecture()
            allowed_attr.remove('tutorials')
        schema = models.LectureSchema(only=allowed_attr)
        response = schema.dump(lecture)
        if lecture.mode == 'off':
            response.update({'tutorials': []})
        return {'lecture': response,
                'subscribed': subscribed} 

def get(self, test_id: str):
        testcase = TestCase.query.options(undefer("message"), joinedload("job")).get(
            test_id
        )

        schema = TestCaseSchema()
        return self.respond_with_schema(schema, testcase) 

def _process(self):
        query = (Category.query
                 .filter(Category.title_matches(request.args['term']),
                         ~Category.is_deleted)
                 .options(undefer('chain_titles'))
                 .order_by(Category.title))
        results = [{
            'title': category.title,
            'path': category.chain_titles[1:-1],
            'url': unicode(category.url)
        } for category in query.limit(7)]
        return jsonify(success=True, results=results, count=query.count()) 

def test_deep_options(self):
        users, items, order_items, Order, Item, User, orders = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.classes.Order,
            self.classes.Item,
            self.classes.User,
            self.tables.orders,
        )

        mapper(
            Item,
            items,
            properties=dict(description=deferred(items.c.description)),
        )
        mapper(
            Order,
            orders,
            properties=dict(items=relationship(Item, secondary=order_items)),
        )
        mapper(
            User,
            users,
            properties=dict(orders=relationship(Order, order_by=orders.c.id)),
        )

        sess = create_session()
        q = sess.query(User).order_by(User.id)
        result = q.all()
        item = result[0].orders[1].items[1]

        def go():
            eq_(item.description, "item 4")

        self.sql_count_(1, go)
        eq_(item.description, "item 4")

        sess.expunge_all()
        result = q.options(undefer("orders.items.description")).all()
        item = result[0].orders[1].items[1]

        def go():
            eq_(item.description, "item 4")

        self.sql_count_(0, go)
        eq_(item.description, "item 4") 

def test_options(self):
        """Options on a mapper to create deferred and undeferred columns"""

        orders, Order = self.tables.orders, self.classes.Order

        mapper(Order, orders)

        sess = create_session()
        q = sess.query(Order).order_by(Order.id).options(defer("user_id"))

        def go():
            q.all()[0].user_id

        self.sql_eq_(
            go,
            [
                (
                    "SELECT orders.id AS orders_id, "
                    "orders.address_id AS orders_address_id, "
                    "orders.description AS orders_description, "
                    "orders.isopen AS orders_isopen "
                    "FROM orders ORDER BY orders.id",
                    {},
                ),
                (
                    "SELECT orders.user_id AS orders_user_id "
                    "FROM orders WHERE orders.id = :param_1",
                    {"param_1": 1},
                ),
            ],
        )
        sess.expunge_all()

        q2 = q.options(undefer("user_id"))
        self.sql_eq_(
            q2.all,
            [
                (
                    "SELECT orders.id AS orders_id, "
                    "orders.user_id AS orders_user_id, "
                    "orders.address_id AS orders_address_id, "
                    "orders.description AS orders_description, "
                    "orders.isopen AS orders_isopen "
                    "FROM orders ORDER BY orders.id",
                    {},
                )
            ],
        ) 

def test_with_deferred(self):
        nodes = self.tables.nodes

        class Node(fixtures.ComparableEntity):
            def append(self, node):
                self.children.append(node)

        mapper(
            Node,
            nodes,
            properties={
                "children": relationship(
                    Node, lazy="joined", join_depth=3, order_by=nodes.c.id
                ),
                "data": deferred(nodes.c.data),
            },
        )
        sess = create_session()
        n1 = Node(data="n1")
        n1.append(Node(data="n11"))
        n1.append(Node(data="n12"))
        sess.add(n1)
        sess.flush()
        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node).order_by(Node.id).first(),
            )

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

        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node)
                .options(undefer("data"))
                .order_by(Node.id)
                .first(),
            )

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

        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node)
                .options(undefer("data"), undefer("children.data"))
                .first(),
            )

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

def test_with_deferred(self):
        nodes = self.tables.nodes

        class Node(fixtures.ComparableEntity):
            def append(self, node):
                self.children.append(node)

        mapper(
            Node,
            nodes,
            properties={
                "children": relationship(
                    Node, lazy="subquery", join_depth=3, order_by=nodes.c.id
                ),
                "data": deferred(nodes.c.data),
            },
        )
        sess = create_session()
        n1 = Node(data="n1")
        n1.append(Node(data="n11"))
        n1.append(Node(data="n12"))
        sess.add(n1)
        sess.flush()
        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node).order_by(Node.id).first(),
            )

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

        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node)
                .options(undefer("data"))
                .order_by(Node.id)
                .first(),
            )

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

        sess.expunge_all()

        def go():
            eq_(
                Node(data="n1", children=[Node(data="n11"), Node(data="n12")]),
                sess.query(Node)
                .options(undefer("data"), undefer("children.data"))
                .first(),
            )

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

def fetch_queue_chunk(self, chunk_size, queue_no):
        logger.info(u"looking for new jobs")

        text_query_pattern = u'''
            with refresh_queue as (
                select id
                from {queue_table}
                where
                    queue_no = {queue_no}
                    and started is null
                order by
                    priority desc,
                    finished asc nulls first,
                    rand
                limit {chunk_size}
                for update skip locked
            )
            update {queue_table} queue_rows_to_update
            set started = now()
            from refresh_queue
            where refresh_queue.id = queue_rows_to_update.id
            returning refresh_queue.id;
        '''

        text_query = text_query_pattern.format(
            chunk_size=chunk_size,
            queue_table=self.table_name(None),
            queue_no=queue_no
        )

        logger.info(u"the queue query is:\n{}".format(text_query))

        job_time = time()
        row_list = db.engine.execute(text(text_query).execution_options(autocommit=True)).fetchall()
        object_ids = [row[0] for row in row_list]
        logger.info(u"got {} ids, took {} seconds".format(len(object_ids), elapsed(job_time)))

        job_time = time()
        q = db.session.query(Pub).options(
            orm.undefer('*')
        ).filter(Pub.id.in_(object_ids))

        objects = q.all()
        logger.info(u"got pub objects in {} seconds".format(elapsed(job_time)))

        return objects