Python expression.select方法代码示例

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def get_db_time():
    """ Gives the utc time on the db. """
    s = session.get_session()
    try:
        storage_date_format = None
        if s.bind.dialect.name == 'oracle':
            query = select([text("sys_extract_utc(systimestamp)")])
        elif s.bind.dialect.name == 'mysql':
            query = select([text("utc_timestamp()")])
        elif s.bind.dialect.name == 'sqlite':
            query = select([text("datetime('now', 'utc')")])
            storage_date_format = '%Y-%m-%d  %H:%M:%S'
        else:
            query = select([func.current_date()])

        for now, in s.execute(query):
            if storage_date_format:
                return datetime.strptime(now, storage_date_format)
            return now

    finally:
        s.remove() 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def subject_add_to_parent(request, *args, **kw):
    context = request.context
    parent_row = context.subject_row

    if not request.has_perm(perm_global_manage_subjects):
        raise APIError(403, "forbidden")

    q = t_subjects_subjects.select().where(and_(
        t_subjects_subjects.c.subject_id == request.validated_params.body["subject_id"],
        t_subjects_subjects.c.part_of_id == parent_row["id"],
    ))

    r = DBSession.execute(q).fetchone()

    if not r:
        q = t_subjects_subjects.insert({
            'subject_id': request.validated_params.body["subject_id"],
            'part_of_id': parent_row["id"],
        })

        update_connection().execute(q)

    return r_status.output({
        "status": "ok"
    }) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def subject_remove_from_parent(request, *args, **kw):
    context = request.context
    parent_row = context.subject_row

    if not request.has_perm(perm_global_manage_subjects):
        raise APIError(403, "forbidden")

    q = t_subjects_subjects.select().where(and_(
        t_subjects_subjects.c.subject_id == request.validated_params.body["subject_id"],
        t_subjects_subjects.c.part_of_id == parent_row["id"],
    ))

    r = DBSession.execute(q).fetchone()

    if r:
        q = t_subjects_subjects.delete().where(and_(
            t_subjects_subjects.c.subject_id == request.validated_params.body["subject_id"],
            t_subjects_subjects.c.part_of_id == parent_row["id"],
        ))

        update_connection().execute(q)

    return r_status.output({
        "status": "ok"
    }) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def subjecttype_search_list(request, *args, **kw):
    context = request.context

    if not request.has_perm(perm_global_search_subjecttypes):
        raise APIError(403, "forbidden")

    q = t_subjecttypes.select().order_by(t_subjecttypes.c.name.asc())
    types = DBSession.execute(q).fetchall()

    ret = {
        "subjecttypes": [{
            "id": st["id"],
            "name": st["name"],
        } for st in types]
    }

    return r_subjecttypelist.output(ret) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def variables_search_list(request, *args, **kw):
    context = request.context
    q = t_variables.select().order_by(t_variables.c.name.asc())
    types = DBSession.execute(q).fetchall()

    if not request.has_perm(perm_global_list_variables):
        raise APIError(403, "forbidden")

    ret = {
        "variables": [{
            "id": st["id"],
            "name": st["name"],
            "increase_permission": st["increase_permission"],
        } for st in types]
    }

    return r_variablelist.output(ret) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def test_implicitly_boolean(self):
        # test for expressions that the database always considers as boolean
        # even if there is no boolean datatype.
        assert not self.table1.c.myid._is_implicitly_boolean
        assert (self.table1.c.myid == 5)._is_implicitly_boolean
        assert (self.table1.c.myid == 5).self_group()._is_implicitly_boolean
        assert (self.table1.c.myid == 5).label("x")._is_implicitly_boolean
        assert not_(self.table1.c.myid == 5)._is_implicitly_boolean
        assert or_(
            self.table1.c.myid == 5, self.table1.c.myid == 7
        )._is_implicitly_boolean
        assert not column("x", Boolean)._is_implicitly_boolean
        assert not (self.table1.c.myid + 5)._is_implicitly_boolean
        assert not not_(column("x", Boolean))._is_implicitly_boolean
        assert (
            not select([self.table1.c.myid])
            .scalar_subquery()
            ._is_implicitly_boolean
        )
        assert not text("x = y")._is_implicitly_boolean
        assert not literal_column("x = y")._is_implicitly_boolean 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def test_in_cte_implicit(self):
        t = table("t", column("x"))

        stmt = select([t.c.x]).cte()

        with expect_warnings(
            r"Coercing CTE object into a select\(\) for use in "
            r"IN\(\); please pass a select\(\) construct explicitly",
        ):
            s2 = select([column("q").in_(stmt)])

        self.assert_compile(
            s2,
            "WITH anon_2 AS (SELECT t.x AS x FROM t) "
            "SELECT q IN (SELECT anon_2.x FROM anon_2) AS anon_1",
        ) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def list_bad_replicas_status(state=BadFilesStatus.BAD, rse_id=None, younger_than=None, older_than=None, limit=None, list_pfns=False, session=None):
    """
    List the bad file replicas history states. Method used by the rucio-ui.
    :param state: The state of the file (SUSPICIOUS or BAD).
    :param rse_id: The RSE id.
    :param younger_than: datetime object to select bad replicas younger than this date.
    :param older_than:  datetime object to select bad replicas older than this date.
    :param limit: The maximum number of replicas returned.
    :param session: The database session in use.
    """
    result = []
    query = session.query(models.BadReplicas.scope, models.BadReplicas.name, models.BadReplicas.rse_id, models.BadReplicas.state, models.BadReplicas.created_at, models.BadReplicas.updated_at)
    if state:
        query = query.filter(models.BadReplicas.state == state)
    if rse_id:
        query = query.filter(models.BadReplicas.rse_id == rse_id)
    if younger_than:
        query = query.filter(models.BadReplicas.created_at >= younger_than)
    if older_than:
        query = query.filter(models.BadReplicas.created_at <= older_than)
    if limit:
        query = query.limit(limit)

    for badfile in query.yield_per(1000):
        if list_pfns:
            result.append({'scope': badfile.scope, 'name': badfile.name, 'type': DIDType.FILE})
        else:
            result.append({'scope': badfile.scope, 'name': badfile.name, 'rse': get_rse_name(rse_id=badfile.rse_id, session=session), 'rse_id': badfile.rse_id, 'state': badfile.state, 'created_at': badfile.created_at, 'updated_at': badfile.updated_at})
    if list_pfns:
        reps = []
        for rep in list_replicas(result, schemes=None, unavailable=False, request_id=None, ignore_availability=True, all_states=True, session=session):
            pfn = None
            if rse_id in rep['rses'] and rep['rses'][rse_id]:
                pfn = rep['rses'][rse_id][0]
                if pfn and pfn not in reps:
                    reps.append(pfn)
            else:
                reps.extend([item for row in rep['rses'].values() for item in row])
        list(set(reps))
        result = reps
    return result 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def list_dids_by_meta(scope, select, session=None):
    """
    Add or update the given metadata to the given did

    :param scope: the scope of the did
    :param name: the name of the did
    :param meta: the metadata to be added or updated
    """
    # Currently for sqlite only add, get and delete is implemented.
    if session.bind.dialect.name == 'sqlite':
        raise NotImplementedError
    if session.bind.dialect.name == 'oracle':
        oracle_version = int(session.connection().connection.version.split('.')[0])
        if oracle_version < 12:
            raise NotImplementedError

    query = session.query(models.DidMeta)
    if scope is not None:
        query = query.filter(models.DidMeta.scope == scope)

    for k, v in iteritems(select):
        if session.bind.dialect.name == 'oracle':
            query = query.filter(text("json_exists(meta,'$.%s?(@==''%s'')')" % (k, v)))
        else:
            query = query.filter(cast(models.DidMeta.meta[k], String) == type_coerce(v, JSON))
    dids = list()
    for row in query.yield_per(10):
        dids.append({'scope': row.scope, 'name': row.name})
    return dids 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def list_quarantined_replicas(rse_id, limit, worker_number=None, total_workers=None, session=None):
    """
    List RSE Quarantined File replicas.

    :param rse_id: the rse id.
    :param limit: The maximum number of replicas returned.
    :param worker_number:      id of the executing worker.
    :param total_workers:      Number of total workers.
    :param session: The database session in use.

    :returns: a list of dictionary replica.
    """

    query = session.query(models.QuarantinedReplica.path,
                          models.QuarantinedReplica.bytes,
                          models.QuarantinedReplica.scope,
                          models.QuarantinedReplica.name,
                          models.QuarantinedReplica.created_at).\
        filter(models.QuarantinedReplica.rse_id == rse_id)

    # do no delete valid replicas
    stmt = exists(select([1]).prefix_with("/*+ index(REPLICAS REPLICAS_PK) */", dialect='oracle')).\
        where(and_(models.RSEFileAssociation.scope == models.QuarantinedReplica.scope,
                   models.RSEFileAssociation.name == models.QuarantinedReplica.name,
                   models.RSEFileAssociation.rse_id == models.QuarantinedReplica.rse_id))
    query = query.filter(not_(stmt))
    query = filter_thread_work(session=session, query=query, total_threads=total_workers, thread_id=worker_number, hash_variable='path')
    return [{'path': path,
             'rse_id': rse_id,
             'created_at': created_at,
             'scope': scope,
             'name': name,
             'bytes': bytes}
            for path, bytes, scope, name, created_at in query.limit(limit)] 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def members(self, ref, cuts=None, order=None, page=None, page_size=None):
        """ List all the distinct members of the given reference, filtered and
        paginated. If the reference describes a dimension, all attributes are
        returned. """
        def prep(cuts, ref, order, columns=None):
            q = select(columns=columns)
            bindings = []
            cuts, q, bindings = Cuts(self).apply(q, bindings, cuts)
            fields, q, bindings = \
                Fields(self).apply(q, bindings, ref, distinct=True)
            ordering, q, bindings = \
                Ordering(self).apply(q, bindings, order, distinct=fields[0])
            q = self.restrict_joins(q, bindings)
            return q, bindings, cuts, fields, ordering

        # Count
        count = count_results(self, prep(cuts, ref, order, [1])[0])

        # Member list
        q, bindings, cuts, fields, ordering = prep(cuts, ref, order)
        page, q = Pagination(self).apply(q, page, page_size)
        q = self.restrict_joins(q, bindings)
        return {
            'total_member_count': count,
            'data': list(generate_results(self, q)),
            'cell': cuts,
            'fields': fields,
            'order': ordering,
            'page': page['page'],
            'page_size': page['page_size']
        } 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def facts(self, fields=None, cuts=None, order=None, page=None,
              page_size=None, page_max=None):
        """ List all facts in the cube, returning only the specified references
        if these are specified. """

        def prep(cuts, columns=None):
            q = select(columns=columns).select_from(self.fact_table)
            bindings = []
            _, q, bindings = Cuts(self).apply(q, bindings, cuts)
            q = self.restrict_joins(q, bindings)
            return q, bindings

        # Count
        count = count_results(self, prep(cuts, [1])[0])

        # Facts
        q, bindings = prep(cuts)
        fields, q, bindings = Fields(self).apply(q, bindings, fields)
        ordering, q, bindings = Ordering(self).apply(q, bindings, order)
        page, q = Pagination(self).apply(q, page, page_size, page_max)
        q = self.restrict_joins(q, bindings)
        return {
            'total_fact_count': count,
            'data': list(generate_results(self, q)),
            'cell': cuts,
            'fields': fields,
            'order': ordering,
            'page': page['page'],
            'page_size': page['page_size']
        } 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def count_results(cube, q):
    """ Get the count of records matching the query. """
    q = select(columns=[func.count(True)], from_obj=q.alias())
    return cube.engine.execute(q).scalar() 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def wait_for_connection(exit_event):
    """Helper method to wait for DB connection"""
    down = True
    while down and not exit_event.is_set():
        try:
            LOG.debug('Trying to re-establish connection to database.')
            get_engine().scalar(select([1]))
            down = False
            LOG.debug('Connection to database re-established.')
        except Exception:
            retry_interval = cfg.CONF.database.retry_interval
            LOG.exception('Connection to database failed. Retrying in %s '
                          'seconds.', retry_interval)
            time.sleep(retry_interval) 

# 需要导入模块: from sqlalchemy.sql import expression [as 别名]
# 或者: from sqlalchemy.sql.expression import select [as 别名]
def exists_by_expr(t, expr):
    # TODO: use exists instead of count
    q = select([func.count("*").label("c")], from_obj=t).where(expr)
    r = meta.DBSession.execute(q).fetchone()
    if r.c > 0:
        return True
    else:
        return False