[SOLVED] Async SQLalchemy: accessing eagerly-loaded empty relationship triggers new lazy-load, raising error

Async SQLalchemy: accessing eagerly-loaded empty relationship triggers new lazy-load, raising error

I am using sqlalchemy + asyncpg, and 'selectin' eager loading.

I have Person items that have one-to-many relationships with Friends.

I insert a Person into my database, with no related Friend entries. If in the same session I try and get that Person from the database, I can access their static (non-relationship) columns fine, but cannot access the friends relationship.

I think trying to access person.friends is triggering a lazy load, despite it being enforced previously as a selectin load. Why is this? How can I avoid it?

# Create the ORM model
class Person(Base):
    __tablename__ = 'items'
    id_ = Column(POSTGRES_UUID(as_uuid=True), primary_key=True)
    name = Column(String(32))
    friends = relationship('Friend', lazy='selectin')

# Create an instance
person_id = uuid4()
person = Person(id_=person_id, name='Alice') # Note that this Person's friends are not set

# Add to database
async with AsyncSession(engine, expire_on_commit=False) as session:
    try:
        session.begin()
        session.add(person)
        await session.commit()
    except:
        await session.rollback()
        raise
    # Get the added person from the database
    created_person = await session.get(person, person_id)
    print(created_person.id_) # Works fine
    print(created_person.friends) # Raises error

Error:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here.
Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

Solution

The solution is to use the populate_existing parameter in get:

populate_existing – causes the method to unconditionally emit a SQL query and refresh the object with the newly loaded data, regardless of whether or not the object is already present.

Replace

created_person = await session.get(person, person_id)

with

created_person = await session.get(person, person_id, populate_existing=True)

session.get documentation