如何使用id列表设置sqlalchemy关系?

Question

我想弄清楚如何使用外键列表更新关系。

以SQLAlchemy文档中的标准父/子关系为例:

class Parent(Base):
__tablename__ = "parent_table"
id: Mapped[int] = mapped_column(primary_key=True)
children: Mapped[list["Child"]] = relationship(back_populates="parent")

class Child(Base):
__tablename__ = "child_table"
id: Mapped[int] = mapped_column(primary_key=True)
parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"), nullable=True)
parent: Mapped["Parent"] = relationship(back_populates="children")

正常关系建模意味着，如果我想更新父对象的子对象列表，我需要拥有所有这些子对象。相反，我想在Parent类上有一个属性，这是相关Child类的id，但也可以更新。

我已经看到了这个答案:如何访问SQLAlchemy中关系的键列表?它给出了"阅读"方程的一边，但我如何写一个有效的setter来完成另一半呢?

注意:我的场景中的孩子已经存在。我怀疑我需要使用update_expression函数，以便建立一个sql查询，将去寻找现有的孩子，并将它们链接起来。但我不确定这是否正确……

这是我目前得到的。

@hybrid_property
def child_ids(self) -> List[uuid.UUID]:
return [child.id for child in self.children]
@child_ids.expression
def child_ids(self):
select([Child.id]).where(
Child.parent_id == self.id
)
@child_ids.setter
def child_ids(self, value):
# What here?
pass

为了完整起见，我特别询问如何在模型中使用properties/hybrid_properties来完成此操作。

Answer 1

这似乎是有效的，但我不确定它将与许多同时请求设置child_ids有多可靠。如果这是一个大问题，你可能会得到一个行锁在父同步调用设置child_ids。

• 删除和插入value和self.children的差异
• 你需要设置delete-orphan级联，否则当使用self.children.remove(child)
时，Child对象以Child(id=5, parent_id=None)结束

class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
@hybrid_property
def child_ids(self):
return [child.id for child in self.children]
@child_ids.expression
def child_ids(self):
return select([Child.id]).where(
Child.parent_id == self.id
)
@child_ids.setter
def child_ids(self, value):
"""
Sync children with given children ids.
value: list[int]
List of children ids.
"""
# Child ids in value found in self.children.
found_child_ids = set()
# Childs in self.children whose id is not in value.
to_delete = set()
for child in self.children:
if child.id not in value:
to_delete.add(child)
else:
found_child_ids.add(child.id)
# Delete children not in value.
for child in to_delete:
# This only deletes the Child object because we have delete-orphan cascade set.
self.children.remove(child)
# Create children with child ids in value that were not found in self.children.
for child_id in (set(value) - found_child_ids):
self.children.append(Child(id=child_id, parent_id=self.id))
children = relationship('Child', back_populates="parent",
# Need this to delete orphaned children.
cascade="all, delete-orphan")
class Child(Base):
__tablename__ = 'childs'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
parent = relationship('Parent', back_populates="children")
metadata.create_all(engine)
with Session(engine) as session, session.begin():
p1 = Parent()
session.add(p1)

with Session(engine) as session:
p1 = session.get(Parent, 1)
# Add 1, 5
p1.child_ids = [1, 5]
session.commit()
# Add 2, Remove 1, Leave 5
p1.child_ids = [2, 5]
session.commit()
# Remove 2, Remove 5
p1.child_ids = []
session.commit()

create_all

后回声

2023-01-17 21:35:51,060 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-17 21:35:51,064 INFO sqlalchemy.engine.Engine INSERT INTO parents DEFAULT VALUES RETURNING parents.id
2023-01-17 21:35:51,065 INFO sqlalchemy.engine.Engine [generated in 0.00058s] {}
2023-01-17 21:35:51,067 INFO sqlalchemy.engine.Engine COMMIT
2023-01-17 21:35:51,104 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-17 21:35:51,106 INFO sqlalchemy.engine.Engine SELECT parents.id AS parents_id 
FROM parents 
WHERE parents.id = %(pk_1)s
2023-01-17 21:35:51,106 INFO sqlalchemy.engine.Engine [generated in 0.00021s] {'pk_1': 1}
2023-01-17 21:35:51,109 INFO sqlalchemy.engine.Engine SELECT childs.id AS childs_id, childs.parent_id AS childs_parent_id 
FROM childs 
WHERE %(param_1)s = childs.parent_id
2023-01-17 21:35:51,110 INFO sqlalchemy.engine.Engine [generated in 0.00017s] {'param_1': 1}
2023-01-17 21:35:51,112 INFO sqlalchemy.engine.Engine INSERT INTO childs (id, parent_id) VALUES (%(id)s, %(parent_id)s)
2023-01-17 21:35:51,112 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ({'id': 1, 'parent_id': 1}, {'id': 5, 'parent_id': 1})
2023-01-17 21:35:51,114 INFO sqlalchemy.engine.Engine COMMIT
2023-01-17 21:35:51,163 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-17 21:35:51,165 INFO sqlalchemy.engine.Engine SELECT parents.id AS parents_id 
FROM parents 
WHERE parents.id = %(pk_1)s
2023-01-17 21:35:51,165 INFO sqlalchemy.engine.Engine [generated in 0.00025s] {'pk_1': 1}
2023-01-17 21:35:51,166 INFO sqlalchemy.engine.Engine SELECT childs.id AS childs_id, childs.parent_id AS childs_parent_id 
FROM childs 
WHERE %(param_1)s = childs.parent_id
2023-01-17 21:35:51,166 INFO sqlalchemy.engine.Engine [cached since 0.05671s ago] {'param_1': 1}
2023-01-17 21:35:51,168 INFO sqlalchemy.engine.Engine INSERT INTO childs (id, parent_id) VALUES (%(id)s, %(parent_id)s)
2023-01-17 21:35:51,168 INFO sqlalchemy.engine.Engine [generated in 0.00017s] {'id': 2, 'parent_id': 1}
2023-01-17 21:35:51,170 INFO sqlalchemy.engine.Engine DELETE FROM childs WHERE childs.id = %(id)s
2023-01-17 21:35:51,170 INFO sqlalchemy.engine.Engine [generated in 0.00015s] {'id': 1}
2023-01-17 21:35:51,170 INFO sqlalchemy.engine.Engine COMMIT
2023-01-17 21:35:51,204 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-17 21:35:51,206 INFO sqlalchemy.engine.Engine SELECT parents.id AS parents_id 
FROM parents 
WHERE parents.id = %(pk_1)s
2023-01-17 21:35:51,206 INFO sqlalchemy.engine.Engine [cached since 0.04138s ago] {'pk_1': 1}
2023-01-17 21:35:51,208 INFO sqlalchemy.engine.Engine SELECT childs.id AS childs_id, childs.parent_id AS childs_parent_id 
FROM childs 
WHERE %(param_1)s = childs.parent_id
2023-01-17 21:35:51,209 INFO sqlalchemy.engine.Engine [cached since 0.09924s ago] {'param_1': 1}
2023-01-17 21:35:51,211 INFO sqlalchemy.engine.Engine DELETE FROM childs WHERE childs.id = %(id)s
2023-01-17 21:35:51,211 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ({'id': 2}, {'id': 5})
2023-01-17 21:35:51,212 INFO sqlalchemy.engine.Engine COMMIT