Python session.query函数代码示例

 def authenticate(self, request):
     request.session = None
     request.user = None
     key = request.COOKIES.get(SESSION_KEY)
     if key:
         auth = Arthur.load(key, datetime.now())
         if auth is None:
             raise UserError("Your session has expired, please login again.")
         if request.path == LOGOUT:
             session.delete(auth)
             session.commit()
             raise UserError("Logged out.")
         request.session = auth
         return auth.user, None, key
     elif (request.POST.get(USER) and request.POST.get(PASS)):
         user = User.load(name=request.POST.get(USER), passwd=request.POST.get(PASS))
         if user is None:
             raise UserError("Invalid user.")
         else:
             key = self.generate_key(user)
             auth = Arthur(key=key, expire=datetime.now()+timedelta(days=1), user=user)
             session.query(Arthur).filter(Arthur.user == user).delete()
             session.add(auth)
             session.commit()
             request.session = auth
             return user, key, key
     else:
         return None, None, None

    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(),
                )
            )
        )

 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, message, user, params):
     
     tick = Updates.current_tick() + (params.group(1) or 1)
     replies = []
     
     Q = session.query(Galaxy.x, Galaxy.y, count())
     Q = Q.join(Target.planet)
     Q = Q.join(Planet.galaxy)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Galaxy.x, Galaxy.y)
     result = Q.all()
     prev = []
     for x, y, bitches in result:
         prev.append("%s:%s(%s)"%(x,y,bitches))
     replies.append("Active bookings: " + ", ".join(prev))
     
     Q = session.query(Alliance.name, count())
     Q = Q.join(Target.planet)
     Q = Q.outerjoin(Planet.intel)
     Q = Q.outerjoin(Intel.alliance)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Alliance.name)
     result = Q.all()
     prev = []
     for name, bitches in result:
         prev.append("%s (%s)"%(name or "Unknown", bitches))
     replies.append("Active bitches: " + ", ".join(prev))
     
     if len(replies) < 1:
         replies.append("No active bookings. This makes %s sad. Please don't make %s sad." %((Config.get("Connection","nick"),)*2))
     message.reply("\n".join(replies))

    def list_fleets(self, message, user, params):
        # Check the planet exists
        planet = Planet.load(*params.group(1,3,5))
        if planet is None:
            message.alert("No planet with coords %s:%s:%s" % params.group(1,3,5))
            return

        # Find all fleets with a known alliance who have defended this planet
        OQ = session.query(coalesce(FleetScan.launch_tick, FleetScan.landing_tick), literal_column("'From'").label("dir"), Planet.x, Planet.y, Planet.z, Alliance.name).select_from(FleetScan)
        OQ = OQ.filter(FleetScan.target_id == planet.id, FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        OQ = OQ.join(Intel, FleetScan.owner_id == Intel.planet_id).filter(Intel.alliance_id != None)
        OQ = OQ.join(Alliance, Intel.alliance_id == Alliance.id).join(Planet, FleetScan.owner_id == Planet.id)

        # Find all fleets with a known alliance who have been defended by this planet
        TQ = session.query(coalesce(FleetScan.launch_tick, FleetScan.landing_tick), literal_column("'To  '").label("dir"), Planet.x, Planet.y, Planet.z, Alliance.name).select_from(FleetScan)
        TQ = TQ.filter(FleetScan.owner_id == planet.id, FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        TQ = TQ.join(Intel, FleetScan.target_id == Intel.planet_id).filter(Intel.alliance_id != None)
        TQ = TQ.join(Alliance, Intel.alliance_id == Alliance.id).join(Planet, FleetScan.target_id == Planet.id)

        # Combine the results into one sorted list
        results = sorted(OQ.all()+TQ.all(), reverse=True)

        # Quit now if there are no results
        if len(results) == 0:
            message.reply("No suggestions found")
            return

        # Reply to the user
        message.reply("Tick  Dir   Planet     Alliance")
        limit = int(params.group(6) or 5)
        for r in results[:limit]:
            message.reply("%4s  %s  %-9s  %s" % (r[0], r[1], "%s:%s:%s" % (r[2], r[3], r[4]), r[5]))
        if len(results) > limit:
            message.reply("%s results not shown (%s total)" % (len(results)-limit, len(results)))

 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.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, tick=Updates.current_tick()*-1)

 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, 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,
                   )

 def load(name, active=True):
     Q = session.query(Alliance).filter_by(active=True)
     alliance = Q.filter(Alliance.name.ilike(name)).first()
     if alliance is None:
         alliance = Q.filter(Alliance.name.ilike(name + "%")).first()
     if alliance is None and active == False:
         Q = session.query(Alliance)
         alliance = Q.filter(Alliance.name.ilike(name)).first()
         if alliance is None:
             alliance = Q.filter(Alliance.name.ilike(name + "%")).first()
     return alliance

 def execute(self, request, user, name):
     alliance = Alliance.load(name)
     if alliance is None:
         return HttpResponseRedirect(reverse("alliance_ranks"))
     
     ph = aliased(PlanetHistory)
     members = count().label("members")
     size = sum(ph.size).label("size")
     value = sum(ph.value).label("value")
     score = sum(ph.score).label("score")
     avg_size = size.op("/")(members).label("avg_size")
     avg_value = value.op("/")(members).label("avg_value")
     t10v = count(case(whens=((ph.value_rank <= 10 ,1),), else_=None)).label("t10v")
     t100v = count(case(whens=((ph.value_rank <= 100 ,1),), else_=None)).label("t100v")
     
     pho = aliased(PlanetHistory)
     sizeo = sum(pho.size).label("sizeo")
     valueo = sum(pho.value).label("valueo")
     scoreo = sum(pho.score).label("scoreo")
     
     Q = session.query(PlanetHistory.tick.label("tick"),
                       Alliance.id.label("id"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.size) DESC)").label("size_rank"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.value) DESC)").label("value_rank"),
                       )
     Q = Q.filter(PlanetHistory.active == True)
     Q = Q.join(PlanetHistory.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.group_by(PlanetHistory.tick, Alliance.id)
     ranks = Q.subquery()
     
     Q = session.query(ph.tick, members,
                       size, value,
                       avg_size, avg_value,
                       size-sizeo, value-valueo, score-scoreo,
                       t10v, t100v,
                       )
     Q = Q.filter(ph.active == True)
     Q = Q.join(ph.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.outerjoin((pho, and_(ph.id==pho.id, ph.tick-1==pho.tick),))
     Q = Q.filter(Intel.alliance == alliance)
     Q = Q.group_by(ph.tick)
     
     Q = Q.from_self().add_columns(ranks.c.size_rank, ranks.c.value_rank)
     Q = Q.outerjoin((ranks, and_(ph.tick == ranks.c.tick, alliance.id == ranks.c.id),))
     Q = Q.order_by(desc(ph.tick))
     
     history = Q.all()
     
     return render("ialliancehistory.tpl", request, alliance=alliance, members=alliance.intel_members, history=history)

 def execute(self, message, user, params):
     
     tag_count = PA.getint("numbers", "tag_count")
     
     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())
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.group_by(Intel.alliance_id)
     result = Q.first()
     if result is None:
         message.reply("No planets in intel match alliance %s"%(alliance.name,))
         return
     
     value, score, size, xp, members = result
     if members <= tag_count:
         reply="%s Members: %s/%s, Value: %s, Avg: %s," % (alliance.name,members,alliance.members,value,value//members)
         reply+=" Score: %s, Avg: %s," % (score,score//members) 
         reply+=" Size: %s, Avg: %s, XP: %s, Avg: %s" % (size,size//members,xp,xp//members)
         message.reply(reply)
         return
     
     Q = session.query(Planet.value, Planet.score, 
                       Planet.size, Planet.xp, 
                       Intel.alliance_id)
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.order_by(desc(Planet.score))
     Q = Q.limit(tag_count)
     Q = Q.from_self(sum(Planet.value), sum(Planet.score),
                     sum(Planet.size), sum(Planet.xp),
                     count())
     Q = Q.group_by(Intel.alliance_id)
     ts_result = Q.first()
     
     ts_value, ts_score, ts_size, ts_xp, ts_members = ts_result
     reply="%s Members: %s/%s (%s)" % (alliance.name,members,alliance.members,ts_members)
     reply+=", Value: %s (%s), Avg: %s (%s)" % (value,ts_value,value//members,ts_value//ts_members)
     reply+=", Score: %s (%s), Avg: %s (%s)" % (score,ts_score,score//members,ts_score//ts_members)
     reply+=", Size: %s (%s), Avg: %s (%s)" % (size,ts_size,size//members,ts_size//ts_members)
     reply+=", XP: %s (%s), Avg: %s (%s)" % (xp,ts_xp,xp//members,ts_xp//ts_members)
     message.reply(reply)

    def list_planets(self, message, user, params):
        oIntel = aliased(Intel)
        tIntel = aliased(Intel)
        
        # Find all planets with unknown alliance, who have been defended by planets (outside of their galaxy) with known alliance
        TQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        TQ = TQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id)
        TQ = TQ.filter(tIntel.alliance_id == None).filter(oIntel.alliance_id != None)
        TQ = TQ.join(Alliance, oIntel.alliance_id == Alliance.id).join(Planet, FleetScan.target_id == Planet.id)
        TQ = TQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name)

        # Find all planets with unknown alliance, who have defended planets (outside of their galaxy) with known alliance
        OQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        OQ = OQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id)
        OQ = OQ.filter(tIntel.alliance_id != None).filter(oIntel.alliance_id == None)
        OQ = OQ.join(Alliance, tIntel.alliance_id == Alliance.id).join(Planet, FleetScan.owner_id == Planet.id)
        OQ = OQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name)

        # A FULL OUTER JOIN would fit nicely here, but SQLAlchemy doesn't support it and I'm trying to stick with ORM, so we'll use Python

        # Combine the results into one sorted list
        results = sorted(TQ.all()+OQ.all())

        # Quit now if there are no results
        if len(results) == 0:
            message.reply("No suggestions found")
            return

        i = 0
        while i < (len(results)-1):
          # Check for planet/alliance combinations that appeared in both lists
          if results[i][:4] == results[i+1][:4]:
            r = list(results.pop(i))
            # Add the fleet counts (r[i+1] has moved to r[i])
            r[4] += results.pop(i)[4]
            results.insert(i, r)
          i+=1

        # Sort by number of fleets using a helper function
        from operator import itemgetter
        results.sort(key=itemgetter(4), reverse=True)

        # Reply to the user
        message.reply("Coords     Suggestion      Fleets")
        limit = int(params.group(1) or 5)
        for r in results[:limit]:
            message.reply("%-9s  %-14s  %s" % ("%s:%s:%s" % (r[0], r[1], r[2]), r[3], r[4]))
        if len(results) > limit:
            message.reply("%s results not shown (%s total)" % (len(results)-limit, len(results)))

    def fleet_overview(self):
        if self.scantype not in ("J",):
            return

        from sqlalchemy.sql.functions import min, sum

        f = aliased(FleetScan)
        a = aliased(FleetScan)
        d = aliased(FleetScan)

        Q = session.query(
            f.landing_tick,
            f.landing_tick - min(Scan.tick),
            count(a.id),
            coalesce(sum(a.fleet_size), 0),
            count(d.id),
            coalesce(sum(d.fleet_size), 0),
        )
        Q = Q.join(f.scan)
        Q = Q.filter(f.scan == self)

        Q = Q.outerjoin((a, and_(a.id == f.id, a.mission.ilike("Attack"))))
        Q = Q.outerjoin((d, and_(d.id == f.id, d.mission.ilike("Defend"))))

        Q = Q.group_by(f.landing_tick)
        Q = Q.order_by(asc(f.landing_tick))
        return Q.all()

 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, message, user, params):
     if params.group(1)[-6:].lower() == "hideme":
         notice = "%s" % (params.group(1)[:-6])
     else:
         notice = "(%s) %s" % (user.name, params.group(1))
     for chan in session.query(Channel).filter(Channel.userlevel > (Config.getint("Access",  "galmate") if "galmate" in Config.options("Access") else 0)).all():
         message.notice(notice, chan.name)