SQLAlchemy ORM events with asyncio

Question

I'm trying to use SQLAlchemy attribute event "set" in asynchronous mode in the same way, as it was for synchronous.

async_session = sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)

Base = declarative_base()


async def init_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


class Event(Base):
    __tablename__ = "events"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
    parent_id = Column(Integer, ForeignKey('events.id', ondelete='CASCADE'))
    children = relationship(
        "Event",
        join_depth=2,
        backref=backref('parents', remote_side=[id])
    )

@event.listens_for(Event.name, "set")
@event.listens_for(Event.updated_at, "set")
def set_event(target, value, oldvalue, initiator):  # noqa
    if target.parents:
        target.parents.updated_at = datetime.now()
    
async def run_main():
    await init_db()
    async with async_session() as session:
        parent = Event(name="Event1")
        subparent = Event(name="Event2")
        child = Event(name="Event3")
        parent.children.append(subparent)
        subparent.children.append(child)
        session.add(parent)
        session.add(subparent)
        session.add(child)
        await session.commit()
        await session.refresh(child)
        print(parent.name, parent.updated_at)
    print("Sleep for 3sec")
    await asyncio.sleep(3)
    async with async_session() as session:       
        result = await session.execute(select(Event).options(selectinload(Event.children)).where(Event.id == 3))
        child = result.scalar_one_or_none()
        result = await session.execute(select(Event).options(selectinload(Event.children)).where(Event.id == 1))
        parent = result.scalar_one_or_none()
        setattr(child, "name", "Event3a")
        session.add(child)
        await session.commit()
        await session.refresh(child)
        print(parent.name, parent.updated_at)

So, we have three instances, parent, subparent and child, where the parent includes subparent and subparent includes child. The idea is to update parent, when the child's name updated. But I'm getting an 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) sys:1: RuntimeWarning: coroutine 'Connection.cursor' was never awaited

It looks like SQLAlchemy tries to lazy_load child.parents. How how to use SQLAlchemy attribute events in async mode correctly?

SQLAlchemy ORM events with asyncio

0 Answers