Python sqlalchemy.func.max() Examples

def ini_names(self, name_table, field):
        if field != GAME and field != STAT:
            s = select([name_table])
            name_dict = dict([(n.name.title().translate(removeDic), n.id)
                              for n in self.conn.execute(s)])

            if field == EVENT:
                self.event_dict = name_dict
            elif field == SITE:
                self.site_dict = name_dict
            elif field == PLAYER:
                self.player_dict = name_dict
            elif field == ANNOTATOR:
                self.annotator_dict = name_dict
            elif field == SOURCE:
                self.source_dict = name_dict

        s = select([func.max(name_table.c.id).label('maxid')])
        maxid = self.conn.execute(s).scalar()
        if maxid is None:
            next_id = 1
        else:
            next_id = maxid + 1

        return next_id 

def get_next_id(session, projectid):
    """ Returns the next identifier of a project ticket or pull-request
    based on the identifier already in the database.
    """
    query1 = session.query(func.max(model.Issue.id)).filter(
        model.Issue.project_id == projectid
    )

    query2 = session.query(func.max(model.PullRequest.id)).filter(
        model.PullRequest.project_id == projectid
    )

    ids = [el[0] for el in query1.union(query2).all() if el[0] is not None]
    nid = 0
    if ids:
        nid = max(ids)

    return nid + 1 

def list_recent_networks(self) -> List[Network]:
        """List the most recently created version of each network (by name)."""
        most_recent_times = self.session.query(
            Network.name.label('network_name'),
            func.max(Network.created).label('max_created'),
        )

        most_recent_times = most_recent_times.group_by(Network.name).subquery('most_recent_times')

        and_condition = and_(
            most_recent_times.c.network_name == Network.name,
            most_recent_times.c.max_created == Network.created,
        )

        most_recent_networks = self.session.query(Network).join(most_recent_times, and_condition)

        return most_recent_networks.all() 

def __init__(self, credentials: TwitterApiCredentials, screen_name: str):
        self._credentials = credentials
        self.screen_name = screen_name
        self.session = Session()

        row = self.session.query(func.max(Tweet.status_id)).first()
        if row is not None:
            since_id = row[0] if row[0] is not None else 0
        else:
            since_id = 0

        self._latest_tweet_processed_id = since_id

        self.scraper_status = self.session.query(ScraperStatus).filter(
            ScraperStatus.screen_name == self.screen_name).first()
        if self.scraper_status is None:
            self.scraper_status = ScraperStatus(screen_name=screen_name, since_id=since_id)
            self.session.add(self.scraper_status)
            self.session.commit() 

def test_00_verify_db(self, testdb):
        b = testdb.query(Project).get(1)
        assert b is not None
        assert b.name == 'P1'
        assert b.notes == 'ProjectOne'
        assert b.is_active is True
        b = testdb.query(Project).get(2)
        assert b is not None
        assert b.name == 'P2'
        assert b.notes == 'ProjectTwo'
        assert b.is_active is True
        b = testdb.query(Project).get(3)
        assert b is not None
        assert b.name == 'P3Inactive'
        assert b.notes == 'ProjectThreeInactive'
        assert b.is_active is False
        assert testdb.query(Project).with_entities(
            func.max(Project.id)
        ).scalar() == 3
        assert testdb.query(BoMItem).with_entities(
            func.max(BoMItem.id)
        ).scalar() == 5 

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 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 test_sort_aggregation_translation_failure(self):
        # This works around a nuance with our choice to hackishly fuse SortBy
        # after Aggregate to produce a single select statement rather than an
        # inline view.
        t = self.alltypes

        agg = t.group_by('string_col').aggregate(
            t.double_col.max().name('foo')
        )
        expr = agg.sort_by(ibis.desc('foo'))

        sat = self.sa_alltypes.alias('t1')
        base = (
            sa.select(
                [sat.c.string_col, F.max(sat.c.double_col).label('foo')]
            ).group_by(sat.c.string_col)
        ).alias('t0')

        ex = (
            sa.select([base.c.string_col, base.c.foo])
            .select_from(base)
            .order_by(sa.desc('foo'))
        )

        self._compare_sqla(expr, ex) 

def sync_time_gaps(self, gaps=None):
        """
        Uses a list([start, end], [start, end])
        to request/load OHLC data from the API to the database.
        :param gaps:
        :return:
        """
        if gaps is None:
            gaps = self.get_time_gaps()

        for start, end in gaps:
            _, max = self.get_time_bump(start, end, bump_start=False)
            if max < end:
                df = DataFrame()
                while max < end:
                    df = df.append(self.request_ohlc(start, max))
                    start, max = self.get_time_bump(start, end)
            else:
                df = self.request_ohlc(start, end)

            if df.empty:
                logger.info("Failed 2nd time on gap {}: "
                            "{} {}".format(self.pair, start, end))
                continue
            self.load_df(df, thread=False,) 

def get_last_run_per_resource():
    """return last N Runs with results for each Resource"""

    # We need an Innerjoin on same table
    # example: https://stackoverflow.com/questions/2411559/
    #    how-do-i-query-sql-for-a-latest-record-date-for-each-user

    sql = """
    select t.resource_identifier, t.identifier, t.success
    from Run t
    inner join (
        select resource_identifier, max(identifier) as MaxId
        from Run
        group by resource_identifier
    ) tm on t.resource_identifier = tm.resource_identifier
    and t.identifier = tm.MaxId;
    """

    # Use raw query on SQLAlchemy, as the programmatic buildup
    # would be overly complex, if even possible.
    last_runs = DB.session.execute(sql)
    return last_runs 

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 _additional_events(self, tracker: DialogueStateTracker) -> Iterator:
        """Return events from the tracker which aren't currently stored."""

        from sqlalchemy import func
        query = self.session.query(func.max(self.SQLEvent.timestamp))
        max_timestamp = query.filter_by(sender_id=tracker.sender_id).scalar()

        if max_timestamp is None:
            max_timestamp = 0

        latest_events = []

        for event in reversed(tracker.events):
            if event.timestamp > max_timestamp:
                latest_events.append(event)
            else:
                break

        return reversed(latest_events) 

def latest_tag_id(self):
        session = self.DBSession()
        res = session.query(func.max(Tag.id).label('latest_tag_id')).scalar()
        session.close()
        return res 

def get(self, filter: dict = None, options: PaginatorOptions = None):
        filter = filter or {}

        query = self.query(Project,
                           func.count(Dag.id),
                           func.max(Task.last_activity)). \
            join(Dag, Dag.project == Project.id, isouter=True). \
            join(Task, isouter=True). \
            group_by(Project.id)

        if filter.get('name'):
            query = query.filter(Project.name.like(f'%{filter["name"]}%'))

        total = query.count()
        paginator = self.paginator(query, options)
        res = []
        for p, dag_count, last_activity in paginator.all():
            last_activity = self.serializer.serialize_datetime(last_activity) \
                if last_activity else None

            file_size, img_size = self.query(func.sum(Dag.file_size),
                                             func.sum(Dag.img_size)).filter(
                Dag.project == p.id).one()

            res.append(
                {
                    'dag_count': dag_count,
                    'last_activity': last_activity,
                    'img_size': int(img_size or 0),
                    'file_size': int(file_size or 0),
                    'id': p.id,
                    'name': p.name,
                    'sync_folders': p.sync_folders,
                    'ignore_folders': p.ignore_folders,
                    'class_names': p.class_names
                })
        return {'total': total, 'data': res} 

def setCounter(self, new_counter, uplink):
        if uplink:
            self.up_link_counter = new_counter
            self.max_up_counter = max(self.max_up_counter,new_counter)
        else:
            self.downLinkCounter = new_counter
            self.max_down_counter = max(self.max_down_counter, new_counter) 

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

            print(f'Phase count:')
            phase_group_count = session.query(Pick.phase, func.count(Pick.phase)) \
                .group_by(Pick.phase).all()
            ps_picks = 0
            for phase, count in phase_group_count:
                if phase in ['P', 'S']:
                    ps_picks += count
                print(f'{count} "{phase}" picks')
            print(f'Total {ps_picks} P + S picks\n')

            station_count = session.query(Pick.station.distinct()).count()
            print(f'Picks cover {station_count} stations:')

            station = session.query(Pick.station.distinct()).order_by(Pick.station).all()
            print([stat[0] for stat in station], '\n')

            no_pick_station = session.query(Inventory.station) \
                .order_by(Inventory.station) \
                .filter(Inventory.station.notin_(session.query(Pick.station))).all()
            if no_pick_station:
                print(f'{len(no_pick_station)} stations without picks:')
                print([stat[0] for stat in no_pick_station], '\n')

            no_geom_station = session.query(Pick.station.distinct()) \
                .order_by(Pick.station) \
                .filter(Pick.station.notin_(session.query(Inventory.station))).all()
            if no_geom_station:
                print(f'{len(no_geom_station)} stations without geometry:')
                print([stat[0] for stat in no_geom_station], '\n') 

def all_last_activity(self):
        query = self.query(Project,
                           func.max(Task.last_activity)). \
            join(Dag, Dag.project == Project.id, isouter=True). \
            join(Task, isouter=True). \
            group_by(Project.id)

        res = query.all()
        for p, last_activity in res:
            p.last_activity = last_activity
        return [r[0] for r in res] 

def geom_summery(self):
        with self.session_scope() as session:
            station = session.query(Inventory.station).order_by(Inventory.station)
            station_count = session.query(Inventory.station).count()
            print(f'Station name:')
            print([stat[0] for stat in station], '\n')
            print(f'Total {station_count} stations\n')

            boundary = session.query(func.min(Inventory.longitude), func.max(Inventory.longitude),
                                     func.min(Inventory.latitude), func.max(Inventory.latitude)).all()
            print(f'Station 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') 

def get_apps(request: Request, app_type: str, page: int):
    """
    获取app
    - uri[app类型(all/iOS/android)-app_type: str, 页码(从1起)-page: int], format[时间s-t: int]
    :param request:
    :return:
    """
    time = Date.time2datetime(request.args.get('t'))
    if not time:
        raise BadRequest('')

    if page <= 0:
        log.debug('page need greater zero')
        raise BadRequest('')

    kw = request.args.get('kw')

    session = Session()
    query = session.query(AppModel, AppVersionModel.version_code, AppVersionModel.version_name,
                          func.max(AppVersionModel.create_at).label('_update_at')) \
        .join(AppVersionModel, AppModel.id == AppVersionModel.app_id) \
        .filter(AppModel.create_at <= time)
    if app_type != 'all':  # 安装包类型过滤
        query = query.filter(AppModel.type == app_type)

    if kw:
        query = query.filter(AppModel.name.like('%{}%'.format(kw)))

    result = query.order_by(desc(AppModel.create_at)) \
        .group_by(AppModel.short_chain_uri_) \
        .offset((page - 1) * Config.apps_limit) \
        .limit(Config.apps_limit) \
        .all()

    datas = []
    for app, version_code, version_name, _ in result:
        app.version_code = version_code
        app.version_name = version_name
        datas.append(app)

    return JsonResult.ok(datas).response_json() 

def get_last_n_days_import(n):
    return (Electricity.query.
            with_entities(func.strftime('%Y-%m-%dT00:00:00', Electricity.created_at).label('created_at'),
                          (func.max(Electricity.meter_180) - func.min(Electricity.meter_180))
                          .label('electricity_import'))
            .filter(Electricity.created_at > (datetime.now() - timedelta(days=n)))
            .group_by(func.strftime('%Y-%m-%d', Electricity.created_at))
            .all()) 

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 get_todays_electricity():
    return (Electricity.query
            .with_entities((func.max(Electricity.meter_280) - func.min(Electricity.meter_280)).label('todays_export'),
                           (func.max(Electricity.meter_180) - func.min(Electricity.meter_180)).label('todays_import'))
            .filter(func.strftime('%Y-%m-%d', Electricity.created_at) == datetime.now().strftime('%Y-%m-%d'))
            .group_by(func.strftime('%Y-%m-%d', Electricity.created_at))
            .first()) 

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 

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_max_daily_energy_last_seven_days():
    """Returns the maximum daily yield within the last 7 days
    :return: returns the maximum energy yielded in the last 7 days
    """
    return (PVData.query
            .with_entities(func.max(PVData.daily_energy).label('max_daily_energy'))
            .filter(PVData.created_at >= (datetime.now() - timedelta(days=7)))
            .first().max_daily_energy) 

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_last_n_days(n):
    """Returns a list of daily yields
    :param n: number of last days
    :return: list of daily yields
    """
    return (PVData.query.
            with_entities(func.strftime('%Y-%m-%dT00:00:00', PVData.created_at).label('created_at'),
                          func.max(PVData.daily_energy).label('daily_energy'))
            .filter(PVData.created_at > (datetime.now() - timedelta(days=n)))
            .group_by(func.strftime('%Y-%m-%d', PVData.created_at))
            .all()) 

def get_7_day_max_energy_series(current_date):
    """
    :param current_date: pivot date of the energy series
    :return: theoretical maximum energy series ± 3 days around current date
    """
    return (PVData.query
            .with_entities(func.strftime('%H:%M:00', PVData.created_at).label('pvdata_created_at'),
                           func.max(PVData.current_power).label('pv_max'))
            .filter(PVData.created_at >= (current_date - timedelta(days=3)).strftime('%Y-%m-%d'))
            .filter(PVData.created_at <= (current_date + timedelta(days=3)).strftime('%Y-%m-%d'))
            .filter(PVData.current_power > 0)
            .group_by(func.strftime('%H:%M:00', PVData.created_at))
            .all()) 

def latest_post_id(self):
        session = self.DBSession()
        res = session.query(func.max(Post.id).label('latest_post_id')).scalar()
        session.close()
        return res 

def get_todays_max_power():
    """
    :return: the maximum enegy yieled today
    """
    todays_max_power = (PVData.query
                        .with_entities(func.max(PVData.current_power).label('todays_max_power'))
                        .filter(PVData.created_at >= datetime.now())
                        .first()
                        .todays_max_power)

    if todays_max_power is None:
        todays_max_power = 0

    return todays_max_power