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.

178 Examples 7

Example 1

Project: pychess
Source File: database.py
View license
    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 2

Project: pgcontents
Source File: query.py
View license
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 3

Project: torext
Source File: sql.py
View license
    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 4

View license
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 5

Project: bigg_models
Source File: curation.py
View license
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 6

Project: Snoopy
Source File: main.py
View license
@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 7

View license
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 8

Project: beavy
Source File: admin_model_view.py
View license
    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 9

Project: tvb-framework
Source File: operation_dao.py
View license
    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 10

Project: shiva-server
Source File: main.py
View license
    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 11

Project: plenario
Source File: shape.py
View license
def _aggregate_point_data(args):
    meta_params = ('dataset', 'shapeset', 'data_type', 'geom', 'offset', 'limit')
    meta_vals = (args.data.get(k) for k in meta_params)
    dataset, shapeset, data_type, geom, offset, limit = meta_vals

    q = detail_query(args, aggregate=True)
    q = q.add_columns(func.count(dataset.c.hash))

    res_cols = []
    columns = [str(col) for col in dataset.columns]
    columns += [str(col) for col in shapeset.columns]
    for col in columns:
        col = col.split('.')
        if col[0] == shapeset.name:
            res_cols.append(col[1])
    res_cols.append('count')

    return [OrderedDict(zip(res_cols, res)) for res in q.all()]

Example 12

Project: plenario
Source File: ShapeMetadata.py
View license
    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 13

Project: raco
Source File: catalog.py
View license
    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 14

Project: pyspider
Source File: taskdb.py
View license
    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 15

Project: sqlalchemy
Source File: test_select.py
View license
    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 16

Project: sqlalchemy
Source File: test_firebird.py
View license
    @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 17

Project: faf
Source File: queries.py
View license
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 18

Project: blaze
Source File: sql.py
View license
@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 19

Project: ckanext-googleanalytics
Source File: dbutil.py
View license
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 20

Project: glottolog3
Source File: check_db_consistency.py
View license
    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 21

Project: glossary-bot
Source File: views.py
View license
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 22

Project: knowledge-repo
Source File: models.py
View license
    @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 23

Project: knowledge-repo
Source File: models.py
View license
    @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 24

Project: knowledge-repo
Source File: models.py
View license
    @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 25

Project: knowledge-repo
Source File: models.py
View license
    @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 26

Project: knowledge-repo
Source File: models.py
View license
    @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 27

Project: ushine-learning
Source File: test_base_model.py
View license
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 28

Project: portingdb
Source File: htmlreport.py
View license
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 29

Project: flask-admin
Source File: view.py
View license
    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 30

Project: GeoHealthCheck
Source File: models.py
View license
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 31

Project: Flask-Blogging
Source File: __init__.py
View license
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 32

Project: holmes-api
Source File: domain.py
View license
    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()

Example 33

Project: holmes-api
Source File: domain.py
View license
    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 34

Project: holmes-api
Source File: domain.py
View license
    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 35

Project: holmes-api
Source File: request.py
View license
    @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 36

Project: impactstory-tng
Source File: refset.py
View license
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 37

Project: impactstory-tng
Source File: refset.py
View license
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 38

Project: JARR
Source File: abstract.py
View license
    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 39

Project: JARR
Source File: article.py
View license
    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 40

View license
    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 41

View license
    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 42

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 43

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 44

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 45

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 46

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 47

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 48

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 49

Project: sqlalchemy-utils
Source File: test_sort_query.py
View license
    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 50

Project: redwind
Source File: views.py
View license
@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)