Python sql.asc函数代码示例

 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 execute(self, request, user, page="1", sort="score"):
     page = int(page)
     offset = (page - 1)*50
     order =  {"score" : (asc(Alliance.score_rank),),
               "size"  : (asc(Alliance.size_rank),),
               "avg_score" : (asc(Alliance.score_avg_rank),),
               "avg_size"  : (asc(Alliance.size_avg_rank),),
               "members"   : (asc(Alliance.members_rank),),
               "score_growth" : (desc(Alliance.score_growth),),
               "size_growth"  : (desc(Alliance.size_growth),),
               "avg_score_growth" : (desc(Alliance.score_avg_growth),),
               "avg_size_growth"  : (desc(Alliance.size_avg_growth),),
               "score_growth_pc" : (desc(Alliance.score_growth_pc),),
               "size_growth_pc"  : (desc(Alliance.size_growth_pc),),
               "avg_score_growth_pc" : (desc(Alliance.score_avg_growth_pc),),
               "avg_size_growth_pc"  : (desc(Alliance.size_avg_growth_pc),),
               } 
     if sort not in order.keys():
         sort = "score"
     order = order.get(sort)
     
     Q = session.query(Alliance)
     Q = Q.filter(Alliance.active == True)
     
     count = Q.count()
     pages = count/50 + int(count%50 > 0)
     pages = range(1, 1+pages)
     
     for o in order:
         Q = Q.order_by(o)
     Q = Q.limit(50).offset(offset)
     return render("alliances.tpl", request, alliances=Q.all(), offset=offset, pages=pages, page=page, sort=sort)

    def execute(self, request, user, page="1", sort="score"):
        page = int(page)
        offset = (page - 1) * 50
        order = {
            "score": (asc(Galaxy.score_rank),),
            "value": (asc(Galaxy.value_rank),),
            "size": (asc(Galaxy.size_rank),),
            "xp": (asc(Galaxy.xp_rank),),
        }
        if sort not in order.keys():
            sort = "score"
        order = order.get(sort)

        tick = Updates.midnight_tick()

        Q = session.query(Galaxy, GalaxyHistory)
        Q = Q.outerjoin((GalaxyHistory, and_(Galaxy.id == GalaxyHistory.id, GalaxyHistory.tick == tick)))
        Q = Q.filter(Galaxy.active == True)

        count = Q.count()
        pages = count / 50 + int(count % 50 > 0)
        pages = range(1, 1 + pages)

        for o in order:
            Q = Q.order_by(o)
        Q = Q.limit(50).offset(offset)
        return render("galaxies.tpl", request, galaxies=Q.all(), offset=offset, pages=pages, page=page, sort=sort)

 def execute(self, request, user, page="1", sort="score"):
     page = int(page)
     offset = (page - 1)*50
     order =  {"score" : (asc(Alliance.score_rank),),
               "size"  : (asc(Alliance.size_rank),),
               "avg_score" : (asc(Alliance.score_avg_rank),),
               "avg_size"  : (asc(Alliance.size_avg_rank),),
               "members"   : (asc(Alliance.members_rank),),
               } 
     if sort not in order.keys():
         sort = "score"
     order = order.get(sort)
     
     tick = Updates.midnight_tick()
     
     Q = session.query(Alliance, AllianceHistory)
     Q = Q.outerjoin((AllianceHistory, and_(Alliance.id == AllianceHistory.id, AllianceHistory.tick == tick)))
     Q = Q.filter(Alliance.active == True)
     
     count = Q.count()
     pages = count/50 + int(count%50 > 0)
     pages = range(1, 1+pages)
     
     for o in order:
         Q = Q.order_by(o)
     Q = Q.limit(50).offset(offset)
     return render("alliances.tpl", request, alliances=Q.all(), offset=offset, pages=pages, page=page, sort=sort)

 def execute(self, message, user, params):
     
     alliance = Alliance(name="Unknown") if params.group(1).lower() == "unknown" else Alliance.load(params.group(1))
     if alliance is None:
         message.reply("No alliance matching '%s' found"%(params.group(1),))
         return
     
     tick = Updates.current_tick()
     
     when = int(params.group(2) or 0)
     if when and when < 32:
         when += tick
     elif when and when <= tick:
         message.alert("Can not check status on the past. You wanted tick %s, but current tick is %s." % (when, tick,))
         return
     
     Q = session.query(Planet, User.name, Target.tick)
     Q = Q.join(Target.planet)
     Q = Q.join(Planet.intel) if alliance.id else Q.outerjoin(Planet.intel)
     Q = Q.join(Target.user)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance == (alliance if alliance.id else None))
     Q = Q.filter(Target.tick == when) if when else Q.filter(Target.tick > tick)
     Q = Q.order_by(asc(Planet.x))
     Q = Q.order_by(asc(Planet.y))
     Q = Q.order_by(asc(Planet.z))
     result = Q.all()
     
     if len(result) < 1:
         reply="No active bookings matching alliance %s" %(alliance.name)
         if when:
             reply+=" for tick %s."%(when,)
         message.reply(reply)
         return
     
     reply="Target information for %s"%(alliance.name)
     reply+=" landing on tick %s (eta %s): "%(when,when-tick) if when else ": "
     
     ticks={}
     for planet, user, land in result:
         if not ticks.has_key(land):
             ticks[land]=[]
         ticks[land].append((planet, user,))
     sorted_keys=ticks.keys()
     sorted_keys.sort()
     
     replies = []
     for land in sorted_keys:
         prev=[]
         for planet, user in ticks[land]:
             prev.append("(%s:%s:%s %s)" % (planet.x,planet.y,planet.z,user))
         replies.append("Tick %s (eta %s) "%(land,land-tick) +", ".join(prev))
     replies[0] = reply + replies[0]
     
     message.reply("\n".join(replies))

 def execute(self, request, user, tick):
     Q = session.query(Planet, Scan)
     Q = Q.join(Scan.planet)
     Q = Q.filter(Scan.tick == tick)
     Q = Q.order_by(asc(Planet.x), asc(Planet.y), asc(Planet.z), asc(Scan.scantype), asc(Scan.tick))
     result = Q.all()
     
     group = []
     for planet, scan in result:
         if len(group) < 1 or group[-1][0] is not planet:
             group.append((planet, [scan],))
         else:
             group[-1][1].append(scan)
     
     return render("scans/tick.tpl", request, tick=tick, group=group)

 def execute(self, message, user, params):
     
     alliance = Alliance.load(params.group(1))
     if alliance is None:
         message.reply("No alliance matching '%s' found"%(params.group(1),))
         return
     
     Q = session.query(sum(Planet.value), sum(Planet.score),
                       sum(Planet.size), sum(Planet.xp),
                       count(), Planet.race)
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.group_by(Intel.alliance_id, Planet.race)
     Q = Q.order_by(asc(Planet.race))
     result = Q.all()
     if len(result) < 1:
         message.reply("No planets in intel match alliance %s"%(alliance.name,))
         return
     prev=[]
     for value, score, size, xp, members, race in result:
         reply="%s %s Val(%s)" % (members,race,self.num2short(value/members),)
         reply+=" Score(%s)" % (self.num2short(score/members),)
         reply+=" Size(%s) XP(%s)" % (size/members,self.num2short(xp/members),)
         prev.append(reply)
     reply="Demographics for %s: "%(alliance.name,)+ ' | '.join(prev)
     message.reply(reply)

 def links(self, message, user, params):
     Q = session.query(Request)
     Q = Q.filter(Request.tick > Updates.current_tick() - 5)
     Q = Q.filter(Request.active == True)
     Q = Q.order_by(asc(Request.id))
     
     message.reply(" ".join(map(lambda request: "[%s: %s]" % (request.id, request.link,), Q[:5])))

 def list(self, message, user, params):
     Q = session.query(Request)
     Q = Q.filter(Request.tick > Updates.current_tick() - 5)
     Q = Q.filter(Request.active == True)
     Q = Q.order_by(asc(Request.id))
     
     message.reply(" ".join(map(lambda request: "[%s: %s %s:%s:%s]" % (request.id, request.scantype, request.target.x, request.target.y, request.target.z,), Q.all())))

def chat_state_request(workspace_id, channel_id, last_evt_id):
    data = {}

    if last_evt_id == 0:
        # Send all messages.
        log.debug("Chat messages request: sending all messages.")
        data["mode"] = "all"
        messages = ChatMessage.query.filter(and_(ChatMessage.workspace_id == workspace_id,
            ChatMessage.channel_id == channel_id)).order_by(asc(ChatMessage.date))
    else:
        # Sending an update only.
        log.debug("Chat messages request: sending newest messages.")
        data["mode"] = "update"
        messages = ChatMessage.query.filter(and_(ChatMessage.workspace_id == workspace_id,
            ChatMessage.channel_id == channel_id,
            ChatMessage.evt_id > last_evt_id))

    l = []
    for message in messages:
        d = message.to_dict()
        d['msg'] = cgi.escape(d['msg'])
        l.append(d)
        last_evt_id = max(last_evt_id, d['evt_id'])
    data['messages'] = l

    #log.debug("Chat messages request: data='%s'" % ( str(data) ) )
    log.debug("Chat messages request: data has %s messages." % ( str(len(data["messages"])) ) )

    return last_evt_id, data

def user_state_request(workspace_id, user, mode, last_evt_id):

    ulist = []

    # Get current user id (in pubws mode, it might not be defined yet.. just send WS creator in that case).
    cur_user_id = -1;
    if user: cur_user_id = user['id']

    # Get members.
    last_user_evt_id = 0
    for u in User.query.filter(User.workspace_id == workspace_id).order_by(asc(User.id)):

        # Send all users, except for pubws mode, where we just send only
        # the workspace creator and the current user (if defined).
        if mode == kwmolib.MODE_WS or \
            ( mode == kwmolib.MODE_PUBWS and \
                ( u.id in [0, 1, cur_user_id] ) ):
            ulist.append(u.to_dict())

        # Scan ALL the users fetched to get the latest event ID, no matter if in skurl mode or not.
        last_user_evt_id = u.evt_id

    log.debug("user_state_request(): Users list has %i entries." % ( len(ulist) ) )

    return max(last_evt_id, last_user_evt_id), ulist

 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 properties(self, name):
    connection = self._client.connect()
    rval = {}

    for interval,config in self._intervals.items():
      rval.setdefault(interval, {})

      stmt = select([self._table.c.i_time]).where(
        and_(
          self._table.c.name==name,
          self._table.c.interval==interval
        )
      ).order_by( asc(self._table.c.i_time) ).limit(1)
      rval[interval]['first'] = config['i_calc'].from_bucket(
        connection.execute(stmt).first()['i_time'] )

      stmt = select([self._table.c.i_time]).where(
        and_(
          self._table.c.name==name,
          self._table.c.interval==interval
        )
      ).order_by( desc(self._table.c.i_time) ).limit(1)
      rval[interval]['last'] = config['i_calc'].from_bucket(
        connection.execute(stmt).first()['i_time'] )

    return rval

    def sort_and_filter_public_analyses_by_column(self,column, order, filter_variable):
        """Sorts the public analyses by the column name and applies a filter on the model variable value selected
        Params:
                column: The name of the column to sort on
                order: either "asc" or "desc"
                filter_variable: the model_variable value used to filter the analyses
        """
        with self.readonly_scope() as session:

            query = session.query(Analysis) \
                        .options(subqueryload(Analysis.point_dataset)) \
                        .options(subqueryload(Analysis.coverage_datasets)) \
                        .options(subqueryload(Analysis.run_by_user)) \
                        .filter(Analysis.viewable_by == None,
                                Analysis.deleted != True)

            if filter_variable:
                query = query.filter(Analysis.model_variable == filter_variable)

            if order == "asc":

                return query.order_by(asc(column)).all()

            elif order == "desc":

                return query.order_by(desc(column)).all()

            else:

                return query.all()

    def list(self, message, user, params):
        Q = session.query(func.count().label("count"), func.max(Request.id).label("max_id"))
        Q = Q.filter(Request.tick > Updates.current_tick() - 5)
        Q = Q.filter(Request.active == True)
        Q = Q.group_by(Request.planet_id, Request.scantype)
        Q = Q.order_by(asc("max_id"))
        SQ = Q.subquery()
        Q = session.query(Request, SQ.c.count).join((SQ, and_(Request.id == SQ.c.max_id)))

        if Q.count() < 1:
            message.reply("There are no open scan requests")
            return

        message.reply(
            " ".join(
                map(
                    lambda (request, count): Config.get("Misc", "reqlist").decode("string_escape")
                    % (
                        request.id,
                        request.target.intel.dists if request.target.intel else "0",
                        "/%s" % request.dists if request.dists > 0 else "",
                        request.scantype,
                        request.target.x,
                        request.target.y,
                        request.target.z,
                    ),
                    Q.all(),
                )
            )
        )