Python sqlalchemy.asc() Examples

def test_oc():
    a = asc('a')
    b = desc('a')
    c = asc('b')
    n = nullslast(desc('a'))

    a = OC(a)
    b = OC(b)
    c = OC(c)
    n = OC(n)

    assert str(a) == str(OC('a'))
    assert a.is_ascending
    assert not b.is_ascending
    assert not n.reversed.reversed.is_ascending
    assert n.reversed.is_ascending
    assert not n.is_ascending # make sure reversed doesn't modify in-place
    assert str(a.element) == str(b.element) == str(n.element)
    assert str(a) == str(b.reversed)
    assert str(n.reversed.reversed) == str(n)

    assert a.name == 'a'
    assert n.name == 'a'
    assert n.quoted_full_name == 'a'
    assert repr(n) == '<OC: a DESC NULLS LAST>' 

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 __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 dispatch_messages(control_function=None):
    """Dispatches all messages currently in queue.
       An optional control_function can return True to break out of the loop prematurely."""
    from saq.database import Message
    attempted_ids = [] # list of Message.id values we want to delete
    for message in saq.db.query(Message).order_by(Message.insert_date.asc()):
        try:
            dispatch(message)
            attempted_ids.append(message.id)
        except Exception as e:
            logging.error(f"unable to dispatch {message}: {e}")

        if control_function is not None and control_function():
            break

    if attempted_ids:
        saq.db.execute(Message.__table__.delete().where(Message.id.in_(attempted_ids)))
        saq.db.commit() 

def execute(self):
        from saq.database import Message
        attempted_ids = [] # list of Message.id values we want to delete
        for message in saq.db.query(Message).order_by(Message.insert_date.asc()):
            try:
                dispatch(message)
                attempted_ids.append(message.id)
            except Exception as e:
                logging.error(f"unable to dispatch {message}: {e}")

            if control_function is not None and control_function():
                break

        if attempted_ids:
            saq.db.execute(Message.__table__.delete().where(Message.id.in_(attempted_ids)))
            saq.db.commit()

#
# global mapping of notification systems to the handlers that process them 

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(self):
        resdata = []
        prices = db_session.query(
            FuelFill
        ).filter(
            FuelFill.cost_per_gallon.__ne__(None)
        ).order_by(asc(FuelFill.date))
        for point in prices.all():
            ds = point.date.strftime('%Y-%m-%d')
            resdata.append({
                'date': ds,
                'price': float(point.cost_per_gallon)
            })
        res = {
            'data': resdata
        }
        return jsonify(res) 

def test_order_manipulation():
    is_asc = lambda c: _get_order_direction(c) == asc_op
    flip = _reverse_order_direction
    scrub = _remove_order_direction
    base = column('a')
    l = base.label('test')
    a = asc(base)
    d = desc(base)
    assert is_asc(a)
    assert not is_asc(d)
    equal_pairs = [
        (scrub(a), base),
        (scrub(d), base),
        (scrub(asc(l)), scrub(a.label('test'))),
        (flip(a), d),
        (flip(d), a),
    ]
    for lhs, rhs in equal_pairs:
        assert str(lhs) == str(rhs) 

def __init__(self, x):
        if isinstance(x, str):
            x = column(x)
        if _get_order_direction(x) is None:
            x = asc(x)
        self.uo = x
        _warn_if_nullable(self.comparable_value)
        self.full_name = str(self.element)
        try:
            table_name, name = self.full_name.split('.', 1)
        except ValueError:
            table_name = None
            name = self.full_name

        self.table_name = table_name
        self.name = name 

def get_by_time(cls, ts, range=None):
        if range == None:
            # XXX TODO: Test this
            return database.db.getSession().query(Blocks).filter(Blocks.timestamp <= ts).first()
        else:
            ts_start = ts-range
            ts_end = ts
            return list(database.db.getSession().query(Blocks).filter(and_(Blocks.timestamp >= ts_start, Blocks.timestamp <= ts_end)).order_by(asc(Blocks.height)))



# def main():
#     PROCESS = "GrinPoolBaseModelBlockTest"
#     from grinlib import lib
#     config = lib.get_config()
#     logger = lib.get_logger(PROCESS)
#     logger.error("test")
#     database = lib.get_db()
# 
# 
# if __name__ == "__main__":
#     main() 

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 test_paginate_query_no_pagination(self):
        self.query.order_by.return_value = self.query
        self.mock_asc.side_effect = ['asc']
        self.mock_desc.side_effect = ['desc']

        utils.paginate_query(self.query, self.model, 5,
                             ['user_id', 'project_id'],
                             sort_dirs=['asc', 'desc'])

        self.mock_asc.assert_called_once_with(self.model.user_id)
        self.mock_desc.assert_called_once_with(self.model.project_id)
        self.query.order_by.assert_has_calls([
            mock.call('asc'),
            mock.call('desc'),
        ])
        self.query.limit.assert_called_once_with(5) 

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 _create_nullsfirst(cls, column):
        """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.

        :func:`.nullsfirst` is intended to modify the expression produced
        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
        should be handled when they are encountered during ordering::


            from sqlalchemy import desc, nullsfirst

            stmt = select([users_table]).\\
                        order_by(nullsfirst(desc(users_table.c.name)))

        The SQL expression from the above would resemble::

            SELECT id, name FROM user ORDER BY name DESC NULLS FIRST

        Like :func:`.asc` and :func:`.desc`, :func:`.nullsfirst` is typically
        invoked from the column expression itself using
        :meth:`.ColumnElement.nullsfirst`, rather than as its standalone
        function version, as in::

            stmt = (select([users_table]).
                    order_by(users_table.c.name.desc().nullsfirst())
                    )

        .. seealso::

            :func:`.asc`

            :func:`.desc`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_text(column), modifier=operators.nullsfirst_op) 

def _create_asc(cls, column):
        """Produce an ascending ``ORDER BY`` clause element.

        e.g.::

            from sqlalchemy import asc
            stmt = select([users_table]).order_by(asc(users_table.c.name))

        will produce SQL as::

            SELECT id, name FROM user ORDER BY name ASC

        The :func:`.asc` function is a standalone version of the
        :meth:`.ColumnElement.asc` method available on all SQL expressions,
        e.g.::


            stmt = select([users_table]).order_by(users_table.c.name.asc())

        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
         with which to apply the :func:`.asc` operation.

        .. seealso::

            :func:`.desc`

            :func:`.nullsfirst`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_text(column), modifier=operators.asc_op) 

def _create_desc(cls, column):
        """Produce a descending ``ORDER BY`` clause element.

        e.g.::

            from sqlalchemy import desc

            stmt = select([users_table]).order_by(desc(users_table.c.name))

        will produce SQL as::

            SELECT id, name FROM user ORDER BY name DESC

        The :func:`.desc` function is a standalone version of the
        :meth:`.ColumnElement.desc` method available on all SQL expressions,
        e.g.::


            stmt = select([users_table]).order_by(users_table.c.name.desc())

        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
         with which to apply the :func:`.desc` operation.

        .. seealso::

            :func:`.asc`

            :func:`.nullsfirst`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_text(column), modifier=operators.desc_op) 

def _create_nullslast(cls, column):
        """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.

        :func:`.nullslast` is intended to modify the expression produced
        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
        should be handled when they are encountered during ordering::


            from sqlalchemy import desc, nullslast

            stmt = select([users_table]).\\
                        order_by(nullslast(desc(users_table.c.name)))

        The SQL expression from the above would resemble::

            SELECT id, name FROM user ORDER BY name DESC NULLS LAST

        Like :func:`.asc` and :func:`.desc`, :func:`.nullslast` is typically
        invoked from the column expression itself using
        :meth:`.ColumnElement.nullslast`, rather than as its standalone
        function version, as in::

            stmt = select([users_table]).\\
                        order_by(users_table.c.name.desc().nullslast())

        .. seealso::

            :func:`.asc`

            :func:`.desc`

            :func:`.nullsfirst`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_text(column), modifier=operators.nullslast_op) 

def paginate_query(query, model, limit, sort_key='created_at',
                   sort_dir='desc', offset=None):
    """Returns a query with sorting / pagination criteria added.

    :param query: the query object to which we should add paging/sorting
    :param model: the ORM model class
    :param limit: maximum number of items to return
    :param sort_key: attributes by which results should be sorted, default is
                      created_at
    :param sort_dir: direction in which results should be sorted (asc, desc)
    :param offset: the number of items to skip from the marker or from the
                    first element.

    :rtype: sqlalchemy.orm.query.Query
    :return: The query with sorting/pagination added.
    """

    try:
        sort_key_attr = getattr(model, sort_key)
    except AttributeError:
        raise exception.InvalidInput(reason='Invalid sort key %s' % sort_key)
    if sort_dir == 'desc':
        query = query.order_by(sqlalchemy.desc(sort_key_attr))
    else:
        query = query.order_by(sqlalchemy.asc(sort_key_attr))

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

    if offset:
        query = query.offset(offset)

    return query 

def get_one_reserve_list_by_cluster_port_for_update(session, cluster_port,
                                                    notification_hostname):
    # SELECT id,hostname FROM reserve_list
    #   WHERE deleted=0 and cluster_port=:cluster_port
    #   and hostname!=:notification_hostname
    #   ORDER by create_at asc limit 1 FOR UPDATE
    with _sqlalchemy_error():
        res = session.query(
            ReserveList).with_for_update().filter_by(deleted=0).filter_by(
                cluster_port=cluster_port).filter(
                    ReserveList.hostname != notification_hostname).order_by(
                        asc(ReserveList.create_at)).first()
    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_by_height(cls, height, range=None):
        if range == None:
            return database.db.getSession().query(Blocks).filter(Blocks.height == height).first()
        else:
            h_start = height-(range-1)
            h_end = height
            return list(database.db.getSession().query(Blocks).filter(and_(Blocks.height >= h_start, Blocks.height <= h_end)).order_by(asc(Blocks.height)))

    # Get stats records falling within requested range 

def _create_asc(cls, column):
        """Produce an ascending ``ORDER BY`` clause element.

        e.g.::

            from sqlalchemy import asc
            stmt = select([users_table]).order_by(asc(users_table.c.name))

        will produce SQL as::

            SELECT id, name FROM user ORDER BY name ASC

        The :func:`.asc` function is a standalone version of the
        :meth:`.ColumnElement.asc` method available on all SQL expressions,
        e.g.::


            stmt = select([users_table]).order_by(users_table.c.name.asc())

        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
         with which to apply the :func:`.asc` operation.

        .. seealso::

            :func:`.desc`

            :func:`.nullsfirst`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_label_reference(column),
            modifier=operators.asc_op,
            wraps_column_expression=False) 

def _create_desc(cls, column):
        """Produce a descending ``ORDER BY`` clause element.

        e.g.::

            from sqlalchemy import desc

            stmt = select([users_table]).order_by(desc(users_table.c.name))

        will produce SQL as::

            SELECT id, name FROM user ORDER BY name DESC

        The :func:`.desc` function is a standalone version of the
        :meth:`.ColumnElement.desc` method available on all SQL expressions,
        e.g.::


            stmt = select([users_table]).order_by(users_table.c.name.desc())

        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
         with which to apply the :func:`.desc` operation.

        .. seealso::

            :func:`.asc`

            :func:`.nullsfirst`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_label_reference(column),
            modifier=operators.desc_op,
            wraps_column_expression=False) 

def _create_nullslast(cls, column):
        """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.

        :func:`.nullslast` is intended to modify the expression produced
        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
        should be handled when they are encountered during ordering::


            from sqlalchemy import desc, nullslast

            stmt = select([users_table]).\\
                        order_by(nullslast(desc(users_table.c.name)))

        The SQL expression from the above would resemble::

            SELECT id, name FROM user ORDER BY name DESC NULLS LAST

        Like :func:`.asc` and :func:`.desc`, :func:`.nullslast` is typically
        invoked from the column expression itself using
        :meth:`.ColumnElement.nullslast`, rather than as its standalone
        function version, as in::

            stmt = select([users_table]).\\
                        order_by(users_table.c.name.desc().nullslast())

        .. seealso::

            :func:`.asc`

            :func:`.desc`

            :func:`.nullsfirst`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_label_reference(column),
            modifier=operators.nullslast_op,
            wraps_column_expression=False) 

def _create_nullsfirst(cls, column):
        """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.

        :func:`.nullsfirst` is intended to modify the expression produced
        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
        should be handled when they are encountered during ordering::


            from sqlalchemy import desc, nullsfirst

            stmt = select([users_table]).\\
                        order_by(nullsfirst(desc(users_table.c.name)))

        The SQL expression from the above would resemble::

            SELECT id, name FROM user ORDER BY name DESC NULLS FIRST

        Like :func:`.asc` and :func:`.desc`, :func:`.nullsfirst` is typically
        invoked from the column expression itself using
        :meth:`.ColumnElement.nullsfirst`, rather than as its standalone
        function version, as in::

            stmt = (select([users_table]).
                    order_by(users_table.c.name.desc().nullsfirst())
                    )

        .. seealso::

            :func:`.asc`

            :func:`.desc`

            :func:`.nullslast`

            :meth:`.Select.order_by`

        """
        return UnaryExpression(
            _literal_as_label_reference(column),
            modifier=operators.nullsfirst_op,
            wraps_column_expression=False) 

def test_mappedocols():
    a = AppendedColumn(OC(asc('a')))
    b = DirectColumn(OC(desc('b')), 0)
    assert a.oc.is_ascending
    assert not b.oc.is_ascending
    assert b.reversed.oc.is_ascending
    assert b.reversed.oc.is_ascending 

def get_first_dirty(cls, from_height=0):
        return database.db.getSession().query(Pool_stats).filter(and_(Pool_stats.dirty == True, Pool_stats.height >= from_height)).order_by(asc(Pool_stats.height)).first()

    # Mark a record dirty by height 

def get_by_time(cls, ts, range):
        if range == None:
            # XXX TODO: Test this
            return database.db.getSession().query(Pool_stats).filter(Pool_stats.timestamp <= ts).first()
        else:
            ts_start = ts-range
            ts_end = ts
            return list(database.db.getSession().query(Pool_stats).filter(and_(Pool_stats.timestamp >= ts_start, Pool_stats.timestamp <= ts_end)).order_by(asc(Pool_stats.height)))


    # Get the earliest dirty stat 

def get_latest(cls, n=None):
        highest = database.db.getSession().query(func.max(Pool_stats.height)).scalar()
        if n == None:
            return database.db.getSession().query(Pool_stats).filter(Pool_stats.height == highest).first()
        else:
            return list(database.db.getSession().query(Pool_stats).filter(Pool_stats.height >= highest-n).order_by(asc(Pool_stats.height)))

    # Get record(s) by height 

def test_paginate_query_attribute_error(self):
        self.mock_asc.return_value = 'asc'

        self.assertRaises(exception.InvalidSortKey,
                          utils.paginate_query, self.query,
                          self.model, 5, ['user_id', 'non-existent key'])

        self.mock_asc.assert_called_once_with(self.model.user_id)
        self.query.order_by.assert_called_once_with('asc')