SQLAlchemy如何筛选子属性完全匹配指定列表的ORM对象

问题背景 #

在Person与Skill的一对多关联场景中，需要筛选恰好且仅拥有目标技能的Person记录。当前查询逻辑会返回同时持有目标技能和其他技能的人员，不符合预期。

现有模型定义 #

class Person(Base):
    __tablename__ = "person"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    skills = relationship("Skill", back_populates="person")


class Skill(Base):
    __tablename__ = "skill"
    id = Column(Integer, primary_key=True)
    skill_name = Column(String(20))
    person_id = Column(Integer, ForeignKey("person.id"))
    person = relationship("Person", back_populates="skills")

测试数据 #

# Person表
id=1, name=Kelly
id=2, name=William
id=3, name=Jerry

# Skill表
id=1, skill_name=Excel, person_id=1
id=2, skill_name=Excel, person_id=2
id=3, skill_name=Python, person_id=2
id=4, skill_name=Social, person_id=3

人员技能对应关系：

• Kelly：仅掌握Excel
• William：掌握Excel、Python
• Jerry：仅掌握社交技能

问题复现 #

原查询存在两处问题：

1. 字段笔误：Skill表存储技能名称的字段为skill_name，原查询写为Skill.name
2. 逻辑缺失：仅筛选了关联有Excel技能的人员，没有排除持有Excel之外其他技能的记录
   原查询代码：

q = session.query(Person).join(Skill).filter(Skill.name == "Excel").all()

执行后会同时返回Kelly和William，不符合「仅返回只掌握Excel的人员」的预期，预期结果应只有Kelly。

解决方案 #

模型定义本身没有错误，无需调整表结构或关联关系，只需要修正查询逻辑，同时满足两个筛选条件即可：

• 人员必须关联目标技能
• 人员没有任何目标技能之外的其他关联技能

方案1：聚合分组过滤（通用性强） #

通过分组聚合统计人员关联的技能情况，用having子句做条件过滤：

from sqlalchemy import func

target_skill = "Excel"
q = session.query(Person)\
    .join(Skill)\
    .group_by(Person.id)\
    .having(
        func.count(Skill.id) == 1,
        func.sum(Skill.skill_name != target_skill) == 0
    ).all()

逻辑说明：

• func.count(Skill.id) == 1：限制人员关联的技能总数量为1
• func.sum(Skill.skill_name != target_skill) == 0：校验不存在任何非目标技能，避免异常数据干扰结果

方案2：NOT EXISTS子查询过滤（大表性能更优） #

通过子查询排除所有持有非目标技能的人员，语义更直观，数据量大时执行效率更高：

target_skill = "Excel"
# 构造子查询：匹配持有非目标技能的人员
exist_other_skill = session.query(Skill.person_id)\
    .filter(Skill.skill_name != target_skill)\
    .exists()

q = session.query(Person)\
    .join(Skill)\
    .filter(
        Skill.skill_name == target_skill,
        ~exist_other_skill.where(Skill.person_id == Person.id)
    ).all()

两种方案执行后都只会返回仅掌握Excel的Kelly，完全符合预期。如果需要筛选同时掌握多个指定技能、且没有其他额外技能的场景，只需要调整having或子查询的判断逻辑即可。

内容的提问来源于stack exchange，提问作者Marcos Garcia