Join query with SQLAlchemy

Question

This performs proper sql query, but returns me only columns from one table

async def get_user_library(current_user = Depends(get_current_user)):
    async with get_session() as session:
        stmt = select(UserLibrary, Songs).join(Songs, UserLibrary.song_id == Songs.id).filter(UserLibrary.user_uuid == current_user)

        result = await session.execute(stmt)
        library_items = result.scalars().all()
    return library_items

Here is my models

class Songs(Base):
    __tablename__ = 'songs'
    id = Column(Integer, primary_key=True, index=True)
    artist = Column(String)
    title = Column(String)
    length = Column(Integer)


class UserLibrary(Base):
    __tablename__ = 'library'
    library_id = Column(Integer, primary_key=True, index=True)
    song_id = Column(Integer, ForeignKey('songs.id'))
    play_count = Column(Integer)
    added_at = Column(DateTime)
    user_uuid = Column(String, ForeignKey('users.uuid'))

Answer 1

You should not use .scalars() in this case. The rows in your library_items will contain attributes for both UserLibrary and Songs under their respective names 1. So you'll have to pass those rows into your objects.

Alternatively, you could use a relationship to load the related objects for you automatically. 2