SqlAlchemy relationship selecting columns

Question

I am trying to find a way to select columns with an sqlalchemy relationships:

I have this two tables:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    label = Column(String)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('public.parent.id'))
    parent = relationship("Parent", back_populates="children")

when i use

db.query( models.Parent).first()

i get the parent object with the list of Children as i was expecting, but what i would like to do is to select only few columns like that:

db.query( models.Parent.id, models.Parent.children)

in this case it doesn't work an i get the following error:

Could not locate column in row for column Children

Answer 1

You can use options with load_only() function.

stmt = select(Parent)

results = await db.execute(
    stmt
    .options(load_only(Parent.id))
    .options(selectinload(Parent.children))
)

*If you want picking some columns, just attach load_only() like this.

.options(selectinload(Parent.children).load_only(Child.id, Child.name))

Yes, I tested execute() but, query() will work.

session.query(User).options(load_only(User.name, User.fullname))

see also: https://docs.sqlalchemy.org/en/14/orm/loading_columns.html#sqlalchemy.orm.load_only