Python sqlalchemy.func.count() Examples

The following are 30 code examples of sqlalchemy.func.count(). 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.func , or try the search function .
Example #1
Source File: temporary_did.py    From rucio with Apache License 2.0 7 votes vote down vote up
def get_count_of_expired_temporary_dids(rse_id, session=None):
    """
    List expired temporary DIDs.

    :param rse_id: the rse id.
    :param session: The database session in use.

    :returns: a count number.
    """
    is_none = None
    count = session.query(func.count(models.TemporaryDataIdentifier.scope)).\
        with_hint(models.TemporaryDataIdentifier, "INDEX(tmp_dids TMP_DIDS_EXPIRED_AT_IDX)", 'oracle').\
        filter(case([(models.TemporaryDataIdentifier.expired_at != is_none, models.TemporaryDataIdentifier.rse_id), ]) == rse_id).\
        one()

    return count[0] or 0 
Example #2
Source File: __init__.py    From designate with Apache License 2.0 6 votes vote down vote up
def count_recordsets(self, context, criterion=None):
        # Ensure that we return only active recordsets
        rjoin = tables.recordsets.join(
            tables.zones,
            tables.recordsets.c.zone_id == tables.zones.c.id)

        query = select([func.count(tables.recordsets.c.id)]).\
            select_from(rjoin).\
            where(tables.zones.c.deleted == '0')

        query = self._apply_criterion(tables.recordsets, query, criterion)
        query = self._apply_tenant_criteria(context, tables.recordsets, query)
        query = self._apply_deleted_criteria(context, tables.recordsets, query)

        resultproxy = self.session.execute(query)
        result = resultproxy.fetchone()

        if result is None:
            return 0

        return result[0]

    # Record Methods 
Example #3
Source File: secrets.py    From vault with MIT License 6 votes vote down vote up
def get_top_logins(limit=10):
    """ Return most popular logins for auto-completion """

    count_ = func.count('*')

    results = get_session().query(SecretModel.login, count_).\
        filter(SecretModel.login != '').\
        group_by(SecretModel.login).\
        order_by(count_.desc()).\
        limit(limit).\
        all()

    if results:
        return [result.login for result in results]

    return [] 
Example #4
Source File: count.py    From py-mongosql with BSD 2-Clause "Simplified" License 6 votes vote down vote up
def input_prepare_query_object(self, query_object):
        # When we count, we don't care about certain things
        if query_object.get('count', False):
            # Performance: do not sort when counting
            query_object.pop('sort', None)
            # We don't care about projections either
            query_object.pop('project', None)
            # Also, remove all skips & limits
            query_object.pop('skip', None)
            query_object.pop('limit', None)
            # Remove all join, but not joinf (as it may filter)
            query_object.pop('join', None)
            # Finally, when we count, we have to remove `max_items` setting from MongoLimit.
            # Only MongoLimit can do it, and it will do it for us.
            # See: MongoLimit.input_prepare_query_object

        return query_object 
Example #5
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 #6
Source File: counting_query_wrapper.py    From py-mongosql with BSD 2-Clause "Simplified" License 6 votes vote down vote up
def __init__(self, query: Query):
        # The original query. We store it just in case.
        self._original_query = query

        # The current query
        # It differs from the originla query in that it is modified with a window function counting the rows
        self._query = query

        # The iterator for query results ; `None` if the query has not yet been executed
        # If the query has been executed, there is always an iterator available, even if there were no results
        self._query_iterator = None

        # The total count ; `None` if the query has not yet been executed
        self._count = None

        # Whether the query is going to return single entities
        self._single_entity = (  # copied from sqlalchemy.orm.loading.instances
            not getattr(query, '_only_return_tuples', False)  # accessing protected properties
            and len(query._entities) == 1
            and query._entities[0].supports_single_entity
        )

        # The method that will fix result rows
        self._row_fixer = self._fix_result_tuple__single_entity if self._single_entity else self._fix_result_tuple__tuple 
Example #7
Source File: helpers.py    From Flask-Large-Application-Example with MIT License 6 votes vote down vote up
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 #8
Source File: NuHeader.py    From ReadableWebProxy with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
def review_probable_validated(self):
		self.log.info("Doing optional validation")
		with db.session_context() as db_sess:
			new_items = db_sess.query(db.NuReleaseItem)           \
					.filter(db.NuReleaseItem.validated == True)        \
					.filter(db.NuReleaseItem.reviewed == 'unverified') \
					.filter(db.NuReleaseItem.actual_target != None)    \
					.order_by(desc(db.NuReleaseItem.first_seen))       \
					.all()


			unverified = db_sess.query(db.NuReleaseItem)           \
					.filter(db.NuReleaseItem.validated == False)        \
					.filter(db.NuReleaseItem.actual_target != None)    \
					.count()

			self.log.info("Have %s items to do validity checks on", len(new_items))
			self.log.info("%s items needing checking", unverified)

			for row in new_items:
				self.review_probable_validated_row(row)

			db_sess.commit() 
Example #9
Source File: __init__.py    From designate with Apache License 2.0 6 votes vote down vote up
def find_tenants(self, context):
        # returns an array of tenant_id & count of their zones
        query = select([tables.zones.c.tenant_id,
                        func.count(tables.zones.c.id)])
        query = self._apply_tenant_criteria(context, tables.zones, query)
        query = self._apply_deleted_criteria(context, tables.zones, query)
        query = query.group_by(tables.zones.c.tenant_id)

        resultproxy = self.session.execute(query)
        results = resultproxy.fetchall()

        tenant_list = objects.TenantList(
            objects=[objects.Tenant(id=t[0], zone_count=t[1]) for t in
                     results])

        tenant_list.obj_reset_changes()

        return tenant_list 
Example #10
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 #11
Source File: cust_filters_sql.py    From SkittBot 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 #12
Source File: blacklist_sql.py    From SkittBot with GNU General Public License v3.0 5 votes vote down vote up
def num_blacklist_filters():
    try:
        return SESSION.query(BlackListFilters).count()
    finally:
        SESSION.close() 
Example #13
Source File: cust_filters_sql.py    From SkittBot with GNU General Public License v3.0 5 votes vote down vote up
def num_filters():
    try:
        return SESSION.query(CustomFilters).count()
    finally:
        SESSION.close() 
Example #14
Source File: __init__.py    From designate with Apache License 2.0 5 votes vote down vote up
def get_tenant(self, context, tenant_id):
        # get list list & count of all zones owned by given tenant_id
        query = select([tables.zones.c.name])
        query = self._apply_tenant_criteria(context, tables.zones, query)
        query = self._apply_deleted_criteria(context, tables.zones, query)
        query = query.where(tables.zones.c.tenant_id == tenant_id)

        resultproxy = self.session.execute(query)
        results = resultproxy.fetchall()

        return objects.Tenant(
            id=tenant_id,
            zone_count=len(results),
            zones=[r[0] for r in results]) 
Example #15
Source File: notes_sql.py    From SkittBot with GNU General Public License v3.0 5 votes vote down vote up
def num_notes():
    try:
        return SESSION.query(Notes).count()
    finally:
        SESSION.close() 
Example #16
Source File: notes_sql.py    From SkittBot 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 #17
Source File: blacklist_sql.py    From SkittBot 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 #18
Source File: test_sqlalchemy_bigquery.py    From pybigquery with MIT License 5 votes vote down vote up
def test_group_by(session, table, session_using_test_dataset, table_using_test_dataset):
    """labels in SELECT clause should be correclty formatted (dots are replaced with underscores)"""
    for session, table in [(session, table), (session_using_test_dataset, table_using_test_dataset)]:
        result = session.query(table.c.string, func.count(table.c.integer)).group_by(table.c.string).all()
    assert len(result) > 0 
Example #19
Source File: test_sqlalchemy.py    From elasticsearch-dbapi with Apache License 2.0 5 votes vote down vote up
def test_select_count(self):
        """
        SQLAlchemy: Test select all
        """
        count = select([func.count("*")], from_obj=self.table_flights).scalar()
        # insert data delays let's assert we have something there
        self.assertGreater(count, 1) 
Example #20
Source File: rules_sql.py    From SkittBot 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 #21
Source File: __init__.py    From designate with Apache License 2.0 5 votes vote down vote up
def count_zones(self, context, criterion=None):
        query = select([func.count(tables.zones.c.id)])
        query = self._apply_criterion(tables.zones, query, criterion)
        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 attribute methods 
Example #22
Source File: sqlalchemy.py    From telethon-session-sqlalchemy with MIT License 5 votes vote down vote up
def has_session(self, session_id: str) -> bool:
        if self.core_mode:
            t = self.Session.__table__
            rows = self.db_engine.execute(select([func.count(t.c.auth_key)])
                                          .where(and_(t.c.session_id == session_id,
                                                      t.c.auth_key != b'')))
            try:
                count, = next(rows)
                return count > 0
            except StopIteration:
                return False
        else:
            return self.Session.query.filter(self.Session.session_id == session_id).count() > 0 
Example #23
Source File: shots_service.py    From zou with GNU Affero General Public License v3.0 5 votes vote down vote up
def remove_shot(shot_id, force=False):
    """
    Remove given shot from database. If it has tasks linked to it, it marks
    the shot as canceled. Deletion can be forced.
    """
    shot = get_shot_raw(shot_id)
    is_tasks_related = Task.query.filter_by(entity_id=shot_id).count() > 0

    if is_tasks_related and not force:
        shot.update({"canceled": True})
        clear_shot_cache(shot_id)
        events.emit("shot:update", {"shot_id": shot_id})
    else:
        from zou.app.services import tasks_service

        tasks = Task.query.filter_by(entity_id=shot_id).all()
        for task in tasks:
            deletion_service.remove_task(task.id, force=True)
            tasks_service.clear_task_cache(str(task.id))

        EntityVersion.delete_all_by(entity_id=shot_id)
        Subscription.delete_all_by(entity_id=shot_id)
        shot.delete()
        clear_shot_cache(shot_id)
        events.emit("shot:delete", {"shot_id": shot_id})

    deleted_shot = shot.serialize(obj_type="Shot")
    return deleted_shot 
Example #24
Source File: __init__.py    From babbage with MIT License 5 votes vote down vote up
def count_results(cube, q):
    """ Get the count of records matching the query. """
    q = select(columns=[func.count(True)], from_obj=q.alias())
    return cube.engine.execute(q).scalar() 
Example #25
Source File: views.py    From ACE with Apache License 2.0 5 votes vote down vote up
def get_created_OR_modified_indicators_during(daterange_start, daterange_end, created=False, modified=False):
    """
    Use SIP to return a DataFrame table representing the status of all indicators created OR modified during
    daterange and by the month (%Y%m) they were created in.i

    :param datetime daterange_start: The datetime representing the start time of the daterange
    :param datetime daterange_end: The datetime representing the end time of the daterange
    :param bool created: If True, return table of created indicators
    :param bool modified: If True, return table of modified indicators
    :return: Pandas DataFrame table
    """
    sip_host = saq.CONFIG.get("sip", "remote_address")
    api_key = saq.CONFIG.get("sip", "api_key")
    sc = pysip.Client(sip_host, api_key, verify=False) 
   
    if created is modified: # they should never be the same
        logging.warning("Created and Modfied flags both set to '{}'".format(created))
        return False
    table_type = "created" if created else "modified" 

    statuses = sc.get('/api/indicators/status')
    statuses = [s['value'] for s in statuses if s['value'] != 'FA']

    status_by_date = {}
    month_ranges = get_month_ranges(daterange_start, daterange_end)
    for month, daterange in month_ranges.items():
        status_counts = {}
        for status in statuses:
            status_counts[status] = sc.get('/indicators?status={0}&{1}_after={2}&{3}_before={4}&count'
                                           .format(status, table_type, daterange[0], table_type, daterange[1]))['count']
        status_by_date[month] = status_counts

    status_by_month = pd.DataFrame.from_dict(status_by_date, orient='index')
    status_by_month.name = "Count of Indicator Status by {} Month".format(table_type.capitalize())
    return status_by_month 
Example #26
Source File: counting_query_wrapper.py    From py-mongosql with BSD 2-Clause "Simplified" License 5 votes vote down vote up
def _get_count_from_result_tuple(row):
        """ Get the count from the result row """
        return row[-1] 
Example #27
Source File: counting_query_wrapper.py    From py-mongosql with BSD 2-Clause "Simplified" License 5 votes vote down vote up
def _get_query_count__make_another_query(self) -> int:
        """ Make an additional query to count the number of rows """
        # Build the query
        q = self._original_query

        # Remove eager loads
        q = q.enable_eagerloads(False)

        # Remove LIMIT and OFFSET
        q = q.limit(None).offset(None)

        # Exec
        return q.count() 
Example #28
Source File: count.py    From py-mongosql with BSD 2-Clause "Simplified" License 5 votes vote down vote up
def alter_query(self, query, as_relation=None):
        """ Apply offset() and limit() to the query """
        if self.count:
            # Previously, we used to do counts like this:
            # >>> query = query.with_entities(func.count())
            # However, when there's no WHERE clause set on a Query, it's left without any reference to the target table.
            # In this case, SqlAlchemy will actually generate a query without a FROM clause, which gives a wrong count!
            # Therefore, we have to make sure that there will always be a FROM clause.
            #
            # Normally, we just do the following:
            # >>> query = query.select_from(self.model)
            # This is supposed to indicate which table to select from.
            # However, it can only be applied when there's no FROM nor ORDER BY clauses present.
            #
            # But wait a second... didn't we just assume that there would be no FROM clause?
            # Have a look at this ugly duckling:
            # >>> Query(User).filter_by().select_from(User)
            # This filter_by() would actually create an EMPTY condition, which will break select_from()'s assertions!
            # This is reported to SqlAlchemy:
            # https://github.com/sqlalchemy/sqlalchemy/issues/4606
            # And (is fixed in version x.x.x | is not going to be fixed)
            #
            # Therefore, we'll try to do it the nice way ; and if it fails, we'll have to do something else.
            try:
                query = query.with_entities(func.count()).select_from(self.model)
            except sa_exc.InvalidRequestError:
                query = query.from_self(func.count())

        return query 
Example #29
Source File: count.py    From py-mongosql with BSD 2-Clause "Simplified" License 5 votes vote down vote up
def input(self, count=None):
        super(MongoCount, self).input(count)
        if not isinstance(count, (int, bool, NoneType)):
            raise InvalidQueryError('Count must be either true or false. Or at least a 1, or a 0')

        # Done
        self.count = count
        return self 
Example #30
Source File: count.py    From py-mongosql with BSD 2-Clause "Simplified" License 5 votes vote down vote up
def __init__(self, model, bags):
        """ Init a count

        :param model: Sqlalchemy model to work with
        :param bags: Model bags
        """
        super(MongoCount, self).__init__(model, bags)

        # On input
        self.count = None