Python sqlalchemy.func.random() Examples

def get_latencies_sample(db_session, endpoint_id, interval, sample_size=500):
    criterion = create_time_based_sample_criterion(interval.start_date(), interval.end_date())

    dialect = db_session.bind.dialect.name

    if dialect in ['sqlite', 'mysql']:
        order_by = func.random() if dialect == 'sqlite' else func.rand()

        items = db_session.query(Request.duration) \
            .filter(Request.endpoint_id == endpoint_id, *criterion) \
            .order_by(order_by) \
            .limit(sample_size) \
            .all()

        durations = [item.duration for item in items]

        return durations
    else:
        return get_latencies_in_timeframe(db_session, endpoint_id, interval.start_date(), interval.end_date()) 

def get_encoding_maps(encrypted_audio_stimuli):
    """
    Build a stimulus key translation map from the `encypted_audio_stimuli`.

    Parameters
    ----------
    encrypted_audio_stimuli: list of tuple
        The first element of each duple is a key, the second is the encrypted audio_file_path
        For all non-references, the key should be of the form E[0-9+]. The order of the stimuli will be random (except
        for the references)

    Returns
    -------
    encoding_map : dict
        A map from unencoded to encoded stimulus keys
    decoding_map : dict
        A map from encoded to unencoded stimulus keys
    decrypted_filenames : dict
        A map from stimulus key to filename
    """
    decrypted_filenames = {}
    encoding_map = {}
    decoding_map = {}

    # decrypt the URLs to find the mapping between s_id and e_id and the real filename
    for k, v in encrypted_audio_stimuli:
        adict = _decode_url(v)
        decrypted_filenames[adict['s_id']] = adict['URL']
        encoding_map[adict['s_id']] = adict['e_id']
        decoding_map[adict['e_id']] = adict['s_id']

    return encoding_map, decoding_map, decrypted_filenames 

def generate_comparison_pairs(condition_datas):
    """
    Generate all stimulus comparison pairs for a condition and return in a random order for a paired comparison test.

    Parameters
    ----------
    condition_datas: list of dict
        List of dictionary of condition data as returned in the test_configuration defined by get_test_configurations()

    Returns
    -------
    condition_datas: list of dict
        List of updated dictionary of condition data with a new field, `comparison_pairs`, which is a list of stimulus
        pairs, e.g. (('E1','E2'),('E5','E8'),...)
    """
    for condition_data in condition_datas:
        stimulus_names = [c[0] for c in condition_data['stimulus_files']]
        pairs = []
        for x in itertools.combinations(stimulus_names, 2):
            if random.randint(0, 1):
                pairs.append(x)
            else:
                pairs.append(x[::-1])
        random.shuffle(pairs)
        condition_data['comparison_pairs'] = pairs

    return condition_datas 

def get_random_people(n, refset_only=False):
    # this simpler way didn't work: func.setseed(0.42)
    # below way is from https://github.com/khanduri/khanduri.github.io/blob/master/_posts/2016-02-26-fetch-rows-in-random-order-with-seed-support.md
    sql = text('select setseed({0});'.format(0.42))
    db.engine.execute(sql)

    q = Person.query
    if refset_only:
        q = q.filter(Person.campaign == "2015_with_urls")

    q = q.order_by(func.random())
    q = q.limit(n)
    people = q.all()
    return people 

def test_generic_random(self):
        assert func.random().type == sqltypes.NULLTYPE
        assert isinstance(func.random(type_=Integer).type, Integer)

        for ret, dialect in [
            ("random()", sqlite.dialect()),
            ("random()", postgresql.dialect()),
            ("rand()", mysql.dialect()),
            ("random()", oracle.dialect()),
        ]:
            self.assert_compile(func.random(), ret, dialect=dialect) 

def get_learnings(how_many=12, sort_order="recent", offset=0):
    ''' Gather and return some recent definitions
    '''
    order_descending = Definition.creation_date.desc()
    order_random = func.random()
    order_alphabetical = Definition.term
    order_function = order_descending
    prefix_singluar = "I recently learned the definition for"
    prefix_plural = "I recently learned definitions for"
    no_definitions_text = "I haven't learned any definitions yet."
    if sort_order == "random":
        order_function = order_random
    elif sort_order == "alpha":
        order_function = order_alphabetical

    if sort_order == "random" or sort_order == "alpha" or offset > 0:
        prefix_singluar = "I know the definition for"
        prefix_plural = "I know definitions for"

    # if how_many is 0, ignore offset and return all results
    if how_many == 0:
        definitions = db.session.query(Definition).order_by(order_function).all()
    # if order is random and there is an offset, randomize the results after the query
    elif sort_order == "random" and offset > 0:
        definitions = db.session.query(Definition).order_by(order_descending).limit(how_many).offset(offset).all()
        random.shuffle(definitions)
    else:
        definitions = db.session.query(Definition).order_by(order_function).limit(how_many).offset(offset).all()

    if not definitions:
        return no_definitions_text, no_definitions_text

    wording = prefix_plural if len(definitions) > 1 else prefix_singluar
    plain_text = "{}: {}".format(wording, ', '.join([item.term for item in definitions]))
    rich_text = "{}: {}".format(wording, ', '.join([make_bold(item.term) for item in definitions]))
    return plain_text, rich_text 

def parse_learnings_params(command_params):
    ''' Parse the passed learnings command params
    '''
    recent_args = {}
    # extract parameters
    params_list = command_params.split(' ')
    for param in params_list:
        if param == "random":
            recent_args['sort_order'] = param
            continue
        if param == "alpha" or param == "alphabetical":
            recent_args['sort_order'] = "alpha"
            continue
        if param == "all":
            recent_args['how_many'] = 0
            continue
        try:
            passed_int = int(param)
            if 'how_many' not in recent_args:
                recent_args['how_many'] = passed_int
            elif 'offset' not in recent_args:
                recent_args['offset'] = passed_int
        except ValueError:
            continue

    return recent_args 

def Random(self):
    """Get the backend-specific random function.

    This can be used to select a random row from a table, e.g.
        session.query(Table).order_by(db.Random()).first()
    """
    if self.url.startswith("mysql"):
      return func.rand
    else:
      return func.random  # for PostgreSQL, SQLite 

def select_random(engine, table_or_columns, limit=5):
    """
    Randomly select some rows from table.
    """
    s = select(table_or_columns).order_by(func.random()).limit(limit)
    return engine.execute(s).fetchall() 

def handle_next(session, bot, chat, tg_chat, user):
    """Handle the /next call or the 'next' button click."""
    # We are tagging a whole sticker set. Skip the current sticker
    if chat.tag_mode == TagMode.STICKER_SET:
        # Check there is a next sticker
        stickers = chat.current_sticker.sticker_set.stickers
        for index, sticker in enumerate(stickers):
            if sticker == chat.current_sticker and index + 1 < len(stickers):
                # We found the next sticker. Send the messages and return
                chat.current_sticker = stickers[index + 1]
                send_tag_messages(chat, tg_chat, user)

                return

        # There are no stickers left, reset the chat and send success message.
        chat.current_sticker.sticker_set.completely_tagged = True
        send_tagged_count_message(session, bot, user, chat)
        tg_chat.send_message(
            "The full sticker set is now tagged.", reply_markup=get_main_keyboard(user)
        )
        chat.cancel(bot)

    # Find a random sticker with no changes
    elif chat.tag_mode == TagMode.RANDOM:
        base_query = (
            session.query(Sticker)
            .outerjoin(Sticker.changes)
            .join(Sticker.sticker_set)
            .filter(Change.id.is_(None))
            .filter(StickerSet.international.is_(False))
            .filter(StickerSet.banned.is_(False))
            .filter(StickerSet.nsfw.is_(False))
            .filter(StickerSet.furry.is_(False))
        )
        # Let the users tag the deluxe sticker set first.
        # If there are no more deluxe sets, just tag another random sticker.
        # Remove the favoring of deluxe stickers until the deluxe pool is bigger again.
        #        sticker = base_query.filter(StickerSet.deluxe.is_(True)) \
        #            .order_by(func.random()) \
        #            .limit(1) \
        #            .one_or_none()
        #        if sticker is None:
        sticker = base_query.order_by(func.random()).limit(1).one_or_none()

        # No stickers for tagging left :)
        if not sticker:
            call_tg_func(
                tg_chat,
                "send_message",
                ["It looks like all stickers are already tagged :)."],
                {"reply_markup": get_main_keyboard(user)},
            )
            chat.cancel(bot)
            return

        # Found a sticker. Send the messages
        chat.current_sticker = sticker
        send_tag_messages(chat, tg_chat, user, send_set_info=True) 

def tablesample(selectable, sampling, name=None, seed=None):
    """Return a :class:`.TableSample` object.

    :class:`.TableSample` is an :class:`.Alias` subclass that represents
    a table with the TABLESAMPLE clause applied to it.
    :func:`~.expression.tablesample`
    is also available from the :class:`.FromClause` class via the
    :meth:`.FromClause.tablesample` method.

    The TABLESAMPLE clause allows selecting a randomly selected approximate
    percentage of rows from a table. It supports multiple sampling methods,
    most commonly BERNOULLI and SYSTEM.

    e.g.::

        from sqlalchemy import func

        selectable = people.tablesample(
                    func.bernoulli(1),
                    name='alias',
                    seed=func.random())
        stmt = select([selectable.c.people_id])

    Assuming ``people`` with a column ``people_id``, the above
    statement would render as::

        SELECT alias.people_id FROM
        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
        REPEATABLE (random())

    .. versionadded:: 1.1

    :param sampling: a ``float`` percentage between 0 and 100 or
        :class:`.functions.Function`.

    :param name: optional alias name

    :param seed: any real-valued SQL expression.  When specified, the
     REPEATABLE sub-clause is also rendered.

    """
    return _interpret_as_from(selectable).tablesample(
        sampling, name=name, seed=seed) 

def tablesample(selectable, sampling, name=None, seed=None):
    """Return a :class:`.TableSample` object.

    :class:`.TableSample` is an :class:`.Alias` subclass that represents
    a table with the TABLESAMPLE clause applied to it.
    :func:`~.expression.tablesample`
    is also available from the :class:`.FromClause` class via the
    :meth:`.FromClause.tablesample` method.

    The TABLESAMPLE clause allows selecting a randomly selected approximate
    percentage of rows from a table. It supports multiple sampling methods,
    most commonly BERNOULLI and SYSTEM.

    e.g.::

        from sqlalchemy import func

        selectable = people.tablesample(
                    func.bernoulli(1),
                    name='alias',
                    seed=func.random())
        stmt = select([selectable.c.people_id])

    Assuming ``people`` with a column ``people_id``, the above
    statement would render as::

        SELECT alias.people_id FROM
        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
        REPEATABLE (random())

    .. versionadded:: 1.1

    :param sampling: a ``float`` percentage between 0 and 100 or
        :class:`.functions.Function`.

    :param name: optional alias name

    :param seed: any real-valued SQL expression.  When specified, the
     REPEATABLE sub-clause is also rendered.

    """
    return _interpret_as_from(selectable).tablesample(
        sampling, name=name, seed=seed) 

def get_test_configurations(condition_ids, participant_id):
    """
    Generate template configuration variables from the list of experimental conditions.

    Parameters
    ----------
    condition_ids : list
    participant_id : int

    Returns
    -------
    test_configuration : list of list of dict
        A list of dictionaries containing all the configuration variables for each test, including a list of conditions
        and their variables
    """
    test_configurations = []

    current_test_id = None
    test_config = None
    for c_id in condition_ids:
        condition = Condition.query.filter_by(id=c_id).first()
        if condition.test_id != current_test_id:
            if test_config is not None:
                test_configurations.append(test_config)
            current_test_id = condition.test_id
            test_config = {'test': json.loads(condition.test.data),
                           'conditions': [],
                           'condition_groups': {}}

        condition_data = json.loads(condition.data)
        condition_group_data = json.loads(condition.group.data)

        if app.config['STIMULUS_ORDER_RANDOMIZED']:
            random.shuffle(condition_group_data['stimulus_files'])
            random.shuffle(condition_data['stimulus_keys'])

        if app.config['ENCRYPT_AUDIO_STIMULI_URLS']:
            condition_group_data['reference_files'] = encrypt_audio_stimuli(condition_group_data['reference_files'],
                                                                            participant_id,
                                                                            condition.group_id)
            condition_group_data['stimulus_files'] = encrypt_audio_stimuli(condition_group_data['stimulus_files'],
                                                                           participant_id,
                                                                           condition.group_id)
            encoding_map, _, _ = get_encoding_maps(condition_group_data['stimulus_files'])
            condition_data['stimulus_keys'] = [encoding_map[key] for key in condition_data['stimulus_keys']]

        test_config['condition_groups'][condition.group_id] = condition_group_data

        # make sure that condition_id is added to the conditions dict
        test_config['conditions'].append(dict({'id': condition.id, 'group_id': condition.group_id}, **condition_data))
    test_configurations.append(test_config)

    return test_configurations 

def _factory(cls, selectable, sampling, name=None, seed=None):
        """Return a :class:`_expression.TableSample` object.

        :class:`_expression.TableSample` is an :class:`_expression.Alias`
        subclass that represents
        a table with the TABLESAMPLE clause applied to it.
        :func:`_expression.tablesample`
        is also available from the :class:`_expression.FromClause`
        class via the
        :meth:`_expression.FromClause.tablesample` method.

        The TABLESAMPLE clause allows selecting a randomly selected approximate
        percentage of rows from a table. It supports multiple sampling methods,
        most commonly BERNOULLI and SYSTEM.

        e.g.::

            from sqlalchemy import func

            selectable = people.tablesample(
                        func.bernoulli(1),
                        name='alias',
                        seed=func.random())
            stmt = select([selectable.c.people_id])

        Assuming ``people`` with a column ``people_id``, the above
        statement would render as::

            SELECT alias.people_id FROM
            people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
            REPEATABLE (random())

        .. versionadded:: 1.1

        :param sampling: a ``float`` percentage between 0 and 100 or
            :class:`_functions.Function`.

        :param name: optional alias name

        :param seed: any real-valued SQL expression.  When specified, the
         REPEATABLE sub-clause is also rendered.

        """
        return coercions.expect(roles.FromClauseRole, selectable).tablesample(
            sampling, name=name, seed=seed
        ) 

def tablesample(selectable, sampling, name=None, seed=None):
    """Return a :class:`.TableSample` object.

    :class:`.TableSample` is an :class:`.Alias` subclass that represents
    a table with the TABLESAMPLE clause applied to it.
    :func:`~.expression.tablesample`
    is also available from the :class:`.FromClause` class via the
    :meth:`.FromClause.tablesample` method.

    The TABLESAMPLE clause allows selecting a randomly selected approximate
    percentage of rows from a table. It supports multiple sampling methods,
    most commonly BERNOULLI and SYSTEM.

    e.g.::

        from sqlalchemy import func

        selectable = people.tablesample(
                    func.bernoulli(1),
                    name='alias',
                    seed=func.random())
        stmt = select([selectable.c.people_id])

    Assuming ``people`` with a column ``people_id``, the above
    statement would render as::

        SELECT alias.people_id FROM
        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
        REPEATABLE (random())

    .. versionadded:: 1.1

    :param sampling: a ``float`` percentage between 0 and 100 or
        :class:`.functions.Function`.

    :param name: optional alias name

    :param seed: any real-valued SQL expression.  When specified, the
     REPEATABLE sub-clause is also rendered.

    """
    return _interpret_as_from(selectable).tablesample(
        sampling, name=name, seed=seed) 

def tablesample(selectable, sampling, name=None, seed=None):
    """Return a :class:`.TableSample` object.

    :class:`.TableSample` is an :class:`.Alias` subclass that represents
    a table with the TABLESAMPLE clause applied to it.
    :func:`~.expression.tablesample`
    is also available from the :class:`.FromClause` class via the
    :meth:`.FromClause.tablesample` method.

    The TABLESAMPLE clause allows selecting a randomly selected approximate
    percentage of rows from a table. It supports multiple sampling methods,
    most commonly BERNOULLI and SYSTEM.

    e.g.::

        from sqlalchemy import func

        selectable = people.tablesample(
                    func.bernoulli(1),
                    name='alias',
                    seed=func.random())
        stmt = select([selectable.c.people_id])

    Assuming ``people`` with a column ``people_id``, the above
    statement would render as::

        SELECT alias.people_id FROM
        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
        REPEATABLE (random())

    .. versionadded:: 1.1

    :param sampling: a ``float`` percentage between 0 and 100 or
        :class:`.functions.Function`.

    :param name: optional alias name

    :param seed: any real-valued SQL expression.  When specified, the
     REPEATABLE sub-clause is also rendered.

    """
    return _interpret_as_from(selectable).tablesample(
        sampling, name=name, seed=seed)