Python sqlalchemy.sql.expression.false() Examples

def fill_indexpage_with_archived_books(self, page, database, db_filter, order, allow_show_archived, *join):
        if current_user.show_detail_random():
            randm = self.session.query(Books) \
                .filter(self.common_filters(allow_show_archived)) \
                .order_by(func.random()) \
                .limit(self.config.config_random_books)
        else:
            randm = false()
        off = int(int(self.config.config_books_per_page) * (page - 1))
        query = self.session.query(database) \
            .join(*join, isouter=True) \
            .filter(db_filter) \
            .filter(self.common_filters(allow_show_archived))
        pagination = Pagination(page, self.config.config_books_per_page,
                                len(query.all()))
        entries = query.order_by(*order).offset(off).limit(self.config.config_books_per_page).all()
        for book in entries:
            book = self.order_authors(book)
        return entries, randm, pagination

    # Orders all Authors in the list according to authors sort 

def has_auth_webhooks(user_id):
    """获取所有我有权访问的Webhooks"""
    # create webhooks
    created_webhooks = WebHook.query.filter_by(
        user_id=user_id, deleted=False).all()

    # collaborator webhooks
    collaborated_webhooks = \
        WebHook.query.join(Collaborator,
                           Collaborator.webhook_id == WebHook.id) \
                     .filter(Collaborator.user_id == user_id) \
                     .filter(WebHook.deleted == false()).all()

    webhooks = created_webhooks + collaborated_webhooks
    # 去重并排序
    webhooks = list(sorted(set(webhooks), key=lambda x: x.id, reverse=True))
    return webhooks 

def set_rse_usage(rse_id, source, used, free, session=None):
    """
    Set RSE usage information.

    :param rse_id: the location id.
    :param source: The information source, e.g. srm.
    :param used: the used space in bytes.
    :param free: the free in bytes.
    :param session: The database session in use.

    :returns: True if successful, otherwise false.
    """
    rse_usage = models.RSEUsage(rse_id=rse_id, source=source, used=used, free=free)
    # versioned_session(session)
    rse_usage = session.merge(rse_usage)
    rse_usage.save(session=session)

    # rse_usage_history = models.RSEUsage.__history_mapper__.class_(rse_id=rse.id, source=source, used=used, free=free)
    # rse_usage_history.save(session=session)

    return True 

def __load_distance_edges_node(rse_id, session=None):
    """
    Loads the outgoing edges of the distance graph for one node.
    :param rse_id:    RSE id to load the edges for.
    :param session:   The DB Session to use.
    :returns:         Dictionary based graph object.
    """

    result = REGION_SHORT.get('distance_graph_%s' % str(rse_id))
    if isinstance(result, NoValue):
        distance_graph = {}
        for distance in session.query(models.Distance).join(models.RSE, models.RSE.id == models.Distance.dest_rse_id)\
                               .filter(models.Distance.src_rse_id == rse_id)\
                               .filter(models.RSE.deleted == false()).all():
            if distance.src_rse_id in distance_graph:
                distance_graph[distance.src_rse_id][distance.dest_rse_id] = distance.ranking
            else:
                distance_graph[distance.src_rse_id] = {distance.dest_rse_id: distance.ranking}
        REGION_SHORT.set('distance_graph_%s' % str(rse_id), distance_graph)
        result = distance_graph
    return result 

def _list_replicas_for_datasets(dataset_clause, state_clause, rse_clause, updated_after, session):
    """
    List file replicas for a list of datasets.

    :param session: The database session in use.
    """
    replica_query = session.query(models.DataIdentifierAssociation.child_scope,
                                  models.DataIdentifierAssociation.child_name,
                                  models.DataIdentifierAssociation.bytes,
                                  models.DataIdentifierAssociation.md5,
                                  models.DataIdentifierAssociation.adler32,
                                  models.RSEFileAssociation.path,
                                  models.RSEFileAssociation.state,
                                  models.RSE.id,
                                  models.RSE.rse,
                                  models.RSE.rse_type,
                                  models.RSE.volatile).\
        with_hint(models.RSEFileAssociation,
                  text="INDEX_RS_ASC(CONTENTS CONTENTS_PK) INDEX_RS_ASC(REPLICAS REPLICAS_PK) NO_INDEX_FFS(CONTENTS CONTENTS_PK)",
                  dialect_name='oracle').\
        outerjoin(models.RSEFileAssociation,
                  and_(models.DataIdentifierAssociation.child_scope == models.RSEFileAssociation.scope,
                       models.DataIdentifierAssociation.child_name == models.RSEFileAssociation.name)).\
        join(models.RSE, models.RSE.id == models.RSEFileAssociation.rse_id).\
        filter(models.RSE.deleted == false()).\
        filter(or_(*dataset_clause)).\
        order_by(models.DataIdentifierAssociation.child_scope,
                 models.DataIdentifierAssociation.child_name)

    if state_clause is not None:
        replica_query = replica_query.filter(and_(state_clause))

    if rse_clause is not None:
        replica_query = replica_query.filter(or_(*rse_clause))

    if updated_after:
        replica_query = replica_query.filter(models.RSEFileAssociation.updated_at >= updated_after)

    for replica in replica_query.yield_per(500):
        yield replica 

def upgrade():
    op.create_index(op.f('ix_repos_created_at'), 'repos', ['created_at'], unique=False)
    op.add_column(
        'repos', sa.Column(
            'mature', sa.Boolean(), nullable=False, server_default=expression.false(), index=True
        )
    )
    op.add_column(
        'repos', sa.Column(
            'status', sa.Enum('promising', 'new', 'unknown', 'deleted', 'hopeless'),
            server_default='new', nullable=False, index=True
        )
    ) 

def __query__(self):
        name = create_search_name(self.identifier)
        word = self.request_args.get(name['word'])
        type = self.request_args.get(name['type'])
        q = None
        if word and type == self.alias_attr.name:
            try:
                q = self.alias_attr == self.type_(word)
            except ValueError:
                q = false()
        return q 

def __query__(self):
        name = create_search_name(camel_to_underscore(self.cls.__name__))
        type_ = self.request_args.get(name['type'])
        word = self.request_args.get(name['word'])
        q = None
        if type_ == self.attribute_name:
            try:
                q = self.attribute == self.type_(word)
            except ValueError:
                return false()
        return q 

def get_top_existing(limit=39):
    cols = [Place.place_id, Place.display_name, Place.area, Place.state,
            Place.candidate_count, Place.item_count]
    c = func.count(Changeset.place_id)

    q = (Place.query.filter(Place.state.in_(['ready', 'load_isa', 'refresh']),
                            Place.area > 0,
                            Place.index_hide == false(),
                            Place.candidate_count > 4)
                    .options(load_only(*cols))
                    .outerjoin(Changeset)
                    .group_by(*cols)
                    .having(c == 0)
                    .order_by((Place.item_count / Place.area).desc()))
    return q[:limit] 

def transformations(self, relationship="all"):
        """Get all the transformations of this info.

        Return a list of transformations involving this info. ``relationship``
        can be "parent" (in which case only transformations where the info is
        the ``info_in`` are returned), "child" (in which case only
        transformations where the info is the ``info_out`` are returned) or
        ``all`` (in which case any transformations where the info is the
        ``info_out`` or the ``info_in`` are returned). The default is ``all``

        """
        if relationship not in ["all", "parent", "child"]:
            raise ValueError(
                "You cannot get transformations of relationship {}".format(relationship)
                + "Relationship can only be parent, child or all."
            )

        if relationship == "all":
            return Transformation.query.filter(
                and_(
                    Transformation.failed == false(),
                    or_(
                        Transformation.info_in == self, Transformation.info_out == self
                    ),
                )
            ).all()

        if relationship == "parent":
            return Transformation.query.filter_by(
                info_in_id=self.id, failed=False
            ).all()

        if relationship == "child":
            return Transformation.query.filter_by(
                info_out_id=self.id, failed=False
            ).all() 

def list_rses(session=None):
    """
    List RSEs in the Quarantined Queues.

    :param session: The database session in use.

    :returns: a list of RSEs.
    """
    query = session.query(models.RSE.id).distinct(models.RSE.id).\
        filter(models.QuarantinedReplica.rse_id == models.RSE.id).\
        filter(models.RSE.deleted == false())
    return [rse for (rse,) in query] 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('keypairs', sa.Column('is_admin', sa.Boolean(), nullable=False, default=False, server_default=false()))
    op.create_index(op.f('ix_keypairs_is_admin'), 'keypairs', ['is_admin'], unique=False)
    # ### end Alembic commands ### 

def set_rse_transfer_limits(rse_id, activity, rse_expression=None, max_transfers=0, transfers=0, waitings=0, volume=0, deadline=1, strategy='fifo', session=None):
    """
    Set RSE transfer limits.

    :param rse_id: The RSE id.
    :param activity: The activity.
    :param rse_expression: RSE expression string.
    :param max_transfers: Maximum transfers.
    :param transfers: Current number of tranfers.
    :param waitings: Current number of waitings.
    :param volume: Maximum transfer volume in bytes.
    :param deadline: Maximum waiting time in hours until a datasets gets released.
    :param strategy: Stragey to handle datasets `fifo` or `grouped_fifo`.
    :param session: The database session in use.

    :returns: True if successful, otherwise false.
    """
    try:
        rse_tr_limit = models.RSETransferLimit(rse_id=rse_id, activity=activity, rse_expression=rse_expression,
                                               max_transfers=max_transfers, transfers=transfers,
                                               waitings=waitings, volume=volume, strategy=strategy, deadline=deadline)
        rse_tr_limit = session.merge(rse_tr_limit)
        rowcount = rse_tr_limit.save(session=session)
        return rowcount
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def set_rse_limits(rse_id, name, value, session=None):
    """
    Set RSE limits.

    :param rse_id: The RSE id.
    :param name: The name of the limit.
    :param value: The feature value. Set to -1 to remove the limit.
    :param session: The database session in use.

    :returns: True if successful, otherwise false.
    """
    rse_limit = models.RSELimit(rse_id=rse_id, name=name, value=value)
    rse_limit = session.merge(rse_limit)
    rse_limit.save(session=session)
    return True 

def get_rses_with_attribute_value(key, value, lookup_key, session=None):
    """
    Return all RSEs with a certain attribute.

    :param key: The key for the attribute.
    :param value: The value for the attribute.
    :param lookup_key: The value of the this key will be returned.
    :param session: The database session in use.

    :returns: List of rse dictionaries with the rse_id and lookup_key/value pair
    """

    result = REGION.get('av-%s-%s-%s' % (key, value, lookup_key))
    if result is NO_VALUE:

        rse_list = []

        subquery = session.query(models.RSEAttrAssociation.rse_id)\
                          .filter(models.RSEAttrAssociation.key == key,
                                  models.RSEAttrAssociation.value == value)\
                          .subquery()

        query = session.query(models.RSEAttrAssociation.rse_id,
                              models.RSEAttrAssociation.key,
                              models.RSEAttrAssociation.value)\
                       .join(models.RSE, models.RSE.id == models.RSEAttrAssociation.rse_id)\
                       .join(subquery, models.RSEAttrAssociation.rse_id == subquery.c.rse_id)\
                       .filter(models.RSE.deleted == false(),
                               models.RSEAttrAssociation.key == lookup_key)

        for row in query:
            rse_list.append({'rse_id': row[0],
                             'key': row[1],
                             'value': row[2]})

        REGION.set('av-%s-%s-%s' % (key, value, lookup_key), rse_list)
        return rse_list

    return result 

def rse_exists(rse, include_deleted=False, session=None):
    """
    Checks to see if RSE exists.

    :param rse: Name of the rse.
    :param session: The database session in use.

    :returns: True if found, otherwise false.
    """
    return True if session.query(models.RSE).filter_by(rse=rse, deleted=include_deleted).first() else False 

def tags_filters():
    negtags_list = current_user.list_denied_tags()
    postags_list = current_user.list_allowed_tags()
    neg_content_tags_filter = false() if negtags_list == [''] else db.Tags.name.in_(negtags_list)
    pos_content_tags_filter = true() if postags_list == [''] else db.Tags.name.in_(postags_list)
    return and_(pos_content_tags_filter, ~neg_content_tags_filter)


# checks if domain is in database (including wildcards)
# example SELECT * FROM @TABLE WHERE  'abcdefg' LIKE Name;
# from https://code.luasoftware.com/tutorials/flask/execute-raw-sql-in-flask-sqlalchemy/ 

def common_filters(self, allow_show_archived=False):
        if not allow_show_archived:
            archived_books = (
                ub.session.query(ub.ArchivedBook)
                    .filter(ub.ArchivedBook.user_id == int(current_user.id))
                    .filter(ub.ArchivedBook.is_archived == True)
                    .all()
            )
            archived_book_ids = [archived_book.book_id for archived_book in archived_books]
            archived_filter = Books.id.notin_(archived_book_ids)
        else:
            archived_filter = true()

        if current_user.filter_language() != "all":
            lang_filter = Books.languages.any(Languages.lang_code == current_user.filter_language())
        else:
            lang_filter = true()
        negtags_list = current_user.list_denied_tags()
        postags_list = current_user.list_allowed_tags()
        neg_content_tags_filter = false() if negtags_list == [''] else Books.tags.any(Tags.name.in_(negtags_list))
        pos_content_tags_filter = true() if postags_list == [''] else Books.tags.any(Tags.name.in_(postags_list))
        if self.config.config_restricted_column:
            pos_cc_list = current_user.allowed_column_value.split(',')
            pos_content_cc_filter = true() if pos_cc_list == [''] else \
                getattr(Books, 'custom_column_' + str(self.config.config_restricted_column)). \
                    any(cc_classes[self.config.config_restricted_column].value.in_(pos_cc_list))
            neg_cc_list = current_user.denied_column_value.split(',')
            neg_content_cc_filter = false() if neg_cc_list == [''] else \
                getattr(Books, 'custom_column_' + str(self.config.config_restricted_column)). \
                    any(cc_classes[self.config.config_restricted_column].value.in_(neg_cc_list))
        else:
            pos_content_cc_filter = true()
            neg_content_cc_filter = false()
        return and_(lang_filter, pos_content_tags_filter, ~neg_content_tags_filter,
                    pos_content_cc_filter, ~neg_content_cc_filter, archived_filter)

    # Fill indexpage with all requested data from database 

def render_hot_books(page):
    if current_user.check_visibility(constants.SIDEBAR_HOT):
        if current_user.show_detail_random():
            random = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()) \
                .order_by(func.random()).limit(config.config_random_books)
        else:
            random = false()
        off = int(int(config.config_books_per_page) * (page - 1))
        all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id)).order_by(
            func.count(ub.Downloads.book_id).desc()).group_by(ub.Downloads.book_id)
        hot_books = all_books.offset(off).limit(config.config_books_per_page)
        entries = list()
        for book in hot_books:
            downloadBook = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()).filter(
                db.Books.id == book.Downloads.book_id).first()
            if downloadBook:
                entries.append(downloadBook)
            else:
                ub.delete_download(book.Downloads.book_id)
                # ub.session.query(ub.Downloads).filter(book.Downloads.book_id == ub.Downloads.book_id).delete()
                # ub.session.commit()
        numBooks = entries.__len__()
        pagination = Pagination(page, config.config_books_per_page, numBooks)
        return render_title_template('index.html', random=random, entries=entries, pagination=pagination,
                                     title=_(u"Hot Books (Most Downloaded)"), page="hot")
    else:
        abort(404) 

def _list_replicas_for_files(file_clause, state_clause, files, rse_clause, updated_after, session):
    """
    List file replicas for a list of files.

    :param session: The database session in use.
    """
    for replica_condition in chunks(file_clause, 50):

        filters = [models.RSEFileAssociation.rse_id == models.RSE.id,
                   models.RSE.deleted == false(),
                   or_(*replica_condition),
                   ]

        if state_clause is not None:
            filters.append(state_clause)

        if rse_clause is not None:
            filters.append(or_(*rse_clause))

        if updated_after:
            filters.append(models.RSEFileAssociation.updated_at >= updated_after)

        whereclause = and_(*filters)

        replica_query = select(columns=(models.RSEFileAssociation.scope,
                                        models.RSEFileAssociation.name,
                                        models.RSEFileAssociation.bytes,
                                        models.RSEFileAssociation.md5,
                                        models.RSEFileAssociation.adler32,
                                        models.RSEFileAssociation.path,
                                        models.RSEFileAssociation.state,
                                        models.RSE.id,
                                        models.RSE.rse,
                                        models.RSE.rse_type,
                                        models.RSE.volatile),
                               whereclause=whereclause,
                               order_by=(models.RSEFileAssociation.scope,
                                         models.RSEFileAssociation.name)).\
            with_hint(models.RSEFileAssociation.scope, text="INDEX(REPLICAS REPLICAS_PK)", dialect_name='oracle').\
            compile()

        for replica in session.execute(replica_query.statement, replica_query.params).fetchall():
            {'scope': replica[0], 'name': replica[1]} in files and files.remove({'scope': replica[0], 'name': replica[1]})
            yield replica

    if files:
        file_wo_clause = []
        for file in files:
            file_wo_clause.append(and_(models.DataIdentifier.scope == file['scope'],
                                       models.DataIdentifier.name == file['name']))
        files_wo_replicas_query = session.query(models.DataIdentifier.scope,
                                                models.DataIdentifier.name,
                                                models.DataIdentifier.bytes,
                                                models.DataIdentifier.md5,
                                                models.DataIdentifier.adler32).\
            filter_by(did_type=DIDType.FILE).filter(or_(*file_wo_clause)).\
            with_hint(models.DataIdentifier, text="INDEX(DIDS DIDS_PK)", dialect_name='oracle')

        for scope, name, bytes, md5, adler32 in files_wo_replicas_query:
            yield scope, name, bytes, md5, adler32, None, None, None, None, None, None
            {'scope': scope, 'name': name} in files and files.remove({'scope': scope, 'name': name}) 

def list_datasets_per_rse(rse_id, filters=None, limit=None, session=None):
    """
    List datasets at a RSE.

    :param rse: the rse id.
    :param filters: dictionary of attributes by which the results should be filtered.
    :param limit: limit number.
    :param session: Database session to use.

    :returns: A list of dict dataset replicas
    """
    query = session.query(models.CollectionReplica.scope,
                          models.CollectionReplica.name,
                          models.RSE.id.label('rse_id'),
                          models.RSE.rse,
                          models.CollectionReplica.bytes,
                          models.CollectionReplica.length,
                          models.CollectionReplica.available_bytes,
                          models.CollectionReplica.available_replicas_cnt.label("available_length"),
                          models.CollectionReplica.state,
                          models.CollectionReplica.created_at,
                          models.CollectionReplica.updated_at,
                          models.CollectionReplica.accessed_at)\
        .filter_by(did_type=DIDType.DATASET)\
        .filter(models.CollectionReplica.rse_id == models.RSE.id)\
        .filter(models.RSE.id == rse_id)\
        .filter(models.RSE.deleted == false())

    for (k, v) in filters and filters.items() or []:
        if k == 'name' or k == 'scope':
            v_str = v if k != 'scope' else v.internal
            if '*' in v_str or '%' in v_str:
                if session.bind.dialect.name == 'postgresql':  # PostgreSQL escapes automatically
                    query = query.filter(getattr(models.CollectionReplica, k).like(v_str.replace('*', '%')))
                else:
                    query = query.filter(getattr(models.CollectionReplica, k).like(v_str.replace('*', '%'), escape='\\'))
            else:
                query = query.filter(getattr(models.CollectionReplica, k) == v)
                # hints ?
        elif k == 'created_before':
            created_before = str_to_date(v)
            query = query.filter(models.CollectionReplica.created_at <= created_before)
        elif k == 'created_after':
            created_after = str_to_date(v)
            query = query.filter(models.CollectionReplica.created_at >= created_after)
        else:
            query = query.filter(getattr(models.CollectionReplica, k) == v)

    if limit:
        query = query.limit(limit)

    for row in query:
        yield row._asdict()