I'm new to SQLAlchemy in general, and even more to its asyncio mode. I'm creating an async session like so (demo/pseudo code):
async_scoped_session(
async_sessionmaker(
bind=create_async_engine(
format_db_url(CONFIG)
)
),
scopefunc=asyncio.current_task
)
and then creating a model like so:
...
async def model(self):
model = TestUser(name='test', age=1)
self.session.add(model)
await self.session.commit()
return model
...
but, when trying to use it:
model = repo.model()
print(model.name)
it fails at the print with:
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)
These two adjustments fix the issue:
await repo.session.refresh(model)
# and/or
id_ = await model.awaitable_attrs.name
But I have the feeling I'm missing something or doing something wrong, I kinda understand the need for awaitable_attrs when accessing a relationship, but it doesn't feel right having to an extra step before being able to access a model's own attribute.
The default behaviour of a SQLAlchemy Session is to expire the attributes of objects in the session after a commit. Accessing an expired attribute triggers a refresh of the object from the database to help avoid working with potentially stale data (e.g., changes to the object made by some other process).
This causes problems with async because it results in "IO attempted in an unexpected place". A workaround is to use expire_on_commit=False as illustrated in an example from the docs:
# expire_on_commit=False will prevent attributes from being expired
# after commit.
async_session = async_sessionmaker(engine, expire_on_commit=False)
Of course, that does increase the possibility of working with stale data, but we can always force a refresh (as shown in the question) if we are really concerned about that.