subquery in select statement

I have two tables (albums,pictures) in a one to many relationship and I want to display each albums details with one picture so I have the following query

select albums.name
    , (
      select pictures.path 
      from pictures 
      where pictures.albumid = albums.id 
      limit 1
    ) as picture 
from albums 
where ...

Now I'm struggling creating this on Pylons with sqlalchemy I tried to do the following

pictur开发者_开发知识库e = Session.query(model.Picture)

sub_q = picture.filter_by(albumid = model.Album.id).limit(1).subquery()

album_q = Session.query(model.Album, sub_q)

result = album_q.all()

but it creates the following statement displaying the incorrect picture beacuse the table albums is included in the subquery

select albums.name
    , (
      select pictures.path 
      from pictures, albums 
      where pictures.albumid = albums.id
    ) 
from albums 
where ...

Am I doing it wrong?, is this even possible in sqlalchemy?.

That works perfectly. I'm sorry but I forgot to say I'm using sqlalchemy reflection. I'm gonna try backreferencing the object and see if it works.

If I understand the basic premise of the question (admittedly it's a little unclear), it sounds using relationships might accomplish this better. From what I'm gathering, a picture has one album, but an album has many pictures? Anyway, that's somewhat irrelevant as the relationship concept is what's important here.

You haven't specified how your model is setup, but using declarative syntax you could define the tables like below:

class Album(Base):
    __tablename__ = 'albums'
    id = Column(Integer, Sequence('album_id_sequence'), primary_key=True)

class Picture(Base):
    __tablename__ = 'pictures'
    id = Column(Integer, Sequence('picture_id_sequence'), primary_key=True)
    albumid = Column(Integer, ForeignKey('albums.id'))
    picture = Column(String(99))
    album = relationship('Album', backref=backref('pictures'))

Sqlalchemy then takes care of all the hard work for you. Now every 'picture' has an 'album' property, callable via 'picture.album'. In code this looks like:

pictures = session.query(Picture).all()
for picture in pictures:
    for album in picture.albums:
        # do stuff with album

And vice-versa, if you have an album, you can get a picture (this is what the backref in the relationship definition does):

albums = session.query(Album).all()
for album in albums:
    album.picture  # the picture object related to the album

I haven't tested the code so chances are there are typos, but hopefully you get the general idea.