search

long2ice / asyncmy

A fast asyncio MySQL/MariaDB driver with replication protocol support
https://github.com/long2ice/asyncmy
Apache License 2.0
264 stars 32 forks source link

Async Session "MissingGreenlet" Error When Switching to Outer Join #85

Closed jossefaz closed 9 months ago

jossefaz commented 9 months ago

I'm using async SQLAlchemy with the asyncmy driver for MySQL in a FastAPI application. My code works fine when using .join(), but when I switch to .outerjoin(), I encounter a MissingGreenlet error. I'm trying to perform a query involving two tables (Table1 and Table2), counting entries from Table2 related to Table1.

from sqlalchemy import select, func
from sqlalchemy.orm import contains_eager

async def query_with_outerjoin(db_session):
    query = (select(Table1, func.count(Table2.id).label('processed'))
             .join(Table1.table2s)
             .outerjoin(Table2.some_relation)  # Switching this from .join() to .outerjoin() causes the error
             .group_by(Table1.id)
             .options(contains_eager(Table1.table2s))
             .distinct())
    result = await db_session.execute(query)
    data = result.unique().all()
    return data

Switching from join to outerjoin causes this error : 

Traceback (most recent call last):
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.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/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/fastapi/applications.py", line 289, in __call__
    await super().__call__(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
    raise exc
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 20, in __call__
    raise e
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 17, in __call__
    await self.app(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/routing.py", line 276, in handle
    await self.app(scope, receive, send)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/starlette/routing.py", line 66, in app
    response = await func(request)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/fastapi/routing.py", line 273, in app
    raw_response = await run_endpoint_function(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/fastapi/routing.py", line 190, in run_endpoint_function
    return await dependant.call(**values)
  File "/Users/anonymous/projects/emedgene/kms-backend/resources/batch/batch_route.py", line 33, in get_batches_route
    chunk_status_list = [ChunkStatus.from_orm(chunk) for chunk in batch.chunks]
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 482, in __get__
    return self.impl.get(state, dict_)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 942, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 978, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 912, in _load_for_state
    return self._emit_lazyload(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 1046, in _emit_lazyload
    result = session.execute(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1806, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1800, in _execute_context
    context = constructor(
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1019, in _init_compiled
    self.cursor = self.create_cursor()
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1390, in create_cursor
    return self.create_default_cursor()
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1393, in create_default_cursor
    return self._dbapi_connection.cursor()
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1099, in cursor
    return self.dbapi_connection.cursor(*args, **kwargs)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 212, in cursor
    return AsyncAdapt_asyncmy_cursor(self)
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 58, in __init__
    self._cursor = self.await_(cursor.__aenter__())
  File "/Users/anonymous/Library/Caches/pypoetry/virtualenvs/project1-py3.10/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 59, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

UPDATE:

I tried to switch from asyncmy to aiomysql : got the same error So i is probably not an error from asyncmy, howeverm any help would be blessed !

vickypalani commented 1 week ago

I think we can perform an outer join using the join() method by setting is_outer=True. Example: .join(is_outer=True).

Ref: https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.join

Can you please add more context to the question?