我们从Python开源项目中，提取了以下14个代码示例，用于说明如何使用sqlalchemy.sql.expression.and_()。
def parse(self, data): self.parser.username = self.username result = self.parser.parse(data, lexer=self.lexer) tables = self.findTables(result) if boartty.db.project_table in tables: result = and_(boartty.db.story_table.c.project_key == boartty.db.project_table.c.key, result) tables.remove(boartty.db.project_table) if boartty.db.tag_table in tables: result = and_(boartty.db.story_tag_table.c.tag_key == boartty.db.tag_table.c.key, boartty.db.story_tag_table.c.story_key == boartty.db.story_table.c.key, result) tables.remove(boartty.db.tag_table) if boartty.db.user_table in tables: result = and_(boartty.db.story_table.c.user_key == boartty.db.user_table.c.key, result) tables.remove(boartty.db.user_table) #if boartty.db.file_table in tables: # result = and_(boartty.db.file_table.c.revision_key == boartty.db.revision_table.c.key, # boartty.db.revision_table.c.story_key == boartty.db.story_table.c.key, # result) # tables.remove(boartty.db.file_table) if tables: raise Exception("Unknown table in search: %s" % tables) return result
def get_rse_account_usage(rse, session=None): """ Returns the account limit and usage for all for all accounts on a RSE. :param rse: The RSE name. :param session: Database session in use. :return: List of dictionnaries. """ result = [] rse_id = get_rse_id(rse=rse, session=session) query = session.query(models.AccountUsage.account, models.AccountUsage.files, models.AccountUsage.bytes, models.AccountLimit.bytes) query = query.join(models.AccountLimit, and_(models.AccountUsage.account == models.AccountLimit.account, models.AccountUsage.rse_id == models.AccountLimit.rse_id)).filter(models.AccountUsage.rse_id == rse_id) account_limits_tmp = query.all() for row in account_limits_tmp: result.append({'rse': rse, 'account': row[0], 'used_files': row[1], 'used_bytes': row[2], 'quota_bytes': row[3]}) return result
def _build_filter_expressions(self, conditions, default_op, relationships): """ :param conditions: conditions dictionary :type conditions: dict :param default_op: a default operator to join all filter expressions :type default_op: function :param relationships: a dict with all joins to apply, describes current state in recurrent calls :type relationships: dict :return: expressions list :rtype: list """ if default_op is None: default_op = and_ expressions = [] for arg, value in conditions.items(): if arg in self._logical_operators: expression = self._parse_logical_op(arg, value, self._logical_operators[arg], relationships) else: expression = self._parse_tokens(self.objects_class, arg.split('__'), value, relationships, lambda c, n, v: operators.eq(n, self.deserialize_column(c, v))) if expression is not None: expressions.append(expression) result = None if len(expressions) > 1: result = default_op(*expressions) if default_op != not_ else not_(and_(*expressions)) elif len(expressions) == 1: result = expressions[0] if default_op != not_ else not_(expressions[0]) return result
def _parse_logical_op(self, arg, value, default_op, relationships): """ :param arg: condition name :type arg: str :param value: condition value :type value: dict | list :param default_op: a default operator to join all filter expressions :type default_op: function :param relationships: a dict with all joins to apply, describes current state in recurrent calls :type relationships: dict :return: expressions list :rtype: list """ if isinstance(value, dict): return self._build_filter_expressions(value, default_op, relationships) if not isinstance(value, list): raise HTTPBadRequest('Invalid attribute', 'Filter attribute {} is invalid'.format(arg)) expressions = [] for subconditions in value: if not isinstance(subconditions, dict): raise HTTPBadRequest('Invalid attribute', 'Filter attribute {} is invalid'.format(arg)) subexpressions = self._build_filter_expressions(subconditions, and_, relationships) if subexpressions is not None: expressions.append(subexpressions) result = None if len(expressions) > 1: result = default_op(*expressions) if default_op != not_ else not_(and_(*expressions)) elif len(expressions) == 1: result = expressions[0] if default_op != not_ else not_(expressions[0]) return result
def get_account_limits(account, rse_ids=None, session=None): """ Returns the account limits for the account on the list of rses. :param account: Account to check the limit for. :param rse_ids: List of RSE ids to check the limit for. :param session: Database session in use. :return: Dictionary {'rse_id': bytes, ...}. """ account_limits = {} if rse_ids: rse_id_clauses = [] for rse_id in rse_ids: rse_id_clauses.append(and_(models.AccountLimit.rse_id == rse_id, models.AccountLimit.account == account)) rse_id_clause_chunks = [rse_id_clauses[x:x + 10] for x in xrange(0, len(rse_id_clauses), 10)] for rse_id_chunk in rse_id_clause_chunks: tmp_limits = session.query(models.AccountLimit).filter(or_(*rse_id_chunk)).all() for limit in tmp_limits: if limit.bytes == -1: account_limits[limit.rse_id] = float("inf") else: account_limits[limit.rse_id] = limit.bytes else: account_limits_tmp = session.query(models.AccountLimit).filter(models.AccountLimit.account == account).all() for limit in account_limits_tmp: if limit.bytes == -1: account_limits[limit.rse_id] = float("inf") else: account_limits[limit.rse_id] = limit.bytes return account_limits
def get_roles(user_id, discussion_id=None): if user_id in SYSTEM_ROLES: return [user_id] session = get_session_maker()() roles = session.query(Role.name).join(UserRole).filter( UserRole.user_id == user_id) if discussion_id: roles = roles.union( session.query(Role.name).join( LocalUserRole).filter(and_( LocalUserRole.user_id == user_id, LocalUserRole.requested == False, LocalUserRole.discussion_id == discussion_id))) return [x[0] for x in roles.distinct()]
def get_permissions(user_id, discussion_id): user_id = user_id or Everyone session = get_session_maker()() if user_id == Everyone: if not discussion_id: return [] permissions = session.query(Permission.name).join( DiscussionPermission, Role).filter( (DiscussionPermission.discussion_id == discussion_id) & (Role.name == user_id)) elif user_id == Authenticated: if not discussion_id: return [] permissions = session.query(Permission.name).join( DiscussionPermission, Role).filter( (DiscussionPermission.discussion_id == discussion_id) & (Role.name.in_((Authenticated, Everyone)))) else: sysadmin = session.query(UserRole).filter_by( user_id=user_id).join(Role).filter_by(name=R_SYSADMIN).first() if sysadmin: return [x[0] for x in session.query(Permission.name).all()] if not discussion_id: return [] permissions = session.query(Permission.name).join( DiscussionPermission, Role, UserRole).filter( UserRole.user_id == user_id, DiscussionPermission.discussion_id == discussion_id ).union(session.query(Permission.name).join( DiscussionPermission, Role, LocalUserRole).filter(and_( LocalUserRole.user_id == user_id, LocalUserRole.requested == False, LocalUserRole.discussion_id == discussion_id, DiscussionPermission.discussion_id == discussion_id)) ).union(session.query(Permission.name).join( DiscussionPermission, Role).filter(and_( DiscussionPermission.discussion_id == discussion_id, Role.name.in_((Authenticated, Everyone))))) return [x[0] for x in permissions.distinct()]
def discussions_with_access(userid, permission=P_READ): from ..models import Discussion userid = userid or Everyone db = Discussion.default_db if userid == Everyone: return db.query(Discussion).join( DiscussionPermission, Role, Permission).filter(and_( Permission.name == permission, Role.name == userid)) elif userid == Authenticated: return db.query(Discussion).join( DiscussionPermission, Role, Permission).filter(and_( Permission.name == permission, Role.name.in_((Authenticated, Everyone)))) else: sysadmin = db.query(UserRole).filter_by( user_id=userid).join(Role).filter_by(name=R_SYSADMIN).first() if sysadmin: return db.query(Discussion).all() perms = db.query(DiscussionPermission).join( Role, Permission, UserRole, User).filter( User.id == userid).filter( Permission.name == permission ).union(db.query(DiscussionPermission).join( Role, Permission).join( LocalUserRole, and_( LocalUserRole.discussion_id == DiscussionPermission.discussion_id, LocalUserRole.requested == False) ).join(User).filter( User.id == userid).filter( Permission.name == permission) ).union(db.query(DiscussionPermission).join( Role, Permission).filter( Role.name.in_((Authenticated, Everyone))).filter( Permission.name == permission) ) return db.query(Discussion).join(perms.subquery('perms'))
def roles_with_permission(discussion, permission=P_READ): return [x for (x,) in discussion.db.query(Role.name).join( DiscussionPermission).join(Permission).filter(and_( Permission.name == permission, DiscussionPermission.discussion == discussion))]
def roles_with_permissions(discussion, *permissions): return [x for (x,) in discussion.db.query(Role.name).join( DiscussionPermission).join(Permission).filter(and_( Permission.name.in_(permissions), DiscussionPermission.discussion == discussion))]
def users_with_permission(discussion_id, permission, id_only=True): from ..models import Discussion # assume all ids valid db = Discussion.default_db user_ids = db.query(User.id).join( LocalUserRole, Role, DiscussionPermission, Permission).filter(and_( Permission.name == permission, LocalUserRole.requested == False, LocalUserRole.discussion_id == discussion_id, DiscussionPermission.discussion_id == discussion_id) ).union( db.query(User.id).join( UserRole, Role, DiscussionPermission, Permission).filter( and_( Permission.name == permission, DiscussionPermission.discussion_id == discussion_id)) ).union( db.query(User.id).join( UserRole, Role).filter( and_( Role.name == R_SYSADMIN, DiscussionPermission.discussion_id == discussion_id)) ).distinct() if id_only: return [AgentProfile.uri_generic(id) for (id, ) in user_ids] else: return db.query(AgentProfile).filter(AgentProfile.id.in_(user_ids)).all()
def prefetch(session, discussion_id): from assembl.lib.sqla import class_registry from assembl.models import DiscussionBoundBase for name, cls in class_registry.items(): if issubclass(cls, DiscussionBoundBase) and not isabstract(cls): mapper = class_mapper(cls) undefers = [undefer(attr.key) for attr in mapper.iterate_properties if getattr(attr, 'deferred', False)] conditions = cls.get_discussion_conditions(discussion_id) session.query(with_polymorphic(cls, "*")).filter( and_(*conditions)).options(*undefers).all()
def create_query(self, id_only=True, tombstones=False): from assembl.models import TombstonableMixin cls = self._class alias = self.class_alias if id_only: query = self._class.default_db.query(alias.id) else: query = self._class.default_db.query(alias) # TODO: Distinguish tombstone condition from other base_conditions if issubclass(cls, TombstonableMixin) and not tombstones: query = query.filter(and_(*cls.base_conditions(alias))) return query
def delete_discussion(session, discussion_id): from assembl.models import ( Base, Discussion, DiscussionBoundBase, Preferences, LangStringEntry) # delete anything related first classes = DiscussionBoundBase._decl_class_registry.values() classes_by_table = defaultdict(list) for cls in classes: if isinstance(cls, type): classes_by_table[getattr(cls, '__table__', None)].append(cls) # Only direct subclass of abstract def is_concrete_class(cls): if isabstract(cls): return False for (i, cls) in enumerate(cls.mro()): if not i: continue if not issubclass(cls, Base): continue return isabstract(cls) concrete_classes = set([cls for cls in itertools.chain( *list(classes_by_table.values())) if issubclass(cls, DiscussionBoundBase) and is_concrete_class(cls)]) concrete_classes.add(Preferences) concrete_classes.add(LangStringEntry) tables = DiscussionBoundBase.metadata.sorted_tables # Special case for preferences discussion = session.query(Discussion).get(discussion_id) session.delete(discussion.preferences) # tables.append(Preferences.__table__) tables.reverse() for table in tables: if table not in classes_by_table: continue for cls in classes_by_table[table]: if cls not in concrete_classes: continue print('deleting', cls.__name__) query = session.query(cls.id) if hasattr(cls, "get_discussion_conditions"): conds = cls.get_discussion_conditions(discussion_id) else: continue assert conds cond = and_(*conds) v = JoinColumnsVisitor(cls, query, classes_by_table) v.traverse(cond) query = v.final_query().filter(cond) if query.count(): print("*" * 20, "Not all deleted!") ids = query.all() for subcls in cls.mro(): if getattr(subcls, '__tablename__', None): session.query(subcls).filter( subcls.id.in_(ids)).delete(False) session.flush()
