Python expression.exists函数代码示例

 def __nonzero__(self):
     session = Session.object_session(self.instance)
     if session is None:
         return bool(self.count())
     for v, in session.query(exists(self.as_scalar())):
         return bool(v)
     return False

def get_user_pending_contact_event(orm, user, contact_id):
    Event_v_all = aliased(Event_v)
    Event_v_new = aliased(Event_v)

    query = orm.query(Event_v_all) \
        .outerjoin((
            Event,
            Event.event_id == Event_v_all.event_id
            )) \
        .join((
            event_contact_v,
            and_(
                event_contact_v.c.event_id == Event_v_all.event_id,
                event_contact_v.c.contact_id == contact_id,
                event_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Event_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Event_v_new.event_id == Event_v_all.event_id,
                Event_v_new.a_time > Event_v_all.a_time,
            )),
            or_(
                Event.a_time == None,
                Event_v_all.a_time > Event.a_time,
            )
        ) \
        .order_by(Event_v_all.a_time.desc()) \

    return query.all()

def get_user_pending_org_address(orm, user, org_id):
    Address_v_all = aliased(Address_v)
    Address_v_new = aliased(Address_v)

    query = orm.query(Address_v_all) \
        .outerjoin((
            Address,
            Address.address_id == Address_v_all.address_id
        )) \
        .join((
            org_address_v,
            and_(
                org_address_v.c.address_id == Address_v_all.address_id,
                org_address_v.c.org_id == org_id,
                org_address_v.c.existence == 1,
            )
        )) \
        .filter(
            Address_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Address_v_new.address_id == Address_v_all.address_id,
                Address_v_new.a_time > Address_v_all.a_time,
            )),
            or_(
                Address.a_time == None,
                Address_v_all.a_time > Address.a_time,
            )
        ) \
        .order_by(Address_v_all.a_time.desc())

    return query.all()

def get_user_pending_contact_org(orm, user, contact_id):
    Org_v_all = aliased(Org_v)
    Org_v_new = aliased(Org_v)

    query = orm.query(Org_v_all) \
        .outerjoin((
            Org,
            Org.org_id == Org_v_all.org_id
            )) \
        .join((
            org_contact_v,
            and_(
                org_contact_v.c.org_id == Org_v_all.org_id,
                org_contact_v.c.contact_id == contact_id,
                org_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Org_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Org_v_new.org_id == Org_v_all.org_id,
                Org_v_new.a_time > Org_v_all.a_time,
            )),
            or_(
                Org.a_time == None,
                Org_v_all.a_time > Org.a_time,
            )
        ) \
        .order_by(Org_v_all.a_time.desc()) \

    return query.all()

    def query_multiple(
            self,
            data,
            filter_col="piece.id",
            table="clefs_ins_piece"):
        if self.validate_table(table):
            _table = self.tables[table]
            _filter_col = getattr(_table.columns, filter_col)
            q = select([_filter_col])
            for elem in data:
                query = _table.select()
                nxtalias = alias(_table)
                for key in elem:
                    col = getattr(nxtalias.columns, key)
                    expr = self.mk_or_expr(elem[key], col)
                    query = query.where(expr)

                alias_filter = getattr(nxtalias.columns, filter_col)
                query = query.where(alias_filter == _filter_col)
                q = q.where(exists(query))

            result_prox = self.execute(q)
            return set([elem[0] for elem in result_prox])
        else:
            raise BadTableException(
                "table {} not in {}".format(
                    table, self.tables.keys()))

    def _buildQuery(self, languageId=None, userId=None, q=None):
        '''
        Builds the general query for blogs.
        '''
        sql = self.session().query(BlogMapped)
        #Hide deleted blogs
        sql = sql.filter(BlogMapped.DeletedOn == None)
        
        if languageId: sql = sql.filter(BlogMapped.Language == languageId)
        if userId:
            #TODO: change it for the new version of Ally-Py, where it is a complete implementation of security 
            qRole = QRole()
            qRole.name = self.admin_role
            isAdmin = len(self.roleService.getRoles(userId, q=qRole))
            if not isAdmin:
                userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \
                                             & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                             & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
                sql = sql.filter(userFilter)

        if q:
            assert isinstance(q, QBlog), 'Invalid query %s' % q
            sql = buildQuery(sql, q, BlogMapped)

            if (QBlog.isOpen in q) and (AsBoolean.value in q.isOpen):
                if q.isOpen.value:
                    sql = sql.filter(BlogMapped.ClosedOn == None)
                else:
                    sql = sql.filter(BlogMapped.ClosedOn != None)

        return sql

def project_exists(*filter_params):
    from sqlalchemy.sql.expression import select, exists
    session = DBSession()
    project_query = session.query(Project).filter_by(*filter_params)
    project_exists_select = select((exists(project_query.statement),))
    engine = Base.metadata.bind
    return engine.execute(project_exists_select).scalar()

    def handle(self, *args, **options):
        # set up
        config = get_config()
        if config is None:
            raise CommandError('Unable to process configuration file p_to_p.yml')

        connection = get_connection(config)
        pedsnet_session = init_pedsnet(connection)
        init_pcornet(connection)

        observation_period = pedsnet_session.query(ObservationPeriod.person_id,
                                                   ObservationPeriod.observation_period_start_date,
                                                   ObservationPeriod.observation_period_end_date,
                                                   ObservationPeriod.site,
                                                   bindparam("chart", 'Y'),
                                                   bindparam("enr_basis", 'E')
                                                   ).filter(
            exists().where(ObservationPeriod.person_id == PersonVisit.person_id)).all()

        odo(observation_period, Enrollment.__table__,
            dshape='var * {patid: string, enr_start_date: date, enr_end_date: date, site: string, chart: String, '
                   'enr_basis: String} '
            )
        # close session
        pedsnet_session.close()

        # ouutput result
        self.stdout.ending = ''
        print('Enrollment ETL completed successfully', end='', file=self.stdout)

def get_user_pending_event_contact(orm, user, event_id):
    Contact_v_all = aliased(Contact_v)
    Contact_v_new = aliased(Contact_v)

    query = orm.query(Contact_v_all) \
        .outerjoin((
            Contact,
            Contact.contact_id == Contact_v_all.contact_id
            )) \
        .join((
            event_contact_v,
            and_(
                event_contact_v.c.contact_id == Contact_v_all.contact_id,
                event_contact_v.c.event_id == event_id,
                event_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Contact_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Contact_v_new.contact_id == Contact_v_all.contact_id,
                Contact_v_new.a_time > Contact_v_all.a_time,
            )),
            or_(
                Contact.a_time == None,
                Contact_v_all.a_time > Contact.a_time,
            )
        ) \
        .order_by(Contact_v_all.a_time.desc())

    return query.all()

    def delete_orphaned(cls):
        with new_session() as session:
            subquery = select([ErrorReport.id])\
                .where(ErrorReport.item_id == Item.id)\
                .limit(1)

            query = delete(ErrorReport).where(~exists(subquery))
            session.execute(query)

 def is_coordinator(self):
     ## circular import :/
     from intranet3.models import Project, Client
     is_coordinator = DBSession.query(exists().where(or_(
         Client.coordinator_id==self.id,
         Project.coordinator_id==self.id
     ))).scalar()
     return is_coordinator

 def where_clause_fn(id) :
   beam_me = beam.alias(name='beam_me')
   beam_other = beam.alias(name='beam_other')
   stmt = select([beam_other.c.id]).\
      where(and_(stem_direction.c.id == beam_other.c.id,
         beam_other.c.val == beam_me.c.val,
         beam_me.c.id == id))
   return exists(stmt)

 def where_clause_fn(id) :
   stmt = select([literal(id).label('id')]).cte(name="anchors", recursive=True)
   stmt_prev = stmt.alias(name='stmt_prev')
   stmt = stmt.union_all(
     select([
       anchor.c.id
     ]).where(anchor.c.val == stmt_prev.c.id)
   )
   return exists(select([stmt.c.id]).where(anchored_table.c.id == stmt.c.id))

 def _has_permission(self, user_id, outing_id):
     """Check if the user with the given id has permission to change an
     outing. That is only users that are currently assigned to the outing
     can modify it.
     """
     return DBSession.query(exists().where(
         and_(
             Association.parent_document_id == user_id,
             Association.child_document_id == outing_id
         ))).scalar()

  def pushDB(self, db, domain_id, rr, value):
    rr = filter_rrtype[rr]

    for ttl, v in self.ttls.items():
      oid = None

      try:
        q = db.query(Entry)
        q = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value))
        q.update({Entry.first_seen: case([(Entry.first_seen > v[1], v[1])], else_=Entry.first_seen),
                  Entry.last_seen: case([(Entry.last_seen < v[2], v[2])], else_=Entry.last_seen),
                  Entry.count: Entry.count + v[0]}, synchronize_session=False)

        q = db.query(Entry.entry_id)
        o = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)).one()

        oid = o[0]
      except NoResultFound:
        entry = Entry()
        entry.domain_id = domain_id
        entry.type = rr
        entry.ttl = ttl
        entry.value = value
        entry.first_seen = v[1]
        entry.last_seen = v[2]
        entry.count = v[0]
        db.add(entry)
        db.flush()

        oid = entry.entry_id

      for s, sv in v[3].items():
        try:
          q = db.query(exists().where((and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)))).scalar()

          if not q:
            raise NoResultFound('')

          q = db.query(DNS_Server)
          q = q.filter(and_(DNS_Server.entry_id == oid, DNS_Server.ip == s))
          q.update({DNS_Server.first_seen: case([(DNS_Server.first_seen > sv[1], sv[1])], else_=DNS_Server.first_seen),
                    DNS_Server.last_seen: case([(DNS_Server.last_seen < sv[2], sv[2])], else_=DNS_Server.last_seen),
                    DNS_Server.count: DNS_Server.count + sv[0]}, synchronize_session=False)

        except NoResultFound:
          dns_server = DNS_Server()
          dns_server.entry_id = oid
          dns_server.ip = s
          dns_server.first_seen = sv[1]
          dns_server.last_seen = sv[2]
          dns_server.count = sv[0]
          db.add(dns_server)
          db.flush()

    db.flush()