本文整理汇总了Python中sqlalchemy.sql.join函数的典型用法代码示例。如果您正苦于以下问题:Python join函数的具体用法?Python join怎么用?Python join使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了join函数的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: _search_for_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)
示例2: upgrade
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))
示例3: _determine_joins
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)))
示例4: __init__
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)
示例5: query
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
示例6: messages_in_narrow_backend
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})
示例7: get_range_data
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_)
示例8: team_score_list
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)
示例9: test_mapify_with_table_object_join
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')
示例10: _getPostInfo
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
示例11: select_media
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
示例12: sflvault_service_put
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)
示例13: __init__
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)
示例14: by_is
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)
示例15: get_album_query
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