Python sql.desc函数代码示例

 def execute(self, request, user):
     
     planet, galaxy = (user.planet, user.planet.galaxy,) if user.planet else (Planet(), Galaxy(),)
     
     planets = session.query(Planet).filter(Planet.active == True)
     galaxies = session.query(Galaxy).filter(Galaxy.active == True)
     alliances = session.query(Alliance).filter(Alliance.active == True)
     
     dup = lambda l,o,c=True: l+[o] if o in session and c and o not in l else l
     
     return render("index.tpl", request,
                  topplanets = dup(planets.order_by(asc(Planet.score_rank))[:20], 
                                   planet),
              roidingplanets = dup(planets.filter(Planet.size_growth > 0).order_by(desc(Planet.size_growth))[:5],
                                   planet, planet.size_growth > 0),
               roidedplanets = dup(planets.filter(Planet.size_growth < 0).order_by(asc(Planet.size_growth))[:5],
                                   planet, planet.size_growth < 0),
                   xpplanets = dup(planets.filter(Planet.xp_growth > 0).order_by(desc(Planet.xp_growth))[:5],
                                   planet, planet.xp_growth > 0),
               bashedplanets = dup(planets.filter(Planet.value_growth < 0).order_by(asc(Planet.value_growth))[:5],
                                   planet, planet.value_growth < 0),
             
                 topgalaxies = dup(galaxies.order_by(asc(Galaxy.score_rank))[:10],
                                   galaxy),
             roidinggalaxies = dup(galaxies.filter(Galaxy.size_growth > 0).order_by(desc(Galaxy.size_growth))[:5],
                                   galaxy, galaxy.size_growth > 0),
              roidedgalaxies = dup(galaxies.filter(Galaxy.size_growth < 0).order_by(asc(Galaxy.size_growth))[:5],
                                   galaxy, galaxy.size_growth < 0),
                  xpgalaxies = dup(galaxies.filter(Galaxy.xp_growth > 0).order_by(desc(Galaxy.xp_growth))[:5],
                                   galaxy, galaxy.xp_growth > 0),
              bashedgalaxies = dup(galaxies.filter(Galaxy.value_growth < 0).order_by(asc(Galaxy.value_growth))[:5],
                                   galaxy, galaxy.value_growth < 0),
             
                topalliances =     alliances.order_by(asc(Alliance.score_rank))[:8],
                         )

def organizations_and_counters():
    '''Query organizations with their counters'''
    query = DB.query(Group,
        func.count(distinct(Package.id)).label('nb_datasets'),
        func.count(distinct(Member.id)).label('nb_members')
    )
    query = query.outerjoin(CertifiedPublicService)
    query = query.outerjoin(Package, and_(
        Group.id == Package.owner_org,
        ~Package.private,
        Package.state == 'active',
    ))
    query = query.outerjoin(Member, and_(
        Member.group_id == Group.id,
        Member.state == 'active',
        Member.table_name == 'user'
    ))
    query = query.filter(Group.state == 'active')
    query = query.filter(Group.approval_status == 'approved')
    query = query.filter(Group.is_organization == True)
    query = query.group_by(Group.id, CertifiedPublicService.organization_id)
    query = query.order_by(
        CertifiedPublicService.organization_id == null(),
        desc('nb_datasets'),
        desc('nb_members'),
        Group.title
    )
    query = query.options(orm.joinedload(Group.certified_public_service))
    return query

def user(request, info, session, userid):
    try:
        u = session.query(User).options(joinedload("permissions")).options(joinedload("tokens")).get(int(userid))
    except NoResultFound:
        raise Http404
    sales = (
        session.query(Transline)
        .filter(Transline.user == u)
        .options(joinedload("transaction"))
        .options(joinedload_all("stockref.stockitem.stocktype.unit"))
        .order_by(desc(Transline.time))[:50]
    )
    payments = (
        session.query(Payment)
        .filter(Payment.user == u)
        .options(joinedload("transaction"))
        .options(joinedload("paytype"))
        .order_by(desc(Payment.time))[:50]
    )
    annotations = (
        session.query(StockAnnotation)
        .options(joinedload_all("stockitem.stocktype"))
        .options(joinedload("type"))
        .filter(StockAnnotation.user == u)
        .order_by(desc(StockAnnotation.time))[:50]
    )
    return ("user.html", {"tuser": u, "sales": sales, "payments": payments, "annotations": annotations})

 def execute(self, request, user, sort=None):
     
     levels = [] + User.levels
     if "galmate" in Config.options("Access"):
         levels.append(("Galaxy", Config.getint("Access","galmate"),))
     else:
         levels.append(("Galaxy", 0,))
     
     if sort is not None:
         levels = [("All", 0,),]
     
     order =  {"name"  : (asc(Channel.name),),
               "userlevel" : (desc(Channel.userlevel),),
               "maxlevel" : (desc(Channel.maxlevel),),
               }
     if sort not in order.keys():
         sort = "name"
     order = order.get(sort)
     
     channels = []
     for level in levels:
         Q = session.query(Channel.name, Channel.userlevel, Channel.maxlevel)
         Q = Q.filter(Channel.userlevel >= level[1])
         Q = Q.filter(Channel.userlevel < levels[levels.index(level)-1][1]) if levels.index(level) > 0 else Q
         for o in order:
             Q = Q.order_by(o)
         
         channels.append((level[0], Q.all(),))
     
     return render("channels.tpl", request, accesslist=channels)

def index():
    campaigns = Campaign.query.order_by(desc(Campaign.status_code), desc(Campaign.id)).all()
    calls = (db.session.query(Campaign.id, func.count(Call.id))
            .filter(Call.status == 'completed')
            .join(Call).group_by(Campaign.id))
    return render_template('campaign/list.html',
        campaigns=campaigns, calls=dict(calls.all()))

    def newer_submissions(self):
        class Newer(object):
            '''You may use me like a list'''
            user = []
            team = []

            def __iter__(self):
                for i in self.user + self.team:
                    yield i

            def __len__(self):
                return len(self.user) + len(self.team)

            def __getitem__(self, i):
                return sorted(self.user + self.team, key=lambda s: s.modified, reverse=True)[0]

        newer = Newer()

        newer.user = (Submission.by_assignment_and_user(self.assignment, self.user)
            .filter(Submission.modified > self.modified).order_by(desc(Submission.modified)).all())
        newer.team = []
        if hasattr(self.user, 'teams'):
            for team in self.teams:
                for member in team.members:
                    if member != self.user:
                        newer.team.extend(Submission.by_assignment_and_user(self.assignment, member)
                            .filter(Submission.modified > self.modified).order_by(desc(Submission.modified)).all())
        return newer

    def execute(self, message, alliance=None, race=None, sortby="score"):
        planet = aliased(Planet)
        planet_intel = aliased(Intel)

        Q = session.query(planet.x, planet.y, planet.z, planet.score, planet.value, planet.size, planet.xp, planet.race, planet_intel.nick)
        Q = Q.outerjoin(planet.intel, planet_intel)
        if alliance:
            Q = Q.filter(planet_intel.alliance == alliance)
        if race:
            Q = Q.filter(planet.race == race)
        Q = Q.group_by(planet.x, planet.y, planet.z, planet.score, planet.value, planet.size, planet.xp, planet.race, planet_intel.nick)
        if sortby == "xp":
            Q = Q.order_by(desc(planet.xp))
        elif sortby == "size":
            Q = Q.order_by(desc(planet.size))
        elif sortby == "value":
            Q = Q.order_by(desc(planet.value))
        else:
            Q = Q.order_by(desc(planet.score))
        result = Q.all()
        
        reply = "Top%s planets" % (" "+race if race is not None else "")
        if alliance:
            reply+=" in %s"%(alliance.name,)
        reply+=" by %s:\n"%(sortby)
        prev = []
        i=0
        for x, y, z, score, value, size, xp, race, nick in result[:10]:
            i+=1
            line = "#%s - %s:%s:%s (%s) - Score: %s Value: %s Size: %s XP: %s"%(i,x,y,z,race,score,value,size,xp)
            if nick:
                line = "%s Nick: %s"%(line,nick)
            prev.append(line)
        print(prev)
        message.reply(reply+"\n".join(prev))

    def execute(self, message, alliance=None, race=None, sortby="score"):
        planet = aliased(Planet)
        planet_intel = aliased(Intel)
        

        Q = session.query(planet.x, planet.y, planet.z, planet_intel.nick)
        Q = Q.outerjoin((planet.intel, planet_intel))
        if alliance:
            Q = Q.filter(planet_intel.alliance == alliance)
        if race:
            Q = Q.filter(planet.race == race)
        if sortby == "xp":
            Q = Q.order_by(desc(planet.xp))
        elif sortby == "size":
            Q = Q.order_by(desc(planet.size))
        elif sortby == "value":
            Q = Q.order_by(desc(planet.value))
        else:
            Q = Q.order_by(desc(planet.score))
        result = Q.all()
        
        reply = "Top%s planets" % (" "+race if race is not None else "")
        if alliance:
            reply+=" in %s"%(alliance.name,)
        reply+=" by %s:\n"%(sortby)
        prev = []
        i=0
        for x, y, z, nick in result[:10]:
            i+=1
            line = "#%2s %12s%s"%(i, "["+nick+"] " if nick else "", Planet.load(x,y,z))
            prev.append(line)
        message.reply(reply+"\n".join(prev))

 def execute(self, request, user, x, y, z, fleets=False):
     week = Updates.week_tick()
     
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     Q = session.query(FleetScan, Planet, Alliance)
     Q = Q.join(FleetScan.target)
     Q = Q.outerjoin(Planet.intel).outerjoin(Intel.alliance)
     Q = Q.filter(FleetScan.owner == planet)
     Q = Q.order_by(desc(FleetScan.landing_tick))
     if not fleets:
         Q = Q.filter(FleetScan.landing_tick >= week)
     outgoing = Q.all()
     
     Q = session.query(FleetScan, Planet, Alliance)
     Q = Q.join(FleetScan.owner)
     Q = Q.outerjoin(Planet.intel).outerjoin(Intel.alliance)
     Q = Q.filter(FleetScan.target == planet)
     Q = Q.order_by(desc(FleetScan.landing_tick))
     if not fleets:
         Q = Q.filter(FleetScan.landing_tick >= week)
     incoming = Q.all()
     
     scan = planet.scan("A") or planet.scan("U")
     
     return render("iplanet.tpl", request, planet=planet, scan=scan, outgoing=outgoing, incoming=incoming)

 def execute(self, request, user, sort=None):
     
     levels = [] + User.levels
     
     if sort is not None:
         levels = [("All member", levels[-1][1],),]
     
     order =  {"name"  : (asc(User.name),),
               "sponsor" : (asc(User.sponsor),),
               "access" : (desc(User.access),desc(User.carebears),asc(User.name),),
               "carebears" : (desc(User.carebears),),
               "planet" : (asc(Planet.x),asc(Planet.y),asc(Planet.z),),
               "defage" : (asc(User.fleetupdated),),
               }
     if sort not in order.keys():
         sort = "name"
     order = order.get(sort)
     
     members = []
     for level in levels:
         Q = session.query(User.name, User.alias, User.sponsor, User.access, User.carebears, Planet, User.fleetupdated,
                           User.phone, User.pubphone, User._smsmode,  or_(User.id == user.id, User.id.in_(session.query(PhoneFriend.user_id).filter_by(friend=user))))
         Q = Q.outerjoin(User.planet)
         Q = Q.filter(User.active == True)
         Q = Q.filter(User.access >= level[1])
         Q = Q.filter(User.access < levels[levels.index(level)-1][1]) if levels.index(level) > 0 else Q
         for o in order:
             Q = Q.order_by(o)
         
         members.append((level[0], Q.all(),))
     
     return render("members.tpl", request, accesslist=members)

def organizations_and_counters():
    '''Query organizations with their counters'''
    memberships = aliased(model.Member)

    query = DB.query(model.Group,
        func.count(distinct(model.Package.id)).label('nb_datasets'),
        func.count(distinct(memberships.id)).label('nb_members')
    )
    query = query.outerjoin(CertifiedPublicService)
    query = query.outerjoin(model.Package, and_(
        model.Group.id == model.Package.owner_org,
        ~model.Package.private,
        model.Package.state == 'active',
    ))
    query = query.outerjoin(memberships, and_(
        memberships.group_id == model.Group.id,
        memberships.state == 'active',
        memberships.table_name == 'user'
    ))
    query = query.filter(model.Group.state == 'active')
    query = query.filter(model.Group.approval_status == 'approved')
    query = query.filter(model.Group.is_organization == True)
    query = query.group_by(model.Group.id, CertifiedPublicService.organization_id)
    query = query.order_by(
        CertifiedPublicService.organization_id == null(),
        desc('nb_datasets'),
        desc('nb_members'),
        model.Group.title
    )
    return query

    def load_plasmids(self, tsession = None, engine = None):

        engine = engine or self.engine
        tsession = tsession or self.tsession

        for p in tsession.query(Plasmid).order_by(desc(Plasmid.date), desc(Plasmid.creator_entry_number)): # todo: parameterize sorting
            k = (p.creator, p.creator_entry_number)
            self.plasmid_order.append(k)
            self.plasmids[k] = p

 def execute(self, request, user, x, y, z, h=False, hs=False, ticks=None):
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not hs:
         sizediffvalue = PlanetHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = PlanetHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = PlanetHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(PlanetHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not (h or hs):
         landings = session.query(PlanetLandings.hour, count()).filter(PlanetLandings.planet==planet).group_by(PlanetLandings.hour).all()
         landed = session.query(PlanetLandedOn.hour, count()).filter(PlanetLandedOn.planet==planet).group_by(PlanetLandedOn.hour).all()
         vdrops = session.query(PlanetValueDrops.hour, count()).filter(PlanetValueDrops.planet==planet).group_by(PlanetValueDrops.hour).all()
         idles = session.query(PlanetIdles.hour, count()).filter(PlanetIdles.planet==planet).group_by(PlanetIdles.hour).all()
         hourstats = {
                         'landings' : dict(landings), 'landingsT' : sum([c for hour,c in landings]),
                         'landed'   : dict(landed),   'landedT'   : sum([c for hour,c in landed]),
                         'vdrops'   : dict(vdrops),   'vdropsT'   : sum([c for hour,c in vdrops]),
                         'idles'    : dict(idles),    'idlesT'    : sum([c for hour,c in idles]),
                         }
     else:
         hourstats = None
     
     if not h:
         Q = session.query(PlanetHistory)
         Q = Q.filter(or_(PlanetHistory.hour == 23, PlanetHistory.tick == Updates.current_tick()))
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["planet.tpl",["hplanet.tpl","hsplanet.tpl"][hs]][h or hs],
                     request,
                     planet = planet,
                     history = history,
                     hour = datetime.utcnow().hour, hourstats = hourstats,
                     hsummary = hsummary,
                     ticks = ticks,
                   )

 def list_inactive(self):
     """Returns a list of bans that are currently inactive."""
     return list(
         self.dbsession.query(Ban)
         .filter(
             or_(Ban.active == False, Ban.active_until <= func.now())  # noqa: E712
         )
         .order_by(desc(Ban.active_until), desc(Ban.created_at))
         .all()
     )

 def execute(self, request, user, x, y, h=False, hs=False, ticks=None):
     galaxy = Galaxy.load(x,y)
     if galaxy is None:
         return HttpResponseRedirect(reverse("galaxy_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not (h or hs):
         Q = session.query(Planet, Intel.nick, Alliance.name)
         Q = Q.outerjoin(Planet.intel)
         Q = Q.outerjoin(Intel.alliance)
         Q = Q.filter(Planet.active == True)
         Q = Q.filter(Planet.galaxy == galaxy)
         Q = Q.order_by(asc(Planet.z))
         planets = Q.all()
         exiles = galaxy.exiles[:10]
     else:
         planets, exiles = None, None
     
     if not hs:
         sizediffvalue = GalaxyHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = GalaxyHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = GalaxyHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(GalaxyHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(GalaxyHistory.current == galaxy)
         Q = Q.order_by(desc(GalaxyHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not h:
         Q = session.query(GalaxyHistory)
         Q = Q.filter(or_(GalaxyHistory.hour == 23, GalaxyHistory.tick == Updates.current_tick()))
         Q = Q.filter(GalaxyHistory.current == galaxy)
         Q = Q.order_by(desc(GalaxyHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["galaxy.tpl",["hgalaxy.tpl","hsgalaxy.tpl"][hs]][h or hs],
                     request,
                     galaxy = galaxy,
                     planets = planets,
                     exiles = exiles,
                     history = history,
                     hsummary = hsummary,
                     ticks = ticks,
                   )