SQLAlchemy session.query(MyModel).all() equivalent in databases?

[RodrigoTMOLima]

Hello. I have the following tables in my database:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent", lazy='joined')

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

I am trying to get all instances of Parent in my database, as well as a list of Child objects related to each parent by using the field parent.children.
When I use the traditional SQL Alchemy session querying format, this works:

with Session() as session:
    parents = session.query(Parent).all()
    # This query returns a list of `Parent` objects, which allows me to use the `children` field.

    for parent in parents:
        print(parent.children)

However, when using the databases library, I seem to only have the option of using the fetch_all() method, which returns a list of Row (SQLite) or Record (PostgreSQL) objects.

query = select(Parent)
rows = await database.fetch_all(query=query)

# Doesn't work:
for row in rows:
    print(row.children)

Instead, I get rows of joined data, such as:

(parent_1, child_1)
(parent_1, child_2)
.
.
.

Is there a way for me to do something similar to session.query.(Parent).all() using the databases library?

Thanks!

[aminalaee]

Hey,
The short answer is no, you won't be able to do that in databases as this is not an ORM.

And as a side note I think that's not the recommended way in SQLAlchemy 1.4 onwards, it should be something like:

from sqlalchemy import select
stmt = select(Parent, Child).join(Parent.children).order_by(Parent.id)

with Session() as session:
    objects = session.query(stmt).scalars().all()

[RodrigoTMOLima]

Thanks for the quick reply. Guess I'll have to stick with SQLAlchemy then.