Python sql.join函数代码示例

 def _search_for_join(mapper, table):
     """find a join between the given mapper's mapped table and the given table.
     will try the mapper's local table first for more specificity, then if not
     found will try the more general mapped table, which in the case of inheritance
     is a join."""
     try:
         return sql.join(mapper.local_table, table)
     except exceptions.ArgumentError, e:
         return sql.join(mapper.mapped_table, table)

def upgrade():
    op.add_column('request',
            sa.Column('payout', sa.Numeric(precision=15, scale=2), index=True,
                nullable=True))

    bind = op.get_bind()
    absolute = select([abs_table.c.value.label('value'),
                       mod_table.c.request_id.label('request_id')])\
            .select_from(join(abs_table, mod_table,
                    mod_table.c.id == abs_table.c.id))\
            .where(mod_table.c.voided_user_id == None)\
            .alias()
    relative = select([rel_table.c.value.label('value'),
                       mod_table.c.request_id.label('request_id')])\
            .select_from(join(rel_table, mod_table,
                    mod_table.c.id == rel_table.c.id))\
            .where(mod_table.c.voided_user_id == None)\
            .alias()
    abs_sum = select([request.c.id.label('request_id'),
                      request.c.base_payout.label('base_payout'),
                      func.sum(absolute.c.value).label('sum')])\
            .select_from(outerjoin(request, absolute,
                    request.c.id == absolute.c.request_id))\
            .group_by(request.c.id)\
            .alias()
    rel_sum = select([request.c.id.label('request_id'),
                      func.sum(relative.c.value).label('sum')])\
            .select_from(outerjoin(request, relative,
                    request.c.id == relative.c.request_id))\
            .group_by(request.c.id)\
            .alias()
    total_sum = select([abs_sum.c.request_id.label('request_id'),
                        ((
                            abs_sum.c.base_payout +
                            case([(abs_sum.c.sum == None, Decimal(0))],
                                    else_=abs_sum.c.sum)) *
                         (
                            1 +
                            case([(rel_sum.c.sum == None, Decimal(0))],
                                    else_=rel_sum.c.sum))).label('payout')])\
            .select_from(join(abs_sum, rel_sum,
                    abs_sum.c.request_id == rel_sum.c.request_id))
    payouts = bind.execute(total_sum)
    for request_id, payout in payouts:
        up = update(request).where(request.c.id == request_id).values(
                payout=payout)
        bind.execute(up)
    op.alter_column('request', 'payout', nullable=False,
            existing_type=sa.Numeric(precision=15, scale=2))

 def _determine_joins(self):
     if self.secondaryjoin is not None and self.secondary is None:
         raise exceptions.ArgumentError("Property '" + self.key + "' specified with secondary join condition but no secondary argument")
     # if join conditions were not specified, figure them out based on foreign keys
     try:
         if self.secondary is not None:
             if self.secondaryjoin is None:
                 self.secondaryjoin = sql.join(self.mapper.unjoined_table, self.secondary).onclause
             if self.primaryjoin is None:
                 self.primaryjoin = sql.join(self.parent.unjoined_table, self.secondary).onclause
         else:
             if self.primaryjoin is None:
                 self.primaryjoin = sql.join(self.parent.unjoined_table, self.target).onclause
     except exceptions.ArgumentError, e:
         raise exceptions.ArgumentError("Error determining primary and/or secondary join for relationship '%s'.  If the underlying error cannot be corrected, you should specify the 'primaryjoin' (and 'secondaryjoin', if there is an association table present) keyword arguments to the relation() function (or for backrefs, by specifying the backref using the backref() function with keyword arguments) to explicitly specify the join conditions.  Nested error is \"%s\"" % (str(self), str(e)))

    def __init__(self, left, right, onclause=None, isouter=False):
        if _is_mapped_class(left) or _is_mapped_class(right):
            if hasattr(left, '_orm_mappers'):
                left_mapper = left._orm_mappers[1]
                adapt_from = left.right
            else:
                left_mapper = _class_to_mapper(left)
                if _is_aliased_class(left):
                    adapt_from = left.alias
                else:
                    adapt_from = None

            right_mapper = _class_to_mapper(right)
            self._orm_mappers = (left_mapper, right_mapper)
            
            if isinstance(onclause, basestring):
                prop = left_mapper.get_property(onclause)

                if _is_aliased_class(right):
                    adapt_to = right.alias
                else:
                    adapt_to = None

                pj, sj, source, dest, target_adapter = prop._create_joins(source_selectable=adapt_from, dest_selectable=adapt_to, source_polymorphic=True, dest_polymorphic=True)

                if sj:
                    left = sql.join(left, prop.secondary, onclause=pj)
                    onclause = sj
                else:
                    onclause = pj
        expression.Join.__init__(self, left, right, onclause, isouter)

 def query(self):
     pq = qualstat_getstatdata(column("eval_type") == "f")
     base = alias(pq)
     query = (select([
         func.array_agg(column("queryid")).label("queryids"),
         "qualid",
         cast(column("quals"), JSONB).label('quals'),
         "occurences",
         "execution_count",
         func.array_agg(column("query")).label("queries"),
         "avg_filter",
         "filter_ratio"
     ]).select_from(
         join(base, powa_databases,
              onclause=(
                  powa_databases.c.oid == literal_column("dbid"))))
         .where(powa_databases.c.datname == bindparam("database"))
         .where(column("avg_filter") > 1000)
         .where(column("filter_ratio") > 0.3)
         .group_by(column("qualid"), column("execution_count"),
                   column("occurences"),
                   cast(column("quals"), JSONB),
                  column("avg_filter"), column("filter_ratio"))
         .order_by(column("occurences").desc())
         .limit(200))
     return query

def messages_in_narrow_backend(request, user_profile,
                               msg_ids = REQ(validator=check_list(check_int)),
                               narrow = REQ(converter=narrow_parameter)):
    # type: (HttpRequest, UserProfile, List[int], List[Dict[str, Any]]) -> HttpResponse

    # Note that this function will only work on messages the user
    # actually received

    # TODO: We assume that the narrow is a search.  For now this works because
    # the browser only ever calls this function for searches, since it can't
    # apply that narrow operator itself.

    query = select([column("message_id"), column("subject"), column("rendered_content")],
                   and_(column("user_profile_id") == literal(user_profile.id),
                        column("message_id").in_(msg_ids)),
                   join(table("zerver_usermessage"), table("zerver_message"),
                        literal_column("zerver_usermessage.message_id") ==
                        literal_column("zerver_message.id")))

    builder = NarrowBuilder(user_profile, column("message_id"))
    for term in narrow:
        query = builder.add_term(query, term)

    sa_conn = get_sqlalchemy_connection()
    query_result = list(sa_conn.execute(query).fetchall())

    search_fields = dict()
    for row in query_result:
        (message_id, subject, rendered_content, content_matches, subject_matches) = row
        search_fields[message_id] = get_search_fields(rendered_content, subject,
                                                      content_matches, subject_matches)

    return json_success({"messages": search_fields})

        def get_range_data(offset_, size_):
            tbl_main = alias(tbl_def, 't')
            join_condition = []

            pk_names_desc = [name+" DESC" for name in pk_names]
            sub_q = select(pk_cols).order_by(", ".join(pk_names_desc)).offset(offset_).limit(1).alias()

            for pk_name in pk_names:
                item = (tbl_main.c[pk_name] <= sub_q.c[pk_name])
                join_condition.append(item)

            if len(join_condition) > 1:
                j = join(tbl_main, sub_q, and_(*join_condition))
            else:
                j = join(tbl_main, sub_q, join_condition[0])

            return select([tbl_main]).select_from(j).order_by(", ".join(pk_names_desc)).limit(size_)

def team_score_list():
    teams = Team.query.filter(Team.role.in_([Team.BLUE, Team.RED]))
    scoring_teams = []
    for team in teams:
        temp = db.session.query(
                functions.sum(CheckResult.success * ServiceCheck.value),
                functions.sum(ServiceCheck.value)) \
            .select_from(
                join(CheckResult,
                     join(ServiceCheck, Service, ServiceCheck.service_id == Service.id),
                     CheckResult.check_id == ServiceCheck.id))

        services = temp.filter(Service.team_id == team.id).first()

        earned = 0
        maximum = 0
        if services[0]:
            earned = services[0]
            maximum = services[1]

        flag_subquery = db.session.\
            query(functions.count(FlagDiscovery.id).label('solve_count'), Flag.value).\
            select_from(join(Flag, FlagDiscovery, Flag.id == FlagDiscovery.flag_id)).\
            filter(Flag.team_id == team.id).\
            group_by(Flag.id).\
            subquery('flag_subquery')
        flags = db.session \
            .query(functions.sum(flag_subquery.c.solve_count * flag_subquery.c.value)).\
            first()

        flags = flags[0] if flags[0] else 0

        injects = score_injects(team)

        team.scores = {
            'services_earned': earned,
            'services_maximum': maximum,
            'injects_earned': injects,
            'flags_lost': flags
        }

        scoring_teams.append(team)

    return render_scoring_page('scoring/index.html', teams=scoring_teams)

 def test_mapify_with_table_object_join(self):
     t1 = Table('test_baph_mapify', self.orm.metadata, useexisting=True)
     t2 = Table('test_baph_mapify_join', self.orm.metadata, autoload=True,
                useexisting=True)
     tjoin = join(t1, t2)
     JoinObj = Mapify(self.orm, tjoin)(MapifiableClass)
     self.assertHasAttr(JoinObj, '__table__')
     self.assertHasAttr(JoinObj, 'id')
     self.assertHasAttr(JoinObj, 'string')
     self.assertHasAttr(JoinObj, 'number_with_decimal_point')
     self.assertHasAttr(JoinObj, 'other_string')

    def _getPostInfo(self, ctx, row):
        post_info = {
                'type': row['post_type'],
                'slug': row['post_name'],
                'datetime': row['post_date'],
                'title': row['post_title'],
                'status': row['post_status'],
                'post_id': row['ID'],
                'post_guid': row['guid'],
                'content': row['post_content'],
                'excerpt': row['post_excerpt']}

        res = ctx.conn.execute(
                select([ctx.users])
                .where(ctx.users.c.ID == row['post_author'])).fetchone()
        if res:
            post_info['author'] = res['user_login']
        else:
            logger.warning("No author on %s" % row['post_name'])
            post_info['author'] = ''

        # TODO: This is super slow. Gotta cache this thing somehow.
        res = ctx.conn.execute(
                join(ctx.term_relationships,
                     join(ctx.term_taxonomy, ctx.terms))
                .select(ctx.term_relationships.c.object_id == row['ID']))
        categories = []
        for r in res:
            if r['taxonomy'] != 'category':
                logger.debug("Skipping taxonomy '%s' on: %s" %
                             (r['taxonomy'], row['post_name']))
                continue
            categories.append(r['slug'])
        post_info['categories'] = categories

        metadata = {}
        post_info['metadata'] = metadata

        return post_info

def select_media(hashtag):

    j = join(TwitterMedia, TwitterHashtag, TwitterMedia.tweet_id == TwitterHashtag.tweet_id)
    q = select([TwitterMedia.media_url, TwitterMedia.tweet_id]).where(TwitterHashtag.hashtag == hashtag).where(TwitterMedia.enabled == True).select_from(j)

    log.debug(q)

    result = []

    for r in connection.execute(q).fetchall():
        result.append(r['media_url'])

    return result

 def sflvault_service_put(self, authtok, service_id, data):
     # 'user_id' required in session.
     # TODO: verify I had access to the service previously.
     req = sql.join(servicegroups_table, usergroups_table,
                    ServiceGroup.group_id==UserGroup.group_id) \
              .join(users_table, User.id==UserGroup.user_id) \
              .select() \
              .where(User.id==self.sess['user_id']) \
              .where(ServiceGroup.service_id==service_id)
     res = list(meta.Session.execute(req))
     if not res:
         return vaultMsg(False, "You don't have access to that service.")
     else:
         return self.vault.service_put(service_id, data)

    def __init__(self, left, right, onclause=None, 
                            isouter=False, join_to_left=True):
        adapt_from = None

        if hasattr(left, '_orm_mappers'):
            left_mapper = left._orm_mappers[1]
            if join_to_left:
                adapt_from = left.right
        else:
            left_mapper, left, left_is_aliased = _entity_info(left)
            if join_to_left and (left_is_aliased or not left_mapper):
                adapt_from = left

        right_mapper, right, right_is_aliased = _entity_info(right)
        if right_is_aliased:
            adapt_to = right
        else:
            adapt_to = None

        if left_mapper or right_mapper:
            self._orm_mappers = (left_mapper, right_mapper)

            if isinstance(onclause, basestring):
                prop = left_mapper.get_property(onclause)
            elif isinstance(onclause, attributes.QueryableAttribute):
                if adapt_from is None:
                    adapt_from = onclause.__clause_element__()
                prop = onclause.property
            elif isinstance(onclause, MapperProperty):
                prop = onclause
            else:
                prop = None

            if prop:
                pj, sj, source, dest, \
                secondary, target_adapter = prop._create_joins(
                                source_selectable=adapt_from,
                                dest_selectable=adapt_to,
                                source_polymorphic=True,
                                dest_polymorphic=True,
                                of_type=right_mapper)

                if sj is not None:
                    left = sql.join(left, secondary, pj, isouter)
                    onclause = sj
                else:
                    onclause = pj
                self._target_adapter = target_adapter

        expression.Join.__init__(self, left, right, onclause, isouter)

 def by_is(self, query, operand, maybe_negate):
     if operand == 'private':
         query = query.select_from(join(query.froms[0], "zerver_recipient",
                                        column("recipient_id") ==
                                        literal_column("zerver_recipient.id")))
         cond = or_(column("type") == Recipient.PERSONAL,
                    column("type") == Recipient.HUDDLE)
         return query.where(maybe_negate(cond))
     elif operand == 'starred':
         cond = column("flags").op("&")(UserMessage.flags.starred.mask) != 0
         return query.where(maybe_negate(cond))
     elif operand == 'mentioned' or operand == 'alerted':
         cond = column("flags").op("&")(UserMessage.flags.mentioned.mask) != 0
         return query.where(maybe_negate(cond))
     raise BadNarrowOperator("unknown 'is' operand " + operand)

    def get_album_query(self):
        from masterapp.config.schema import dbfields

        # Number of songs available on this album subquery
        havesongs = Session.query(Album.id.label('albumid'),
            func.count(Song.id).label('Album_havesongs'),
            func.sum(Song.length).label('Album_length')
        ).join(Album.songs, SongOwner).filter(SongOwner.uid == self.id)
        havesongs = havesongs.group_by(Album.id).subquery()

        query = Session.query(SongOwner.uid.label('Friend_id'), havesongs.c.Album_havesongs,
            havesongs.c.Album_length, User._name.label('Friend_name'),
            *dbfields['album'])
        joined = join(Album, havesongs, Album.id == havesongs.c.albumid)
        query = query.select_from(joined)
        query = query.join(Album.artist).reset_joinpoint()
        query = query.join(Album.songs, SongOwner, SongOwner.user).filter(SongOwner.uid == self.id)
        query = query.group_by(Album)
        return query