Python expression.case函数代码示例

 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

    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

  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)

  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)

  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

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)

  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)

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

  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)

  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)

    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

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

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

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

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