我有一个类似于此处描述的多对多关系,链接为https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object。请注意我的Association表包括一个extra_data
字段。
class Association(Base):
__tablename__ = 'association'
left_id = Column(ForeignKey('left.id'), primary_key=True)
right_id = Column(ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship("Child", secondary="association", back_populates="parents")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
parents = relationship("Parent", secondary="association", back_populates="children")
如果我想获取一个特定的父对象及其子对象,可以这样做:
db_parent = db.query(Parent).where(Parent.id == 1).first()
print(db_parent.children[0].id) # works fine
但是,“extra_data”字段并未作为子元素的属性包含在内。
print(db_parent.children[0].extra_data)
如何编写获取父级的子元素,以便包含extra_data
属性?出现AttributeError: 'Child' object has no attribute 'extra_data'
错误。
完整的工作示例
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session
# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=False)
# Make the DeclarativeMeta
Base = declarative_base()
class Association(Base):
__tablename__ = 'association'
left_id = Column(ForeignKey('left.id'), primary_key=True)
right_id = Column(ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship("Child", secondary="association", back_populates="parents")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
parents = relationship("Parent", secondary="association", back_populates="children")
# Create the tables in the database
Base.metadata.create_all(engine)
# Test it
with Session(bind=engine) as session:
# add parents
p1 = Parent()
session.add(p1)
p2 = Parent()
session.add(p2)
session.commit()
# add children
c1 = Child()
session.add(c1)
c2 = Child()
session.add(c2)
session.commit()
# map children to parents
a1 = Association(left_id=p1.id, right_id=c1.id, extra_data='foo')
a2 = Association(left_id=p1.id, right_id=c2.id, extra_data='bar')
a3 = Association(left_id=p2.id, right_id=c2.id, extra_data='baz')
session.add(a1)
session.add(a2)
session.add(a3)
session.commit()
with Session(bind=engine) as session:
db_parent = session.query(Parent).where(Parent.id == 1).first()
print(db_parent.children[0].id)
print(db_parent.children[0].extra_data)
parent.children[0].child
而不是简单地使用parent.children[0]
来引用子对象。这会破坏下游的Pydantic模型,我使用查询结果进行初始化。 - Benparent.children
包含Child
实例,但是你想通过Child
访问相应连接表行的extra_data
属性? - snakecharmerb