我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用sqlalchemy.distinct()。
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 distinct(self, *criterion): """Apply a ``DISTINCT`` to the query and return the newly resulting ``Query``. :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 worker_count(self): """ Return the number of workers in the account """ query = select([func.count(distinct( user_model.User.id))])\ .where(Organization.id == self.id)\ .where(RoleToUser.archived == False)\ .where(Organization.id == Location.organization_id)\ .where(Location.id == Role.location_id)\ .where(Role.id == RoleToUser.role_id)\ .where(RoleToUser.user_id == user_model.User.id)\ .select_from(RoleToUser)\ .select_from(Role)\ .select_from(Location)\ .select_from(Organization)\ .select_from(user_model.User) # Filter out demo account and Staffjoy emails for email in current_app.config.get("KPI_EMAILS_TO_EXCLUDE"): query = query.where(not_(user_model.User.email.like(email))) workers = db.session.execute(query).fetchone()[0] return workers
def test_returns_correct_results( self, session, model_mapping, model_key, related_model_key, path, result ): model = model_mapping[model_key] alias = sa.orm.aliased(model_mapping[related_model_key]) aggregate = select_correlated_expression( model, sa.func.count(sa.distinct(alias.id)), path, alias ) query = session.query( model.id, aggregate.label('count') ).order_by(model.id) assert query.all() == result
def _get_condition(self): return self._no_criterion_condition( "get", order_by=False, distinct=False)
def _get_existing_condition(self): self._no_criterion_assertion("get", order_by=False, distinct=False)
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 _no_criterion_condition(self, meth, order_by=True, distinct=True): self._no_criterion_assertion(meth, order_by, distinct) self._from_obj = () self._statement = self._criterion = None self._order_by = self._group_by = self._distinct = False
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 _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 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 _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 autocomplete_results(model_attr, term): # model_attr refers to a specified model + field, e.g., Person.name where = model_attr.ilike('%{}%'.format(term)) results = db.session.query(sa.distinct(model_attr))\ .filter(where)\ .order_by(model_attr) results_obj = [{'suggestion': c[0]} for c in results.all()] response = make_response(json.dumps(results_obj)) response.headers['Content-Type'] = 'application/json' return response
def get_bad_matches(place): q = (database.session .query(ItemCandidate.item_id, ItemCandidate.osm_type, ItemCandidate.osm_id) .join(BadMatch).distinct()) return set(tuple(row) for row in q)
def get_tag_list(sort): count = func.count(distinct(Item.item_id)) order_by = ([count, ItemTag.tag_or_key] if sort == 'count' else [ItemTag.tag_or_key]) q = (database.session.query(ItemTag.tag_or_key, func.count(distinct(Item.item_id))) .join(Item) .join(ItemCandidate) # .filter(ItemTag.tag_or_key == sub.c.tag_or_key) .group_by(ItemTag.tag_or_key) .order_by(*order_by)) return [(tag, num) for tag, num in q]
def dbreference_types(self): """Distinct database reference types (``type_``) in :class:`.models.DbReference` :return: List of strings for all available database cross reference types used in model DbReference :rtype: list[str] """ q = self.session.query(distinct(models.DbReference.type_)) return [x[0] for x in q.all()]
def taxids(self): """Distinct NCBI taxonomy identifiers (``taxid``) in :class:`.models.Entry` :return: NCBI taxonomy identifiers :rtype: list[int] """ r = self.session.query(distinct(models.Entry.taxid)).all() return [x[0] for x in r]
def datasets(self): """Distinct datasets (``dataset``) in :class:`.models.Entry` Distinct datasets are SwissProt or/and TrEMBL :return: all distinct dataset types :rtype: list[str] """ r = self.session.query(distinct(models.Entry.dataset)).all() return [x[0] for x in r]
def feature_types(self): """Distinct types (``type_``) in :class:`.models.Feature` :return: all distinct feature types :rtype: list[str] """ r = self.session.query(distinct(models.Feature.type_)).all() return [x[0] for x in r]
def subcellular_locations(self): """Distinct subcellular locations (``location`` in :class:`.models.SubcellularLocation`) :return: all distinct subcellular locations :rtype: list[str] """ return [x[0] for x in self.session.query(models.SubcellularLocation.location).all()]
def keywords(self): """Distinct keywords (``name`` in :class:`.models.Keyword`) :returns: all distinct keywords :rtype: list[str] """ return [x[0] for x in self.session.query(models.Keyword.name).all()]
def diseases(self): """Distinct diseases (``name`` in :class:`.models.Disease`) :returns: all distinct disease names :rtype: list[str] """ return [x[0] for x in self.session.query(models.Disease.name).all()]
