Python sqlalchemy.sql.expression.desc() Examples

def get_server_stats_log(self, before=None, after=None, limit=None, skip=0):

        meta = get_metadata_template()
        query = []

        if before:
            query.append(ServerStatsLogORM.timestamp <= before)

        if after:
            query.append(ServerStatsLogORM.timestamp >= after)

        with self.session_scope() as session:
            pose = session.query(ServerStatsLogORM).filter(*query).order_by(desc("timestamp"))
            meta["n_found"] = get_count_fast(pose)

            data = pose.limit(self.get_limit(limit)).offset(skip).all()
            data = [d.to_dict() for d in data]

        meta["success"] = True

        return {"data": data, "meta": meta} 

def apply(self, q, bindings, ordering, distinct=None):
        """ Sort on a set of field specifications of the type (ref, direction)
        in order of the submitted list. """
        info = []
        for (ref, direction) in self.parse(ordering):
            info.append((ref, direction))
            table, column = self.cube.model[ref].bind(self.cube)
            if distinct is not None and distinct != ref:
                column = asc(ref) if direction == 'asc' else desc(ref)
            else:
                column = column.label(column.name)
                column = column.asc() if direction == 'asc' else column.desc()
                bindings.append(Binding(table, ref))
            if self.cube.is_postgresql:
                column = column.nullslast()
            q = q.order_by(column)

        if not len(self.results):
            for column in q.columns:
                column = column.asc()
                if self.cube.is_postgresql:
                    column = column.nullslast()
                q = q.order_by(column)
        return info, q, bindings 

def scheduled_operation_log_delete_oldest(context, operation_id,
                                          retained_num, excepted_states):
    table = models.ScheduledOperationLog
    session = get_session()
    with session.begin():
        result = model_query(context, table, session=session).filter_by(
            operation_id=operation_id).order_by(
            expression.desc(table.created_at)).limit(retained_num).all()

        if not result or len(result) < retained_num:
            return
        oldest_create_time = result[-1]['created_at']

        if excepted_states and isinstance(excepted_states, list):
            filters = expression.and_(
                table.operation_id == operation_id,
                table.created_at < oldest_create_time,
                table.state.notin_(excepted_states))
        else:
            filters = expression.and_(
                table.operation_id == operation_id,
                table.created_at < oldest_create_time)

        model_query(context, table, session=session).filter(
            filters).delete(synchronize_session=False) 

def metadata_trend(num_days):
    results = db.session.query(
        RepoMean.repo_id, func.substring_index(
            func.group_concat(
                RepoMean.value.op('ORDER BY')(expression.desc(RepoMean.created_at))
            ), ',', 2)
        )\
        .filter(RepoMean.created_at >= datetime.now() + timedelta(days=num_days * -1))\
        .group_by(RepoMean.repo_id)\
        .all()
    for result in filter(lambda x: ',' in x[1], results):
        curr, prev = map(lambda v: float(v), result[1].split(','))
        if is_worth_decreased(curr, prev):
            log.info(
                'Mean value of {0} is {1}, previous was {2}. The "worth" has been decreased by 1'
                .format(result[0], curr, prev)
            )
            db.session.query(Repo)\
                .filter(Repo.id == result[0])\
                .update({Repo.worth: Repo.worth - 1})
            db.session.commit() 

def collection_get(self):
        """
        ---
        get:
          security:
            - Bearer: [read]
            - Basic: [read]
          tags:
            - "v1"
          summary: "return all lectures"
          description: ""
          operationId: "lecture_collection_get"
          consumes:
            - "application/json"
          produces:
            - "application/json"
          responses:
            200:
              description: "response for 200 code"
              schema:
                $ref: "#/definitions/CollectionLecture"
        """
        lectures = (
            self.db.query(models.Lecture)
            .order_by(desc(models.Lecture.term), models.Lecture.name)
            .options(joinedload(models.Lecture.assistants))
            .filter(models.Lecture.is_visible == True) # pylint: disable=C0121
            .all()
        )
        schema = models.LectureSchema(many=True, only=allowed_attributes.collection_lecture())
        return schema.dump(lectures) 

def check_lib_versions(self):
        """Check the stored versions of elemental and fractal"""

        # check if versions table exist
        if not self.engine.dialect.has_table(self.engine, "versions"):
            return None

        with self.session_scope() as session:
            db_ver = session.query(VersionsORM).order_by(VersionsORM.created_on.desc())

            # Table exists but empty
            if db_ver.count() == 0:
                elemental_version = qcelemental.__version__
                fractal_version = qcfractal.__version__
                engine_version = qcengine.__version__
                current = VersionsORM(
                    elemental_version=elemental_version, fractal_version=fractal_version, engine_version=engine_version
                )
                session.add(current)
                session.commit()
            else:
                current = db_ver.first()

            ver = current.to_dict(exclude=["id"])

        return ver 

def last_known_mean(repo_id: int, default: float = 0.0):
    last_mean = db.session.query(RepoMean.value) \
        .filter(RepoMean.repo_id == repo_id) \
        .order_by(expression.desc(RepoMean.created_at)) \
        .first()
    return default if not last_mean else last_mean.value 

def __query__(self):
        if self.order == self.DESCENDANT:
            query = desc(self.attribute)
        elif self.order == self.ASCENDANT:
            query = asc(self.attribute)
        return query 

def __call__(self):
        lectures = self.db.query(models.Lecture).order_by(desc(models.Lecture.term), models.Lecture.name).options(joinedload(models.Lecture.assistants))
        if self.request.GET.get('show_all', '0')=='0':
            lectures = lectures.filter(models.Lecture.is_visible == True)
        lectures = lectures.all()
        sticky_lectures = []
        if lectures:
            newest_term = lectures[0].term
            sticky_lectures = [l for l in lectures if l.term == newest_term and self.is_ana_or_la(l)]
            lectures = [l for l in lectures if not l in sticky_lectures]
        return {'lectures': lectures,
                'sticky_lectures': sticky_lectures} 

def actions_get(self, context, container_uuid):
        """Get all container actions for the provided uuid."""
        query = model_query(models.ContainerAction).\
            filter_by(container_uuid=container_uuid)
        actions = _paginate_query(models.ContainerAction, sort_dir='desc',
                                  sort_key='created_at', query=query)

        return actions 

def _sort_query(self):
        ''' Sort the SQLA query object by a given parameter '''

        if not isinstance(self.sort, type(None)):
            # set the sort variable ModelClass parameter
            if '.' in self.sort:
                param = self.datamodel.parameters[str(self.sort)].full
            else:
                param = self.datamodel.parameters.get_full_from_remote(self.sort)
            sortparam = self._marvinform._param_form_lookup.mapToColumn(param)

            # check if sort param actually in the parameter list
            if sortparam.class_ not in self._modellist:
                return

            # If order is specified, then do the sort
            if self.order:
                assert self.order in ['asc', 'desc'], 'Sort order parameter must be either "asc" or "desc"'

                # Check if order by already applied
                if 'ORDER' in str(self.query.statement):
                    self.query = self.query.order_by(None)
                # Do the sorting
                if 'desc' in self.order:
                    self.query = self.query.order_by(desc(sortparam))
                else:
                    self.query = self.query.order_by(sortparam) 

def get_notification_list_by_hostname(session, notification_hostname):
    # sql = "SELECT id, notification_id, notification_hostname, "
    # "notification_uuid, notification_cluster_port, recover_by "
    # "FROM notification_list "
    # "WHERE progress = 0 AND notification_hostname = '%s' "
    # "ORDER BY create_at DESC, id DESC"
    # % ("notification_hostname")
    with _sqlalchemy_error():
        res = session.query(NotificationList).filter_by(progress=0).filter_by(
            notification_hostname=notification_hostname).order_by(
                desc(NotificationList.create_at),
                desc(NotificationList.id)).all()
    return res 

def get_reprocessing_records_list(session, notification_uuid):
    # sql = "SELECT id, notification_id, notification_hostname, "
    # "notification_uuid, notification_cluster_port, recover_by "
    # "FROM notification_list "
    # "WHERE progress = 0 AND notification_uuid = '%s' "
    # "ORDER BY create_at DESC, id DESC"
    # % (row.get("notification_uuid"))
    with _sqlalchemy_error():
        res = session.query(NotificationList).filter_by(
            progress=0).filter_by(notification_uuid=notification_uuid).order_by(
                desc(NotificationList.create_at),
                desc(NotificationList.id)).all()
    return res 

def get_vm_list_by_uuid_and_progress_sorted(session, notification_uuid):
    # sql = "SELECT id, uuid FROM vm_list " \
    #       "WHERE uuid = '%s' " \
    #       "AND (progress = 0 OR progress = 1) " \
    #       "ORDER BY recover_by ASC, create_at DESC" \
    #       % (row.get("uuid"))
    with _sqlalchemy_error():
        res = session.query(VmList).filter_by(
            uuid=notification_uuid).filter(or_(
                VmList.progress == 0, VmList.progress == 1)).order_by(
                    asc(VmList.recover_by), desc(VmList.create_at)
        ).all()
    return res 

def get_one_vm_list_by_uuid_and_progress_create_at_last(session,
                                                        notification_uuid):
    # SELECT * FROM vm_list WHERE uuid = :notification_uuid \
    #   AND (progress = 0 OR progress = 1) \
    #   ORDER BY create_at DESC LIMIT 1
    with _sqlalchemy_error():
        res = session.query(VmList).filter_by(uuid=notification_uuid).filter(
            or_(VmList.progress == 0, VmList.progress == 1)).order_by(
                desc(VmList.create_at)).first()
    return res 

def get_one_vm_list_by_uuid_create_at_last(session, uuid):
    # SELECT progress, create_at, retry_cnt FROM vm_list \
    #   WHERE uuid = :uuid ORDER BY create_at DESC LIMIT 1
    with _sqlalchemy_error():
        res = session.query(VmList).filter_by(uuid=uuid).order_by(
            desc(VmList.create_at)).first()
    return res 

def action_events_get(self, context, action_id):
        query = model_query(models.ContainerActionEvent).\
            filter_by(action_id=action_id)
        events = _paginate_query(models.ContainerActionEvent, sort_dir='desc',
                                 sort_key='created_at', query=query)
        return events 

def _action_get_last_created_by_container_uuid(self, context,
                                                   container_uuid):
        result = model_query(models.ContainerAction).\
            filter_by(container_uuid=container_uuid).\
            order_by(desc("created_at"), desc("id")).\
            first()
        return result 

def _generate_paginate_query(context, session, marker, limit, sort_keys,
                             sort_dirs, filters, offset=None,
                             paginate_type=models.Plan, use_model=False,
                             **kwargs):
    """Generate the query to include the filters and the paginate options.

    Returns a query with sorting / pagination criteria added or None
    if the given filters will not yield any results.

    :param context: context to query under
    :param session: the session to use
    :param marker: the last item of the previous page; we returns the next
                    results after this value.
    :param limit: maximum number of items to return
    :param sort_keys: list of attributes by which results should be sorted,
                      paired with corresponding item in sort_dirs
    :param sort_dirs: list of directions in which results should be sorted,
                      paired with corresponding item in sort_keys
    :param filters: dictionary of filters; values that are in lists, tuples,
                    or sets cause an 'IN' operation, while exact matching
                    is used for other values, see _process_plan_filters
                    function for more information
    :param offset: number of items to skip
    :param paginate_type: type of pagination to generate
    :returns: updated query or None
    """
    get_query, process_filters, get = PAGINATION_HELPERS[paginate_type]

    sort_keys, sort_dirs = process_sort_params(sort_keys,
                                               sort_dirs,
                                               default_dir='desc')
    if use_model:
        query = get_query(context, session=session, **kwargs)
    else:
        query = get_query(context, session=session)

    if filters:
        query = process_filters(query, filters)
        if query is None:
            return None

    marker_object = None
    if marker is not None:
        marker_object = get(context, marker, session=session)

    query = sqlalchemyutils.paginate_query(query, paginate_type, limit,
                                           sort_keys,
                                           marker=marker_object,
                                           sort_dirs=sort_dirs)
    if offset:
        query = query.offset(offset)
    return query 

def get_services(
        self,
        id: Union[List[str], str] = None,
        procedure_id: Union[List[str], str] = None,
        hash_index: Union[List[str], str] = None,
        status: str = None,
        limit: int = None,
        skip: int = 0,
        return_json=True,
    ):
        """

        Parameters
        ----------
        id / hash_index : List of str or str
            service id / hash_index that ran the results
        projection : list/set/tuple of keys, default is None
            The fields to return, default to return all
        limit : int, default is None
            maximum number of results to return
            if 'limit' is greater than the global setting self._max_limit,
            the self._max_limit will be returned instead
            (This is to avoid overloading the server)
        skip : int, default is 0
            skip the first 'skip' resaults. Used to paginate
        return_json : bool, deafult is True
            Return the results as a list of json instead of objects

        Returns
        -------
        Dict with keys: data, meta
            Data is the objects found
        """

        meta = get_metadata_template()
        query = format_query(ServiceQueueORM, id=id, hash_index=hash_index, procedure_id=procedure_id, status=status)

        with self.session_scope() as session:
            data = (
                session.query(ServiceQueueORM)
                .filter(*query)
                .order_by(ServiceQueueORM.priority.desc(), ServiceQueueORM.created_on)
                .limit(limit)
                .offset(skip)
                .all()
            )
            data = [x.to_dict() for x in data]

        meta["n_found"] = len(data)
        meta["success"] = True

        # except Exception as err:
        #     meta['error_description'] = str(err)

        return {"data": data, "meta": meta} 

def queue_get_next(
        self, manager, available_programs, available_procedures, limit=100, tag=None, as_json=True
    ) -> List[TaskRecord]:
        """Done in a transaction"""

        # Figure out query, tagless has no requirements

        proc_filt = TaskQueueORM.procedure.in_([p.lower() for p in available_procedures])
        none_filt = TaskQueueORM.procedure == None  # lgtm [py/test-equals-none]

        order_by = []
        if tag is not None:
            if isinstance(tag, str):
                tag = [tag]
            # task_order = expression_case([(TaskQueueORM.tag == t, num) for num, t in enumerate(tag)])
            # order_by.append(task_order)

        order_by.extend([TaskQueueORM.priority.desc(), TaskQueueORM.created_on])
        queries = []
        if tag is not None:
            for t in tag:
                query = format_query(TaskQueueORM, status=TaskStatusEnum.waiting, program=available_programs, tag=t)
                query.append(or_(proc_filt, none_filt))
                queries.append(query)
        else:
            query = format_query(TaskQueueORM, status=TaskStatusEnum.waiting, program=available_programs)
            query.append((or_(proc_filt, none_filt)))
            queries.append(query)
        new_limit = limit
        ids = []
        found = []
        with self.session_scope() as session:
            for q in queries:
                if new_limit == 0:
                    break
                query = session.query(TaskQueueORM).filter(*q).order_by(*order_by).limit(new_limit)
                # from sqlalchemy.dialects import postgresql
                # print(query.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
                new_items = query.all()
                found.extend(new_items)
                new_limit = limit - len(new_items)
                ids.extend([x.id for x in new_items])
            update_fields = {"status": TaskStatusEnum.running, "modified_on": dt.utcnow(), "manager": manager}
            # # Bulk update operation in SQL
            update_count = (
                session.query(TaskQueueORM)
                .filter(TaskQueueORM.id.in_(ids))
                .update(update_fields, synchronize_session=False)
            )

            if as_json:
                # avoid another trip to the DB to get the updated values, set them here
                found = [TaskRecord(**task.to_dict(exclude=update_fields.keys()), **update_fields) for task in found]
            session.commit()

        if update_count != len(found):
            self.logger.warning("QUEUE: Number of found projects does not match the number of updated projects.")

        return found