Python sqlalchemy.sql.func.count() Examples

def append(self,obj):
        with self.obj.backend.transaction(implicit = True):

            #if the object is not yet in a DB, we save it first.

            if obj.pk is None:
                self.obj.backend.save(obj)

            relationship_table = self.params['relationship_table']
            condition = and_(relationship_table.c[self.params['related_pk_field_name']] == obj.pk,
                             relationship_table.c[self.params['pk_field_name']] == self.obj.pk)
            s = select([func.count(text('*'))]).where(condition)
            result = self.obj.backend.connection.execute(s)
            cnt = result.first()[0]
            if cnt:
                return #the object is already inside
            values = {
                self.params['pk_field_name'] : self.obj.pk,
                self.params['related_pk_field_name'] : obj.pk
            }
            insert = relationship_table.insert().values(**values)
            self.obj.backend.connection.execute(insert)
            self._queryset = None 

def exposed_db_count_netlocs():
	'''
	Select and count the number of instances for each netloc in
	the database.

	Returns the netlocs sorted by count in decending order.
	'''

	with db.session_context() as sess:
		q = sess.query(db.WebPages.netloc, func.count(db.WebPages.netloc).label("count")) \
			.group_by(db.WebPages.netloc)\
			.order_by(desc(func.count(db.WebPages.netloc)))
		print("Doing query.")
		res = q.all()
		res = list(res)
		for row in res:
			print("Row: ", row)

		with open("nl_counts.json", "w") as fp:
			json.dump(res, fp) 

def topic_worksheet(self, wb):
        from dexter.models.views import DocumentsView

        ws = wb.add_worksheet('topics')

        # topic groups
        rows = self.filter(
            db.session.query(
                DocumentsView.c.topic_group,
                func.count(1).label('count')
            )
            .join(Document)
            .group_by('topic_group')).all()
        rownum = 3 + self.write_table(ws, 'TopicGroups', rows)

        # topics
        rows = self.filter(
            db.session.query(
                DocumentsView.c.topic,
                func.count(1).label('count')
            )
            .join(Document)
            .group_by('topic')).all()
        self.write_table(ws, 'Topics', rows, rownum=rownum) 

def origin_worksheet(self, wb):
        from dexter.models.views import DocumentsView

        ws = wb.add_worksheet('origins')

        query = db.session.query(
            DocumentsView.c.origin,
            func.count(1).label('count')
        )\
            .join(Document)\
            .group_by('origin')
        rows = self.filter(query).all()
        rownum = 3 + self.write_table(ws, 'Origins', rows)

        query = db.session.query(
            DocumentsView.c.origin_group,
            func.count(1).label('count')
        )\
            .join(Document)\
            .group_by('origin_group')
        rows = self.filter(query).all()
        self.write_table(ws, 'OriginGroups', rows, rownum=rownum) 

def age_scores(self, row):
        """ Counts of source ages per medium, and their entropy. """
        self.scores_ws.write(row, 0, 'Child Ages')

        rows = self.filter(
            db.session.query(
                Medium.name,
                SourceAge.name,
                func.count(1).label('freq'))
            .join(Document)
            .join(DocumentSource)
            .join(SourceAge)
            .filter(DocumentSource.source_type == 'child')
            .group_by(Medium.name, SourceAge.name)
            .order_by(Medium.name)
        ).all()

        ages = list(set(r[1] for r in rows))
        ages.sort()

        row = self.write_score_table(ages, rows, row) + 1
        self.write_simple_score_row('Diversity of Ages', self.entropy(rows), row)

        return row 

def count_utterances(self, ids):
        """
        Return dict from person ID to number of utterances they had in
        these documents.
        """
        rows = db.session.query(
                Person.id,
                func.count(1).label('count')
                )\
                .join(Entity, Entity.person_id == Person.id)\
                .join(Utterance, Utterance.entity_id == Entity.id)\
                .filter(Utterance.doc_id.in_(self.doc_ids))\
                .filter(Person.id.in_(ids))\
                .group_by(Person.id)\
                .all()

        return dict((p[0], p[1]) for p in rows) 

def race_scores(self, row):
        """ Counts of source races per medium, and their entropy. """
        self.scores_ws.write(row, 0, 'Races')

        rows = self.filter(
            db.session.query(
                Medium.name,
                Race.name,
                func.count(1).label('freq'))
            .join(Document)
            .join(DocumentSource)
            .join(Race)
            .filter(DocumentSource.source_type == 'child')
            .group_by(Medium.name, Race.name)
            .order_by(Medium.name)
        ).all()

        races = list(set(r[1] for r in rows))
        races.sort()

        row = self.write_score_table(races, rows, row) + 1
        self.write_simple_score_row('Diversity of Races', self.entropy(rows), row)

        return row 

def fairness_chart(self):
        query = db.session.query(
            Fairness.name.label('t'),
            func.count(distinct(DocumentFairness.doc_id))
        )\
            .join(DocumentFairness)\
            .join(Document, DocumentFairness.doc_id == Document.id)\
            .group_by('t')

        rows = self.filter(query).all()
        counts = dict(rows)
        counts.setdefault('Fair', 0)

        # missing documents are considered fair
        counts['Fair'] += len(self.doc_ids) - sum(counts.itervalues())

        return {
            'values': counts
        } 

def _get_issues_by_account(self):
        acct_alias = aliased(IssueProperty)

        issues = (
            db.query(func.count(Issue.issue_id), Account.account_name)
                .join(acct_alias, Issue.issue_id == acct_alias.issue_id)
                .join(Account, acct_alias.value == Account.account_id)
                .filter(
                Account.account_type_id == aws_account_type_id,
                Account.enabled == 1,
                Issue.issue_type_id == reqtag_type_id,
                acct_alias.name == 'account_id'
            )
                .group_by(Account.account_name)
                .all()
        )

        return defaultdict(int, map(reversed, issues)) 

def share_replica_data_get_for_project(context, project_id, user_id=None,
                                       session=None, share_type_id=None):
    session = session or get_session()
    query = model_query(
        context, models.ShareInstance,
        func.count(models.ShareInstance.id),
        func.sum(models.Share.size),
        read_deleted="no",
        session=session).join(
        models.Share,
        models.ShareInstance.share_id == models.Share.id).filter(
        models.Share.project_id == project_id).filter(
        models.ShareInstance.replica_state.isnot(None))

    if share_type_id:
        query = query.filter(
            models.ShareInstance.share_type_id == share_type_id)
    elif user_id:
        query = query.filter(models.Share.user_id == user_id)

    result = query.first()
    return result[0] or 0, result[1] or 0 

def snapshot_data_get_for_project(context, project_id, user_id,
                                  share_type_id=None, session=None):
    query = (model_query(context, models.ShareSnapshot,
                         func.count(models.ShareSnapshot.id),
                         func.sum(models.ShareSnapshot.size),
                         read_deleted="no",
                         session=session).
             filter_by(project_id=project_id))

    if share_type_id:
        query = query.join(
            models.ShareInstance,
            models.ShareInstance.share_id == models.ShareSnapshot.share_id,
        ).filter_by(share_type_id=share_type_id)
    elif user_id:
        query = query.filter_by(user_id=user_id)
    result = query.first()

    return result[0] or 0, result[1] or 0 

def share_delete(context, share_id):
    session = get_session()

    with session.begin():
        share_ref = share_get(context, share_id, session)

        if len(share_ref.instances) > 0:
            msg = _("Share %(id)s has %(count)s share instances.") % {
                'id': share_id, 'count': len(share_ref.instances)}
            raise exception.InvalidShare(msg)

        share_ref.soft_delete(session=session)

        (session.query(models.ShareMetadata).
            filter_by(share_id=share_id).soft_delete())


################### 

def _get_project_quota_usages(context, session, project_id):
    rows = (model_query(context, models.QuotaUsage,
                        read_deleted="no",
                        session=session).
            filter_by(project_id=project_id).
            filter(models.QuotaUsage.share_type_id is None).
            with_lockmode('update').
            all())
    result = dict()
    # Get the total count of in_use,reserved
    for row in rows:
        if row.resource in result:
            result[row.resource]['in_use'] += row.in_use
            result[row.resource]['reserved'] += row.reserved
            result[row.resource]['total'] += (row.in_use + row.reserved)
        else:
            result[row.resource] = dict(in_use=row.in_use,
                                        reserved=row.reserved,
                                        total=row.in_use + row.reserved)
    return result 

def stats(self, survey_id):
        """Get stats for a survey."""
        result = (
            self.session
            .query(
                func.max(Survey.created_on),
                func.min(Submission.save_time),
                func.max(Submission.save_time),
                func.count(Submission.id),
            )
            .select_from(Submission)
            .join(Survey)
            # TODO: ask @jmwohl what this line is supposed to do
            # .filter(User.id == self.current_user_model.id)
            .filter(Submission.survey_id == survey_id)
            .one()
        )

        response = {
            "created_on": result[0],
            "earliest_submission_time": result[1],
            "latest_submission_time": result[2],
            "num_submissions": result[3]
        }
        return response 

def __getitem__(self,key):
        if isinstance(key, slice):
            start, stop, step = key.start, key.stop, key.step
            if step != None:
                raise IndexError("SQL backend dos not support steps in slices")
            if key.start == None:
                start = 0
            if key.stop == None:
                stop = len(self)
            if start < 0:
                start = len(self) + start
            if stop < 0:
                stop = len(self) + stop
            qs = copy.copy(self)
            if start:
                qs.offset(start)
            qs.limit(stop-start)
            qs.objects = None
            qs.count = None
            return qs
        if self.deserialized_objects is None:
            self.get_deserialized_objects()
        return self.deserialized_objects[key] 

def _validate_unique_container_name(self, context, name):
        if not CONF.compute.unique_container_name_scope:
            return
        lowername = name.lower()
        base_query = model_query(models.Container).\
            filter(func.lower(models.Container.name) == lowername)
        if CONF.compute.unique_container_name_scope == 'project':
            container_with_same_name = base_query.\
                filter_by(project_id=context.project_id).count()
        elif CONF.compute.unique_container_name_scope == 'global':
            container_with_same_name = base_query.count()
        else:
            return

        if container_with_same_name > 0:
            raise exception.ContainerAlreadyExists(field='name',
                                                   value=lowername) 

def share_group_type_destroy(context, type_id):
    session = get_session()
    with session.begin():
        _share_group_type_get(context, type_id, session)
        results = model_query(
            context, models.ShareGroup, session=session, read_deleted="no",
        ).filter_by(
            share_group_type_id=type_id,
        ).count()
        if results:
            LOG.error('Share group type %s deletion failed, it in use.',
                      type_id)
            raise exception.ShareGroupTypeInUse(type_id=type_id)
        model_query(
            context, models.ShareGroupTypeSpecs, session=session,
        ).filter_by(
            share_group_type_id=type_id,
        ).soft_delete()
        model_query(
            context, models.ShareGroupTypeShareTypeMapping, session=session
        ).filter_by(
            share_group_type_id=type_id,
        ).soft_delete()
        model_query(
            context, models.ShareGroupTypeProjects, session=session
        ).filter_by(
            share_group_type_id=type_id,
        ).soft_delete()
        model_query(
            context, models.ShareGroupTypes, session=session
        ).filter_by(
            id=type_id,
        ).soft_delete() 

def users_chart(self):
        query = db.session.query(
            func.ifnull(Document.checked_by_user_id, Document.created_by_user_id),
            func.count(Document.id),
        ).group_by(Document.created_by_user_id)
        rows = self.filter(query).all()
        users = dict((u.id, u.short_name()) for u in User.query.filter(User.id.in_(r[0] for r in rows)))

        return {
            'values': dict((users.get(r[0], 'None'), r[1]) for r in rows)
        } 

def published_chart(self):
        query = db.session.query(
            func.date_format(Document.published_at, '%Y/%m/%d').label('t'),
            func.count(Document.id),
        ).group_by('t')

        return {
            'values': dict(self.filter(query).all())
        } 

def created_chart(self):
        query = db.session.query(
            func.date_format(Document.created_at, '%Y/%m/%d').label('t'),
            func.count(Document.id),
        ).group_by('t')

        return {
            'values': dict(self.filter(query).all())
        } 

def share_type_access_remove(context, type_id, project_id):
    """Remove given tenant from the share type access list."""
    share_type_id = _share_type_get_id_from_share_type(context, type_id)

    count = (_share_type_access_query(context).
             filter_by(share_type_id=share_type_id).
             filter_by(project_id=project_id).
             soft_delete(synchronize_session=False))
    if count == 0:
        raise exception.ShareTypeAccessNotFound(
            share_type_id=type_id, project_id=project_id)

#################### 

def _check_for_existing_access(context, resource, resource_id, access_type,
                               access_to):

    session = get_session()
    if resource == 'share':
        query_method = _share_access_get_query
        access_to_field = models.ShareAccessMapping.access_to
    else:
        query_method = _share_snapshot_access_get_query
        access_to_field = models.ShareSnapshotAccessMapping.access_to

    with session.begin():
        if access_type == 'ip':
            rules = query_method(
                context, session, {'%s_id' % resource: resource_id,
                                   'access_type': access_type}).filter(
                access_to_field.startswith(access_to.split('/')[0])).all()

            matching_rules = [
                rule for rule in rules if
                ipaddress.ip_network(six.text_type(access_to)) ==
                ipaddress.ip_network(six.text_type(rule['access_to']))
            ]
            return len(matching_rules) > 0
        else:
            return query_method(
                context, session, {'%s_id' % resource: resource_id,
                                   'access_type': access_type,
                                   'access_to': access_to}).count() > 0 

def child_victimisation_worksheet(self, wb):
        from dexter.models.views import DocumentChildrenView

        ws = wb.add_worksheet('child_secondary_victimisation')

        rows = self.filter(
            db.session.query(
                func.sum(DocumentChildrenView.c.secondary_victim_source == 'secondary-victim-source', type_=Integer).label('secondary_victim_source'),
                func.sum(DocumentChildrenView.c.secondary_victim_identified == 'secondary-victim-identified', type_=Integer).label('secondary_victim_identified'),
                func.sum(DocumentChildrenView.c.secondary_victim_victim_of_abuse == 'secondary-victim-abused', type_=Integer).label('secondary_victim_victim_of_abuse'),
                func.sum(DocumentChildrenView.c.secondary_victim_source_identified_abused == 'secondary-victim-source-identified-abused', type_=Integer).label('secondary_victim_source_identified_abused'),
            )
            .join(Document)).all()
        if not rows:
            return

        d = rows[0]._asdict()
        data = [[k, d[k]] for k in sorted(d.keys(), key=len)]
        ws.add_table(0, 0, len(data), 1, {
            'name': 'ChildSecondaryVictimisation',
            'data': data,
            'columns': [
                {'header': ''},
                {'header': 'count'},
            ]
        }) 

def share_data_get_for_project(context, project_id, user_id,
                               share_type_id=None, session=None):
    query = (model_query(context, models.Share,
                         func.count(models.Share.id),
                         func.sum(models.Share.size),
                         read_deleted="no",
                         session=session).
             filter_by(project_id=project_id))
    if share_type_id:
        query = query.join("instances").filter_by(share_type_id=share_type_id)
    elif user_id:
        query = query.filter_by(user_id=user_id)
    result = query.first()
    return (result[0] or 0, result[1] or 0) 

def child_race_worksheets(self, wb):
        """
        For documents with child sources, give various breakdowns by race of
        those children. All reports are source focused, providing counts
        of *sources* in each category.
        """
        from dexter.models.views import DocumentsView, DocumentSourcesView

        # races
        rows = self.filter(
            db.session.query(
                DocumentSourcesView.c.race,
                func.count(DocumentSourcesView.c.document_source_id).label('count')
            )
            .join(Document)
            .filter(DocumentSourcesView.c.source_type == 'child')
            .group_by('race')).all()

        ws = wb.add_worksheet('child_races')
        rownum = 3 + self.write_table(ws, 'ChildRace', rows)

        # topics by race
        query = self.filter(
            db.session.query(
                DocumentsView.c.topic_group,
                DocumentSourcesView.c.race,
                func.count(DocumentSourcesView.c.document_source_id).label('count')
            )
            .join(Document)
            .join(DocumentSourcesView, DocumentsView.c.document_id == DocumentSourcesView.c.document_id)
            .filter(DocumentSourcesView.c.source_type == 'child')
            .group_by('topic_group', 'race')
            .order_by('topic_group'))

        self.write_summed_table(ws, 'RaceTopics', query, rownum=rownum) 

def child_context_worksheet(self, wb):
        from dexter.models.views import DocumentChildrenView

        rows = self.filter(
            db.session.query(
                func.sum(DocumentChildrenView.c.basic_context == 'basic-context', type_=Integer).label('basic_context'),
                func.sum(DocumentChildrenView.c.causes_mentioned == 'causes-mentioned', type_=Integer).label('causes_mentioned'),
                func.sum(DocumentChildrenView.c.consequences_mentioned == 'consequences-mentioned', type_=Integer).label('consequences_mentioned'),
                func.sum(DocumentChildrenView.c.solutions_offered == 'solutions-offered', type_=Integer).label('solutions_offered'),
                func.sum(DocumentChildrenView.c.relevant_policies == 'relevant-policies', type_=Integer).label('relevant_policies'),
                func.sum(DocumentChildrenView.c.self_help_offered == 'self-help-offered', type_=Integer).label('self_help_offered'),
            )
            .join(Document)).all()
        if not rows:
            return

        ws = wb.add_worksheet('child_context')

        d = rows[0]._asdict()
        data = [[k, d[k]] for k in d.keys()]
        ws.add_table(0, 0, len(data), 1, {
            'name': 'ChildContext',
            'data': data,
            'columns': [
                {'header': ''},
                {'header': 'count'},
            ]
        }) 

def list_submissions(self, survey_id):
        """List all submissions for a survey."""
        sub_resource = SubmissionResource()
        sub_resource.ref_rh = self.ref_rh
        sub_resource.request = self.request
        sub_resource.application = self.application
        where = Submission.survey_id == survey_id
        result = sub_resource.list(where=where)
        response = sub_resource.wrap_list_response(result)
        if sub_resource.content_type == 'csv':
            title = (
                self.session
                .query(Survey.title[Survey.default_language])
                .filter_by(id=survey_id)
                .scalar()
            )
            self._set_filename('survey_{}_submissions'.format(title), 'csv')
        else:
            response['total_entries'] = (
                self.session
                .query(func.count(Submission.id))
                .filter_by(survey_id=survey_id)
                .scalar()
            )
            response['survey_id'] = survey_id
        return response 

def totals(self, row):
        """ Counts of articles and sources """
        self.scores_ws.write(row, 0, 'Articles')
        rows = self.filter(
            db.session.query(
                Medium.name,
                func.count(1).label('freq'))
            .join(Document)
            .group_by(Medium.name)
        ).all()
        self.write_simple_score_row('Total articles', rows, row)

        row += 2

        self.scores_ws.write(row, 0, 'Sources')
        rows = self.filter(
            db.session.query(
                Medium.name,
                func.count(1).label('freq'))
            .join(Document)
            .join(DocumentSource)
            .group_by(Medium.name)
        ).all()
        self.write_simple_score_row('Total sources', rows, row)

        return row 

def get_metadata(self, docname, moderator):
        session = Session()
        subquery = session.query(
            Comment.node_id,
            func.count('*').label('comment_count')).group_by(
            Comment.node_id).subquery()
        nodes = session.query(Node.id, subquery.c.comment_count).outerjoin(
            (subquery, Node.id == subquery.c.node_id)).filter(
            Node.document == docname)
        session.close()
        session.commit()
        return dict([(k, v or 0) for k, v in nodes]) 

def count_share_groups(context, project_id, user_id=None,
                       share_type_id=None, session=None):
    query = model_query(
        context, models.ShareGroup,
        func.count(models.ShareGroup.id),
        read_deleted="no",
        session=session).filter_by(project_id=project_id)
    if share_type_id:
        query = query.join("share_group_share_type_mappings").filter_by(
            share_type_id=share_type_id)
    elif user_id is not None:
        query = query.filter_by(user_id=user_id)
    return query.first()[0]