Python sqlalchemy.desc() Examples

def exposed_db_count_netlocs():
	'''
	Select and count the number of instances for each netloc in
	the database.

	Returns the netlocs sorted by count in decending order.
	'''

	with db.session_context() as sess:
		q = sess.query(db.WebPages.netloc, func.count(db.WebPages.netloc).label("count")) \
			.group_by(db.WebPages.netloc)\
			.order_by(desc(func.count(db.WebPages.netloc)))
		print("Doing query.")
		res = q.all()
		res = list(res)
		for row in res:
			print("Row: ", row)

		with open("nl_counts.json", "w") as fp:
			json.dump(res, fp) 

def apply_default_ordering(queryset):
    model = get_queryset_model(queryset)
    mapper = inspect(model)
    pk = mapper.primary_key[0].name
    ordering = queryset._order_by if queryset._order_by else []

    def is_pk(x):
        if isinstance(x, AnnotatedColumnElement):
            return x.name == pk
        elif isinstance(x, UnaryExpression):
            return x.element.name == pk and x.modifier == operators.desc_op
        return False

    if ordering is None or not any(map(is_pk, ordering)):
        order_by = list(ordering or []) + [desc(pk)]
        queryset = queryset.order_by(*order_by)

    return queryset 

def batch_load_by_user(cls, context, user_uuids, *,
                                 domain_name=None, group_id=None):
        from .user import users
        async with context['dbpool'].acquire() as conn:
            # TODO: num_attached count group-by
            j = sa.join(vfolders, users, vfolders.c.user == users.c.uuid)
            query = (
                sa.select([vfolders])
                .select_from(j)
                .where(vfolders.c.user.in_(user_uuids))
                .order_by(sa.desc(vfolders.c.created_at))
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if group_id is not None:
                query = query.where(vfolders.c.group == group_id)
            return await batch_multiresult(
                context, conn, query, cls,
                user_uuids, lambda row: row['user']
            ) 

def load_slice(cls, context, limit, offset, *,
                         domain_name=None, group_id=None, user_id=None,
                         order_key=None, order_asc=None):
        from .user import users
        async with context['dbpool'].acquire() as conn:
            if order_key is None:
                _ordering = vfolders.c.created_at
            else:
                _order_func = sa.asc if order_asc else sa.desc
                _ordering = _order_func(getattr(vfolders.c, order_key))
            j = sa.join(vfolders, users, vfolders.c.user == users.c.uuid)
            query = (
                sa.select([vfolders])
                .select_from(j)
                .order_by(_ordering)
                .limit(limit)
                .offset(offset)
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if group_id is not None:
                query = query.where(vfolders.c.group == group_id)
            if user_id is not None:
                query = query.where(vfolders.c.user == user_id)
            return [cls.from_row(context, r) async for r in conn.execute(query)] 

def __init__(self, params, sort_dir=constants.DEFAULT_SORT_DIR):
        """Pagination Helper takes params and a default sort direction

        :param params: Contains the following:
                       limit: maximum number of items to return
                       marker: the last item of the previous page; we return
                               the next results after this value.
                       sort: array of attr by which results should be sorted
        :param sort_dir: default direction to sort (asc, desc)
        """
        self.marker = params.get('marker')
        self.sort_dir = self._validate_sort_dir(sort_dir)
        self.limit = self._parse_limit(params)
        self.sort_keys = self._parse_sort_keys(params)
        self.params = params
        self.filters = None
        self.page_reverse = params.get('page_reverse', 'False') 

def get_output_files_for_output_type_and_asset_instance(
    asset_instance_id, temporal_entity_id, output_type_id, representation=None
):
    """
    Get output files created for given asset instance and output type.
    """
    query = OutputFile.query.filter(
        OutputFile.asset_instance_id == asset_instance_id,
        OutputFile.output_type_id == output_type_id,
        OutputFile.temporal_entity_id == temporal_entity_id,
    ).order_by(desc(OutputFile.revision))

    if representation is not None:
        query = query.filter(OutputFile.representation == representation)

    output_files = query.all()
    return OutputFile.serialize_list(output_files) 

def get_table(request: web.Request) -> web.Response:
    instance_id = request.match_info.get("id", "")
    instance = PluginInstance.get(instance_id, None)
    if not instance:
        return resp.instance_not_found
    elif not instance.inst_db:
        return resp.plugin_has_no_database
    tables = instance.get_db_tables()
    try:
        table = tables[request.match_info.get("table", "")]
    except KeyError:
        return resp.table_not_found
    try:
        order = [tuple(order.split(":")) for order in request.query.getall("order")]
        order = [(asc if sort.lower() == "asc" else desc)(table.columns[column])
                 if sort else table.columns[column]
                 for column, sort in order]
    except KeyError:
        order = []
    limit = int(request.query.get("limit", 100))
    return execute_query(instance, table.select().order_by(*order).limit(limit)) 

def get_output_files_for_output_type_and_entity(
    entity_id, output_type_id, representation=None
):
    """
    Get output files created for given entity and output type.
    """
    query = (
        OutputFile.query.filter(OutputFile.entity_id == entity_id)
        .filter(OutputFile.asset_instance_id == None)  # noqa Entity only
        .filter(OutputFile.output_type_id == output_type_id)
        .order_by(desc(OutputFile.revision))
    )

    if representation is not None:
        query = query.filter(OutputFile.representation == representation)

    output_files = query.all()
    return OutputFile.serialize_list(output_files) 

def query_for_task_instance(task_instance, descending=False, session=None):
        """
        Returns query for task reschedules for a given the task instance.

        :param session: the database session object
        :type session: sqlalchemy.orm.session.Session
        :param task_instance: the task instance to find task reschedules for
        :type task_instance: airflow.models.TaskInstance
        :param descending: If True then records are returned in descending order
        :type descending: bool
        """
        TR = TaskReschedule
        qry = (
            session
            .query(TR)
            .filter(TR.dag_id == task_instance.dag_id,
                    TR.task_id == task_instance.task_id,
                    TR.execution_date == task_instance.execution_date,
                    TR.try_number == task_instance.try_number)
        )
        if descending:
            return qry.order_by(desc(TR.id))
        else:
            return qry.order_by(asc(TR.id)) 

def get_working_files_for_entity(entity_id, task_id=None, name=None):
    """
    Retrieve all working files for a given entity and specified parameters
    ordered by revision from biggest to smallest revision.
    """
    query = WorkingFile.query.filter_by(entity_id=entity_id)

    if task_id:
        query = query.filter(WorkingFile.task_id == task_id)
    if name:
        query = query.filter(WorkingFile.name == name)

    query = query.filter(WorkingFile.revision >= 0).order_by(
        desc(WorkingFile.revision)
    )

    working_files = query.all()
    return fields.serialize_models(working_files) 

def paginate(
        self, db_session: Session, query=None, page=None, per_page=None, count=True, query_all=False, **kwargs
    ):
        if not query:
            query = db_session.query(self.model).filter_by(**kwargs).order_by(desc(self.model.id))

        if query_all:
            items = query.all()
        else:
            items = query.limit(per_page).offset((page - 1) * per_page).all()

        if not count:
            total = None
        else:
            total = query.order_by(None).count()

        return Pagination(query, page, per_page, total, items) 

def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        if len(value) < 2:
            return qs.filter(sql.false())

        ordering = value.split(',')

        def map_field(name):
            descending = False
            if name.startswith('-'):
                name = name[1:]
                descending = True
            field = getattr(self.model, name)
            if descending:
                field = desc(field)
            return field

        if ordering:
            qs = qs.order_by(*map(lambda x: map_field(x), ordering))

        return qs 

def get_watchlist_messages(session, user, limit=None):

    watched = user_watch_list(session, user.username)

    watched_list = [watch.id for watch in watched]

    events = (
        session.query(model.PagureLog)
        .filter(model.PagureLog.project_id.in_(watched_list))
        .order_by(model.PagureLog.id.desc())
    )

    if limit is not None:
        events = events.limit(limit)

    events = events.all()

    return events 

def get_last_trades(session, page_size = None, offset = None, sort_column = None, sort_order='ASC'):

    trades = session.query(Trade).order_by(
      Trade.created.desc())

    if page_size:
      trades = trades.limit(page_size)
    if offset:
      trades = trades.offset(offset)
    if sort_column:
      if sort_order == 'ASC':
        trades = trades.order(sort_column)
      else:
        trades = trades.order(sort_column).desc()

    return trades 

def renderFeedsSourceTable(source, page=1):
	feeds = g.session.query(db.RssFeedPost) \
		.filter(db.RssFeedPost.srcname == source)  \
		.order_by(desc(db.RssFeedPost.published))

	if feeds is None:
		flash('No feeds? Something is /probably/ broken!.')
		return redirect(url_for('renderFeedsTable'))

	feed_entries = paginate(feeds, page, app.config['FEED_ITEMS_PER_PAGE'])

	return render_template('rss-pages/feeds.html',
						   subheader = "Source = '%s'" % source,
						   sequence_item   = feed_entries,
						   page            = page
						   ) 

def renderFeedsTable(page=1):

	feeds = g.session.query(db.RssFeedPost)       \
		.order_by(desc(db.RssFeedPost.published))


	feeds = feeds.options(joinedload('tag_rel'))
	feeds = feeds.options(joinedload('author_rel'))



	if feeds is None:
		flash('No feeds? Something is /probably/ broken!.')
		return redirect(url_for('renderFeedsTable'))

	feed_entries = paginate(feeds, page, app.config['FEED_ITEMS_PER_PAGE'])

	return render_template('rss-pages/feeds.html',
						   subheader = "",
						   sequence_item   = feed_entries,
						   page            = page
						   ) 

def review_probable_validated(self):
		self.log.info("Doing optional validation")
		with db.session_context() as db_sess:
			new_items = db_sess.query(db.NuReleaseItem)           \
					.filter(db.NuReleaseItem.validated == True)        \
					.filter(db.NuReleaseItem.reviewed == 'unverified') \
					.filter(db.NuReleaseItem.actual_target != None)    \
					.order_by(desc(db.NuReleaseItem.first_seen))       \
					.all()


			unverified = db_sess.query(db.NuReleaseItem)           \
					.filter(db.NuReleaseItem.validated == False)        \
					.filter(db.NuReleaseItem.actual_target != None)    \
					.count()

			self.log.info("Have %s items to do validity checks on", len(new_items))
			self.log.info("%s items needing checking", unverified)

			for row in new_items:
				self.review_probable_validated_row(row)

			db_sess.commit() 

def errors(self, ctx, count : int=5, page : int=0, excludestring=None):
		"""Gets a list of the most recent errors from loggingdb"""

		for error in loggingdb.session.query(loggingdb_spec.Error).order_by(desc(loggingdb_spec.Error.timestamp)).offset(page * count):
			if count <= 0:
				return
			error_chunks = error.error_text_chunks()
			try:
				if excludestring and re.search(excludestring, "\n".join(error_chunks)):
					continue # skip this because our excludestring matches
			except Exception:
				raise UserError("Regex parsing error")
			await ctx.send(embed=error.to_embed(self))
			for chunk in error_chunks[:-1]:
				await ctx.send(chunk)
				await asyncio.sleep(0.5)

			await ctx.send(error_chunks[-1] + "\n_ _")
			await asyncio.sleep(0.5)
			count -= 1 

def get_list(session, broker_id, status_list, country = None, state=None, client_id=None,  page_size = None, offset = None, sort_column = None, sort_order='ASC'):
    query = session.query(User).filter( User.verified.in_( status_list ) ).filter(User.broker_id==broker_id)

    if country:
      query = query.filter(User.country_code == country)
    if state:
      query = query.filter(User.state == state)

    if client_id:
      query = query.filter( or_( User.username.like(client_id), User.email.like('%' + client_id + '%'), User.verification_data.like( '%'  + client_id + '%') ) )


    if page_size:
      query = query.limit(page_size)
    if offset:
      query = query.offset(offset)
    if sort_column:
      if sort_order == 'ASC':
        query = query.order(sort_column)
      else:
        query = query.order(sort_column).desc()
    return query 

def get_all(cls, include_disabled=True):
        """Returns a list of all accounts of a given type

        Args:
            include_disabled (`bool`): Include disabled accounts. Default: `True`

        Returns:
            list of account objects
        """
        if cls == BaseAccount:
            raise InquisitorError('get_all on BaseAccount is not supported')

        account_type_id = db.AccountType.find_one(account_type=cls.account_type).account_type_id
        qry = db.Account.order_by(desc(Account.enabled), Account.account_type_id, Account.account_name)

        if not include_disabled:
            qry = qry.filter(Account.enabled == 1)

        accounts = qry.find(Account.account_type_id == account_type_id)

        return {res.account_id: cls(res) for res in accounts} 

def index():
    sorter = request.args.get("sorter", None)
    if sorter is None:
        sorter = "random"
        initial_sorting = True
    else:
        initial_sorting = False
    order = request.args.get("order", None)
    criterion = SORTER.get(sorter, DEFAULT_SORTER)
    if order == DEFAULT_ORDER:
        criterion = desc(criterion)

    projects = Project.query.filter(Project.is_active.is_(True)).order_by(
        nullslast(criterion)
    )
    return {
        "projects": projects,
        "sorter": sorter,
        "initial_sorting": initial_sorting,
        "order": order,
        "DEFAULT_ORDER": DEFAULT_ORDER,
    } 

def get_list(session, broker_id, account_id, status_list, page_size, offset, filter_array) :
    query = session.query(Withdraw).filter( Withdraw.status.in_( status_list ) ).filter(Withdraw.broker_id==broker_id)

    if account_id:
      query = query.filter( Withdraw.account_id == account_id  )

    for filter in filter_array:
      if filter:
        if filter.isdigit():
          query = query.filter( or_( Withdraw.data.like('%' + filter + '%' ),
                                     Withdraw.currency == filter,
                                     Withdraw.amount == int(filter) * 1e8,
                                     ))
        else:
          query = query.filter( or_( Withdraw.data.like('%' + filter + '%'),
                                     Withdraw.currency == filter ) )

    query = query.order_by(Withdraw.created.desc())

    if page_size:
      query = query.limit(page_size)
    if offset:
      query = query.offset(offset)

    return query 

def _unpick_search(sort, allowed_fields=None, total=None):
    ''' This is a helper function that takes a sort string
    eg 'name asc, last_modified desc' and returns a list of
    split field order eg [('name', 'asc'), ('last_modified', 'desc')]
    allowed_fields can limit which field names are ok.
    total controls how many sorts can be specifed '''
    sorts = []
    split_sort = sort.split(',')
    for part in split_sort:
        split_part = part.strip().split()
        field = split_part[0]
        if len(split_part) > 1:
            order = split_part[1].lower()
        else:
            order = 'asc'
        if allowed_fields:
            if field not in allowed_fields:
                raise ValidationError('Cannot sort by field `%s`' % field)
        if order not in ['asc', 'desc']:
            raise ValidationError('Invalid sort direction `%s`' % order)
        sorts.append((field, order))
    if total and len(sorts) > total:
        raise ValidationError(
            'Too many sort criteria provided only %s allowed' % total)
    return sorts 

def get_apps(request: Request, app_type: str, page: int):
    """
    获取app
    - uri[app类型(all/iOS/android)-app_type: str, 页码(从1起)-page: int], format[时间s-t: int]
    :param request:
    :return:
    """
    time = Date.time2datetime(request.args.get('t'))
    if not time:
        raise BadRequest('')

    if page <= 0:
        log.debug('page need greater zero')
        raise BadRequest('')

    kw = request.args.get('kw')

    session = Session()
    query = session.query(AppModel, AppVersionModel.version_code, AppVersionModel.version_name,
                          func.max(AppVersionModel.create_at).label('_update_at')) \
        .join(AppVersionModel, AppModel.id == AppVersionModel.app_id) \
        .filter(AppModel.create_at <= time)
    if app_type != 'all':  # 安装包类型过滤
        query = query.filter(AppModel.type == app_type)

    if kw:
        query = query.filter(AppModel.name.like('%{}%'.format(kw)))

    result = query.order_by(desc(AppModel.create_at)) \
        .group_by(AppModel.short_chain_uri_) \
        .offset((page - 1) * Config.apps_limit) \
        .limit(Config.apps_limit) \
        .all()

    datas = []
    for app, version_code, version_name, _ in result:
        app.version_code = version_code
        app.version_name = version_name
        datas.append(app)

    return JsonResult.ok(datas).response_json() 

def add_asset_instance_to_scene(scene_id, asset_id, description=""):
    """
    Create a new asset instance for given asset and scene.
    """
    instance = (
        AssetInstance.query.filter(AssetInstance.scene_id == scene_id)
        .filter(AssetInstance.asset_id == asset_id)
        .order_by(desc(AssetInstance.number))
        .first()
    )

    number = 1
    if instance is not None:
        number = instance.number + 1
    name = build_asset_instance_name(asset_id, number)

    asset_instance = AssetInstance.create(
        asset_id=asset_id,
        scene_id=scene_id,
        number=number,
        name=name,
        description=description,
    ).serialize()

    events.emit(
        "asset_instance:new",
        {
            "scene_id": scene_id,
            "asset_id": asset_id,
            "asset_instance_id": asset_instance["id"],
        },
    )
    return asset_instance 

def sort_query(query, table, sort_keys, sort_dir=None, sort_dirs=None):

    if 'id' not in sort_keys:
        # TODO(justinsb): If this ever gives a false-positive, check
        # the actual primary key, rather than assuming its id
        LOG.warning('Id not in sort_keys; is sort_keys unique?')

    assert(not (sort_dir and sort_dirs))

    # Default the sort direction to ascending
    if sort_dirs is None and sort_dir is None:
        sort_dir = 'asc'

    # Ensure a per-column sort direction
    if sort_dirs is None:
        sort_dirs = [sort_dir for _sort_key in sort_keys]

    assert(len(sort_dirs) == len(sort_keys))

    for current_sort_key, current_sort_dir in \
            six.moves.zip(sort_keys, sort_dirs):
        try:
            sort_dir_func = {
                'asc': sqlalchemy.asc,
                'desc': sqlalchemy.desc,
            }[current_sort_dir]
        except KeyError:
            raise ValueError(_("Unknown sort direction, "
                               "must be 'desc' or 'asc'"))
        try:
            sort_key_attr = getattr(table.c, current_sort_key)
        except AttributeError:
            raise utils.InvalidSortKey()
        query = query.order_by(sort_dir_func(sort_key_attr))

    return query, sort_dirs 

def paginate_query(query, table, limit, sort_keys, marker=None,
                   sort_dir=None, sort_dirs=None):

    # Add sorting
    query, sort_dirs = sort_query(query, table, sort_keys, sort_dir=sort_dir)

    # Add pagination
    if marker is not None:
        marker_values = []
        for sort_key in sort_keys:
            v = marker[sort_key]
            marker_values.append(v)

        # Build up an array of sort criteria as in the docstring
        criteria_list = []
        for i in range(len(sort_keys)):
            crit_attrs = []
            for j in range(i):
                table_attr = getattr(table.c, sort_keys[j])
                crit_attrs.append((table_attr == marker_values[j]))

            table_attr = getattr(table.c, sort_keys[i])
            if sort_dirs[i] == 'desc':
                crit_attrs.append((table_attr < marker_values[i]))
            else:
                crit_attrs.append((table_attr > marker_values[i]))

            criteria = sqlalchemy.sql.and_(*crit_attrs)
            criteria_list.append(criteria)

        f = sqlalchemy.sql.or_(*criteria_list)
        query = query.where(f)

    if limit is not None:
        query = query.limit(limit)

    return query 

def list(self, request):
        await require(request, Permissions.view)
        columns_names = list(self._table.c.keys())
        q = validate_query(request.query, columns_names)
        paging = calc_pagination(q, self._primary_key)

        filters = q.get('_filters')
        async with self.pool.acquire() as conn:
            if filters:
                query = create_filter(self.table, filters)
            else:
                query = self.table.select()
            count = await conn.scalar(
                sa.select([sa.func.count()])
                .select_from(query.alias('foo')))

            sort_dir = sa.asc if paging.sort_dir == ASC else sa.desc
            cursor = await conn.execute(
                query
                .offset(paging.offset)
                .limit(paging.limit)
                .order_by(sort_dir(paging.sort_field)))

            recs = await cursor.fetchall()

            entities = list(map(dict, recs))

        headers = {'X-Total-Count': str(count)}
        return json_response(entities, headers=headers) 

def get_preview_files_for_project(project_id, page=-1):
    """
    Return all preview files for given project.
    """
    query = (
        PreviewFile.query.join(Task)
        .filter(Task.project_id == project_id)
        .order_by(desc(PreviewFile.updated_at))
    )
    return query_utils.get_paginated_results(query, page) 

def get_preview_files_for_task(task_id):
    """
    Get all preview files for given task.
    """
    previews = PreviewFile.filter_by(task_id=task_id).order_by(
        PreviewFile.revision.desc()
    )
    return fields.serialize_models(previews)