Python sqlalchemy.func.min() Examples

def event_summery(self):
        with self.session_scope() as session:
            time = session.query(func.min(Event.time), func.max(Event.time)).all()
            print(f'Event time duration:')
            print(f'From: {time[0][0].isoformat()}')
            print(f'To:   {time[0][1].isoformat()}\n')

            event_count = session.query(Event).count()
            print(f'Total {event_count} events\n')

            boundary = session.query(func.min(Event.longitude), func.max(Event.longitude),
                                     func.min(Event.latitude), func.max(Event.latitude)).all()
            print(f'Event boundary:')
            print(f'West: {boundary[0][0]:>8.4f}')
            print(f'East: {boundary[0][1]:>8.4f}')
            print(f'South: {boundary[0][2]:>7.4f}')
            print(f'North: {boundary[0][3]:>7.4f}\n')
            self.pick_summery() 

def get_new_packages(cls):
        '''
        @return: Returns list of new pkgs and date when they were created, in
                 format: [(id, date_ordinal), ...]
        '''
        def new_packages():
            # Can't filter by time in select because 'min' function has to
            # be 'for all time' else you get first revision in the time period.
            package_revision = table('package_revision')
            revision = table('revision')
            s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision)]).group_by(package_revision.c.id).order_by(func.min(revision.c.timestamp))
            res = model.Session.execute(s).fetchall() # [(id, datetime), ...]
            res_pickleable = []
            for pkg_id, created_datetime in res:
                res_pickleable.append((pkg_id, created_datetime.toordinal()))
            return res_pickleable
        if cache_enabled:
            week_commences = cls.get_date_week_started(datetime.date.today())
            key = 'all_new_packages_%s' + week_commences.strftime(DATE_FORMAT)
            new_packages = our_cache.get_value(key=key,
                                               createfunc=new_packages)
        else:
            new_packages = new_packages()
        return new_packages 

def test_aggregate(self):
        foo, Foo = self.tables.foo, self.classes.Foo

        sess = create_session()
        query = sess.query(Foo)
        assert query.count() == 100
        assert sess.query(func.min(foo.c.bar)).filter(
            foo.c.bar < 30
        ).one() == (0,)

        assert sess.query(func.max(foo.c.bar)).filter(
            foo.c.bar < 30
        ).one() == (29,)

        eq_(
            query.filter(foo.c.bar < 30)
            .with_entities(sa.func.max(foo.c.bar))
            .scalar(),
            29,
        ) 

def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        y_column = getattr(self.model, value['y_column'])

        if value['y_func'] == 'count':
            y_func = func.count(y_column)
        elif value['y_func'] == 'sum':
            y_func = func.sum(y_column)
        elif value['y_func'] == 'min':
            y_func = func.min(y_column)
        elif value['y_func'] == 'max':
            y_func = func.max(y_column)
        elif value['y_func'] == 'avg':
            y_func = func.avg(y_column)
        else:
            return qs.filter(sql.false())

        qs = qs.session.query(y_func).one()

        return qs 

def get_first_requests(db_session, endpoint_id, limit=None):
    """
    Returns a list with all versions and when they're first used
    :param db_session: session for the database
    :param limit: only return the most recent versions
    :param endpoint_id: id of the endpoint
    :return list of tuples with versions
    """
    query = (
        db_session.query(
            Request.version_requested, func.min(Request.time_requested).label('first_used')
        )
        .filter(Request.endpoint_id == endpoint_id)
        .group_by(Request.version_requested)
        .order_by(desc('first_used'))
    )
    if limit:
        query = query.limit(limit)
    return query.all() 

def test_core2(dburl):
    with S(dburl, echo=ECHO) as s:
        sel = select([Book.score]).order_by(Book.id)
        check_paging_core(sel, s)

        sel = select([Book.score]) \
            .order_by(Author.id - Book.id, Book.id) \
            .where(Author.id == Book.author_id)
        check_paging_core(sel, s)

        sel = select([Book.author_id, func.count()]) \
            .group_by(Book.author_id) \
            .order_by(func.sum(Book.popularity))
        check_paging_core(sel, s)

        v = func.sum(func.coalesce(Book.a, 0)) + func.min(Book.b)
        sel = select([Book.author_id, func.count(), v]) \
            .group_by(Book.author_id) \
            .order_by(v)
        check_paging_core(sel, s) 

def _raw_statistics_time_range(cls, s, statistics_owner=None):
        '''
        The time range over which statistics exist (optionally restricted by owner),
        ignoring constants at "time zero".  This is the first to the last time for
        any statistics - it pays no attention to gaps.
        '''
        from .statistic import StatisticJournal, StatisticName
        q = s.query(func.min(StatisticJournal.time), func.max(StatisticJournal.time)). \
            filter(StatisticJournal.time > to_time(2 * 24 * 60 * 60.0))
        if statistics_owner:
            q = q.join(StatisticName).filter(StatisticName.owner == statistics_owner)
        start, finish = q.one()   # skip entire first day because tz
        if start and finish:
            return start, finish
        else:
            raise NoStatistics('No statistics are currently defined') 

def get_nvd_github_patch_candidates():
    """Fetches concrete github.com commit links from the Nvd database.

    :return:
    """

    patch_regex = r"github\.com/([^/]+)/([^/]+)/commit/([^/]+)"

    sub_query = (db.session.query(func.min(Reference.id)).filter(
        Reference.link.op("regexp")(patch_regex)).group_by(
            Reference.nvd_json_id))
    github_commit_candidates = (db.session.query(
        Nvd.cve_id, Reference.link, Vulnerability).select_from(
            join(Nvd, Reference).outerjoin(
                Vulnerability, Nvd.cve_id == Vulnerability.cve_id)).filter(
                    Reference.id.in_(sub_query)).with_labels())

    return github_commit_candidates 

def get_versions(db_session, endpoint_id=None, limit=None):
    """
    Returns a list of length 'limit' with the versions that are used in the application
    :param db_session: session for the database
    :param endpoint_id: only get the version that are used in this endpoint
    :param limit: only return the most recent versions
    :return: a list of tuples with the versions (as a string) and dates, from oldest to newest
    """
    query = db_session.query(Request.version_requested, func.min(Request.time_requested))
    if endpoint_id:
        query = query.filter(Request.endpoint_id == endpoint_id)
    query = query.group_by(Request.version_requested)
    if limit:
        query = query.limit(limit)
    return query.all() 

def min_response_time(self):
        result = 0
        if self.runs.count() > 0:
            query = [run.response_time for run in self.runs]
            result = min(query)
        return result 

def get_first_run():
    """return last Run"""
    return DB.session.query(Run).filter(
        Run.identifier == DB.session.query(func.min(Run.identifier))).first() 

def get_min_max_times(self, session):
        crit = self.obj.stock_id == self.stock_id
        max_sel = func.max(self.obj.time)
        min_sel = func.min(self.obj.time)
        max_date = session.query(max_sel).filter(crit).one()[0]
        min_date = session.query(min_sel).filter(crit).one()[0]
        try:
            return timestamp_to_local(min_date), timestamp_to_local(max_date)
        except ValueError:
            return None, None 

def parent_tasks_stats(self):
        task_parent = aliased(Task)
        task_child = aliased(Task)

        task_status = []
        for e in TaskStatus:
            task_status.append(
                func.sum(
                    case(whens=[(task_child.status == e.value, 1)],
                         else_=0).label(e.name)
                )
            )

        times = [func.min(task_child.started), func.max(task_child.finished)]

        parent_statuses = [
            TaskStatus.Queued.value, TaskStatus.InProgress.value
        ]

        query = self.query(task_parent, *times, *task_status). \
            filter(task_parent.status.in_(parent_statuses)). \
            filter(task_child.continued.__eq__(False)). \
            join(task_child, task_parent.id == task_child.parent,
                 isouter=True). \
            group_by(task_parent.id)

        res = []
        for task, started, finished, *(statuses) in query.all():
            res.append(
                [
                    task, started, finished,
                    {e: s
                     for e, s in zip(TaskStatus, statuses)}
                ]
            )

        return res 

def documents_unofficial_licenses(docid):
    doc = db.documents.alias()
    pac = db.packages.alias()
    pfi = db.packages_files.alias()
    fil = db.files.alias()
    fli = db.files_licenses.alias()
    lic = db.licenses.alias()
    return (select ([
        doc.c.document_id,
        lic.c.license_id,
        func.coalesce(lic.c.name, lic.c.short_name).label('name'),
        ('LicenseRef-' + lic.c.short_name).label('id_string'),
        func.min(fli.c.extracted_text).label('extracted_text'),
        lic.c.cross_reference,
        lic.c.comment
        ])
    .select_from(
        doc
        .join(pac, doc.c.package_id == pac.c.package_id)
        .join(pfi, pac.c.package_id == pfi.c.package_id)
        .join(fil, pfi.c.file_id == fil.c.file_id)
        .join(fli, fil.c.file_id == fli.c.file_id)
        .join(lic, fli.c.license_id == lic.c.license_id)
        )
    .where(
        and_(
            lic.c.is_spdx_official == False,
            doc.c.document_id == docid
            )
        )
    .group_by(
        doc.c.document_id,
        lic.c.license_id,
        lic.c.short_name,
        lic.c.cross_reference,
        lic.c.comment
        )
    ) 

def _calculate_value(self, s, statistic_name, summary, order_asc, start_time, finish_time, interval, measures):

        t = _tables()
        sjx = inspect(STATISTIC_JOURNAL_CLASSES[statistic_name.statistic_journal_type]).local_table
        units = statistic_name.units
        activity_group_id = interval.activity_group.id if interval.activity_group else None

        if summary == S.MAX:
            result = func.max(sjx.c.value)
        elif summary == S.MIN:
            result = func.min(sjx.c.value)
        elif summary == S.SUM:
            result = func.sum(sjx.c.value)
        elif summary == S.CNT:
            result = func.count(sjx.c.value)
            units = None
        elif summary == S.AVG:
            result = func.avg(sjx.c.value)
        elif summary == S.MSR:
            self._calculate_measures(s, statistic_name, order_asc, start_time, finish_time, interval, measures)
            return None, None
        else:
            raise Exception('Bad summary: %s' % summary)

        stmt = select([result]). \
            select_from(sjx).select_from(t.sj).select_from(t.src). \
            where(and_(t.sj.c.id == sjx.c.id,
                       t.sj.c.statistic_name_id == statistic_name.id,
                       t.sj.c.time >= start_time,
                       t.sj.c.time < finish_time,
                       t.sj.c.source_id == t.src.c.id,
                       t.src.c.activity_group_id == activity_group_id))

        return next(s.connection().execute(stmt))[0], units 

def remove_duplicates(self, table, match_columns: list):
        with self.session_scope() as session:
            attrs = attrgetter(*match_columns)
            table_columns = attrs(table)
            distinct = session.query(table, func.min(table.id)) \
                .group_by(*table_columns)
            duplicate = session.query(table) \
                .filter(table.id.notin_(distinct.with_entities(table.id))) \
                .delete(synchronize_session='fetch')
            print(f'Remove {duplicate} duplicate {table.__tablename__}s') 

def blank(self, window):
        """Render a blank box for this glyph's position and size."""

        glyph = self.glyph
        x = min(max(self.x, 0), MAX_X)
        width = min(glyph.width, MAX_X - x) or 1
        for y_a in xrange(self.y, self.y + glyph.height):
            y = y_a
            window.addstr(y + VERT_PADDING, x + HORIZ_PADDING, " " * width)

        if self.label:
            self._render_label(window, True) 

def edit_rebuild():
    form = EditRebuildForm()
    if not form.validate_on_submit():
        abort(400)
    rebuild = db.query(CoprRebuild)\
        .filter_by(request_id=form.request_id.data,
                   package_id=form.package_id.data)\
        .first_or_404()
    if not rebuild.request.editable:
        abort(403)
    if form.action.data == 'move-top':
        db.query(CoprRebuild)\
            .filter(CoprRebuild.request_id == rebuild.request_id)\
            .filter(CoprRebuild.state == None)\
            .filter(CoprRebuild.order < rebuild.order)\
            .update({'order': CoprRebuild.order + 1})
        rebuild.order = db.query(func.min(CoprRebuild.order) - 1)\
            .filter(CoprRebuild.request_id == rebuild.request_id)\
            .filter(CoprRebuild.state == None)\
            .scalar()
        # Moving to top should ensure the package will be scheduled
        rebuild.request.schedule_count += 1
        rebuild.request.state = 'in progress'
    elif form.action.data == 'remove':
        db.query(CoprRebuild)\
            .filter(CoprRebuild.request_id == rebuild.request_id)\
            .filter(CoprRebuild.order > rebuild.order)\
            .update({'order': CoprRebuild.order - 1})
        db.delete(rebuild)
    db.commit()
    return redirect(url_for('rebuild_request_detail', request_id=rebuild.request_id)) 

def get_variables(session, experiment, frequency=None):
    """
    Returns a DataFrame of variables for a given experiment and optionally
    a given diagnostic frequency.
    """

    q = (session
         .query(CFVariable.name,
                CFVariable.long_name,
                NCFile.frequency,
                NCFile.ncfile,
                func.count(NCFile.ncfile).label('# ncfiles'),
                func.min(NCFile.time_start).label('time_start'),
                func.max(NCFile.time_end).label('time_end'))
         .join(NCFile.experiment)
         .join(NCFile.ncvars)
         .join(NCVar.variable)
         .filter(NCExperiment.experiment == experiment)
         .order_by(NCFile.frequency,
                   CFVariable.name,
                   NCFile.time_start,
                   NCFile.ncfile)
         .group_by(CFVariable.name, NCFile.frequency))

    if frequency is not None:
        q = q.filter(NCFile.frequency == frequency)

    return pd.DataFrame(q) 

def reindex_all(index=None, author_index=None, recreate=False, batch=50, start=-1, end=-1, synchronous=False):
    """ Recreate the index and add all the records from the db to ES. """

    if recreate:
        recreate_index(index=index)
        recreate_index(index=author_index)

    qry = db.session.query(func.max(RecordIdentifier.recid).label("max_recid"),
                           func.min(RecordIdentifier.recid).label("min_recid"),
                           )
    res = qry.one()
    min_recid = res.min_recid
    max_recid = res.max_recid

    if max_recid and min_recid:

        if start != -1:
            min_recid = max(start, min_recid)
        if end != -1:
            max_recid = min(end, max_recid)
        print('min_recid = {}'.format(min_recid))
        print('max_recid = {}'.format(max_recid))

        count = min_recid
        while count <= max_recid:
            rec_ids = list(range(count, min(count + batch, max_recid + 1)))
            if synchronous:
                reindex_batch(rec_ids, index)
            else:
                print('Sending batch of IDs {0} to {1} to celery'.format(rec_ids[0], rec_ids[-1]))
                reindex_batch.delay(rec_ids, index)
            count += batch 

def downgrade():
    """Remove share_export_locations table.

    This method can lead to data loss because only first export_location
    is saved in shares table.
    """

    op.add_column('shares',
                  sa.Column('export_location', sa.String(255)))

    export_locations_table = table(
        'share_export_locations',
        sa.Column('share_id', sa.String(length=36)),
        sa.Column('path', sa.String(length=255)),
        sa.Column('updated_at', sa.DateTime),
        sa.Column('deleted', sa.Integer))

    connection = op.get_bind()
    session = sa.orm.Session(bind=connection.connect())
    export_locations = session.query(
        func.min(export_locations_table.c.updated_at),
        export_locations_table.c.share_id,
        export_locations_table.c.path).filter(
            export_locations_table.c.deleted == 0).group_by(
                export_locations_table.c.share_id,
                export_locations_table.c.path).all()

    shares = sa.Table('shares', sa.MetaData(),
                      autoload=True, autoload_with=connection)

    for location in export_locations:
        # pylint: disable=no-value-for-parameter
        update = (shares.update().where(shares.c.id == location.share_id).
                  values(export_location=location.path))
        connection.execute(update)

    op.drop_table('share_export_locations')
    session.close_all() 

def status_code(self) -> int:
        """
        For all the transactions associated with this task, get the lowest status code
        """
        return min(set(t.status_code for t in self.transactions) or [3]) 

def status(cls):
        return (
            case(
                STATUS_INT_TO_STRING,
                value=(
                    select([func.min(BlockchainTransaction.status_code)])
                        .where(BlockchainTransaction.blockchain_task_id == cls.id)
                        .label('lowest_status')
                ),
                else_='UNSTARTED'
            )
        ) 

def get_deleted_packages(cls):
        '''
        @return: Returns list of deleted pkgs and date when they were deleted, in
                 format: [(id, date_ordinal), ...]
        '''
        def deleted_packages():
            # Can't filter by time in select because 'min' function has to
            # be 'for all time' else you get first revision in the time period.
            package_revision = table('package_revision')
            revision = table('revision')
            s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision)]).\
                where(package_revision.c.state==model.State.DELETED).\
                group_by(package_revision.c.id).\
                order_by(func.min(revision.c.timestamp))
            res = model.Session.execute(s).fetchall() # [(id, datetime), ...]
            res_pickleable = []
            for pkg_id, deleted_datetime in res:
                res_pickleable.append((pkg_id, deleted_datetime.toordinal()))
            return res_pickleable
        if cache_enabled:
            week_commences = cls.get_date_week_started(datetime.date.today())
            key = 'all_deleted_packages_%s' + week_commences.strftime(DATE_FORMAT)
            deleted_packages = our_cache.get_value(key=key,
                                                   createfunc=deleted_packages)
        else:
            deleted_packages = deleted_packages()
        return deleted_packages 

def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        x_column = getattr(self.model, value['x_column'])
        y_column = getattr(self.model, value['y_column'])

        if value['y_func'] == 'count':
            y_func = func.count(y_column)
        elif value['y_func'] == 'sum':
            y_func = func.sum(y_column)
        elif value['y_func'] == 'min':
            y_func = func.min(y_column)
        elif value['y_func'] == 'max':
            y_func = func.max(y_column)
        elif value['y_func'] == 'avg':
            y_func = func.avg(y_column)
        else:
            return qs.filter(sql.false())

        if value['x_lookup'] and value['x_lookup'] in ['date']:
            x_lookup = getattr(func, value['x_lookup'])
            x_func = x_lookup(x_column)
        else:
            x_func = x_column

        qs = qs.session.query(x_func.label('group'), y_func.label('y_func')).group_by('group').order_by('group').all()

        return qs 

def get_earliest(cls):
        lowest = database.db.getSession().query(func.min(Blocks.height)).scalar()
        return database.db.getSession().query(Blocks).filter(Blocks.height == lowest).first()

    # Get a single record by nonce 

def get_yearly_series():
    """Returns a list of yearly generated energy for past years
    :return: list of yearly generated energy for past years
    """
    return (PVData.query
            .with_entities(func.strftime('%Y', PVData.created_at).label('year'),
                           (func.max(PVData.total_energy) - func.min(PVData.total_energy)).label('yearly_output'))
            .group_by(func.strftime('%Y', PVData.created_at))
            .all()) 

def get_yearly_data(year):
    """Returns the yielded energy for the current year
    :param year: year of the data
    :return: returns an array of monthly energy for a given year
    """
    return (PVData.query
            .with_entities((func.max(PVData.total_energy) - func.min(PVData.total_energy)).label('total_energy'))
            .filter(func.strftime('%Y', PVData.created_at) == str(year))
            .group_by(func.strftime('%Y-%m', PVData.created_at))
            .all()) 

def get_yearly_average_data():
    """Returns the monthly averages for the previous year
    :return: returns an array of monthly averages for previous years
    """
    current_year = str(datetime.now().year)
    query = """SELECT 
                   avg(monthly_yield),
                   min(monthly_yield),
                   max(monthly_yield) 
               FROM 
                   (
                       SELECT 
                           strftime('%m', created_at) AS month, 
                           max(total_energy) - min(total_energy) AS monthly_yield 
                       FROM 
                           pvdata 
                       WHERE 
                           strftime('%Y', created_at) < ?
                       GROUP BY 
                           strftime('%Y-%m', created_at)
                   ) subq 
               WHERE 
                   monthly_yield > 0 
               GROUP BY 
                   month;"""
    result = db.engine.execute(query, current_year)
    return result 

def get_current_year_prediction():
    """Computes the prediction of kWh for the remaining days of the current year

    :return: the number of kWh for the remaining year
    """
    query = """SELECT 
                    SUM(energy) AS prediction 
                FROM (
                    SELECT 
                        max(total_energy) - min(total_energy) AS energy 
                    FROM 
                        pvdata 
                    WHERE 
                        strftime('%Y', created_at) = strftime('%Y', 'now') 
                    UNION 
                    SELECT 
                        AVG(max_rest_year - min_rest_year) AS energy 
                    FROM 
                        (
                            SELECT 
                                min(total_energy) min_rest_year, 
                                max(total_energy) max_rest_year 
                            FROM 
                                pvdata 
                            WHERE 
                                strftime('%j', created_at) > strftime('%j', 'now') 
                                AND strftime('%Y', created_at) < strftime('%Y', 'now') 
                            GROUP BY 
                                strftime('%Y', created_at)
                        ) q
                );"""
    result = db.engine.execute(query)
    return result