Python func.row_number方法代碼示例

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_clause_expansion(self):
        Data = self.classes.Data

        b1 = Bundle("b1", Data.id, Data.d1, Data.d2)

        sess = Session()
        self.assert_compile(
            sess.query(Data).order_by(b1),
            "SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2, data.d3 AS data_d3 FROM data "
            "ORDER BY data.id, data.d1, data.d2",
        )

        self.assert_compile(
            sess.query(func.row_number().over(order_by=b1)),
            "SELECT row_number() OVER (ORDER BY data.id, data.d1, data.d2) "
            "AS anon_1 FROM data",
        ) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def over(self, partition_by=None, order_by=None, rows=None, range_=None):
        """Produce an OVER clause against this function.

        Used against aggregate or so-called "window" functions,
        for database backends that support window functions.

        The expression::

            func.row_number().over(order_by='x')

        is shorthand for::

            from sqlalchemy import over
            over(func.row_number(), order_by='x')

        See :func:`_expression.over` for a full description.

        """
        return Over(
            self,
            partition_by=partition_by,
            order_by=order_by,
            rows=rows,
            range_=range_,
        ) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_over_invalid_framespecs(self):
        assert_raises_message(
            exc.ArgumentError,
            "Integer or None expected for range value",
            func.row_number().over,
            range_=("foo", 8),
        )

        assert_raises_message(
            exc.ArgumentError,
            "Integer or None expected for range value",
            func.row_number().over,
            range_=(-5, "foo"),
        )

        assert_raises_message(
            exc.ArgumentError,
            "'range_' and 'rows' are mutually exclusive",
            func.row_number().over,
            range_=(-5, 8),
            rows=(-2, 5),
        ) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def __init__(self, func, partition_by=None, order_by=None):
        """Produce an :class:`.Over` object against a function.

        Used against aggregate or so-called "window" functions,
        for database backends that support window functions.

        E.g.::

            from sqlalchemy import over
            over(func.row_number(), order_by='x')

        Would produce "ROW_NUMBER() OVER(ORDER BY x)".

        :param func: a :class:`.FunctionElement` construct, typically
         generated by :data:`~.expression.func`.
        :param partition_by: a column element or string, or a list
         of such, that will be used as the PARTITION BY clause
         of the OVER construct.
        :param order_by: a column element or string, or a list
         of such, that will be used as the ORDER BY clause
         of the OVER construct.

        This function is also available from the :data:`~.expression.func`
        construct itself via the :meth:`.FunctionElement.over` method.

        .. versionadded:: 0.7

        """
        self.func = func
        if order_by is not None:
            self.order_by = ClauseList(
                *util.to_list(order_by),
                _literal_as_text=_literal_as_label_reference)
        if partition_by is not None:
            self.partition_by = ClauseList(
                *util.to_list(partition_by),
                _literal_as_text=_literal_as_label_reference) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def get_storage_at_block(cls, current_session, block_number):
        """
        Gives the storage values at requested block

        :param int block_number: Block number of desired storage, always
        required since it is called from method `State.get_state_at_block`
        """
        row_number_column = func.row_number().over(
            partition_by=[StorageDiff.address, StorageDiff.position],
            order_by=[StorageDiff.block_number.desc(),
                      StorageDiff.transaction_index.desc()])\
            .label('row_number')
        query = current_session.db_session.query(StorageDiff.address, StorageDiff.position, StorageDiff.storage_to.label('storage'))
        query = query.add_column(row_number_column)
        query = query.filter(
            and_(
                StorageDiff.block_number <= block_number,
                StorageDiff.storage_to != '0x0000000000000000000000000000000000000000000000000000000000000000'))
        query = query.from_self().filter(row_number_column == 1)

        for row in query:
            if row.storage is not None:
                if hex_to_integer(row.storage) != 0:
                    storage = cls.add_storage(address=row.address, position=row.position, storage=row.storage)
                else:
                    # not adding storage positions where value is 0, since parity discards these positions during export
                    logger.debug('address: {}, position {}, storage: {} is zero'.format(row.address, row.position, row.storage))
                current_session.db_session.add(storage)
            else:
                logger.debug('address: {}, position {}, storage: {} is none'.format(row.address, row.position, row.storage)) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def get_row_number(Model, queryset, instance):
    mapper = inspect(Model)
    pk = mapper.primary_key[0].name

    subqquery = queryset.with_entities(
        mapper.primary_key[0].label('__inner__pk'),
        func.row_number().over(order_by=queryset._order_by).label('__inner__row')
    ).subquery()

    rest = queryset.session.query(subqquery.c.__inner__row).filter(subqquery.c.__inner__pk == getattr(instance, pk))
    return rest.scalar() 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def get_row_siblings(Model, queryset, row_number):
    mapper = inspect(Model)
    pk = mapper.primary_key[0].name

    has_prev = row_number > 1
    offset = row_number - 2 if has_prev else row_number - 1
    limit = 3 if has_prev else 2

    rows = queryset.options(load_only(pk)).limit(limit).offset(offset).all()

    if has_prev:
        next_index = 2
    else:
        next_index = 1

    if next_index >= len(rows):
        next_index = None

    if has_prev:
        prev_index = 0
    else:
        prev_index = None

    def map_row(row):
        return dict(((pk, getattr(row, pk)),))

    return {
        'prev': map_row(rows[prev_index]) if prev_index is not None else None,
        'next': map_row(rows[next_index]) if next_index is not None else None
    } 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def get_model_siblings(request, Model, instance, queryset):
    count = queryset_count_optimized(request, queryset)

    if count > 10000:
        return {}

    queryset = apply_default_ordering(queryset)
    row_number = get_row_number(Model, queryset, instance)

    if not row_number:
        return {}

    return get_row_siblings(Model, queryset, row_number) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def column_windows(session, column, windowsize):
    """Return a series of WHERE clauses against
    a given column that break it into windows.

    Result is an iterable of tuples, consisting of
    ((start, end), whereclause), where (start, end) are the ids.

    Requires a database that supports window functions,
    i.e. Postgresql, SQL Server, Oracle.

    Enhance this yourself !  Add a "where" argument
    so that windows of just a subset of rows can
    be computed.

    """

    def int_for_range(start_id, end_id):
        if end_id:
            return and_(column >= start_id, column < end_id)
        else:
            return column >= start_id

    q = session.query(
        column, func.row_number().over(order_by=column).label("rownum")
    ).from_self(column)

    if windowsize > 1:
        q = q.filter(sqlalchemy.text("rownum %% %d=1" % windowsize))

    intervals = [id for id, in q]

    while intervals:
        start = intervals.pop(0)
        if intervals:
            end = intervals[0]
        else:
            end = None
        yield int_for_range(start, end) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def __init__(self, func, partition_by=None, order_by=None):
        """Produce an :class:`.Over` object against a function.

        Used against aggregate or so-called "window" functions,
        for database backends that support window functions.

        E.g.::

            from sqlalchemy import over
            over(func.row_number(), order_by='x')

        Would produce "ROW_NUMBER() OVER(ORDER BY x)".

        :param func: a :class:`.FunctionElement` construct, typically
         generated by :data:`~.expression.func`.
        :param partition_by: a column element or string, or a list
         of such, that will be used as the PARTITION BY clause
         of the OVER construct.
        :param order_by: a column element or string, or a list
         of such, that will be used as the ORDER BY clause
         of the OVER construct.

        This function is also available from the :data:`~.expression.func`
        construct itself via the :meth:`.FunctionElement.over` method.

        .. versionadded:: 0.7

        """
        self.func = func
        if order_by is not None:
            self.order_by = ClauseList(*util.to_list(order_by))
        if partition_by is not None:
            self.partition_by = ClauseList(*util.to_list(partition_by)) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def query(self, source, target, core, column, pkey):
        # Get all POIs of fclass `on`
        pois = select(
            [source.c[self.source_id], source.c.WKT],
            source.c[self.source_column] == self.source_filter,
        ).cte("pois")
        # Compute the distance from `column` to each POI within given distance
        distance = func.ST_Distance(
            core.ST_GeoFromText(target.c[column]),
            core.ST_GeoFromText(pois.c.WKT),
        )
        pairs = (
            select(
                [target, distance.label(self.feature_name)],
                distance < self.within,
            )
            .select_from(pois)
            .cte("pairs")
        )
        # Partition results to get the smallest distance (nearest POI)
        query = select(
            [
                pairs,
                func.row_number()
                .over(
                    partition_by=pairs.c[pkey],
                    order_by=pairs.c[self.feature_name].asc(),
                )
                .label("row_number"),
            ]
        ).select_from(pairs)
        query = select(
            [col for col in query.columns if col.key != "row_number"],
            query.c["row_number"] == 1,
        )
        return query 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_no_paren_fns(self):
        for fn, expected in [
            (func.uid(), "uid"),
            (func.UID(), "UID"),
            (func.sysdate(), "sysdate"),
            (func.row_number(), "row_number()"),
            (func.rank(), "rank()"),
            (func.now(), "CURRENT_TIMESTAMP"),
            (func.current_timestamp(), "CURRENT_TIMESTAMP"),
            (func.user(), "USER"),
        ]:
            self.assert_compile(fn, expected) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_over(self):
        expr = func.row_number().over(order_by=t1.c.col1)
        expr2 = CloningVisitor().traverse(expr)
        assert str(expr) == str(expr2)

        assert expr in visitors.iterate(expr, {}) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_within_group(self):
        expr = func.row_number().within_group(t1.c.col1)
        expr2 = CloningVisitor().traverse(expr)
        assert str(expr) == str(expr2)

        assert expr in visitors.iterate(expr, {}) 

# 需要導入模塊: from sqlalchemy import func [as 別名]
# 或者: from sqlalchemy.func import row_number [as 別名]
def test_over(self):
        stmt = select([column("foo"), column("bar")]).subquery()
        stmt = select(
            [func.row_number().over(order_by="foo", partition_by="bar")]
        ).select_from(stmt)

        self.assert_compile(
            stmt,
            "SELECT row_number() OVER "
            "(PARTITION BY anon_2.bar ORDER BY anon_2.foo) "
            "AS anon_1 FROM (SELECT foo, bar) AS anon_2",
        )