Retrieving many-to-many relation properties using SQLAlchemy

Question

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

Answer 1

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.