How to use the sqlalchemy.func function in SQLAlchemy

a1 = Address(email_address='foo')
        sess.add_all([u1, a1])
        sess.flush()

        assert testing.db.scalar(select([func.count(1)]).where(addresses.c.user_id!=None)) == 0
        u1 = sess.query(User).get(u1.id)
        u1.addresses.append(a1)
        sess.flush()

        assert testing.db.execute(select([addresses]).where(addresses.c.user_id!=None)).fetchall() == [
            (a1.id, u1.id, 'foo')
        ]

        u1.addresses.remove(a1)
        sess.flush()
        assert testing.db.scalar(select([func.count(1)]).where(addresses.c.user_id!=None)) == 0

        u1.addresses.append(a1)
        sess.flush()
        assert testing.db.execute(select([addresses]).where(addresses.c.user_id!=None)).fetchall() == [
            (a1.id, u1.id, 'foo')
        ]

        a2= Address(email_address='bar')
        u1.addresses.remove(a1)
        u1.addresses.append(a2)
        sess.flush()
        assert testing.db.execute(select([addresses]).where(addresses.c.user_id!=None)).fetchall() == [
            (a2.id, u1.id, 'bar')
        ]

def get_strict_matching_sticker_sets(session, context):
    """Get all sticker sets by accumulated score for strict search."""
    strict_subquery = get_strict_matching_query(session, context, sticker_set=True) \
        .subquery('strict_sticker_subq')

    score = func.sum(strict_subquery.c.score_with_usage).label('score')
    matching_sets = session.query(StickerSet, score) \
        .join(strict_subquery, StickerSet.name == strict_subquery.c.name) \
        .group_by(StickerSet) \
        .order_by(score.desc()) \
        .limit(8) \
        .offset(context.offset) \
        .all()

    return matching_sets

def estimate(self, entry):
        with Session() as session:
            series = session.query(Series).filter(Series.name == entry['series_name']).first()
            if not series:
                return
            episodes = (session.query(Episode).join(Episode.series).
                        filter(Episode.season != None).
                        filter(Series.id == series.id).
                        filter(Episode.season == func.max(Episode.season).select()).
                        order_by(desc(Episode.number)).limit(2).all())

            if len(episodes) < 2:
                return
            # If last two eps were not contiguous, don't guess
            if episodes[0].number != episodes[1].number + 1:
                return
            # If first_seen in None, return
            if episodes[0].first_seen is None or episodes[1].first_seen is None:
                return
            last_diff = episodes[0].first_seen - episodes[1].first_seen
            # If last eps were grabbed close together, we might be catching up, don't guess
            # Or, if last eps were too far apart, don't guess
            # TODO: What range?
            if last_diff < timedelta(days=2) or last_diff > timedelta(days=10):
                return

def get(self, query):
        """Sites matching query"""
        table = Visit.__table__
        attr = Visit.domain
        countcol = func.count(1)
        sites_query = (
            self.db
            .query(attr.label('host'), countcol.label('count'))
            .filter(attr.like('%%%s%%' % query))
            .filter(in_last_month())
            .group_by(attr)
            .order_by(desc(countcol)))
        sites = sites_query[:20]
        subquery = sites_query.subquery()
        all_ = (self.db.query(func.sum(subquery.c.count))
                .select_from(subquery)
                .scalar())
        self.render('sites_table.html', sites=sites, all_=all_)

# FROM
    # data_centers
    # JOIN stations ON data_centers.id = stations.datacenter_id
    # JOIN channels on channels.station_id = stations.id
    # JOIN segments on segments.channel_id = channels.id
    # GROUP BY stations.id

    def countif(key, binexpr):
        NULL = literal_column("NULL")
        return func.count(case([(binexpr, Segment.id)], else_=NULL)).label(key)

    qry = session.query(DataCenter.id.label('dc_id'),  # @UndefinedVariable
                        Station.id.label('station_id'),
                        Station.latitude.label('lat'),
                        Station.longitude.label('lon'),
                        func.count(Segment.id).label('num_segments'),
                        *[countif(k, v) for k, v in binexprs2count.items()])

    # ok seems that back referenced relationships are instantiated only after the first query is
    # made:
    # https://stackoverflow.com/questions/14921777/backref-class-attribute
    # workaround:
    configure_mappers()

    return qry.join(DataCenter.stations,  # @UndefinedVariable
                    Station.channels,  # @UndefinedVariable
                    Channel.segments,  # @UndefinedVariable
                    ).group_by(DataCenter.id, Station.id)

:param imdb_id: Imdb id
    :param tmdb_id: Tmdb id
    :param session: Optional session to use, new session used otherwise
    :param queue_name: Name of movie queue to get items from
    :return: Title of forgotten movie
    :raises QueueError: If queued item could not be found with given arguments
    """
    log.debug('queue_del - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s',
              title, imdb_id, tmdb_id, movie_id, queue_name)
    query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
    if imdb_id:
        query = query.filter(QueuedMovie.imdb_id == imdb_id)
    elif tmdb_id:
        query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
    elif title:
        query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
    elif movie_id:
        query = query.filter(QueuedMovie.id == movie_id)
    try:
        item = query.one()
        title = item.title
        session.delete(item)
        return title
    except NoResultFound as e:
        raise QueueError(
            'title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s not found in queue %s' % (
                title, imdb_id, tmdb_id, movie_id, queue_name))
    except MultipleResultsFound:
        raise QueueError('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s matches multiple results in queue %s' %
                         (title, imdb_id, tmdb_id, movie_id, queue_name))

.subquery())

    arches = (db.session.query(Arch, sub.c.cnt).join(sub)
              .order_by(desc("cnt"))
              .all())

    exes = (db.session.query(ReportExecutable.path,
                             func.sum(ReportExecutable.count).label("cnt"))
            .join(Report)
            .filter(Report.id.in_(report_ids))
            .group_by(ReportExecutable.path)
            .order_by(desc("cnt"))
            .all())

    sub = (db.session.query(ReportPackage.installed_package_id,
                            func.sum(ReportPackage.count).label("cnt"))
           .join(Report)
           .filter(Report.id.in_(report_ids))
           .group_by(ReportPackage.installed_package_id)
           .subquery())
    packages_known = db.session.query(Package, sub.c.cnt).join(sub).all()

    packages_unknown = (db.session.query(ReportUnknownPackage,
                                         ReportUnknownPackage.count)
                        .join(Report)
                        .filter(Report.id.in_(report_ids))).all()

    packages = packages_known + packages_unknown

    # creates a package_counts list with this structure:
    # [(package name, count, [(package version, count in the version)])]
    names = defaultdict(lambda: {"count": 0, "versions": defaultdict(int)})

A submission is an attempt from an event participant to solve a challenge.
    """

    __tablename__ = 'Submissions'

    id = DB.Column(DB.Integer, primary_key=True)
    """The unique ID of the submission. Should be generated by the database. Used as primary key."""
    team_id = DB.Column(DB.Integer, ForeignKey('Teams.id'), nullable=True)
    """The ID of the team who made the submission. Used as foreign key."""
    challenge_id = DB.Column(DB.Integer, ForeignKey('Challenges.id'), nullable=True)
    """The ID of the challenge that this submission attempted to solve. Used as foreign key."""
    input = DB.Column(DB.String(64))
    """The solution that was submitted."""
    is_correct = DB.Column(DB.Boolean)
    """Whether or not the submission is correct."""
    time = DB.Column(DB.DateTime, server_default=func.now())
    """The date and time of the submission."""

    def __repr__(self):
        return ''\
            .format(self.id, self.team_id, self.challenge_id, self.input, self.is_correct)

    def __eq__(self, other):
        return self.id == other.id and \
            self.team_id == other.team_id and \
            self.challenge_id == other.challenge_id and \
            self.input == other.input and \
            self.is_correct == other.is_correct and \
            self.time == other.time

def _get_metadata_query(*, entity_type, entity_id):
    model = _get_metadata_model(entity_type)
    query = db.session.query(func.json_object_agg(model.key, model.metadata_item))
    if entity_type == 'session':
        related = Session
    elif entity_type == 'test':
        related = Test
    else:
        error_abort('Invalid entity type', requests.codes.bad_request)
    query = query.join(related)
    if isinstance(entity_id, int):
        query = query.filter(related.id == entity_id)
    else:
        query = query.filter(related.logical_id == entity_id)
    return query

AccountItem.id.label('id'),
                AccountItem.name.label('name'),
                ServiceItem.base_price.label('base_price')
            )
            .join(ServiceItem, TourSale.service_item)
            .join(Service, ServiceItem.service)
            .join(AccountItem, Service.account_item)
            .filter(TourSale.id == tour_sale.id)
        )
        subq = query.subquery()
        return (
            DBSession.query(
                subq.c.id,
                subq.c.name,
                func.count(subq.c.id).label('cnt'),
                (func.sum(subq.c.base_price) / rate).label('price')
            )
            .group_by(subq.c.id, subq.c.name)
            .order_by(subq.c.name)
        )