Python sqlalchemy.distinct() Examples

The following are 30 code examples of sqlalchemy.distinct(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy , or try the search function .
Example #1
Source File: cust_filters_sql.py    From EmiliaHikari with GNU General Public License v3.0 6 votes vote down vote up
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 
Example #2
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 6 votes vote down vote up
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) 
Example #3
Source File: utils.py    From MegaQC with GNU General Public License v3.0 6 votes vote down vote up
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 
Example #4
Source File: __init__.py    From designate with Apache License 2.0 6 votes vote down vote up
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
    ## 
Example #5
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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 
Example #6
Source File: elements.py    From planespotter with MIT License 5 votes vote down vote up
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) 
Example #7
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
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) 
Example #8
Source File: representation.py    From anima with MIT License 5 votes vote down vote up
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 
Example #9
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
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() 
Example #10
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
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 
Example #11
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
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) 
Example #12
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
def _get_existing_condition(self):
        self._no_criterion_assertion("get", order_by=False, distinct=False) 
Example #13
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
def _get_condition(self):
        return self._no_criterion_condition(
            "get", order_by=False, distinct=False) 
Example #14
Source File: query.py    From planespotter with MIT License 5 votes vote down vote up
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
        } 
Example #15
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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() 
Example #16
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example #17
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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
        } 
Example #18
Source File: assets_query.py    From sparrow with GNU General Public License v3.0 5 votes vote down vote up
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 
Example #19
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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) 
Example #20
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
def _get_existing_condition(self):
        self._no_criterion_assertion("get", order_by=False, distinct=False) 
Example #21
Source File: query.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
def _get_condition(self):
        return self._no_criterion_condition(
            "get", order_by=False, distinct=False) 
Example #22
Source File: elements.py    From Fluid-Designer with GNU General Public License v3.0 5 votes vote down vote up
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) 
Example #23
Source File: client_db.py    From news_spider with MIT License 5 votes vote down vote up
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() 
Example #24
Source File: blacklist_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
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() 
Example #25
Source File: blacklist_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
def num_blacklist_filter_chats():
    try:
        return SESSION.query(func.count(distinct(BlackListFilters.chat_id))).scalar()
    finally:
        SESSION.close() 
Example #26
Source File: notes_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
def num_chats():
    try:
        return SESSION.query(func.count(distinct(Notes.chat_id))).scalar()
    finally:
        SESSION.close() 
Example #27
Source File: cust_filters_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
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() 
Example #28
Source File: cust_filters_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
def num_chats():
	try:
		return SESSION.query(func.count(distinct(CustomFilters.chat_id))).scalar()
	finally:
		SESSION.close() 
Example #29
Source File: rules_sql.py    From EmiliaHikari with GNU General Public License v3.0 5 votes vote down vote up
def num_chats():
    try:
        return SESSION.query(func.count(distinct(Rules.chat_id))).scalar()
    finally:
        SESSION.close() 
Example #30
Source File: query.py    From pagure with GNU General Public License v2.0 5 votes vote down vote up
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()])