使用 SQLAlchemy 检索多对多关系属性
我有一个多对多关系，其中关系表包含的列多于仅包含主键的列。举个例子，考虑一个幻灯片放映系统，其中每个图像都可以有自己的超时，并且根据幻灯片的不同有不同的超时。一个愚蠢的例子，但为了说明起见，它必须这样做；）
所以我想我会做类似以下的事情（使用声明性）：
show_has_image = Table( 'show_has_image',
DeclarativeBase.metadata,
Column( 'show_id', Integer, ForeignKey( 'show.id' ) ),
Column( 'image_id', Integer, ForeignKey( 'image.id' ) ),
Column( 'timeout', Integer, default=5 ),
PrimaryKeyConstraint( 'show_id', 'image_id' )
)
class Show(DeclarativeBase):
__tablename__ = "show"
id = Column( Integer, primary_key = True )
name = Column( Unicode(64), nullable = False)
class Image(DeclarativeBase):
__tablename__ = "image"
id = Column( Integer, primary_key = True )
name = Column( Unicode(64), nullable = False)
data = Column(Binary, nullable = True)
show = relation( "Show",
secondary=show_has_image,
backref="images" )
我将如何访问“超时”值？我在文档中找不到有关此的任何内容。 到目前为止，检索图像很简单：
show = DBSession.query(Show).filter_by(id=show_id).one()
for image in show.images:
print image.name
# print image.timeout <--- Obviously this cannot work, as SA has no idea
# how to map this field.
我非常乐意让它按照我在前面的代码中概述的方式工作。当然，我可以向 Image 类添加一个 timeout 属性，该属性将动态获取值。但这会导致不必要的 SQL 查询。
我宁愿在一个查询中全部返回。在 SQL 中这很简单：
SELECT i.name, si.timeout
FROM show s
INNER JOIN show_has_image si ON (si.show_id = s.id)
INNER JOIN image i ON (si.image_id = i.id)
WHERE s.id = :show_id
I have a many-to-many relationship in which the relation-table contains more columns than only the primary key. As an example, consider a slide show system in which each image could have it's own timeout, and a different timeout depending on the slideshow. A daft example, but it will have to do for the sake of illustration ;)
So I imagine I would do something like the following (using Declarative):
show_has_image = Table( 'show_has_image',
DeclarativeBase.metadata,
Column( 'show_id', Integer, ForeignKey( 'show.id' ) ),
Column( 'image_id', Integer, ForeignKey( 'image.id' ) ),
Column( 'timeout', Integer, default=5 ),
PrimaryKeyConstraint( 'show_id', 'image_id' )
)
class Show(DeclarativeBase):
__tablename__ = "show"
id = Column( Integer, primary_key = True )
name = Column( Unicode(64), nullable = False)
class Image(DeclarativeBase):
__tablename__ = "image"
id = Column( Integer, primary_key = True )
name = Column( Unicode(64), nullable = False)
data = Column(Binary, nullable = True)
show = relation( "Show",
secondary=show_has_image,
backref="images" )
How would I access the "timeout" value? I cannot find anything in the docs about this.
So far, retrieving the images is straightforward:
show = DBSession.query(Show).filter_by(id=show_id).one()
for image in show.images:
print image.name
# print image.timeout <--- Obviously this cannot work, as SA has no idea
# how to map this field.
I'd be more than happy to have it work the way I just outlined in the previous code. Granted, I could add a timeout property to the Image class which would fetch the value dynamically. But that would result in unnecessary SQL queries.
I'd rather have it all returned in a single query. In SQL it's easy:
SELECT i.name, si.timeout
FROM show s
INNER JOIN show_has_image si ON (si.show_id = s.id)
INNER JOIN image i ON (si.image_id = i.id)
WHERE s.id = :show_id
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以基于 show_has_image 表（使用复合主键）定义中间模型并定义与其的关系。然后使用
association_proxy定义Show.images属性。You can define intermediate model based on show_has_image table (use composite primary key) and define relations to it. Then use
association_proxyto defineShow.imagesproperty.