Retrieving many-to-many relation properties using SQLAlchemy
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 unnecess开发者_运维知识库ary 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
You can define intermediate model based on show_has_image table (use composite primary key) and define relations to it. Then use association_proxy
to define Show.images
property.
精彩评论