Python sql.exists函数代码示例

 def __eq__(self, other):
     if other is None:
         if self.prop.direction in [ONETOMANY, MANYTOMANY]:
             return ~sql.exists([1], self.prop.primaryjoin)
         else:
             return self.prop._optimized_compare(None)
     elif self.prop.uselist:
         if not hasattr(other, "__iter__"):
             raise exceptions.InvalidRequestError(
                 "Can only compare a collection to an iterable object.  Use contains()."
             )
         else:
             j = self.prop.primaryjoin
             if self.prop.secondaryjoin:
                 j = j & self.prop.secondaryjoin
             clauses = []
             for o in other:
                 clauses.append(
                     sql.exists(
                         [1],
                         j
                         & sql.and_(
                             *[
                                 x == y
                                 for (x, y) in zip(
                                     self.prop.mapper.primary_key, self.prop.mapper.primary_key_from_instance(o)
                                 )
                             ]
                         ),
                     )
                 )
             return sql.and_(*clauses)
     else:
         return self.prop._optimized_compare(other)

def member_join_post(db):
    nodes = [ (n.id,n.node_name) for n in db.query(models.SlcNode)]
    form = forms.member_join_form(nodes)
    if not form.validates(source=request.params):
        return render("join", form=form)    
        
    if db.query(exists().where(models.SlcMember.member_name == form.d.username)).scalar():
        return render("join",form=form,msg=u"用户{0}已被使用".format(form.d.username))
        
    if db.query(exists().where(models.SlcMember.email == form.d.email)).scalar():
        return render("join",form=form,msg=u"用户邮箱{0}已被使用".format(form.d.email))
    
    member = models.SlcMember()
    member.node_id = form.d.node_id
    member.realname = form.d.realname
    member.member_name = form.d.username
    member.password = md5(form.d.password.encode()).hexdigest()
    member.idcard = form.d.idcard
    member.sex = form.d.sex
    member.age = int(form.d.age)
    member.email = form.d.email
    member.mobile = form.d.mobile
    member.address = form.d.address
    member.create_time = utils.get_currtime()
    member.update_time = utils.get_currtime()
    db.add(member) 
    db.commit()
   
    logger.info(u"新用户注册成功,member_name=%s"%member.member_name)
    redirect('/login')

    def _create_debile_binaries(self, session, source, pkg):
        arch_all = session.query(Arch).filter(Arch.name == "all").one()
        arches = session.query(Arch).filter(Arch.name.in_(pkg.installed_archs)).all()

        if arch_all in source.arches and arch_all not in arches and source.affinity in arches:
            if not session.query(
                exists().where((Job.source == source) & (Job.arch == arch_all) & Job.check.has(Check.build == True))
            ).scalar():
                # We have the arch:affinity binary but is still lacking the arch:all binary
                # Make sure debile builds the arch:all binary separately
                check = session.query(Check).filter(Check.build == True).one()
                job = Job(check=check, arch=arch_all, source=source, binary=None)
                session.add(job)

        for arch in arches:
            if session.query(exists().where((Binary.source == source) & (Binary.arch == arch))).scalar():
                continue

            # Find the job for this binary
            job = (
                session.query(Job)
                .join(Job.check)
                .filter(Job.source == source, Job.arch == arch, Check.build == True)
                .first()
            )

            if not job and arch == arch_all and source.affinity in arches:
                # The arch:all binary might have been created by the arch:affinity build job.
                job = (
                    session.query(Job)
                    .join(Job.check)
                    .filter(Job.source == source, Job.arch == source.affinity, Check.build == True)
                    .first()
                )

            if job and (not job.finished_at or job.failed is True):
                # Dak accepted a binary upload that debile-master didn't ask for
                if arch != arch_all and not any(job.built_binaries):
                    session.delete(job)
                job = None

            if job:
                binary = job.new_binary(arch)
            else:
                binary = Binary(source=source, arch=arch, uploaded_at=datetime.utcnow())
            session.add(binary)

            for name, arch, filename in pkg.binaries:
                if arch == binary.arch.name:
                    directory, _, filename = filename.rpartition("/")
                    deb = Deb(binary=binary, directory=directory, filename=filename)
                    session.add(deb)

            print("Created binary for %s %s on %s" % (binary.name, binary.version, binary.arch))
            emit("accept", "binary", binary.debilize())

def q_where_exists():
    from sqlalchemy.sql import exists

    stmt = exists().where(Image.owner_id == User.id)
    for (name,) in session.query(User.name).filter(stmt):
        print name
    stmt = exists().where(User.name == "not exists")
    for (name,) in session.query(User.name).filter(stmt):
        print name
    else:
        print "not exists"

def add_application(obj, override=True, temp=False):
    """
    PatentApplication Object converting to tables via SQLAlchemy
    Necessary to convert dates to datetime because of SQLite (OK on MySQL)

    Case Sensitivity and Table Reflection
    MySQL has inconsistent support for case-sensitive identifier names,
    basing support on specific details of the underlying operating system.
    However, it has been observed that no matter what case sensitivity
    behavior is present, the names of tables in foreign key declarations
    are always received from the database as all-lower case, making it
    impossible to accurately reflect a schema where inter-related tables
    use mixed-case identifier names.

    Therefore it is strongly advised that table names be declared as all
    lower case both within SQLAlchemy as well as on the MySQL database
    itself, especially if database reflection features are to be used.
    """

    # if the application exists, remove it so we can replace it
    (app_exists, ), = appsession.query(exists().where(schema.App_Application.number == obj.application))
    if app_exists:
        if override:
            app_query = appsession.query(schema.App_Application).filter(schema.App_Application.number == obj.application)
            appsession.delete(app_query.one())
        else:
            return
    if len(obj.app["number"]) < 3:
        return

    app = schema.App_Application(**obj.app)
    # lots of abstracts seem to be missing. why?
    add_all_app_fields(obj, app)

    appsession.merge(app)

def start(session):
    print("Dividing numbers rounds them to second decimal!")
    points = 0
    username = input("Enter your playername>")
    user = User(name=username, score=points)
    print("Welcome {}! Let the game begin!".format(username))
    last_answer_correct = True
    while(last_answer_correct):
        expression = Expression.generate_expression()
        print("What is the answer to {} {} {}".format(
            expression[0], expression[2], expression[1]))
        answer = input("?>")
        if(float(answer) == expression[3]):
            print("?>Correct!")
            points += 1
        else:
            score = calculate_score(points)
            print("Incorrect! Ending game. You score is: {}".format(score))
            last_answer_correct = False
            if user.score < score:
                user.score = score
                session.query(User).filter(User.name==username).update({"score": score})
    if(session.query(exists().where(User.name == username)).scalar() == 0):
        session.add(user)
    session.commit()

def insert_flow_data(**kwargs):
    """
    Inserts flow cytometry data, read from csv files. Creates a
    flowproject record if one does not already exist for the data to
    be inserted, and then creates a related flowdata record.
    """
    session = Session()
    # print(kwargs)
    if 'flowproject_name' in kwargs:
        proj_exists = session.query(exists().where(FlowProject.project_name==kwargs['flowproject_name'])).scalar()
        if(proj_exists == False): # we don't have this project yet, so create it.
            newFlowproj = FlowProject()
            newFlowproj.project_name = kwargs['flowproject_name']
            session.add(newFlowproj)
            logging.info("Adding new flowproject {}.".format(kwargs['flowproject_name']))
        newFlowdata = FlowData(**kwargs)
        session.add(newFlowdata)
        try:
            session.commit()
        except IntegrityError as e:
            reason = e.message
            logging.error("Error committing new FlowProject record: {}".format(reason))
            session.rollback()

    else:
        # should alert the user here too... someday
        logging.error("Function 'insert_flow_data' requires a flowproject_name parameter.")

    def test_conditional_update_force_order(self):
        volume = self._create_volume()

        has_snapshot_filter = sql.exists().where(
            models.Snapshot.volume_id == models.Volume.id)

        case_values = volume.Case([(has_snapshot_filter, 'has-snapshot')],
                                  else_='no-snapshot')

        values = {'status': 'deleting',
                  'previous_status': volume.model.status,
                  'migration_status': case_values}

        order = ['status']

        with mock.patch('cinder.db.sqlalchemy.api.model_query') as model_query:
            update = model_query.return_value.filter.return_value.update
            update.return_value = 0
            self.assertFalse(volume.conditional_update(
                values, {'status': 'available'}, order=order))

        # We check that we are passing values to update to SQLAlchemy in the
        # right order
        self.assertEqual(1, update.call_count)
        self.assertListEqual(
            [('status', 'deleting'),
             ('previous_status', volume.model.status),
             ('migration_status', mock.ANY)],
            list(update.call_args[0][0]))
        self.assertDictEqual(
            {'synchronize_session': False,
             'update_args': {'preserve_parameter_order': True}},
            update.call_args[1])

    def add_cacti_result(self, name, access_time, cycle_time, area):
        """Add a CACTI result."""

        # Insert into the local cache.
        name_hash = self.get_hash(name)
        self.cacti_results[name_hash] = (access_time, cycle_time, area)

        # Insert into the database.
        stmt = cacti_results_table.insert().from_select([
                cacti_results_table.c.name_hash,
                cacti_results_table.c.name,
                cacti_results_table.c.area,
                cacti_results_table.c.access_time,
                cacti_results_table.c.cycle_time,
            ], select([
                literal(name_hash),
                literal(str(name)),
                literal(area),
                literal(access_time),
                literal(cycle_time),
            ]).where(
                ~exists([cacti_results_table.c.name_hash]).where(
                    cacti_results_table.c.name_hash == name_hash
                )
            )
        )
        self._execute(stmt)
        return True

    def add_score(self, mod, mem, full, score):

        # Update the cache.
        s = str(mod) + str(mem)
        if full:
            s += ':full'
        score_hash = self.get_hash(s)
        self.scores[score_hash] = score

        # Update the database.
        mod_id = self._get_model_id(mod)
        stmt = scores_table.insert().from_select([
                scores_table.c.model_id,
                scores_table.c.score_hash,
                scores_table.c.score,
            ], select([
                literal(mod_id),
                literal(score_hash),
                literal(score),
            ]).where(
                ~exists([scores_table.c.score]).where(
                    scores_table.c.score_hash == score_hash
                )
            )
        )
        self._execute(stmt)
        return True

    def gets_show_in_firstvalid(cls, session, provider_id=None, hotel_name=None, city_id=None, start=0,
                                limit=20, status=-1):
        from models.room_type_mapping import RoomTypeMappingModel

        stmt = exists().where(and_(HotelMappingModel.provider_id == RoomTypeMappingModel.provider_id,
                HotelMappingModel.provider_hotel_id == RoomTypeMappingModel.provider_hotel_id,
                RoomTypeMappingModel.status == cls.STATUS.wait_first_valid,
                RoomTypeMappingModel.is_delete == 0))

        query = session.query(HotelMappingModel)
        if provider_id:
            query = query.filter(HotelMappingModel.provider_id == provider_id)
        if city_id:
            query = query.filter(HotelMappingModel.city_id == city_id)
        if hotel_name:
            query = query.filter(HotelMappingModel.provider_hotel_name.like(u'%{}%'.format(hotel_name)))
        if status != -1:
            query = query.filter(HotelMappingModel.status == status)

        query = query.filter(HotelMappingModel.provider_id != 6)\
            .filter(HotelMappingModel.is_delete == 0)\
            .filter(HotelMappingModel.status != HotelMappingModel.STATUS.init)
        # if status == -1:
        query = query.filter(or_(stmt, HotelMappingModel.status != HotelMappingModel.STATUS.init))
        # else:
        #         query = query.filter(and_(stmt))

        r = query.offset(start).limit(limit).all()
        total = query.count()

        return r, total

def add_addresses(session):
    """ Add an AddressAssignment record for every PrimaryNameAssociation """
    q = session.query(PrimaryNameAssociation)
    q = q.join(System, DnsDomain)
    q = q.filter(System.ip != None)
    q = q.filter(~exists().where(AddressAssignment.ip == System.ip))
    q = q.options(contains_eager('dns_record'))
    q = q.options(contains_eager('dns_record.dns_domain'))
    q = q.options(subqueryload_all('hardware_entity.interfaces.vlans.assignments'))
    q = q.options(subqueryload_all('hardware_entity.interfaces._vlan_ids'))

    count = 0
    pnas = q.all()
    for pna in pnas:
        hw = pna.hardware_entity
        if len(hw.interfaces) != 1:
            print "{0} has an unexpected number of interfaces, skipping: " \
                    "{1}".format(hw, len(hw.interfaces))
            continue
        iface = hw.interfaces[0]
        if len(iface.vlans[0].addresses):
            print "{0} already has addresses, skipping".format(iface)
            continue
        #print "Adding AddressAssignment record for {0:l}".format(hw)
        iface.vlans[0].addresses.append(pna.dns_record.ip)
        count += 1

    session.flush()
    print "Added %d AddressAssignment records" % count

    def gets_show_in_ebooking(cls, session, hotel_name=None, city_id=None, merchant_ids=None, is_new=None, start=0, limit=20):
        query = session.query(HotelMappingModel)\
                .filter(HotelMappingModel.is_delete == 0)\
                .filter(HotelMappingModel.provider_id == 6,
                        HotelMappingModel.status == cls.STATUS.valid_complete)

        if is_new is not None:
            from models.room_type_mapping import RoomTypeMappingModel
            stmt = exists().where(and_(HotelMappingModel.provider_id == RoomTypeMappingModel.provider_id,
                        HotelMappingModel.provider_hotel_id == RoomTypeMappingModel.provider_hotel_id,
                        RoomTypeMappingModel.is_new == 1,
                        RoomTypeMappingModel.is_delete == 0))
            query = query.filter(or_(stmt, HotelMappingModel.is_new == is_new))

        if city_id:
            query = query.filter(HotelMappingModel.city_id == city_id)
        if merchant_ids is not None:
            query = query.filter(HotelMappingModel.merchant_id.in_(merchant_ids))
        if hotel_name:
            query = query.filter(HotelMappingModel.provider_hotel_name.like(u'%{}%'.format(hotel_name)))

        r = query.order_by(HotelMappingModel.id.desc()).offset(start).limit(limit).all()
        total = query.count()

        return r, total

def add_interfaces(session):
    """ Add a default interface for all HW that has an IP """
    q = session.query(HardwareEntity)
    q = q.filter(~exists().where(Interface.hardware_entity_id == HardwareEntity.id))
    q = q.outerjoin(PrimaryNameAssociation, System, DnsDomain)
    q = q.options(contains_eager('_primary_name_asc'))
    q = q.options(contains_eager('_primary_name_asc.dns_record'))
    q = q.options(contains_eager('_primary_name_asc.dns_record.dns_domain'))
    q = q.filter(System.ip != None)

    hws = q.all()
    count = 0
    for hw in hws:
        if hw.hardware_type == "machine":
            interface = "eth0"
            itype = "public"
        elif hw.hardware_type == "switch":
            interface = "xge"
            itype = "oa"
        else:
            interface = "oa"
            itype = "oa"

        #print "Adding default interface for {0:l}".format(hw)

        dbinterface = Interface(hardware_entity=hw, name=interface,
                                interface_type="oa",
                                comments="Created automatically by upgrade script")
        session.add(dbinterface)
        count += 1

    session.flush()
    print "Added %d interfaces" % count

    def load(self, bed_file, alt_id=None, group_id='unknown', update=False):
        """Populate coverage data into a chanjo database.

        Args:
            bed_file (str): path to "chanjo annotate" BED output
            alt_id (str): sample id to switch to
            group_id (str, optional): group id for added verbosity
        """
        with codecs.open(bed_file, 'r', encoding='utf-8') as handle:
            if alt_id:
                bed_stream = switch_ids(alt_id, handle)
            else:
                bed_stream = handle

            logger.debug('check if coverage data already exists')
            exists_query = exists().where(Sample.id == alt_id)
            is_loaded = (self.db.query(exists_query).scalar())
            if is_loaded:
                logger.warn("Coverage already loaded for %s: %s",
                            group_id, bed_file)
                if update:
                    logger.debug('remove existing coverage data')
                    self._delete_sample(alt_id)
                else:
                    return

            import_bed(self.db, bed_stream)