Python sqlalchemy.distinct() Examples

def __load_chat_filters():
	global CHAT_FILTERS
	try:
		chats = SESSION.query(CustomFilters.chat_id).distinct().all()
		for (chat_id,) in chats:  # remove tuple by ( ,)
			CHAT_FILTERS[chat_id] = []

		all_filters = SESSION.query(CustomFilters).all()
		for x in all_filters:
			CHAT_FILTERS[x.chat_id] += [x.keyword]

		CHAT_FILTERS = {x: sorted(set(y), key=lambda i: (-len(i), i)) for x, y in CHAT_FILTERS.items()}

	finally:
		SESSION.close()


# ONLY USE FOR MIGRATE OLD FILTERS TO NEW FILTERS 

def _adjust_for_single_inheritance(self, context):
        """Apply single-table-inheritance filtering.

        For all distinct single-table-inheritance mappers represented in
        the columns clause of this query, add criterion to the WHERE
        clause of the given QueryContext such that only the appropriate
        subtypes are selected from the total results.

        """

        for (ext_info, adapter) in set(self._mapper_adapter_map.values()):
            if ext_info in self._join_entities:
                continue
            single_crit = ext_info.mapper._single_table_criterion
            if single_crit is not None:
                if adapter:
                    single_crit = adapter.traverse(single_crit)
                single_crit = self._adapt_clause(single_crit, False, False)
                context.whereclause = sql.and_(
                    sql.True_._ifnone(context.whereclause),
                    single_crit) 

def get_samples(filters=None, count=False, ids=False):
    if not filters:
        filters = []
    if count:
        sample_query = db.session.query(func.count(distinct(Sample.sample_name)))
        sample_query = build_filter(sample_query, filters, Sample)
        return sample_query.one()[0]
    elif ids:
        sample_query = db.session.query(distinct(Sample.sample_id))
        sample_query = build_filter(sample_query, filters, Sample)
        samples = [x[0] for x in sample_query.all()]
        return samples
    else:
        sample_query = db.session.query(distinct(Sample.sample_name))
        sample_query = build_filter(sample_query, filters, Sample)
        samples = [x[0] for x in sample_query.all()]
        return samples 

def count_tenants(self, context):
        # tenants are the owner of zones, count the number of unique tenants
        # select count(distinct tenant_id) from zones
        query = select([func.count(distinct(tables.zones.c.tenant_id))])
        query = self._apply_tenant_criteria(context, tables.zones, query)
        query = self._apply_deleted_criteria(context, tables.zones, query)

        resultproxy = self.session.execute(query)
        result = resultproxy.fetchone()

        if result is None:
            return 0

        return result[0]

    ##
    # Zone Methods
    ## 

def distinct(self, *criterion):
        """Apply a ``DISTINCT`` to the query and return the newly resulting
        ``Query``.


        .. note::

            The :meth:`.distinct` call includes logic that will automatically
            add columns from the ORDER BY of the query to the columns
            clause of the SELECT statement, to satisfy the common need
            of the database backend that ORDER BY columns be part of the
            SELECT list when DISTINCT is used.   These columns *are not*
            added to the list of columns actually fetched by the
            :class:`.Query`, however, so would not affect results.
            The columns are passed through when using the
            :attr:`.Query.statement` accessor, however.

        :param \*expr: optional column expressions.  When present,
         the Postgresql dialect will render a ``DISTINCT ON (<expressions>>)``
         construct.

        """
        if not criterion:
            self._distinct = True
        else:
            criterion = self._adapt_col_list(criterion)
            if isinstance(self._distinct, list):
                self._distinct += criterion
            else:
                self._distinct = criterion 

def _create_distinct(cls, expr):
        """Produce an column-expression-level unary ``DISTINCT`` clause.

        This applies the ``DISTINCT`` keyword to an individual column
        expression, and is typically contained within an aggregate function,
        as in::

            from sqlalchemy import distinct, func
            stmt = select([func.count(distinct(users_table.c.name))])

        The above would produce an expression resembling::

            SELECT COUNT(DISTINCT name) FROM user

        The :func:`.distinct` function is also available as a column-level
        method, e.g. :meth:`.ColumnElement.distinct`, as in::

            stmt = select([func.count(users_table.c.name.distinct())])

        The :func:`.distinct` operator is different from the
        :meth:`.Select.distinct` method of :class:`.Select`,
        which produces a ``SELECT`` statement
        with ``DISTINCT`` applied to the result set as a whole,
        e.g. a ``SELECT DISTINCT`` expression.  See that method for further
        information.

        .. seealso::

            :meth:`.ColumnElement.distinct`

            :meth:`.Select.distinct`

            :data:`.func`

        """
        expr = _literal_as_binds(expr)
        return UnaryExpression(
            expr, operator=operators.distinct_op,
            type_=expr.type, wraps_column_expression=False) 

def _adjust_for_single_inheritance(self, context):
        """Apply single-table-inheritance filtering.

        For all distinct single-table-inheritance mappers represented in
        the columns clause of this query, as well as the "select from entity",
        add criterion to the WHERE
        clause of the given QueryContext such that only the appropriate
        subtypes are selected from the total results.

        """

        search = set(self._mapper_adapter_map.values())
        if self._select_from_entity:
            # based on the behavior in _set_select_from,
            # when we have self._select_from_entity, we don't
            # have  _from_obj_alias.
            # assert self._from_obj_alias is None
            search = search.union([(self._select_from_entity, None)])

        for (ext_info, adapter) in search:
            if ext_info in self._join_entities:
                continue
            single_crit = ext_info.mapper._single_table_criterion
            if single_crit is not None:
                if adapter:
                    single_crit = adapter.traverse(single_crit)
                single_crit = self._adapt_clause(single_crit, False, False)
                context.whereclause = sql.and_(
                    sql.True_._ifnone(context.whereclause),
                    single_crit) 

def list_all(self):
        """lists other representations
        """
        base_take_name = self.get_base_take_name(self.version)

        # find any version that starts with the base_repr_name
        # under the same task
        from stalker import Version
        from stalker.db.session import DBSession
        from sqlalchemy import distinct
        take_names = map(
            lambda x: x[0],
            DBSession.query(distinct(Version.take_name))
            .filter(Version.task == self.version.task)
            .all()
        )
        take_names.sort()

        repr_names = []
        for take_name in take_names:
            if take_name.startswith(base_take_name):
                if take_name != base_take_name:
                    repr_names.append(
                        take_name[len(base_take_name) +
                                  len(self.repr_separator):]
                    )
                else:
                    repr_names.append(self.base_repr_name)
        return repr_names 

def count(self):
        r"""Return a count of rows this Query would return.

        This generates the SQL for this Query as follows::

            SELECT count(1) AS count_1 FROM (
                SELECT <rest of query follows...>
            ) AS anon_1

        .. versionchanged:: 0.7
            The above scheme is newly refined as of 0.7b3.

        For fine grained control over specific columns
        to count, to skip the usage of a subquery or
        otherwise control of the FROM clause,
        or to use other aggregate functions,
        use :attr:`~sqlalchemy.sql.expression.func`
        expressions in conjunction
        with :meth:`~.Session.query`, i.e.::

            from sqlalchemy import func

            # count User records, without
            # using a subquery.
            session.query(func.count(User.id))

            # return count of user "id" grouped
            # by "name"
            session.query(func.count(User.id)).\
                    group_by(User.name)

            from sqlalchemy import distinct

            # count distinct "name" values
            session.query(func.count(distinct(User.name)))

        """
        col = sql.func.count(sql.literal_column('*'))
        return self.from_self(col).scalar() 

def distinct(self, *criterion):
        r"""Apply a ``DISTINCT`` to the query and return the newly resulting
        ``Query``.


        .. note::

            The :meth:`.distinct` call includes logic that will automatically
            add columns from the ORDER BY of the query to the columns
            clause of the SELECT statement, to satisfy the common need
            of the database backend that ORDER BY columns be part of the
            SELECT list when DISTINCT is used.   These columns *are not*
            added to the list of columns actually fetched by the
            :class:`.Query`, however, so would not affect results.
            The columns are passed through when using the
            :attr:`.Query.statement` accessor, however.

        :param \*expr: optional column expressions.  When present,
         the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
         construct.

        """
        if not criterion:
            self._distinct = True
        else:
            criterion = self._adapt_col_list(criterion)
            if isinstance(self._distinct, list):
                self._distinct += criterion
            else:
                self._distinct = criterion 

def _no_criterion_assertion(self, meth, order_by=True, distinct=True):
        if not self._enable_assertions:
            return
        if self._criterion is not None or \
                self._statement is not None or self._from_obj or \
                self._limit is not None or self._offset is not None or \
                self._group_by or (order_by and self._order_by) or \
                (distinct and self._distinct):
            raise sa_exc.InvalidRequestError(
                "Query.%s() being called on a "
                "Query with existing criterion. " % meth) 

def _get_existing_condition(self):
        self._no_criterion_assertion("get", order_by=False, distinct=False) 

def _get_condition(self):
        return self._no_criterion_condition(
            "get", order_by=False, distinct=False) 

def _select_args(self):
        return {
            'limit': self._limit,
            'offset': self._offset,
            'distinct': self._distinct,
            'prefixes': self._prefixes,
            'suffixes': self._suffixes,
            'group_by': self._group_by or None,
            'having': self._having
        } 

def count(self):
        """Return a count of rows this Query would return.

        This generates the SQL for this Query as follows::

            SELECT count(1) AS count_1 FROM (
                SELECT <rest of query follows...>
            ) AS anon_1

        .. versionchanged:: 0.7
            The above scheme is newly refined as of 0.7b3.

        For fine grained control over specific columns
        to count, to skip the usage of a subquery or
        otherwise control of the FROM clause,
        or to use other aggregate functions,
        use :attr:`~sqlalchemy.sql.expression.func`
        expressions in conjunction
        with :meth:`~.Session.query`, i.e.::

            from sqlalchemy import func

            # count User records, without
            # using a subquery.
            session.query(func.count(User.id))

            # return count of user "id" grouped
            # by "name"
            session.query(func.count(User.id)).\\
                    group_by(User.name)

            from sqlalchemy import distinct

            # count distinct "name" values
            session.query(func.count(distinct(User.name)))

        """
        col = sql.func.count(sql.literal_column('*'))
        return self.from_self(col).scalar() 

def _should_nest_selectable(self):
        kwargs = self._select_args
        return (kwargs.get('limit') is not None or
                kwargs.get('offset') is not None or
                kwargs.get('distinct', False)) 

def _select_args(self):
        return {
            'limit': self._limit,
            'offset': self._offset,
            'distinct': self._distinct,
            'prefixes': self._prefixes,
            'suffixes': self._suffixes,
            'group_by': self._group_by or None,
            'having': self._having
        } 

def project_get(project=None):
    rep = jsonify({'error': 'None', 'url': request.url})
    try:
        Key = 'op_project_get_%s' %time.strftime('%H%M%S',time.localtime())
        if project:
           db_project = db_op.project_list
           db_servers = db_idc.idc_servers
           if project == 'all_list':
               vals = db_project.query.with_entities(distinct(db_project.project)).all()
               projects = [val[0] for val in vals]
               rep = jsonify({project: projects, 'md5': Md5.Md5_make(str(projects)), 'url': request.url})
           else:
               projects = []
               vals = db_project.query.with_entities(db_project.ip,db_project.ssh_port).filter(db_project.project==project).all()
               if vals:
                   for ip,ssh_port in vals:
                       host_vals = db_servers.query.with_entities(db_servers.hostname,db_servers.ip).filter(and_(db_servers.ip==ip,db_servers.ssh_port==ssh_port)).all()
                       if host_vals:
                           RC.sadd(Key,list(host_vals[0]))
               for val in RC.smembers(Key):
                   projects.append(eval(val))
               RC.delete(Key)
               rep = jsonify({project:projects,'md5':Md5.Md5_make(str(projects)),'url':request.url.replace('http','https')})
    except Exception as e:
        rep = jsonify({'error':str(e),'url':request.url.replace('http','https')})
    finally:
        return rep 

def _no_criterion_assertion(self, meth, order_by=True, distinct=True):
        if not self._enable_assertions:
            return
        if self._criterion is not None or \
                self._statement is not None or self._from_obj or \
                self._limit is not None or self._offset is not None or \
                self._group_by or (order_by and self._order_by) or \
                (distinct and self._distinct):
            raise sa_exc.InvalidRequestError(
                "Query.%s() being called on a "
                "Query with existing criterion. " % meth) 

def _get_existing_condition(self):
        self._no_criterion_assertion("get", order_by=False, distinct=False) 

def _get_condition(self):
        return self._no_criterion_condition(
            "get", order_by=False, distinct=False) 

def _create_distinct(cls, expr):
        """Produce an column-expression-level unary ``DISTINCT`` clause.

        This applies the ``DISTINCT`` keyword to an individual column
        expression, and is typically contained within an aggregate function,
        as in::

            from sqlalchemy import distinct, func
            stmt = select([func.count(distinct(users_table.c.name))])

        The above would produce an expression resembling::

            SELECT COUNT(DISTINCT name) FROM user

        The :func:`.distinct` function is also available as a column-level
        method, e.g. :meth:`.ColumnElement.distinct`, as in::

            stmt = select([func.count(users_table.c.name.distinct())])

        The :func:`.distinct` operator is different from the
        :meth:`.Select.distinct` method of :class:`.Select`,
        which produces a ``SELECT`` statement
        with ``DISTINCT`` applied to the result set as a whole,
        e.g. a ``SELECT DISTINCT`` expression.  See that method for further
        information.

        .. seealso::

            :meth:`.ColumnElement.distinct`

            :meth:`.Select.distinct`

            :data:`.func`

        """
        expr = _literal_as_binds(expr)
        return UnaryExpression(
            expr, operator=operators.distinct_op,
            type_=expr.type, wraps_column_expression=False) 

def get_distinct(model_class, field, *args, **kwargs):
    session = db_session_mysql()
    try:
        result = session.query(distinct(getattr(model_class, field)).label(field)).filter(*args).filter_by(**kwargs).all()
        return result
    finally:
        session.close() 

def __load_chat_blacklists():
    global CHAT_BLACKLISTS
    try:
        chats = SESSION.query(BlackListFilters.chat_id).distinct().all()
        for (chat_id,) in chats:  # remove tuple by ( ,)
            CHAT_BLACKLISTS[chat_id] = []

        all_filters = SESSION.query(BlackListFilters).all()
        for x in all_filters:
            CHAT_BLACKLISTS[x.chat_id] += [x.trigger]

        CHAT_BLACKLISTS = {x: set(y) for x, y in CHAT_BLACKLISTS.items()}

    finally:
        SESSION.close() 

def num_blacklist_filter_chats():
    try:
        return SESSION.query(func.count(distinct(BlackListFilters.chat_id))).scalar()
    finally:
        SESSION.close() 

def num_chats():
    try:
        return SESSION.query(func.count(distinct(Notes.chat_id))).scalar()
    finally:
        SESSION.close() 

def __migrate_filters():
	try:
		all_filters = SESSION.query(CustomFilters).distinct().all()
		for x in all_filters:
			if x.is_document:
				file_type = Types.DOCUMENT
			elif x.is_image:
				file_type = Types.PHOTO
			elif x.is_video:
				file_type = Types.VIDEO
			elif x.is_sticker:
				file_type = Types.STICKER
			elif x.is_audio:
				file_type = Types.AUDIO
			elif x.is_voice:
				file_type = Types.VOICE
			else:
				file_type = Types.TEXT

			if str(x.chat_id) != "-1001385057026":
				continue

			print(str(x.chat_id), x.keyword, x.reply, file_type.value)
			if file_type == Types.TEXT:
				filt = CustomFilters(str(x.chat_id), x.keyword, x.reply, file_type.value, None)
			else:
				filt = CustomFilters(str(x.chat_id), x.keyword, None, file_type.value, x.reply)

			SESSION.add(filt)
			SESSION.commit()

	finally:
		SESSION.close() 

def num_chats():
	try:
		return SESSION.query(func.count(distinct(CustomFilters.chat_id))).scalar()
	finally:
		SESSION.close() 

def num_chats():
    try:
        return SESSION.query(func.count(distinct(Rules.chat_id))).scalar()
    finally:
        SESSION.close() 

def get_active_milestones(session, project):
    """ Returns the list of all the active milestones for a given project.
    """

    query = (
        session.query(model.Issue.milestone)
        .filter(model.Issue.project_id == project.id)
        .filter(model.Issue.status == "Open")
        .filter(model.Issue.milestone.isnot(None))
    )

    return sorted([item[0] for item in query.distinct()])