I'd like to use AsyncSession.sync_session but the code below fails with error. The code is a simplified version of real case.
import sqlalchemy as sa
import uvicorn
from fastapi import FastAPI, Depends
from sqlalchemy import MetaData, select
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import declarative_base
app = FastAPI()
metadata = MetaData()
Base = declarative_base(metadata=metadata)
engine = create_async_engine(
"postgresql+asyncpg://postgres:postgres@localhost:5433/portal-podrjadchika-local", echo=True
)
SessionLocal = async_sessionmaker(bind=engine, expire_on_commit=False)
class User(Base):
__tablename__ = "users"
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True, index=True)
first_name = sa.Column(sa.String)
last_name = sa.Column(sa.String)
async def get_session() -> AsyncSession:
session = SessionLocal()
try:
yield session
await session.commit()
except Exception as e:
await session.rollback()
raise e
finally:
await session.close()
@app.on_event("startup")
async def on_startup() -> None:
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
@app.post('/users')
async def get_users(session: AsyncSession = Depends(get_session)):
stmt = select(User)
print(session.sync_session.scalars(stmt)) # fails with error 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)
return "user"
if __name__ == "__main__":
uvicorn.run("so:app", reload=True)
Traceback:
ERROR: Exception in ASGI application Traceback (most recent call
last): File
"/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 408, in run_asgi
result = await app( # type: ignore[func-returns-value] File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py",
line 84, in __call__
return await self.app(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/fastapi/applications.py",
line 1054, in __call__
await super().__call__(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/applications.py",
line 123, in __call__
await self.middleware_stack(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/middleware/errors.py",
line 186, in __call__
raise exc File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/middleware/errors.py",
line 164, in __call__
await self.app(scope, receive, _send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 65, in __call__
await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send) File
"/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/_exception_handler.py",
line 64, in wrapped_app
raise exc File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/_exception_handler.py",
line 53, in wrapped_app
await app(scope, receive, sender) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/routing.py",
line 756, in __call__
await self.middleware_stack(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/routing.py",
line 776, in app
await route.handle(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/routing.py",
line 297, in handle
await self.app(scope, receive, send) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/routing.py",
line 77, in app
await wrap_app_handling_exceptions(app, request)(scope, receive, send) File
"/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/_exception_handler.py",
line 64, in wrapped_app
raise exc File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/_exception_handler.py",
line 53, in wrapped_app
await app(scope, receive, sender) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/starlette/routing.py",
line 72, in app
response = await func(request) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/fastapi/routing.py",
line 278, in app
raw_response = await run_endpoint_function( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/fastapi/routing.py",
line 191, in run_endpoint_function
return await dependant.call(**values) File "/Users/alber.aleksandrov/PycharmProjects/Playground/fstp/so.py", line
50, in get_users
print(session.sync_session.scalars(stmt)) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py",
line 2337, in scalars
return self._execute_internal( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py",
line 2120, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement( File
"/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/orm/context.py",
line 293, in orm_execute_statement
result = conn.execute( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 1412, in execute
return meth( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py",
line 483, in _execute_on_connection
return connection._execute_clauseelement( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 1635, in _execute_clauseelement
ret = self._execute_context( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 1844, in _execute_context
return self._exec_single_context( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 1984, in _exec_single_context
self._handle_dbapi_exception( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 2342, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2]) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py",
line 1965, in _exec_single_context
self.dialect.do_execute( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py",
line 921, in do_execute
cursor.execute(statement, parameters) File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py",
line 561, in execute
self._adapt_connection.await_( File "/Users/alber.aleksandrov/PycharmProjects/Playground/venv3.10/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py",
line 116, in await_only
raise exc.MissingGreenlet( 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)
How can I fix the error? Or how to have both sync and async session with the same scope of objects, single transaction? I need it to make Factory Boy https://factoryboy.readthedocs.io/en/stable/orms.html#sqlalchemy use sync session to instanciate objects.
Because you are using async session you need to await the method.
@app.post('/users')
async def get_users(session: AsyncSession = Depends(get_session)):
stmt = select(User)
result = await session.scalars(stmt)
users = result.all() # Fetch all the users
return users
For the factory-boy part it doesn't need to be the same transaction or session to use it. You just need to use the same database. To do that just update _create a method in factory:
@classmethod
def _create(cls, model_class, *args, **kwargs):
with some_sync_session() as session:
session.expire_on_commit = False
obj = model_class(*args, **kwargs)
session.add(obj)
session.commit()
session.expunge_all()
return obj
some_sync_session is just sessionmaker which points to the correct database.
I did the similar stuff when I was writing tests for my package. You can check how I set up model factory here at github.