Python sqlalchemy.join() Examples

def batch_load_by_name_user(cls, context, names):
        async with context['dbpool'].acquire() as conn:
            access_key = context['access_key']
            j = sa.join(
                keypairs, keypair_resource_policies,
                keypairs.c.resource_policy == keypair_resource_policies.c.name
            )
            query = (sa.select([keypair_resource_policies])
                       .select_from(j)
                       .where((keypair_resource_policies.c.name.in_(names)) &
                              (keypairs.c.access_key == access_key))
                       .order_by(keypair_resource_policies.c.name))
            return await batch_result(
                context, conn, query, cls,
                names, lambda row: row['name'],
            ) 

def __init__(self, left, right, onclause=None, isouter=False):
        """Construct a new :class:`.Join`.

        The usual entrypoint here is the :func:`~.expression.join`
        function or the :meth:`.FromClause.join` method of any
        :class:`.FromClause` object.

        """
        self.left = _interpret_as_from(left)
        self.right = _interpret_as_from(right).self_group()

        if onclause is None:
            self.onclause = self._match_primaries(self.left, self.right)
        else:
            self.onclause = onclause

        self.isouter = isouter 

def usage_per_month(request: web.Request, params: Any) -> web.Response:
    '''
    Return usage statistics of terminated containers belonged to the given group for a specified
    period in dates.
    The date/time comparison is done using the configured timezone.

    :param year int: The year.
    :param month int: The month.
    '''
    log.info('USAGE_PER_MONTH (g:[{}], month:{})',
             ','.join(params['group_ids']), params['month'])
    local_tz = request.app['config']['system']['timezone']
    try:
        start_date = datetime.strptime(params['month'], '%Y%m').replace(tzinfo=local_tz)
        end_date = start_date + relativedelta(months=+1)
    except ValueError:
        raise InvalidAPIParameters(extra_msg='Invalid date values')
    resp = await get_container_stats_for_period(request, start_date, end_date, params['group_ids'])
    log.debug('container list are retrieved for month {0}', params['month'])
    return web.json_response(resp, status=200) 

def batch_load_by_email(
        cls, context, user_ids, *,
        domain_name=None, is_active=None,
    ) -> Sequence[Sequence[Optional[KeyPair]]]:
        from .user import users
        async with context['dbpool'].acquire() as conn:
            j = sa.join(keypairs, users, keypairs.c.user == users.c.uuid)
            query = (sa.select([keypairs])
                       .select_from(j)
                       .where(keypairs.c.user_id.in_(user_ids)))
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if is_active is not None:
                query = query.where(keypairs.c.is_active == is_active)
            return await batch_multiresult(
                context, conn, query, cls,
                user_ids, lambda row: row['user_id'],
            ) 

def __init__(self, left, right, onclause=None, isouter=False):
        """Construct a new :class:`.Join`.

        The usual entrypoint here is the :func:`~.expression.join`
        function or the :meth:`.FromClause.join` method of any
        :class:`.FromClause` object.

        """
        self.left = _interpret_as_from(left)
        self.right = _interpret_as_from(right).self_group()

        if onclause is None:
            self.onclause = self._match_primaries(self.left, self.right)
        else:
            self.onclause = onclause

        self.isouter = isouter 

def load_count(
        context, *,
        domain_name=None,
        email=None,
        is_active=None,
    ) -> int:
        from .user import users
        async with context['dbpool'].acquire() as conn:
            j = sa.join(keypairs, users, keypairs.c.user == users.c.uuid)
            query = (
                sa.select([sa.func.count(keypairs.c.access_key)])
                .select_from(j)
                .as_scalar()
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if email is not None:
                query = query.where(keypairs.c.user_id == email)
            if is_active is not None:
                query = query.where(keypairs.c.is_active == is_active)
            result = await conn.execute(query)
            count = await result.fetchone()
            return count[0] 

def load_all(
        cls, context, *,
        domain_name=None,
        is_active=None,
        limit=None,
    ) -> Sequence[KeyPair]:
        from .user import users
        async with context['dbpool'].acquire() as conn:
            j = sa.join(keypairs, users, keypairs.c.user == users.c.uuid)
            query = (
                sa.select([keypairs])
                .select_from(j)
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if is_active is not None:
                query = query.where(keypairs.c.is_active == is_active)
            if limit is not None:
                query = query.limit(limit)
            return [
                cls.from_row(context, row) async for row in conn.execute(query)
            ] 

def add(self, local_columns, remote_columns, **kwargs):
        """ Add a new foreign key

        :param remote_field: The column of the remote model
        :rtype: MigrationConstraintForeignKey instance
        """
        remote_columns = [
            x.property.columns[0] if hasattr(x, 'property') else x
            for x in remote_columns]

        remote_table = set(x.table.name for x in remote_columns)
        if len(remote_table) != 1:
            raise MigrationException("Remote column must have the same table "
                                     "(%s)" % ', '.join(remote_table))

        remote_table = remote_table.pop()
        remote_columns_names = [x.name for x in remote_columns]
        self.table.migration.operation.create_foreign_key(
            self.name, self.table.name, remote_table,
            local_columns, remote_columns_names,
            source_schema=self.table.schema,
            referent_schema=remote_columns[0].table.schema,
            **kwargs)
        return self 

def __init__(self, left, right, onclause=None, isouter=False, full=False):
        """Construct a new :class:`.Join`.

        The usual entrypoint here is the :func:`~.expression.join`
        function or the :meth:`.FromClause.join` method of any
        :class:`.FromClause` object.

        """
        self.left = _interpret_as_from(left)
        self.right = _interpret_as_from(right).self_group()

        if onclause is None:
            self.onclause = self._match_primaries(self.left, self.right)
        else:
            self.onclause = onclause

        self.isouter = isouter
        self.full = full 

def batch_load_detail(cls, context, session_ids, *,
                                domain_name=None, access_key=None):
        async with context['dbpool'].acquire() as conn:
            j = (
                kernels
                .join(groups, groups.c.id == kernels.c.group_id)
                .join(users, users.c.uuid == kernels.c.user_uuid)
            )
            query = (
                sa.select([kernels, groups.c.name, users.c.email])
                .select_from(j)
                .where(
                    (kernels.c.role == 'master') &
                    (kernels.c.id.in_(session_ids))
                ))
            if domain_name is not None:
                query = query.where(kernels.c.domain_name == domain_name)
            if access_key is not None:
                query = query.where(kernels.c.access_key == access_key)
            return await batch_result(
                context, conn, query, cls,
                session_ids, lambda row: row['id'],
            ) 

def batch_load_by_dependency(cls, context, session_ids):
        async with context['dbpool'].acquire() as conn:
            j = sa.join(
                kernels, kernel_dependencies,
                kernels.c.id == kernel_dependencies.c.depends_on,
            )
            query = (
                sa.select([kernels])
                .select_from(j)
                .where(
                    (kernels.c.role == 'master') &
                    (kernel_dependencies.c.kernel_id.in_(session_ids))
                )
            )
            return await batch_multiresult(
                context, conn, query, cls,
                session_ids, lambda row: row['id'],
            ) 

def batch_load_detail(cls, context, container_ids, *,
                                domain_name=None, access_key=None):
        async with context['dbpool'].acquire() as conn:
            j = (
                kernels
                .join(groups, groups.c.id == kernels.c.group_id)
                .join(users, users.c.uuid == kernels.c.user_uuid)
            )
            query = (
                sa.select([kernels, groups.c.name, users.c.email])
                .select_from(j)
                .where(
                    (kernels.c.id.in_(container_ids))
                ))
            if domain_name is not None:
                query = query.where(kernels.c.domain_name == domain_name)
            if access_key is not None:
                query = query.where(kernels.c.access_key == access_key)
            return await batch_result(
                context, conn, query, cls,
                container_ids, lambda row: row['id'],
            ) 

def batch_load_by_user(cls, context, user_uuids, *,
                                 domain_name=None, group_id=None):
        from .user import users
        async with context['dbpool'].acquire() as conn:
            # TODO: num_attached count group-by
            j = sa.join(vfolders, users, vfolders.c.user == users.c.uuid)
            query = (
                sa.select([vfolders])
                .select_from(j)
                .where(vfolders.c.user.in_(user_uuids))
                .order_by(sa.desc(vfolders.c.created_at))
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if group_id is not None:
                query = query.where(vfolders.c.group == group_id)
            return await batch_multiresult(
                context, conn, query, cls,
                user_uuids, lambda row: row['user']
            ) 

def load_slice(cls, context, limit, offset, *,
                         domain_name=None, group_id=None, user_id=None,
                         order_key=None, order_asc=None):
        from .user import users
        async with context['dbpool'].acquire() as conn:
            if order_key is None:
                _ordering = vfolders.c.created_at
            else:
                _order_func = sa.asc if order_asc else sa.desc
                _ordering = _order_func(getattr(vfolders.c, order_key))
            j = sa.join(vfolders, users, vfolders.c.user == users.c.uuid)
            query = (
                sa.select([vfolders])
                .select_from(j)
                .order_by(_ordering)
                .limit(limit)
                .offset(offset)
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if group_id is not None:
                query = query.where(vfolders.c.group == group_id)
            if user_id is not None:
                query = query.where(vfolders.c.user == user_id)
            return [cls.from_row(context, r) async for r in conn.execute(query)] 

def load_count(cls, context, *,
                         domain_name=None, group_id=None, user_id=None):
        from .user import users
        async with context['dbpool'].acquire() as conn:
            j = sa.join(vfolders, users, vfolders.c.user == users.c.uuid)
            query = (
                sa.select([sa.func.count(vfolders.c.id)])
                .select_from(j)
                .as_scalar()
            )
            if domain_name is not None:
                query = query.where(users.c.domain_name == domain_name)
            if group_id is not None:
                query = query.where(vfolders.c.group == group_id)
            if user_id is not None:
                query = query.where(vfolders.c.user == user_id)
            result = await conn.execute(query)
            count = await result.fetchone()
            return count[0] 

def largest_groups(cls, limit=10):
        member = table('member')
        package = table('package')

        j = join(member, package,
                 member.c.table_id == package.c.id)

        s = select([member.c.group_id, func.count(member.c.table_id)]).\
            select_from(j).\
            group_by(member.c.group_id).\
            where(and_(member.c.group_id!=None, member.c.table_name=='package', package.c.private==False, package.c.state=='active')).\
            order_by(func.count(member.c.table_id).desc()).\
            limit(limit)

        res_ids = model.Session.execute(s).fetchall()
        res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), val) for group_id, val in res_ids]
        return res_groups 

def __init__(self, left, right, onclause=None, isouter=False, full=False):
        """Construct a new :class:`.Join`.

        The usual entrypoint here is the :func:`~.expression.join`
        function or the :meth:`.FromClause.join` method of any
        :class:`.FromClause` object.

        """
        self.left = _interpret_as_from(left)
        self.right = _interpret_as_from(right).self_group()

        if onclause is None:
            self.onclause = self._match_primaries(self.left, self.right)
        else:
            self.onclause = onclause

        self.isouter = isouter
        self.full = full 

def load_all_user(cls, context, access_key):
        async with context['dbpool'].acquire() as conn:
            query = (sa.select([keypairs.c.user_id])
                       .select_from(keypairs)
                       .where(keypairs.c.access_key == access_key))
            result = await conn.execute(query)
            row = await result.fetchone()
            user_id = row['user_id']
            j = sa.join(
                keypairs, keypair_resource_policies,
                keypairs.c.resource_policy == keypair_resource_policies.c.name
            )
            query = (sa.select([keypair_resource_policies])
                       .select_from(j)
                       .where((keypairs.c.user_id == user_id)))
            return [cls.from_row(context, r) async for r in conn.execute(query)] 

def format_name(self, *columns):
        if columns:
            cols = [x.name for x in columns]
            cols.sort()
            cols = '_'.join(cols)
            return 'idx_%s_on_%s' % (cols, self.table.name)

        return None 

def suffix_with(self, *expr, **kw):
        r"""Add one or more expressions following the statement as a whole.

        This is used to support backend-specific suffix keywords on
        certain constructs.

        E.g.::

            stmt = select([col1, col2]).cte().suffix_with(
                "cycle empno set y_cycle to 1 default 0", dialect="oracle")

        Multiple suffixes can be specified by multiple calls
        to :meth:`.suffix_with`.

        :param \*expr: textual or :class:`.ClauseElement` construct which
         will be rendered following the target clause.
        :param \**kw: A single keyword 'dialect' is accepted.  This is an
         optional string dialect name which will
         limit rendering of this suffix to only that dialect.

        """
        dialect = kw.pop('dialect', None)
        if kw:
            raise exc.ArgumentError("Unsupported argument(s): %s" %
                                    ",".join(kw))
        self._setup_suffixes(expr, dialect) 

def most_edited_packages(cls, limit=10):
        package_revision = table('package_revision')
        package = table('package')

        s = select([package_revision.c.id, func.count(package_revision.c.revision_id)], from_obj=[package_revision.join(package)]).\
            where(and_(package.c.private==False, package.c.state=='active', )).\
            group_by(package_revision.c.id).\
            order_by(func.count(package_revision.c.revision_id).desc()).\
            limit(limit)
        res_ids = model.Session.execute(s).fetchall()
        res_pkgs = [(model.Session.query(model.Package).get(unicode(pkg_id)), val) for pkg_id, val in res_ids]
        return res_pkgs 

def init_change_pk(self, diff):
        name, table, constraint = diff
        raise MigrationException(
            (
                "Change primary key constraint %s on %s: (%s). "
                "AnyBlok can't determine the good action to do "
                "for relation ship based on primary key who changed, "
                "You must make the migration by your self before."
            ) % (name, table, ', '.join([x.name for x in constraint.columns]))) 

def _joincond_trim_constraints(
            cls, a, b, constraints, consider_as_foreign_keys):
        # more than one constraint matched.  narrow down the list
        # to include just those FKCs that match exactly to
        # "consider_as_foreign_keys".
        if consider_as_foreign_keys:
            for const in list(constraints):
                if set(f.parent for f in const.elements) != set(
                        consider_as_foreign_keys):
                    del constraints[const]

        # if still multiple constraints, but
        # they all refer to the exact same end result, use it.
        if len(constraints) > 1:
            dedupe = set(tuple(crit) for crit in constraints.values())
            if len(dedupe) == 1:
                key = list(constraints)[0]
                constraints = {key: constraints[key]}

        if len(constraints) != 1:
            raise exc.AmbiguousForeignKeysError(
                "Can't determine join between '%s' and '%s'; "
                "tables have more than one foreign key "
                "constraint relationship between them. "
                "Please specify the 'onclause' of this "
                "join explicitly." % (a.description, b.description)) 

def init_add_constraint(self, diff):
        self.raise_if_withoutautomigration()
        _, constraint = diff
        columns = [x.name for x in constraint.columns]
        self.log_names.append('Add unique constraint on %s (%s)' % (
            constraint.table.name, ', '.join(columns))) 

def init_add_fk(self, diff):
        self.raise_if_withoutautomigration()
        _, fk = diff
        from_ = []
        to_ = []
        for column in fk.columns:
            for fk_ in column.foreign_keys:
                from_.append('%s.%s' % (fk.table.name, column.name))
                to_.append(fk_.target_fullname)

        self.log_names.append('Add Foreign keys on (%s) => (%s)' % (
            ', '.join(from_), ', '.join(to_))) 

def init_add_index(self, diff):
        self.raise_if_withoutautomigration()
        _, constraint = diff
        columns = [x.name for x in constraint.columns]
        if self.table_is_added(constraint.table):
            return True

        self.log_names.append('Add index constraint on %s (%s)' % (
            constraint.table.name, ', '.join(columns))) 

def get_deleted_packages(cls):
        '''
        @return: Returns list of deleted pkgs and date when they were deleted, in
                 format: [(id, date_ordinal), ...]
        '''
        def deleted_packages():
            # Can't filter by time in select because 'min' function has to
            # be 'for all time' else you get first revision in the time period.
            package_revision = table('package_revision')
            revision = table('revision')
            s = select([package_revision.c.id, func.min(revision.c.timestamp)], from_obj=[package_revision.join(revision)]).\
                where(package_revision.c.state==model.State.DELETED).\
                group_by(package_revision.c.id).\
                order_by(func.min(revision.c.timestamp))
            res = model.Session.execute(s).fetchall() # [(id, datetime), ...]
            res_pickleable = []
            for pkg_id, deleted_datetime in res:
                res_pickleable.append((pkg_id, deleted_datetime.toordinal()))
            return res_pickleable
        if cache_enabled:
            week_commences = cls.get_date_week_started(datetime.date.today())
            key = 'all_deleted_packages_%s' + week_commences.strftime(DATE_FORMAT)
            deleted_packages = our_cache.get_value(key=key,
                                                   createfunc=deleted_packages)
        else:
            deleted_packages = deleted_packages()
        return deleted_packages 

def get_package_revisions(cls):
        '''
        @return: Returns list of revisions and date of them, in
                 format: [(id, date), ...]
        '''
        package_revision = table('package_revision')
        revision = table('revision')
        s = select([package_revision.c.id, revision.c.timestamp], from_obj=[package_revision.join(revision)]).order_by(revision.c.timestamp)
        res = model.Session.execute(s).fetchall() # [(id, datetime), ...]
        return res 

def top_tags(cls, limit=10, returned_tag_info='object'): # by package
        assert returned_tag_info in ('name', 'id', 'object')
        tag = table('tag')
        package_tag = table('package_tag')
        package = table('package')
        if returned_tag_info == 'name':
            from_obj = [package_tag.join(tag)]
            tag_column = tag.c.name
        else:
            from_obj = None
            tag_column = package_tag.c.tag_id
        j = join(package_tag, package,
                 package_tag.c.package_id == package.c.id)
        s = select([tag_column, func.count(package_tag.c.package_id)],
                    from_obj=from_obj).\
            select_from(j).\
            where(and_(package_tag.c.state=='active', package.c.private == False, package.c.state == 'active' ))
        s = s.group_by(tag_column).\
            order_by(func.count(package_tag.c.package_id).desc()).\
            limit(limit)
        res_col = model.Session.execute(s).fetchall()
        if returned_tag_info in ('id', 'name'):
            return res_col
        elif returned_tag_info == 'object':
            res_tags = [(model.Session.query(model.Tag).get(unicode(tag_id)), val) for tag_id, val in res_col]
            return res_tags 

def _joincond_trim_constraints(
            cls, a, b, constraints, consider_as_foreign_keys):
        # more than one constraint matched.  narrow down the list
        # to include just those FKCs that match exactly to
        # "consider_as_foreign_keys".
        if consider_as_foreign_keys:
            for const in list(constraints):
                if set(f.parent for f in const.elements) != set(
                        consider_as_foreign_keys):
                    del constraints[const]

        # if still multiple constraints, but
        # they all refer to the exact same end result, use it.
        if len(constraints) > 1:
            dedupe = set(tuple(crit) for crit in constraints.values())
            if len(dedupe) == 1:
                key = list(constraints)[0]
                constraints = {key: constraints[key]}

        if len(constraints) != 1:
            raise exc.AmbiguousForeignKeysError(
                "Can't determine join between '%s' and '%s'; "
                "tables have more than one foreign key "
                "constraint relationship between them. "
                "Please specify the 'onclause' of this "
                "join explicitly." % (a.description, b.description))