Python sqlalchemy.or_() Examples

def get_online_admins(self, session=None) -> list:
        admins = session.query(GlobalRoles).filter(or_(
                GlobalRoles.roles.ilike('%{}%'.format(RoleKeys.SUPER_USER)),
                GlobalRoles.roles.ilike('%{}%'.format(RoleKeys.GLOBAL_MODERATOR))
        )).all()
        admin_ids = [admin.user_id for admin in admins]
        if len(admin_ids) == 0:
            return []

        online_admins = session.query(UserStatus)\
            .filter(UserStatus.status.in_([
                UserKeys.STATUS_INVISIBLE,
                UserKeys.STATUS_AVAILABLE,
                UserKeys.STATUS_CHAT]))\
            .filter(UserStatus.uuid.in_(admin_ids)).all()
        return [admin.uuid for admin in online_admins] 

def get_metadata_bulk(dids, session=None):
    """
    Get metadata for a list of dids
    :param dids: A list of dids.
    :param session: The database session in use.
    """
    condition = []
    for did in dids:
        condition.append(and_(models.DataIdentifier.scope == did['scope'],
                              models.DataIdentifier.name == did['name']))

    try:
        for chunk in chunks(condition, 50):
            for row in session.query(models.DataIdentifier).with_hint(models.DataIdentifier, "INDEX(DIDS DIDS_PK)", 'oracle').filter(or_(*chunk)):
                data = {}
                for column in row.__table__.columns:
                    data[column.name] = getattr(row, column.name)
                yield data
    except NoResultFound:
        raise exception.DataIdentifierNotFound('No Data Identifiers found') 

def delete_tags(context, item_ids, tag_pairs=None):
    if not item_ids:
        return

    query = (model_query(context, models.Tag).
             filter_by(project_id=context.project_id).
             filter(models.Tag.item_id.in_(item_ids)))

    if tag_pairs:
        tag_fltr = None
        for tag_pair in tag_pairs:
            pair_fltr = None
            for col in ('key', 'value'):
                if col in tag_pair:
                    expr = getattr(models.Tag, col) == tag_pair[col]
                    pair_fltr = (expr if pair_fltr is None else
                                 and_(pair_fltr, expr))
            if pair_fltr is not None:
                tag_fltr = (pair_fltr if tag_fltr is None else
                            or_(tag_fltr, pair_fltr))
        if tag_fltr is not None:
            query = query.filter(tag_fltr)

    query.delete(synchronize_session=False) 

def get_ranking(config):
    routes = []
    session = db.session

    for r in level.routes:
        rl = []
        for user, levels, timestamp in \
                session.query(User, db.func.count(LevelState.level),db.func.max(LevelState.timestamp)).\
                join(LevelState).\
                filter(LevelState.route==r.name).\
                filter(LevelState.timestamp < config['END_TIME']).\
                filter(LevelState.state=='solved').\
                filter(or_(User.seat == None, User.seat != 'Control')).\
                group_by(User):
            rl.append(Score(user, levels, timestamp))
        routes.append(rl)

    combined, routes, global_prizes, route_prizes = rank(routes, config)

    grank = map_ranking(1, combined, global_prizes)
    rrank = [map_ranking(1 + config['GLOBAL_PRIZES'], routes[i], route_prizes[i]) for i in range(len(routes))]

    return grank, rrank 

def __or__(self, other):
        """Implement the ``|`` operator.

        When used with SQL expressions, results in an
        OR operation, equivalent to
        :func:`~.expression.or_`, that is::

            a | b

        is equivalent to::

            from sqlalchemy import or_
            or_(a, b)

        Care should be taken when using ``|`` regarding
        operator precedence; the ``|`` operator has the highest precedence.
        The operands should be enclosed in parenthesis if they contain
        further sub expressions::

            (a == 2) | (b == 4)

        """
        return self.operate(or_, other) 

def add_item(context, kind, data):
    item_ref = models.Item()
    item_ref.update({
        "project_id": context.project_id,
        "id": _new_id(kind),
    })
    item_ref.update(_pack_item_data(data))
    try:
        item_ref.save()
    except db_exception.DBDuplicateEntry as ex:
        if (models.ITEMS_OS_ID_INDEX_NAME not in ex.columns and
                'os_id' not in ex.columns):
            raise
        item_ref = (model_query(context, models.Item).
                    filter_by(os_id=data["os_id"]).
                    filter(or_(models.Item.project_id == context.project_id,
                               models.Item.project_id.is_(None))).
                    filter(models.Item.id.like('%s-%%' % kind)).
                    one())
        item_data = _unpack_item_data(item_ref)
        item_data.update(data)
        item_ref.update(_pack_item_data(item_data))
        item_ref.project_id = context.project_id
        item_ref.save()
    return _unpack_item_data(item_ref) 

def get_and_lock_file_replicas(scope, name, nowait=False, restrict_rses=None, session=None):
    """
    Get file replicas for a specific scope:name.

    :param scope:          The scope of the did.
    :param name:           The name of the did.
    :param nowait:         Nowait parameter for the FOR UPDATE statement
    :param restrict_rses:  Possible RSE_ids to filter on.
    :param session:        The db session in use.
    :returns:              List of SQLAlchemy Replica Objects
    """

    query = session.query(models.RSEFileAssociation).filter_by(scope=scope, name=name).filter(models.RSEFileAssociation.state != ReplicaState.BEING_DELETED)
    if restrict_rses is not None:
        if len(restrict_rses) < 10:
            rse_clause = []
            for rse_id in restrict_rses:
                rse_clause.append(models.RSEFileAssociation.rse_id == rse_id)
            if rse_clause:
                query = query.filter(or_(*rse_clause))
    return query.with_for_update(nowait=nowait).all() 

def get_source_replicas(scope, name, source_rses=None, session=None):
    """
    Get soruce replicas for a specific scope:name.

    :param scope:          The scope of the did.
    :param name:           The name of the did.
    :param soruce_rses:    Possible RSE_ids to filter on.
    :param session:        The db session in use.
    :returns:              List of SQLAlchemy Replica Objects
    """

    query = session.query(models.RSEFileAssociation.rse_id).filter_by(scope=scope, name=name).filter(models.RSEFileAssociation.state == ReplicaState.AVAILABLE)
    if source_rses:
        if len(source_rses) < 10:
            rse_clause = []
            for rse_id in source_rses:
                rse_clause.append(models.RSEFileAssociation.rse_id == rse_id)
            if rse_clause:
                query = query.filter(or_(*rse_clause))
    return [a[0] for a in query.all()] 

def bulk_delete_bad_pfns(pfns, session=None):
    """
    Bulk delete bad PFNs.

    :param pfns: the list of new files.
    :param session: The database session in use.

    :returns: True is successful.
    """
    pfn_clause = []
    for pfn in pfns:
        pfn_clause.append(models.BadPFNs.path == pfn)

    for chunk in chunks(pfn_clause, 100):
        query = session.query(models.BadPFNs).filter(or_(*chunk))
        query.delete(synchronize_session=False)

    return True 

def list_exceptions(exception_id, states, session=None):
    """
    List exceptions to Lifetime Model.

    :param exception_id: The id of the exception
    :param states:       The states to filter
    :param session:      The database session in use.
    """

    state_clause = []
    if states:
        state_clause = [models.LifetimeExceptions.state == state for state in states]

    query = session.query(models.LifetimeExceptions)
    if state_clause != []:
        query = query.filter(or_(*state_clause))
    if exception_id:
        query = query.filter_by(id=exception_id)

    for exception in query.yield_per(5):
        yield {'id': exception.id, 'scope': exception.scope, 'name': exception.name,
               'did_type': exception.did_type, 'account': exception.account,
               'pattern': exception.pattern, 'comments': exception.comments,
               'state': exception.state, 'created_at': exception.created_at,
               'expires_at': exception.expires_at} 

def onekey_recover_rooms():
        with db.session.begin(subtransactions=True):
            (ViewIspStatus.query.filter_by(closed=False).
             update({"closed": True,
                     "is_health": True}))
            ViewMigrateHistory.query.filter_by(state='migrated').update({'state': 'recovered'})
            q = ViewDomainNameState.query.filter(sqlalchemy.or_(
                ViewDomainNameState.origin_enabled_rooms != ViewDomainNameState.enabled_rooms,
                ViewDomainNameState.origin_state != ViewDomainNameState.state
            ))
            if q.count() == 0:
                raise BadParam('no domain to recover', msg_ch=u'没有可恢复的域名')
            for item in q:
                item.enabled_rooms = item.origin_enabled_rooms
                item.state = item.origin_state
            MigrateDal.increase_serial_num(NORMAL_TO_CNAME.values()) 

def delete_messages(messages, session=None):
    """
    Delete all messages with the given IDs, and archive them to the history.

    :param messages: The messages to delete as a list of dictionaries.
    """
    message_condition = []
    for message in messages:
        message_condition.append(Message.id == message['id'])
        if len(message['payload']) > 4000:
            message['payload_nolimit'] = message.pop('payload')

    try:
        if message_condition:
            session.query(Message).\
                with_hint(Message, "index(messages MESSAGES_ID_PK)", 'oracle').\
                filter(or_(*message_condition)).\
                delete(synchronize_session=False)

            session.bulk_insert_mappings(MessageHistory, messages)
    except IntegrityError as e:
        raise RucioException(e.args) 

def delete_volatile_replicas(rse_id, replicas, session=None):
    """
    Bulk delete volatile replicas.

    :param rse_id: the rse id.
    :param replicas: the list of volatile replicas.
    :param session: The database session in use.
    :returns: True is successful.
    """
    # first check that the rse is a volatile one
    try:
        session.query(models.RSE.id).filter_by(rse_id=rse_id, volatile=True).one()
    except NoResultFound:
        raise exception.UnsupportedOperation('No volatile rse found for %s !' % get_rse_name(rse_id=rse_id, session=session))

    conditions = []
    for replica in replicas:
        conditions.append(and_(models.RSEFileAssociation.scope == replica['scope'],
                               models.RSEFileAssociation.name == replica['name']))

    if conditions:
        session.query(models.RSEFileAssociation).\
            filter(models.RSEFileAssociation.rse_id == rse_id).\
            filter(or_(*conditions)).\
            delete(synchronize_session=False) 

def get_user_filters(user):
    clauses = []
    sfq = db.session.query(SampleFilter)
    clauses.append(SampleFilter.user_id == user.user_id)
    if not user.is_admin:
        clauses.append(SampleFilter.is_public == True)
    sfq.filter(or_(*clauses))
    sfs = sfq.all()
    data = [
        {
            "name": x.sample_filter_name,
            "set": x.sample_filter_tag,
            "id": x.sample_filter_id,
            "filters": json.loads(x.sample_filter_data),
        }
        for x in sfs
    ]
    return data 

def find_or_create_from_user_input(db_session, twitch_helix_api, input, always_fresh=False):
        input = User._normalize_user_username_input(input)

        if not always_fresh:
            user_from_db = (
                db_session.query(User)
                .filter(or_(User.login == input, User.name == input))
                .order_by(User.login_last_updated.desc())
                .limit(1)
                .one_or_none()
            )

            if user_from_db is not None:
                return user_from_db

        basics = twitch_helix_api.get_user_basics_by_login(input)
        if basics is None:
            return None

        return User.from_basics(db_session, basics) 

def run(self, query):
        assert isinstance(query, dict)
        # no support for faceting atm
        self.facets = {}
        limit = min(1000, int(query.get('rows', 10)))

        q = query.get('q')
        ourq = model.Session.query(model.Package.id).filter_by(state='active')

        def makelike(field):
            _attr = getattr(model.Package, field)
            return _attr.ilike('%' + term + '%')
        if q and q not in ('""', "''", '*:*'):
            terms = q.split()
            # TODO: tags ...?
            fields = ['name', 'title', 'notes']
            for term in terms:
                args = [makelike(field) for field in fields]
                subq = or_(*args)
                ourq = ourq.filter(subq)
        self.count = ourq.count()
        ourq = ourq.limit(limit)
        self.results = [{'id': r[0]} for r in ourq.all()]

        return {'results': self.results, 'count': self.count} 

def _edge_one_node(nodes: List[Node]):
        """Get edges where either the source or target are in the list of nodes.

        Note: doing this with the nodes directly is not yet supported by SQLAlchemy

        .. code-block:: python

            return or_(
                Edge.source.in_(nodes),
                Edge.target.in_(nodes),
            )
        """
        node_ids = [node.id for node in nodes]

        return or_(
            Edge.source_id.in_(node_ids),
            Edge.target_id.in_(node_ids),
        ) 

def dota_keyphrase_query(self, keyphrase):
		variables = [
			QueryVariable("hero", self.hero_aliases, lambda query, value: query.filter(Response.hero_id == value)),
			QueryVariable("criteria", self.criteria_aliases, lambda query, value: query.filter(or_(Response.criteria.like(value + "%"), Response.criteria.like("%|" + value + "%")))),
		]

		if keyphrase is None:
			words = []
		else:
			keyphrase = keyphrase.lower()
			words = keyphrase.split(" ")

		extract_var_prefix(words, variables)

		query = await self.smart_dota_query(words, variables)

		while query is None and extract_var(words, variables):
			query = await self.smart_dota_query(words, variables)

		return query 

def _apply_tenant_criteria(self, context, table, query,
                               include_null_tenant=True):
        if hasattr(table.c, 'tenant_id'):
            if not context.all_tenants:
                # NOTE: The query doesn't work with table.c.tenant_id is None,
                # so I had to force flake8 to skip the check
                if include_null_tenant:
                    query = query.where(or_(
                            table.c.tenant_id == context.project_id,
                            table.c.tenant_id == None))  # NOQA
                else:
                    query = query.where(
                        table.c.tenant_id == context.project_id
                    )

        return query 

def validate(self):
        rv = BaseForm.validate(self)
        if not rv:
            return False

        if self.username.data in self.password.data:
            self.password.errors.append('Password must not contain the username.')
            return False

        if self.edit:
            return True

        user = User.query.filter(or_(User.name == self.username.data,
                                     User.email == self.email.data)).first()
        if not user:
            return True
        if user.name == self.username.data:
            self.username.errors.append(ERROR_USERNAME_EXISTS)
        if user.email == self.email.data:
            self.email.errors.append(ERROR_EMAIL_EXISTS)
        return False 

def apply_single_column_filter(query, filters, target_table, account_join_attribute=None, user_join_attribute=None):


    if target_table.__tablename__ == TransferAccount.__tablename__ and account_join_attribute is not None:
        query = query.join(TransferAccount, TransferAccount.id == account_join_attribute)
    elif target_table.__tablename__ == User.__tablename__ and user_join_attribute is not None:
        query = query.join(User, User.id == user_join_attribute)

    for batches in filters:
        to_batch = []
        for _filt in batches:
            column = _filt[0]
            comparator = _filt[1]
            val = _filt[2]

            if comparator == 'EQ':
                val = val if isinstance(val, list) else [val]
                to_batch.append(getattr(target_table, column).in_(val))
            elif comparator == 'GT':
                to_batch.append(getattr(target_table, column) > val)
            elif comparator == "LT":
                to_batch.append(getattr(target_table, column) < val)
        query = query.filter(or_(*to_batch))
    return query 

def relation_exists(self, parent_pid, child_pid):
        """Determine if given relation already exists."""
        return (
            PIDRelation.query.filter_by(relation_type=self.relation_type.id)
            .filter(
                or_(
                    and_(
                        PIDRelation.parent == parent_pid,
                        PIDRelation.child == child_pid,
                    ),
                    and_(
                        PIDRelation.parent == child_pid,
                        PIDRelation.child == parent_pid,
                    ),
                )
            )
            .count()
            > 0
        ) 

def get_any_relation_of(self, *pids):
        """Get any relation when given PIDs are parent or child.

        :arg pids: one or multiple PIDs
        """
        all_relation_pids = set()

        for pid in pids:
            query = PIDRelation.query.filter_by(
                relation_type=self.relation_type.id
            ).filter(or_(PIDRelation.parent == pid, PIDRelation.child == pid))

            results = query.all()
            if results:
                parent = results[0].parent
                if parent == pid:
                    for result in results:
                        all_relation_pids.add(result)
                else:
                    # get relations of the parent
                    for result in self.get_relations_by_parent(parent):
                        all_relation_pids.add(result)

        return list(all_relation_pids) 

def search_vulnerabilities_numerical(searched_text, db_table):
    """
    Perform a search based on vulnerabilities' description, file, id, and port (only if it is an exploit) for an only
    numerical search input.
    :param searched_text: the search input.
    :param db_table: the DB table in which we want to perform the search.
    :return: a queryset with search results.
    """
    session = start_session()
    if db_table == 'searcher_exploit':
        queryset = session.query(Exploit).filter(or_(Exploit.description.contains(searched_text),
                                                     Exploit.id == int(searched_text),
                                                     Exploit.file.contains(searched_text),
                                                     Exploit.port == int(searched_text)
                                                     ))
    else:
        queryset = session.query(Shellcode).filter(or_(Shellcode.description.contains(searched_text),
                                                       Shellcode.id == int(searched_text),
                                                       Shellcode.file.contains(searched_text)
                                                       ))
    session.close()
    return queryset2list(queryset) 

def request_feedback_questions(user):
    questions = current_app.config['DEFAULT_FEEDBACK_QUESTIONS']
    balance_below_trigger = current_app.config['FEEDBACK_TRIGGERED_WHEN_BALANCE_BELOW']
    transfer_count_above_trigger = current_app.config['FEEDBACK_TRIGGERED_WHEN_TRANSFER_COUNT_ABOVE']
    if transfer_count_above_trigger == -1:
        # Makes it easy to disable the transfer count trigger by setting it to -1
        transfer_count_above_trigger = 999999999999

    user_transfer_accounts = TransferAccount.query.execution_options(show_all=True).filter(
        TransferAccount.users.any(User.id.in_([user.id]))).all()

    if user.has_beneficiary_role and (len(user_transfer_accounts) > 0):
        # todo: this will raise an error No 'user.transfer_account_id'. fix.
        # transfer_account = TransferAccount.query.get(user.transfer_account_id)
        transfer_account = user_transfer_accounts[0]  # get the first transfer account. todo: fix this for many-to-many

        transfer_number = CreditTransfer.query.filter(or_(
            CreditTransfer.recipient_user == user,
            CreditTransfer.sender_user == user
        )).count()

        feedback = Feedback.query.filter(and_(Feedback.user == user,
                                              Feedback.question.in_(questions))).first()

        if feedback is None and transfer_account.is_approved:

            if transfer_account.balance < balance_below_trigger or transfer_number > transfer_count_above_trigger:
                return questions

    return [] 

def get_open_pages(self, limit=10):
        from page import PageNew
        pages = db.session.query(PageNew).\
            distinct(PageNew.normalized_title).\
            filter(PageNew.endpoint_id == self.id).\
            filter(PageNew.num_pub_matches.isnot(None), PageNew.num_pub_matches >= 1).\
            filter(or_(PageNew.scrape_pdf_url.isnot(None), PageNew.scrape_metadata_url.isnot(None))).\
            limit(limit).all()
        return [(p.id, p.url, p.normalized_title, p.pub.url, p.pub.unpaywall_api_url, p.scrape_version) for p in pages] 

def get_num_open_with_dois(self):
        from page import PageNew
        num = db.session.query(PageNew.id).\
            distinct(PageNew.normalized_title).\
            filter(PageNew.endpoint_id == self.id).\
            filter(PageNew.num_pub_matches.isnot(None), PageNew.num_pub_matches >= 1).\
            filter(or_(PageNew.scrape_pdf_url.isnot(None), PageNew.scrape_metadata_url.isnot(None))).\
            count()
        return num 

def missing_field_filter(api_response_field, retry_apis):
    #  don't look up things we already tried and got no response for unless retry_apis is set
    if retry_apis:
        return or_(api_response_field.is_(None), api_response_field == {})
    else:
        return api_response_field.is_(None) 

def run(retry_apis):
    start = time()

    journal_ids = db.session.query(Journal.issn_l).filter(
        or_(
            missing_field_filter(Journal.api_raw_crossref, retry_apis),
            missing_field_filter(Journal.api_raw_issn, retry_apis),
        )
    ).all()

    logger.info('trying to update {} journals'.format(len(journal_ids)))

    chunk_size = 50
    for i in range(0, len(journal_ids), chunk_size):
        id_chunk = journal_ids[i:i+chunk_size]
        journals = Journal.query.filter(Journal.issn_l.in_(id_chunk)).all()

        for journal in journals:
            # try all issns, issn-l first
            issns = set(journal.issns)
            issns.discard(journal.issn_l)
            issns = [journal.issn_l] + list(issns)

            if journal.api_raw_crossref is None or (retry_apis and journal.api_raw_crossref == {}):
                logger.info('getting crossref api response for {}'.format(journal.issn_l))
                journal.api_raw_crossref = get_first_response(call_crossref_api, issns) or {}

            if journal.api_raw_issn is None or (retry_apis and journal.api_raw_issn == {}):
                logger.info('getting issn api response for {}'.format(journal.issn_l))
                journal.api_raw_issn = get_first_response(call_issn_api, issns) or {}

            db.session.merge(journal)

        safe_commit(db)

    db.session.remove()

    logger.info('finished update in {}'.format(timedelta(seconds=elapsed(start)))) 

def get_tags(context, kinds=None, item_ids=None):
    query = (model_query(context, models.Tag).
             filter_by(project_id=context.project_id))
    if kinds:
        fltr = None
        for kind in kinds:
            expr = models.Tag.item_id.like('%s-%%' % kind)
            fltr = expr if fltr is None else or_(fltr, expr)
        query = query.filter(fltr)
    if item_ids:
        query = query.filter(models.Tag.item_id.in_(item_ids))
    return [dict(item_id=tag.item_id,
                 key=tag.key,
                 value=tag.value)
            for tag in query.all()]