本文整理汇总了Python中sqlalchemy.sql.expression.case函数的典型用法代码示例。如果您正苦于以下问题:Python case函数的具体用法?Python case怎么用?Python case使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了case函数的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: sort
def sort(self, builder):
column = self.column
translationClass = column.translationClass
attr = column.attr
whens = []
for language in column.languages:
key = ('translation', translationClass, language)
onFactory = lambda aliasedTable: and_(
aliasedTable.foreign_id == builder.mappedClass.id,
aliasedTable.local_language == language,
)
aliasedTable = builder.joinOn(key, onFactory, translationClass)
aliasedColumn = getattr(aliasedTable, attr)
whens.append((aliasedColumn != None, aliasedColumn))
if attr == 'name':
default = builder.mappedClass.identifier
else:
default = None
query = builder.query
if self.descending:
order = case(whens, else_=default).desc().nullslast()
else:
order = case(whens, else_=default).asc().nullsfirst()
query = query.order_by(order)
builder.query = query
示例2: birth_time
def birth_time(cls):
hour = cast(func.extract("hour", cls.birth_datetime), String)
minute = cast(func.extract("minute", cls.birth_datetime), String)
hour = case([(func.length(hour) == 1, "0" + hour)], else_=hour)
minute = case([(func.length(minute) == 1, "0" + minute)], else_=minute)
return hour + ":" + minute
示例3: _generate_stmt
def _generate_stmt(self, id) :
dynamic = self.dynamic
anchor_x = self.anchor_x
note_box = self.note_box
dynamic_direction = self.dynamic_direction
dynamic_padding = self.dynamic_padding
staff_position = self.staff_position
from_anchor_x = self.from_anchor_x
dynamic_padding_default = dynamic_padding.alias(name='dynamic_padding_default')
dynamic_to_staff_position = select([
dynamic.c.id.label('id'),
# ughh...2.0 magic number for staff
((dynamic_direction.c.val * case([(dynamic_padding.c.val != None, dynamic_padding.c.val)], else_=dynamic_padding_default.c.val)) +
case([(dynamic_direction.c.val == 1,
sql_min_max([note_box.c.y + note_box.c.height, 2.0], True)),
(dynamic_direction.c.val == -1,
sql_min_max([note_box.c.y, -2.0], False))])).label('val')
]).select_from(dynamic.outerjoin(dynamic_padding, onclause = dynamic.c.id == dynamic_padding.c.id)).\
where(safe_eq_comp(note_box.c.id if from_anchor_x else dynamic.c.id, id)).\
where(anchor_x.c.id == dynamic.c.id).\
where(note_box.c.id == anchor_x.c.val).\
where(dynamic_padding_default.c.id == -1).\
where(dynamic.c.id == dynamic_direction.c.id).\
cte(name='dynamic_to_staff_position')
self.register_stmt(dynamic_to_staff_position)
self.insert = simple_insert(staff_position, dynamic_to_staff_position)
示例4: _generate_stmt
def _generate_stmt(self, id) :
font_name = self.font_name
font_size = self.font_size
dots = self.dots
glyph_box = self.glyph_box
dot_padding = self.dot_padding
dot_width = self.dot_width
dot_padding_default = dot_padding.alias(name='dot_padding_default')
dots_to_dot_widths = select([
dots.c.id.label('id'),
case([(dots.c.val == 0, 0.0)], else_ = ((glyph_box.c.width * font_size.c.val * dots.c.val / 20.0) +
case([(dot_padding.c.val != None, dot_padding.c.val)] , else_ = dot_padding_default.c.val) * (dots.c.val - 1)
)).label('val')
]).select_from(dots.outerjoin(dot_padding, onclause = dots.c.id == dot_padding.c.id)).\
where(safe_eq_comp(dots.c.id, id)).\
where(and_(dot_padding_default.c.id == -1,
dots.c.id == font_name.c.id,
dots.c.id == font_size.c.id,
font_name.c.val == glyph_box.c.name,
glyph_box.c.unicode == "U+E1E7")).\
cte(name='dots_to_dot_widths')
self.register_stmt(dots_to_dot_widths)
self.insert = simple_insert(dot_width, dots_to_dot_widths)
示例5: pushDB
def pushDB(self, db, sensor_id, parent_domain_id):
q = db.query(Domain.domain_id)
q = q.filter(and_(Domain.parent_domain_id == parent_domain_id, Domain.domain_name == self.name))
res = q.all()
if len(res) == 0:
o = Domain()
o.domain_name = self.name
o.parent_domain_id = parent_domain_id
db.add(o)
db.flush()
self.oid = o.domain_id
sd = Sensor_Domain()
sd.domain_id = self.oid
sd.sensor_id = sensor_id
sd.first_seen = self.fs
sd.last_seen = self.ls
db.add(sd)
db.flush()
else:
self.oid = res[0][0]
q = db.query(Sensor_Domain)
q = q.filter(and_(Sensor_Domain.domain_id == self.oid, Sensor_Domain.sensor_id == sensor_id))
q.update({Sensor_Domain.first_seen: case([(Sensor_Domain.first_seen > self.fs, self.fs)], else_=Sensor_Domain.first_seen),
Sensor_Domain.last_seen: case([(Sensor_Domain.last_seen < self.ls, self.ls)], else_=Sensor_Domain.last_seen)}, synchronize_session=False)
return self.oid
示例6: _http_soap
def _http_soap(session, cluster_id):
return session.query(
HTTPSOAP.id, HTTPSOAP.name, HTTPSOAP.is_active,
HTTPSOAP.is_internal, HTTPSOAP.transport, HTTPSOAP.host,
HTTPSOAP.url_path, HTTPSOAP.method, HTTPSOAP.soap_action,
HTTPSOAP.soap_version, HTTPSOAP.data_format, HTTPSOAP.security_id,
HTTPSOAP.connection,
case([(HTTPSOAP.ping_method != None, HTTPSOAP.ping_method)], else_=DEFAULT_HTTP_PING_METHOD).label('ping_method'), # noqa
case([(HTTPSOAP.pool_size != None, HTTPSOAP.pool_size)], else_=DEFAULT_HTTP_POOL_SIZE).label('pool_size'),
case([(HTTPSOAP.merge_url_params_req != None, HTTPSOAP.merge_url_params_req)], else_=True).label('merge_url_params_req'),
case([(HTTPSOAP.url_params_pri != None, HTTPSOAP.url_params_pri)], else_=URL_PARAMS_PRIORITY.DEFAULT).label('url_params_pri'),
case([(HTTPSOAP.params_pri != None, HTTPSOAP.params_pri)], else_=PARAMS_PRIORITY.DEFAULT).label('params_pri'),
SecurityBase.sec_type,
Service.name.label('service_name'),
Service.id.label('service_id'),
Service.impl_name.label('service_impl_name'),
SecurityBase.name.label('security_name'),
SecurityBase.username.label('username'),
SecurityBase.password.label('password'),
SecurityBase.password_type.label('password_type'),).\
outerjoin(Service, Service.id==HTTPSOAP.service_id).\
outerjoin(SecurityBase, HTTPSOAP.security_id==SecurityBase.id).\
filter(Cluster.id==HTTPSOAP.cluster_id).\
filter(Cluster.id==cluster_id).\
order_by(HTTPSOAP.name)
示例7: _generate_stmt
def _generate_stmt(self, id) :
font_name = self.font_name
font_size = self.font_size
unicode = self.unicode
glyph_box = self.glyph_box
glyph_stencil = self.glyph_stencil
alignment_directive = self.alignment_directive
writer = self.writer
extra_eq = self.extra_eq
generics_to_stencils = select([
font_name.c.id.label('id'),
literal(writer).label('writer'),
literal(0).label('sub_id'),
font_name.c.val.label('font_name'),
font_size.c.val.label('font_size'),
unicode.c.val.label('unicode'),
case([(alignment_directive.c.x != None, glyph_box.c.x + (alignment_directive.c.x * glyph_box.c.width))], else_=0).label('x'),
case([(alignment_directive.c.y != None, glyph_box.c.y + glyph_box.c.height - (alignment_directive.c.y * glyph_box.c.height))], else_=0).label('y'),
]).select_from(font_name.outerjoin(alignment_directive, onclause = alignment_directive.c.id == font_name.c.id)).\
where(safe_eq_comp(font_name.c.id, id)).\
where(and_(glyph_box.c.name == font_name.c.val,
glyph_box.c.unicode == unicode.c.val,
font_name.c.id == font_size.c.id,
font_name.c.id == unicode.c.id,
*extra_eq
)).\
cte(name='generics_to_stencils')
self.register_stmt(generics_to_stencils)
self.insert = simple_insert(glyph_stencil, generics_to_stencils)
示例8: demographic_etl
def demographic_etl(config):
# set up
connection = get_connection(config)
pedsnet_session = init_pedsnet(connection)
init_pcornet(connection)
# multiple aliases for pedsnet_pcornet_valueset_map
# to allow the three named joins
gender_value_map = aliased(ValueSetMap)
ethnicity_value_map = aliased(ValueSetMap)
race_value_map = aliased(ValueSetMap)
# extract the data from the person table
person = pedsnet_session.query(Person.person_id,
Person.birth_date,
Person.birth_time,
coalesce(gender_value_map.target_concept, 'OT'),
coalesce(ethnicity_value_map.target_concept, 'OT'),
coalesce(race_value_map.target_concept, 'OT'),
bindparam("biobank_flag", "N"),
Person.gender_source_value,
Person.ethnicity_source_value,
Person.race_source_value,
Person.site,
bindparam("gender_identity", None),
bindparam("raw_gender_identity", None),
bindparam("sexual_orientation", None),
bindparam("raw_sexual_orientation", None)
). \
outerjoin(gender_value_map,
and_(gender_value_map.source_concept_class == 'Gender',
case([(and_(Person.gender_concept_id == None,
gender_value_map.source_concept_id == None), True)],
else_=cast(Person.gender_concept_id, String(200)) ==
gender_value_map.source_concept_id))). \
outerjoin(ethnicity_value_map,
and_(ethnicity_value_map.source_concept_class == 'Hispanic',
case([(and_(Person.ethnicity_concept_id == None,
ethnicity_value_map.source_concept_id == None), True)],
else_=cast(Person.ethnicity_concept_id, String(200)) ==
ethnicity_value_map.source_concept_id))). \
outerjoin(race_value_map,
and_(race_value_map.source_concept_class == 'Race',
case([(and_(Person.race_concept_id == None,
race_value_map.source_concept_id == None), True)],
else_=cast(Person.race_concept_id, String(200)) ==
race_value_map.source_concept_id))).all()
# transform data to pcornet names and types
# load to demographic table
odo(person, Demographic.__table__,
dshape='var * {patid: string, birth_date: date, birth_time: string, sex: string,'
'hispanic: string, race: string, biobank_flag: string, raw_sex: string,'
'raw_hispanic: string, raw_race:string, site: string, gender_identity: string,'
'raw_gender_identity: string, sexual_orientation: string, raw_sexual_orientation: string}'
)
# close session
pedsnet_session.close()
示例9: _generate_stmt
def _generate_stmt(self, id) :
#print "@@ON ID", id
## ugggh for y_position
ledger_line = self.ledger_line
n_lines = self.n_lines
staff_space = self.staff_space
staff_symbol = self.staff_symbol
rhythmic_head_width = self.rhythmic_head_width
y_position = self.y_position
line_stencil = self.line_stencil
ledger_line_to_line_stencil = select([
ledger_line.c.id.label('id'),
literal('ledger_line_to_line_stencil').label('writer'),
literal(0).label('sub_id'),
literal(-0.6).label('x0'),
(case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y0'),
(rhythmic_head_width.c.val + 1.0).label('x1'),
(case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y1'),
literal(0.13).label('thickness')
]).\
where(safe_eq_comp(ledger_line.c.id, id)).\
where(func.abs(ledger_line.c.val) > 0).\
where(n_lines.c.id == staff_symbol.c.val).\
where(staff_space.c.id == staff_symbol.c.val).\
where(y_position.c.id == ledger_line.c.id).\
where(staff_symbol.c.id == ledger_line.c.id).\
where(rhythmic_head_width.c.id == staff_symbol.c.id).\
cte(name="ledger_line_to_line_stencil", recursive = True)
#where(safe_eq_comp(ledger_line.c.id, id))
self.register_stmt(ledger_line_to_line_stencil)
ledger_line_to_line_stencil_prev = ledger_line_to_line_stencil.alias(name="ledger_line_to_line_stencil_prev")
ledger_line_to_line_stencil = ledger_line_to_line_stencil.union_all(
select([
ledger_line_to_line_stencil_prev.c.id,
literal('ledger_line_to_line_stencil'),
ledger_line_to_line_stencil_prev.c.sub_id + 1,
ledger_line_to_line_stencil_prev.c.x0,
ledger_line_to_line_stencil_prev.c.y0 + (staff_space.c.val * - 1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)),
ledger_line_to_line_stencil_prev.c.x1,
ledger_line_to_line_stencil_prev.c.y1 + (staff_space.c.val * -1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)),
ledger_line_to_line_stencil_prev.c.thickness
]).\
where(staff_space.c.id == staff_symbol.c.val).\
where(staff_symbol.c.id == ledger_line_to_line_stencil_prev.c.id).\
where(ledger_line_to_line_stencil_prev.c.id == ledger_line.c.id).\
where(ledger_line_to_line_stencil_prev.c.sub_id < func.abs(ledger_line.c.val) - 1)
)
self.register_stmt(ledger_line_to_line_stencil)
self.insert = simple_insert(line_stencil, ledger_line_to_line_stencil)
示例10: _generate_stmt
def _generate_stmt(self, id) :
natural_stem_direction = self.natural_stem_direction
beam = self.beam
stem_direction = self.stem_direction
beam_specialize = self.beam_specialize
my_beam = select([
beam.c.val.label('beam')
]).where(safe_eq_comp(beam.c.id, id)).cte(name = 'my_beam')
self.register_stmt(my_beam)
others_beamed_with_me = select([
beam.c.id.label('id'),
beam.c.val.label('val')
]).where(beam.c.val == my_beam.c.beam).\
cte(name = 'others_beamed_with_me')
self.register_stmt(others_beamed_with_me)
prevailing_direction = select([
func.sum(natural_stem_direction.c.val).label('val'),
]).where(natural_stem_direction.c.id == others_beamed_with_me.c.id).\
cte(name="prevailing_direction")
self.register_stmt(prevailing_direction)
stem_direction_for_beams = select([
others_beamed_with_me.c.id.label('id'),
prevailing_direction.c.val.label('val'),
]).\
cte(name="stem_direction_for_beams")
self.register_stmt(stem_direction_for_beams)
natural_stem_direction_to_stem_direction = select([
natural_stem_direction.c.id.label('id'),
case([(stem_direction_for_beams.c.val != None,
case([(stem_direction_for_beams.c.val > 0, 1)], else_ = -1))],
else_ = natural_stem_direction.c.val).label('val')
]).select_from(natural_stem_direction.\
outerjoin(stem_direction_for_beams,
onclause=natural_stem_direction.c.id == stem_direction_for_beams.c.id)).\
where(safe_eq_comp(natural_stem_direction.c.id, id))
natural_stem_direction_to_stem_direction = natural_stem_direction_to_stem_direction.union(
select([
stem_direction_for_beams.c.id.label('id'),
case([(stem_direction_for_beams.c.val > 0, 1)], else_ = -1).label('val')
])).\
cte(name='natural_stem_direction_to_stem_direction')
self.register_stmt(natural_stem_direction_to_stem_direction)
self.insert = simple_insert(stem_direction, natural_stem_direction_to_stem_direction)
示例11: birth_date
def birth_date(cls):
year = cast(cls.year_of_birth, String)
month = cast(cls.month_of_birth, String)
day = cast(cls.day_of_birth, String)
month = case([(month == "", "01")],
else_=case([(func.length(month) == 1, "0" + month)], else_=month))
day = case([(day == "", "01")],
else_=case([(func.length(day) == 1, "0" + day)], else_=day))
return year + "-" + month + "-" + day
示例12: view_index
def view_index(session, request, lang=None):
""" Does the main index page for DDTSS, with list of languages and stats """
user = get_user(request, session)
if lang is None:
lang = user.lastlanguage_ref
if lang is None:
lang = 'xx'
if lang != 'xx':
return redirect('ddtss_index_lang', lang)
pending_translations = session.query(Languages, \
func.sum(expression.case([(PendingTranslation.state == PendingTranslation.STATE_PENDING_TRANSLATION, 1)], else_=0)), \
func.sum(expression.case([(PendingTranslation.state == PendingTranslation.STATE_PENDING_REVIEW, 1)], else_=0))) \
.outerjoin(PendingTranslation) \
.group_by(Languages) \
.all()
translated = session.query(Languages.language, func.count(Translation.description_id)) \
.join(Translation, Translation.language == Languages.language) \
.group_by(Languages.language) \
.all()
# Convert (lang,count) pairs to dict
translated = dict(translated)
# Combine into one resultset
languages = []
total_pending_translation = 0
total_pending_review = 0
total_translated = 0
for row in pending_translations:
languages.append(dict(language=row[0].language,
fullname=row[0].fullname,
enabled=row[0].enabled_ddtss,
pending_translation=row[1],
pending_review=row[2],
translated=translated.get(row[0].language, 0)))
total_pending_translation += row[1]
total_pending_review += row[2]
total_translated += translated.get(row[0].language, 0)
# Sort by translated descending
#languages.sort(key=lambda x:x['translated'], reverse=True)
return render_to_response("ddtss/index.html",
{'languages': languages,
'user': user,
'total_pending_translation': total_pending_translation,
'total_pending_review': total_pending_review,
'total_translated': total_translated},
context_instance=RequestContext(request))
示例13: get_datasets
def get_datasets(dataset_ids,**kwargs):
"""
Get a single dataset, by ID
"""
user_id = int(kwargs.get('user_id'))
datasets = []
if len(dataset_ids) == 0:
return []
try:
dataset_rs = DBSession.query(Dataset.dataset_id,
Dataset.data_type,
Dataset.data_units,
Dataset.data_dimen,
Dataset.data_name,
Dataset.hidden,
Dataset.cr_date,
Dataset.created_by,
DatasetOwner.user_id,
null().label('metadata'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.start_time).label('start_time'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.frequency).label('frequency'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.value).label('value')).filter(
Dataset.dataset_id.in_(dataset_ids)).outerjoin(DatasetOwner,
and_(DatasetOwner.dataset_id==Dataset.dataset_id,
DatasetOwner.user_id==user_id)).all()
#convert the value row into a string as it is returned as a binary
for dataset_row in dataset_rs:
dataset_dict = dataset_row._asdict()
if dataset_row.value is not None:
dataset_dict['value'] = str(dataset_row.value)
if dataset_row.hidden == 'N' or (dataset_row.hidden == 'Y' and dataset_row.user_id is not None):
metadata = DBSession.query(Metadata).filter(Metadata.dataset_id == dataset_row.dataset_id).all()
dataset_dict['metadata'] = metadata
else:
dataset_dict['metadata'] = []
datasets.append(namedtuple('Dataset', dataset_dict.keys())(**dataset_dict))
except NoResultFound:
raise ResourceNotFoundError("Datasets not found.")
return datasets
示例14: get_dataset
def get_dataset(dataset_id,**kwargs):
"""
Get a single dataset, by ID
"""
user_id = int(kwargs.get('user_id'))
if dataset_id is None:
return None
try:
dataset_rs = DBSession.query(Dataset.dataset_id,
Dataset.data_type,
Dataset.data_units,
Dataset.data_dimen,
Dataset.data_name,
Dataset.hidden,
Dataset.cr_date,
Dataset.created_by,
DatasetOwner.user_id,
null().label('metadata'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.start_time).label('start_time'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.frequency).label('frequency'),
case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)],
else_=Dataset.value).label('value')).filter(
Dataset.dataset_id==dataset_id).outerjoin(DatasetOwner,
and_(DatasetOwner.dataset_id==Dataset.dataset_id,
DatasetOwner.user_id==user_id)).one()
rs_dict = dataset_rs._asdict()
#convert the value row into a string as it is returned as a binary
if dataset_rs.value is not None:
rs_dict['value'] = str(dataset_rs.value)
if dataset_rs.hidden == 'N' or (dataset_rs.hidden == 'Y' and dataset_rs.user_id is not None):
metadata = DBSession.query(Metadata).filter(Metadata.dataset_id==dataset_id).all()
rs_dict['metadata'] = metadata
else:
rs_dict['metadata'] = []
except NoResultFound:
raise HydraError("Dataset %s does not exist."%(dataset_id))
dataset = namedtuple('Dataset', rs_dict.keys())(**rs_dict)
return dataset
示例15: leaderboard_query
def leaderboard_query(session, start_date, until_date):
"""
This is, admittedly, a really ugly sql query. Query optimization has not
been performed, but it shouldn't be anything more complicated than a few
indices. Good luck.
"""
#start_date = datetime.strptime(start_date, '%Y-%m-%d')
#until_date = datetime.strptime(until_date_str, '%Y-%m-%d')
subq = session\
.query(
Instance,
InstanceType,
User,
case([(Instance.end_date != None, Instance.end_date)], else_=now()).label('stop_date'))\
.join(Instance.user)\
.join(Instance.type)\
.subquery()
uptime_column = case(
[
(subq.c.created_date > until_date, 0),
(subq.c.stop_date < start_date, 0)
],
else_=extract('epoch',
func.LEAST(subq.c.stop_date, cast(until_date, DateTime)) -
func.GREATEST(subq.c.created_date, cast(start_date, DateTime))
)
)
print subq.c
subq2 = session.query(
subq.c.user_id,
sum(case([(uptime_column == 0, 0)], else_=1)).label('instance_count'),
#func.count(subq.c.instance_id).label('instance_count'),
sum(uptime_column).label('uptime'),
sum(uptime_column * subq.c.cpu).label('cpu_seconds')
).group_by(subq.c.user_id).order_by(desc('cpu_seconds')).subquery()
q = session.query(
subq2.c.user_id,
subq2.c.uptime,
subq2.c.cpu_seconds,
subq2.c.instance_count,
User.username,
User.is_staff,
User.name
).join(User)
return q