sqlalchemy.func.count

Here are the examples of the python api sqlalchemy.func.count taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

157 Examples 7

Example 1

Project: faf Source File: queries.py
def get_empty_problems(db):
    """
    Return a list of pyfaf.storage.Problem that have no reports.
    """
    return (db.session.query(Problem)
                      .outerjoin(Report)
                      .group_by(Problem)
                      .having(func.count(Report.id) == 0)
                      .all())

Example 2

Project: knowledge-repo Source File: models.py
    @view_user_count.expression
    def view_user_count(self):
        return (select([func.count(distinct(PageView.user_id))])
                .where(PageView.object_id == self.id)
                .where(PageView.object_type == 'post')
                .label("view_user_count"))

Example 3

Project: sqlalchemy-utils Source File: test_get_query_entities.py
    def test_with_subquery(self, session, Article):
        number_of_articles = (
            sa.select(
                [sa.func.count(Article.id)],
            )
            .select_from(
                Article.__table__
            )
        ).label('number_of_articles')

        query = session.query(Article, number_of_articles)
        assert get_query_entities(query) == [
            Article,
            number_of_articles
        ]

Example 4

Project: adhocracy Source File: tag.py
Function: complete
    @classmethod
    def complete(cls, prefix, limit=5, instance_filter=True):
        from tagging import Tagging
        from delegateable import Delegateable
        q = meta.Session.query(Tag)
        q = q.add_column(func.count(Tagging.id))
        q = q.join(Tagging)
        q = q.filter(func.lower(Tag.name).like(prefix.lower() + "%"))
        if ifilter.has_instance() and instance_filter:
            q = q.join(Delegateable)
            q = q.filter(Delegateable.instance_id == ifilter.get_instance().id)
        q = q.group_by(Tag.id, Tag.create_time, Tag.name)
        q = q.order_by(func.count(Tagging.id).desc())
        # SQLAlchemy turns this into a cueming subquery:
        # if limit is not None:
        #     q = q.limit(limit)
        # print "QUERY", q
        return q.all()[:limit]

Example 5

Project: impactstory-tng Source File: refset.py
def num_people_in_db():
    count_q = base_count_people_query()
    count_q = count_q.statement.with_only_columns([func.count()]).order_by(None)
    count = db.session.execute(count_q).scalar()
    print u"db person count", count
    return count

Example 6

Project: glottolog3 Source File: check_db_consistency.py
    def invalid_query(self, session, exclude=SPECIAL_FAMILIES):
        child = orm.aliased(Languoid, flat=True)
        return session.query(Languoid)\
            .filter_by(active=True, level=LanguoidLevel.family)\
            .filter(Languoid.family.has(Languoid.name.notin_(exclude)))\
            .join(TreeClosureTable, TreeClosureTable.parent_pk == Languoid.pk)\
            .outerjoin(child, and_(
                TreeClosureTable.child_pk == child.pk,
                TreeClosureTable.depth > 0,
                child.level == LanguoidLevel.language))\
            .group_by(Language.pk, Languoid.pk)\
            .having(func.count(child.pk) < 2)\
            .order_by(Languoid.id)

Example 7

Project: sqlalchemy-utils Source File: test_sort_query.py
    def test_column_property(self, session, TextItem):
        TextItem.item_count = sa.orm.column_property(
            sa.select(
                [
                    sa.func.count('1')
                ],
            )
            .select_from(TextItem.__table__)
            .label('item_count')
        )

        query = sort_query(
            session.query(TextItem),
            'item_count'
        )
        assert_contains('ORDER BY item_count', query)

Example 8

Project: plenario Source File: ShapeMetadata.py
    def _get_num_shapes(self):
        table = self.shape_table
        # Arbitrarily select the first column of the table to count against
        count_query = select([func.count(table.c.geom)])
        # Should return only one row.
        # And we want the 0th and only attribute of that row (the count).
        return session.execute(count_query).fetchone()[0]

Example 9

Project: pyspider Source File: taskdb.py
Function: status_count
    def status_count(self, project):
        result = dict()
        if project not in self.projects:
            self._list_project()
        if project not in self.projects:
            return result

        self.table.name = self._tablename(project)
        for status, count in self.engine.execute(
                self.table.select()
                .with_only_columns((self.table.c.status, func.count(1)))
                .group_by(self.table.c.status)):
            result[status] = count
        return result

Example 10

Project: lemur Source File: service.py
Function: stats
def stats(**kwargs):
    """
    Helper that defines some useful statistics about destinations.

    :param kwargs:
    :return:
    """
    items = database.db.session.query(Destination.label, func.count(certificate_destination_associations.c.certificate_id))\
        .join(certificate_destination_associations)\
        .group_by(Destination.label).all()

    keys = []
    values = []
    for key, count in items:
        keys.append(key)
        values.append(count)

    return {'labels': keys, 'values': values}

Example 11

Project: portingdb Source File: htmlreport.py
def get_status_summary(db, filter=None):
    query = db.query(tables.Status)
    query = query.join(tables.Package, tables.Status.packages)
    query = query.add_column(func.count(tables.Package.name))
    if filter:
        query = filter(query)
    query = query.group_by(tables.Package.status)
    query = query.order_by(tables.Status.order)
    return list(query)

Example 12

Project: holmes-api Source File: domain.py
    def get_bad_request_count(self, db):
        from holmes.models import Request

        sample_query = self.get_sample_requests(
            db,
            lambda query: query.filter(Request.status_code > 399)
        )

        return db \
            .query(func.count(sample_query.columns.id)) \
            .scalar()

Example 13

Project: blaze Source File: sql.py
Function: compute_up
@dispatch(count, Select)
def compute_up(t, s, **kwargs):
    if t.axis != (0,):
        raise ValueError('axis not equal to 0 not defined for SQL reductions')
    al = next(aliases)
    try:
        s2 = s.alias(al)
        col = list(s2.primary_key)[0]
    except (KeyError, IndexError):
        s2 = s.alias(al)
        col = list(s2.columns)[0]

    result = sa.func.count(col)

    return select([list(inner_columns(result))[0].label(t._name)])

Example 14

Project: JARR Source File: abstract.py
Function: count_by
    def _count_by(self, elem_to_group_by, filters):
        if self.user_id:
            filters['user_id'] = self.user_id
        return dict(db.session.query(elem_to_group_by, func.count('id'))
                              .filter(*self._to_filters(**filters))
                              .group_by(elem_to_group_by).all())

Example 15

Project: tvb-framework Source File: operation_dao.py
Function: get_operation_numbers
    def get_operation_numbers(self, proj_id):
        """
        Count total number of operations started for current project.
        """
        stats = self.session.query(model.Operation.status, func.count(model.Operation.id)
                                    ).filter_by(fk_launched_in=proj_id
                                    ).group_by(model.Operation.status).all()
        stats = dict(stats)
        finished = stats.get(model.STATUS_FINISHED, 0)
        started = stats.get(model.STATUS_STARTED, 0)
        failed = stats.get(model.STATUS_ERROR, 0)
        canceled = stats.get(model.STATUS_CANCELED, 0)
        pending = stats.get(model.STATUS_PENDING, 0)

        return finished, started, failed, canceled, pending

Example 16

Project: sqlalchemy-utils Source File: test_sort_query.py
    def test_column_property(self, session, Article, Category):
        Category.article_count = sa.orm.column_property(
            sa.select([sa.func.count(Article.id)])
            .where(Article.category_id == Category.id)
            .label('article_count')
        )

        query = session.query(Category)
        query = sort_query(query, 'article_count')
        assert_contains('article_count ASC', query)

Example 17

Project: knowledge-repo Source File: models.py
    @view_count.expression
    def view_count(self):
        return (select([func.count(PageView.id)])
                .where(PageView.object_id == self.id)
                .where(PageView.object_type == 'post')
                .label("view_count"))

Example 18

Project: adhocracy Source File: page.py
Function: contributors
    def contributors(self):
        from user import User
        from text import Text
        q = meta.Session.query(User)
        q = q.join(Text)
        q = q.add_column(func.count(Text.id))
        q = q.filter(Text.page == self)
        q = q.group_by(User.id)
        q = q.order_by(func.count(Text.id).desc())
        cbs = super(Page, self).contributors()
        return self._join_contributors(cbs, q.all(), second_factor=2)

Example 19

Project: bigg_models Source File: curation.py
def refreshModelCount(modelId):
    session = Session()
    metabolite_count = (session
            .query(func.count(ModelCompartmentalizedComponent.id))
            .filter(ModelCompartmentalizedComponent.model_id == modelId)
            .scalar())
    
    reaction_count = (session.query(func.count(ModelReaction.id))
        .filter(ModelReaction.model_id == modelId)
        .scalar())
    
    gene_count = (session.query(func.count(ModelGene.id))
                .filter(ModelGene.model_id == modelId)       
                .scalar())
    session.query(ModelCount).filter(ModelCount.model_id == modelId).update({ModelCount.gene_count : gene_count, ModelCount.metabolite_count : metabolite_count, ModelCount.reaction_count : reaction_count})
    session.commit()
    session.close()

Example 20

Project: MailingListStats Source File: report.py
    def get_total_messages(self, session):
        '''SELECT mailing_list_url, count(*) as t
             FROM messages
            GROUP BY mailing_list_url;'''

        ret = session.query(db.Messages.mailing_list_url,
                            func.count(db.Messages.mailing_list_url)) \
            .group_by(db.Messages.mailing_list_url)
        return ret.all()

Example 21

Project: knowledge-repo Source File: models.py
    @comment_count.expression
    def comment_count(self):
        return (select([func.count(Comment.id)])
                .where(Comment.post_id == self.id)
                .where(Comment.object_type == 'post')
                .label("comments_count"))

Example 22

Project: ushine-learning Source File: test_base_model.py
def test_bulk_create():
    """Tests creation of several items in a single batch"""
    categories = []
    categories.append(Category(title='category 1',
                               origin_category_id=1,
                               origin_parent_id=0))
    categories.append(Category(title='category 2',
                               origin_category_id=2,
                               origin_parent_id=0))
    categories.append(Category(title='category 2 child',
                               origin_category_id=3,
                               origin_parent_id=2))
    Category.create_all(categories)

    count = db.session.query(func.count('*')).select_from(Category).scalar()
    assert_equals(count, 3)

Example 23

Project: Snoopy Source File: main.py
@app.route('/drone/list', methods=['POST'])
@login_required
def drone_list_json():
    try:
        with db.SessionCtx() as session:
            devlist = session.query(
                db.Probe.monitor, func.count(distinct(db.Probe.device_mac))
            ).group_by(db.Probe.monitor).all()
            devlist = [dict(zip(('devname', 'n_macs'), d)) for d in devlist]
            return jsonify(success=True, drones=devlist)
    except Exception:
        logging.exception('Error getting monitor list:')
        return jsonify(success=False, errors=['Internal error'])

Example 24

Project: sqlalchemy Source File: test_select.py
    def test_group_by_composed(self):
        table = self.tables.some_table
        expr = (table.c.x + table.c.y).label('lx')
        stmt = select([func.count(table.c.id), expr]).group_by(expr).order_by(expr)
        self._assert_result(
            stmt,
            [(1, 3), (1, 5), (1, 7)]
        )

Example 25

Project: flask-admin Source File: view.py
Function: get_count_query
    def get_count_query(self):
        """
            Return a the count query for the model type

            A ``query(self.model).count()`` approach produces an excessive
            subquery, so ``query(func.count('*'))`` should be used instead.

            See commit ``#45a2723`` for details.
        """
        return self.session.query(func.count('*')).select_from(self.model)

Example 26

Project: GeoHealthCheck Source File: models.py
Function: get_resource_types_counts
def get_resource_types_counts():
    """return frequency counts and totals of registered resource types"""

    mrt = Resource.resource_type
    return [
        DB.session.query(mrt, func.count(mrt)).group_by(mrt),
        DB.session.query(mrt).count()
    ]

Example 27

Project: holmes-api Source File: domain.py
    def get_good_request_count(self, db):
        from holmes.models import Request

        sample_query = self.get_sample_requests(
            db,
            lambda query: query.filter(Request.status_code < 400)
        )

        return db \
            .query(func.count(sample_query.columns.id)) \
            .scalar()

Example 28

Project: Flask-Blogging Source File: __init__.py
def get_tag_data(sqla_storage):
    engine = sqla_storage.engine
    with engine.begin() as conn:
        tag_posts_table = sqla_storage.tag_posts_table
        tag_table = sqla_storage.tag_table

        tag_cloud_stmt = sqla.select([
            tag_table.c.text,func.count(tag_posts_table.c.tag_id)]).group_by(
            tag_posts_table.c.tag_id
        ).where(tag_table.c.id == tag_posts_table.c.tag_id).limit(10)
        tag_cloud = conn.execute(tag_cloud_stmt).fetchall()
    return tag_cloud

Example 29

Project: holmes-api Source File: request.py
    @classmethod
    def get_requests_by_status_count(self, domain_name, status_code, db):
        return db \
            .query(func.count(Request.id)) \
            .filter(Request.domain_name == domain_name) \
            .filter(Request.status_code == status_code) \
            .scalar()

Example 30

Project: beavy Source File: admin_model_view.py
Function: get_count_query
    def get_count_query(self):
        """
            Return a the count query for the model type

            A ``query(self.model).count()`` approach produces an excessive
            subquery, so ``query(func.count('*'))`` should be used instead.

            See commit ``#45a2723`` for details.
        """
        if 'polymorphic_identity' in getattr(self.model, "__mapper_args__",
                                             {}):
            return self.session.query(func.count('*')).select_from(
                self.model.query.selectable)

        return self.session.query(func.count('*')).select_from(self.model)

Example 31

Project: impactstory-tng Source File: refset.py
def num_people_in_refset():
    count_q = base_count_people_query()
    count_q = refine_refset_query(count_q)
    count_q = count_q.statement.with_only_columns([func.count()]).order_by(None)
    count = db.session.execute(count_q).scalar()
    print u"refsize count", count
    return count

Example 32

Project: ckanext-googleanalytics Source File: dbutil.py
def _update_visits(table_name, item_id, recently, ever):
    stats = get_table(table_name)
    id_col_name = "%s_id" % table_name[:-len("_stats")]
    id_col = getattr(stats.c, id_col_name)
    s = select([func.count(id_col)],
               id_col == item_id)
    connection = model.Session.connection()
    count = connection.execute(s).fetchone()
    if count and count[0]:
        connection.execute(stats.update()\
            .where(id_col == item_id)\
            .values(visits_recently=recently,
                    visits_ever=ever))
    else:
        values = {id_col_name: item_id,
                  'visits_recently': recently,
                  'visits_ever': ever}
        connection.execute(stats.insert()\
                           .values(**values))

Example 33

Project: JARR Source File: article.py
    def count_by_user_id(self, **filters):
        last_conn_max = datetime.utcnow() - timedelta(days=30)
        return dict(db.session.query(Article.user_id, func.count(Article.id))
                              .filter(*self._to_filters(**filters))
                              .join(User).filter(User.is_active == True,
                                        User.last_connection >= last_conn_max)
                              .group_by(Article.user_id).all())

Example 34

Project: Flask-Large-Application-Example Source File: helpers.py
def count(column, value, glob=False):
    """Counts number of rows with value in a column. This function is case-insensitive.

    Positional arguments:
    column -- the SQLAlchemy column object to search in (e.g. Table.a_column).
    value -- the value to search for, any string.

    Keyword arguments:
    glob -- enable %globbing% search (default False).

    Returns:
    Number of rows that match. Equivalent of SELECT count(*) FROM.
    """
    query = db.session.query(func.count('*'))
    if glob:
        query = query.filter(column.ilike(value))
    else:
        query = query.filter(func.lower(column) == value.lower())
    return query.one()[0]

Example 35

Project: sqlalchemy-utils Source File: test_sort_query.py
    def test_subqueried_scalar(self, session, Article, Category):
        article_count = (
            sa.sql.select(
                [sa.func.count(Article.id)],
                from_obj=[Article.__table__]
            )
            .where(Article.category_id == Category.id)
            .correlate(Category.__table__)
        )

        query = session.query(
            Category, article_count.label('articles')
        )
        query = sort_query(query, '-articles')
        assert_contains('ORDER BY articles DESC', query)

Example 36

Project: glossary-bot Source File: views.py
def get_stats():
    ''' Gather and return some statistics
    '''
    entries = db.session.query(func.count(Definition.term)).scalar()
    definers = db.session.query(func.count(distinct(Definition.user_name))).scalar()
    queries = db.session.query(func.count(Interaction.action)).scalar()
    outputs = (
        (u'I have definitions for', entries, u'term', u'terms', u'I don\'t have any definitions'),
        (u'', definers, u'person has defined terms', u'people have defined terms', u'Nobody has defined terms'),
        (u'I\'ve been asked for definitions', queries, u'time', u'times', u'Nobody has asked me for definitions')
    )
    lines = []
    for prefix, period, singular, plural, empty_line in outputs:
        if period:
            lines.append(u'{}{} {}'.format(u'{} '.format(prefix) if prefix else u'', period, singular if period == 1 else plural))
        else:
            lines.append(empty_line)
    # return the message
    return u'\n'.join(lines)

Example 37

Project: sqlalchemy-utils Source File: test_sort_query.py
    def test_column_property_descending(self, session, Article, Category):
        Category.article_count = sa.orm.column_property(
            sa.select([sa.func.count(Article.id)])
            .where(Article.category_id == Category.id)
            .label('article_count')
        )

        query = session.query(Category)
        query = sort_query(query, '-article_count')
        assert_contains('article_count DESC', query)

Example 38

Project: shiva-server Source File: main.py
Function: make_unique
    def _make_unique(self, model):
        """
        Retrieves all repeated slugs for a given model and appends the
        instance's primary key to it.

        """

        slugs = q(model).group_by(model.slug).\
            having(func.count(model.slug) > 1)

        for row in slugs:
            slug = row.slug
            for instance in q(model).filter_by(slug=row.slug):
                instance.slug += '-%s' % instance.pk

        return slugs

Example 39

Project: redwind Source File: views.py
@views.route('/tags/')
def tag_cloud():
    query = db.session.query(
        Tag.name, sqlalchemy.func.count(Post.id)
    ).join(Tag.posts)
    query = query.filter(sqlalchemy.sql.expression.not_(Post.deleted))
    if not flask_login.current_user.is_authenticated():
        query = query.filter(sqlalchemy.sql.expression.not_(Post.draft))
    query = query.group_by(Tag.id).order_by(Tag.name)
    query = query.having(sqlalchemy.func.count(Post.id) >= MIN_TAG_COUNT)
    tagdict = {}
    for name, count in query.all():
        tagdict[name] = tagdict.get(name, 0) + count
    tags = [
        {"name": name, "count": tagdict[name]}
        for name in sorted(tagdict)
    ]
    return render_tags("Tags", tags)

Example 40

Project: knowledge-repo Source File: models.py
    @hybrid_property
    def view_user_count(self):
        return (db.session.query(func.count(distinct(PageView.user_id)))
                          .filter(PageView.object_id == self.id)
                          .filter(PageView.object_type == 'post')
                          .scalar())

Example 41

Project: adhocracy Source File: tag.py
    @classmethod
    def popular_tags(cls, limit=None):
        from tagging import Tagging
        from delegateable import Delegateable
        q = meta.Session.query(Tag)
        q = q.add_column(func.count(Tagging.id))
        q = q.join(Tagging)
        if ifilter.has_instance():
            q = q.join(Delegateable)
            q = q.filter(Delegateable.instance_id == ifilter.get_instance().id)
        q = q.group_by(Tag.id, Tag.create_time, Tag.name)
        q = q.order_by(func.count(Tagging.id).desc())
        # SQLAlchemy turns this into a cueming subquery:
        # if limit is not None:
        #     q = q.limit(limit)
        # print "QUERY", q
        tags = [(k, v) for k, v in q.all() if v > 0]
        return tags[:limit]

Example 42

Project: pgcontents Source File: query.py
def _dir_exists(db, user_id, db_dirname):
    """
    Internal implementation of dir_exists.

    Expects a db-style path name.
    """
    return db.execute(
        select(
            [func.count(directories.c.name)],
        ).where(
            and_(
                directories.c.user_id == user_id,
                directories.c.name == db_dirname,
            ),
        )
    ).scalar() != 0

Example 43

Project: MailingListStats Source File: report.py
    def get_messages_by_year(self, session):
        '''SELECT m.mailing_list_url,
                  extract(year from m.first_date) as year,
                  count(distinct(lower(mp.email_address)))
             FROM messages m, messages_people mp
            WHERE m.message_ID = mp.message_ID
              AND type_of_recipient = 'From'
            GROUP BY m.mailing_list_url, year;'''

        ret = session.query(db.Messages.mailing_list_url,
                            extract('year', db.Messages.first_date),
                            func.count(db.Messages.mailing_list_url)) \
            .group_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date)) \
            .order_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date))
        return ret.all()

Example 44

Project: knowledge-repo Source File: models.py
    @vote_count.expression
    def vote_count(self):
        return (select([func.count(Vote.id)])
                .where(Vote.object_id == self.id)
                .where(Vote.object_type == 'post')
                .label("vote_count"))

Example 45

Project: raco Source File: catalog.py
    def _convert_zeroary_expr(self, cols, expr, input_scheme):
        if isinstance(expr, expression.COUNTALL):
            return func.count(cols[0])
        if isinstance(expr, expression.Literal):
            return literal(expr.value,
                           raco_to_type[expr.typeof(input_scheme, None)])
        raise NotImplementedError("expression {} to sql".format(type(expr)))

Example 46

Project: pychess Source File: database.py
Function: update_count
    def update_count(self):
        if self.ply is not None and self.ply <= STAT_PLY_MAX:
            # update self.count in get_bitboards()
            pass
        else:
            if self.where_tags is not None and self.where_bitboards is not None:
                stmt = select([func.count()], from_obj=self.from_obj).where(self.where_tags).where(self.where_bitboards)
            elif self.where_tags is not None:
                stmt = select([func.count()], from_obj=self.from_obj).where(self.where_tags)
            elif self.where_bitboards is not None:
                stmt = select([func.count()], from_obj=self.from_obj).where(self.where_bitboards)
            else:
                stmt = count_games
            self.count = self.engine.execute(stmt).scalar()

Example 47

Project: torext Source File: sql.py
Function: paginator
    def paginator(self, _max, index):
        count = self.with_entities(func.count(self.entity_class.id)).scalar()

        if (index - 1) * _max > count or index <= 0:
            raise ValueError('index or max argument overflow')

        if not count:
            return count, self

        ret = self.offset(_max * (index - 1)).limit(_max)
        return count, ret

Example 48

Project: sqlalchemy-redshift Source File: test_unload_from_select.py
def test_basic_unload_case():
    """Tests that the simplest type of UnloadFromSelect works."""

    unload = dialect.UnloadFromSelect(
        select=sa.select([sa.func.count(table.c.id)]),
        unload_location='s3://bucket/key',
        access_key_id=access_key_id,
        secret_access_key=secret_access_key,
    )

    expected_result = """
        UNLOAD ('SELECT count(t1.id) AS count_1 FROM t1')
        TO 's3://bucket/key'
        CREDENTIALS '{creds}'
    """.format(creds=creds)

    assert clean(compile_query(unload)) == clean(expected_result)

Example 49

Project: sqlalchemy Source File: test_firebird.py
    @testing.provide_metadata
    def test_strlen(self):
        metadata = self.metadata

        # On FB the length() function is implemented by an external UDF,
        # strlen().  Various SA tests fail because they pass a parameter
        # to it, and that does not work (it always results the maximum
        # string length the UDF was declared to accept). This test
        # checks that at least it works ok in other cases.

        t = Table('t1', metadata, Column('id', Integer,
                  Sequence('t1idseq'), primary_key=True), Column('name'
                  , String(10)))
        metadata.create_all()
        t.insert(values=dict(name='dante')).execute()
        t.insert(values=dict(name='alighieri')).execute()
        select([func.count(t.c.id)], func.length(t.c.name)
               == 5).execute().first()[0] == 1

Example 50

Project: holmes-api Source File: domain.py
    def get_active_review_count(self, db, page_filter=None):
        from holmes.models import Review, Page

        query = db.query(func.count(distinct(Review.page_id)))  # See #111

        if page_filter:
            query = query.join(Page, Page.id == Review.page_id) \
                .filter(Page.url.like('%s/%s%%' % (self.url, page_filter)))

        query = query.filter(Review.is_active == True, Review.domain_id == self.id)

        return query.scalar()
See More Examples - Go to Next Page
Page 1 Selected Page 2 Page 3 Page 4