Python elements.and_函数代码示例

def update_deployments(refdes, dnum, uid, launch, recover, lat, lon, cruise, depth, session):
    # print
    # print
    # print refdes, dnum, uid, launch, recover, lat, lon, cruise, depth
    deployments = session.query(Deployment).filter(and_(Deployment.sensor_uid == uid,
                                                        Deployment.deployment == dnum)).all()

    deployments += session.query(Deployment).filter(and_(Deployment.mooring_uid == uid,
                                                         Deployment.deployment == dnum)).all()

    # for each in deployments:
    #     print 'depl', each

    if deployments:
        for d in deployments:
            d.launch_date = launch
            d.recover_date = recover
            d.latitude = lat
            d.longitude = lon
            d.cruise = cruise
            d.depth = depth
            session.add(d)

    else:
        d = Deployment(refdes=refdes, deployment=dnum, launch_date=launch, recover_date=recover,
                       latitude=lat, longitude=lon, cruise=cruise, depth=depth, mooring_uid=uid)
        session.add(d)

    session.flush()

def get_advanced_search_query(employer_id, params, status):
    skills = params.get('skills')
    locations = params.get('locations')
    role = params.get('role')
    name = params.get('name')
    salary = params.get('salary')

    query = DBSession.query(Candidate.id).filter(Candidate.status == status)

    if employer_id:
        query = query.outerjoin(V_CANDIDATE_CURRENT_EMPLOYERS,
                                and_(V_CANDIDATE_CURRENT_EMPLOYERS.c.candidate_id == Candidate.id,
                                     V_CANDIDATE_CURRENT_EMPLOYERS.c.employer_id == employer_id)) \
            .filter(V_CANDIDATE_CURRENT_EMPLOYERS.c.candidate_id == None)

    if locations:
        query = query.join(PreferredLocation, Candidate.id == PreferredLocation.candidate_id)

        country_filter = set([c['country_iso'] for c in locations])
        city_filter = [and_(City.name == loc['city'], City.country_iso == loc['country_iso']) for loc in locations]
        city_ids = DBSession.query(City.id).filter(or_(*city_filter)).all()

        query = query.filter(or_(PreferredLocation.city_id.in_(city_ids),
                                 PreferredLocation.country_iso.in_(country_filter)))

    if salary or role:
        query = query.join(TargetPosition)
        if salary:
            query = query.filter(TargetPosition.minimum_salary <= salary)
        if role:
            role = get_by_name_or_raise(Role, role)
            query = query.filter(TargetPosition.role_id == role.id)

    if name and employer_id:
        name = name.lower()
        employer_ids = func.array_agg(Offer.employer_id, type_=ARRAY(TEXT)).label('employer_ids')
        offer_query = DBSession.query(Offer.candidate_id, employer_ids).filter(Offer.accepted != None) \
            .group_by(Offer.candidate_id).subquery()
        query = query.outerjoin(offer_query, offer_query.c.candidate_id == Candidate.id).filter(
            or_(cast(Candidate.id, TEXT).startswith(name),
                and_(
                    or_(func.lower(Candidate.first_name).startswith(name),
                        func.lower(Candidate.last_name).startswith(name)),
                    or_(
                        offer_query.c.employer_ids.any(str(employer_id)),
                        Candidate.anonymous == False
                    )
                )
            )
        )

    query = query.group_by(Candidate.id)

    if skills:
        query = query.join(CandidateSkill).join(Skill).filter(Skill.name.in_(skills)) \
            .having(func.count(Skill.name) == len(skills))
    return query

    def _entry_query(self, session, entry):
        db_entry = session.query(EntryListEntry).filter(and_(
            EntryListEntry.list_id == self._db_list(session).id,
            or_(
                EntryListEntry.title == entry['title'], and_(
                    EntryListEntry.original_url,
                    EntryListEntry.original_url == entry[
                        'original_url'])))).first()

        return db_entry

def _to_bool_clause(constraint):
    if constraint is not None:
        if isinstance(constraint, dict):
            return and_(column(k) == v for k, v in constraint.items())
        else:
            return or_(and_(_convert_predicate(predicate)
                            for predicate in conjunction_clause)
                       for conjunction_clause in constraint)
    else:
        return text('')

    def filter_query_for_content_label_as_path(
            self,
            query: Query,
            content_label_as_file: str,
            is_case_sensitive: bool = False,
    ) -> Query:
        """
        Apply normalised filters to found Content corresponding as given label.
        :param query: query to modify
        :param content_label_as_file: label in this
        FILE version, use Content.get_label_as_file().
        :param is_case_sensitive: Take care about case or not
        :return: modified query
        """
        file_name, file_extension = os.path.splitext(content_label_as_file)

        label_filter = Content.label == content_label_as_file
        file_name_filter = Content.label == file_name
        file_extension_filter = Content.file_extension == file_extension

        if not is_case_sensitive:
            label_filter = func.lower(Content.label) == \
                           func.lower(content_label_as_file)
            file_name_filter = func.lower(Content.label) == \
                               func.lower(file_name)
            file_extension_filter = func.lower(Content.file_extension) == \
                                    func.lower(file_extension)

        return query.filter(or_(
            and_(
                Content.type == ContentType.File,
                file_name_filter,
                file_extension_filter,
            ),
            and_(
                Content.type == ContentType.Thread,
                file_name_filter,
                file_extension_filter,
            ),
            and_(
                Content.type == ContentType.Page,
                file_name_filter,
                file_extension_filter,
            ),
            and_(
                Content.type == ContentType.Folder,
                label_filter,
            ),
        ))

    def __real_base_query(self, workspace: Workspace=None):
        result = self.get_canonical_query()

        # Exclude non displayable types
        if not self._force_show_all_types:
            result = result.filter(Content.type.in_(self.DISPLAYABLE_CONTENTS))

        if workspace:
            result = result.filter(Content.workspace_id==workspace.workspace_id)

        # Security layer: if user provided, filter
        # with user workspaces privileges
        if self._user and not self._disable_user_workspaces_filter:
            user = DBSession.query(User).get(self._user_id)
            # Filter according to user workspaces
            workspace_ids = [r.workspace_id for r in user.roles \
                             if r.role>=UserRoleInWorkspace.READER]
            result = result.filter(or_(
                Content.workspace_id.in_(workspace_ids),
                # And allow access to non workspace document when he is owner
                and_(
                    Content.workspace_id == None,
                    Content.owner_id == self._user_id,
                )
            ))

        return result

 def _find_entry(self, entry, session=None):
     """Finds `MovieListMovie` corresponding to this entry, if it exists."""
     for id_name in SUPPORTED_IDS:
         if entry.get(id_name):
             log.debug('trying to match movie based off id %s: %s', id_name, entry[id_name])
             res = (self._db_list(session).movies.join(MovieListMovie.ids).filter(
                 and_(
                     MovieListID.id_name == id_name,
                     MovieListID.id_value == entry[id_name]))
                    .first())
             if res:
                 log.debug('found movie %s', res)
                 return res
     # Fall back to title/year match
     if entry.get('movie_name') and entry.get('movie_year'):
         name, year = entry['movie_name'], entry['movie_year']
     else:
         name, year = split_title_year(entry['title'])
     if not name:
         log.verbose('no movie name to match, skipping')
         return
     log.debug('trying to match movie based of name: %s and year: %d', name, year)
     res = (self._db_list(session).movies.filter(func.lower(MovieListMovie.title) == name.lower())
            .filter(MovieListMovie.year == year).first())
     if res:
         log.debug('found movie %s', res)
     return res

def get_movie_by_id(list_id, movie_id, session=None):
    log.debug('fetching movie with id %d from list id %d', movie_id, list_id)
    return (
        session.query(MovieListMovie)
        .filter(and_(MovieListMovie.id == movie_id, MovieListMovie.list_id == list_id))
        .one()
    )

def value_combo_exists(value, **kwargs):
    """ Queries the database for any values in the unique store that alrdeady exist with given value and metadata combo
    """
    kwargs['unique_value'] = {kwargs['field_name']: value}
    del kwargs['field_name']
    filters = [(getitem(Unique, arg_name) == arg_val) for arg_name, arg_val in kwargs.items()]
    return DB.session.query(Unique.query.exists().where(and_(*filters))).scalar()

def get_entry_by_id(list_id, entry_id, session=None):
    log.debug('fetching entry with id %d from list id %d', entry_id, list_id)
    return (
        session.query(EntryListEntry)
        .filter(and_(EntryListEntry.id == entry_id, EntryListEntry.list_id == list_id))
        .one()
    )

 def _entry_query(self, session, entry, approved=None):
     query = session.query(PendingListEntry).filter(PendingListEntry.list_id == self._db_list(session).id). \
         filter(or_(PendingListEntry.title == entry['title'],
                    and_(PendingListEntry.original_url, PendingListEntry.original_url == entry['original_url'])))
     if approved:
         query = query.filter(PendingListEntry.approved == True)
     return query.first()

 def _find_entry(self, entry, session=None):
     """Finds `MovieListMovie` corresponding to this entry, if it exists."""
     for id_name in MovieListBase().supported_ids:
         if entry.get(id_name):
             log.verbose('trying to match movie based off id %s: %s', id_name, entry[id_name])
             res = (self._db_list(session).movies.join(MovieListMovie.ids).filter(
                 and_(
                     MovieListID.id_name == id_name,
                     MovieListID.id_value == entry[id_name]))
                    .first())
             if res:
                 log.verbose('found movie %s', res)
                 return res
     # Fall back to title/year match
     if not entry.get('movie_name'):
         self._parse_title(entry)
     if entry.get('movie_name'):
         name = entry['movie_name']
         year = entry.get('movie_year') if entry.get('movie_year') else None
     else:
         log.warning('Could not get a movie name, skipping')
         return
     log.verbose('trying to match movie based of name: %s and year: %s', name, year)
     res = (self._db_list(session).movies.filter(func.lower(MovieListMovie.title) == name.lower())
            .filter(MovieListMovie.year == year).first())
     if res:
         log.verbose('found movie %s', res)
     return res

def find_deployment(refdes, deployment):
    # print 'find_deployment(%r, %r)' % (refdes, deployment)
    # attempt to find an exact match
    record = session.query(Deployment).filter(and_(Deployment.refdes == refdes,
                                                   Deployment.deployment == deployment)).first()
    if record:
        # print 'found exact'
        return record

    parts = refdes.split('-', 2)
    mooring = parts[0]
    node = None
    if len(parts) > 1:
        node = parts[1]

    # no exact match, attempt to find the node
    if node is not None:
        record = session.query(Deployment).filter(Deployment.refdes == '-'.join((mooring, node))).first()
        if record:
            # print 'found node'
            return record

    # no node, find the mooring
    record = session.query(Deployment).filter(Deployment.refdes == mooring).first()
    # if record:
    #     print 'found mooring'
    return record

def get_questions(connection: Connection,
                  survey_id: str,
                  auth_user_id: [str, None]=None,
                  email: [str, None]=None) -> ResultProxy:
    """
    Get all the questions for a survey identified by survey_id ordered by
    sequence number restricted by auth_user.

    :param connection: a SQLAlchemy Connection
    :param survey_id: the UUID of the survey
    :param auth_user_id: the UUID of the user
    :param email: the user's e-mail address
    :return: an iterable of the questions (RowProxy)
    """

    table = question_table.join(survey_table)
    conds = [question_table.c.survey_id == survey_id]

    if auth_user_id is not None:
        if email is not None:
            raise TypeError('You cannot specify both auth_user_id and email')
        conds.append(survey_table.c.auth_user_id == auth_user_id)
    elif email is not None:
        table = table.join(auth_user_table)
        conds.append(auth_user_table.c.email == email)
    else:
        raise TypeError('You must specify either auth_user_id or email')

    questions = connection.execute(
        select([question_table]).select_from(table).where(
            and_(*conds)).order_by('sequence_number asc'))
    return questions

def get_users_with_filters(*filters):
    general_filters = [User.login_enabled == True,
                       User.roles_rel.any(
                           and_(RoleXUser.role_name == RolesTypes.founder, RoleXUser.is_primary == True))]
    notif_filters = filters[0]
    if len(filters) > 1:
        notif_filters = or_(*filters)
    return DBSession.query(User).filter(*(general_filters + [notif_filters])).all()