Sqlalchemy: Async generator already executing

[AEzzatA]

Versions

• OS: Docker Ubuntu
• Python: 3.7
• SQLAlchemy: 1.3.20
• Database: PostgreSQL 11
• DBAPI: Psycopg2

Hello,

I have a problem while querying the database from within FastAPI view.
The view is normal python sync function not async and therefore the whole SQLalchemy engine..etc

I have a query that essentially looks like this

QUERY: str = """
SELECT * FROM somechema.some_table
AND time_start BETWEEN '{start_time}'::timestamp AND '{end_time}'::timestamp;
"""
session.execute(query)

this leads sometimes to a strange error

Important to note this does not happen every time.. which is strange and hard to reproduce

I suspect something might be going wrong here?

  File "sqlalchemy/orm/session.py", line 1292, in execute
    clause, params or {}
  File "sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "sqlalchemy/engine/base.py", line 1514, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "sqlalchemy/engine/base.py", line 1287, in _execute_context
    context.executemany,
  File "sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "contextlib.py", line 119, in __exit__
    next(self.gen)

ValueError contextlib in __exit__
generator already executing

so, what might be the problem here? and how would I mitigate it?

[zzzeek]

well, there's an event handler of some kind trying to do something, and then that seems to be running into some kind of problem with whatever kind of container you are running your code within. I'm not familiar with FastAPI but it seems to be running in some kind of container, which based on your bringing up of "async" I would gather is doing some kind of non-blocking IO mediation. Unless you can provide an MCVE that showed how this is actually running, this is not something I'm familiar with and you'd want to talk to people that know FastAPI.

[tiangolo]

Hey @AEzzatA , as @zzzeek says, please create a minimum example that replicates the issue.

It's also probably better in the FastAPI repo: https://github.com/tiangolo/fastapi/issues/new/choose

This is most probably an error related to how you are creating the session and/or dependencies in your FastAPI app rather than a SQLAlchemy issue.

Try first following the tutorial: https://fastapi.tiangolo.com/tutorial/sql-databases/

And then create a minimal example using the code from the tutorial, and seeing if that solves your problem, or if you can replicate the error. There's also a big chance that you'll realize what's happening during the process of creating that minimal example. :nerd_face: :crossed_fingers:

[graingert]

@AEzzatA it looks like you cut off the important part of the Traceback, can you post the lines between:

Traceback (most recent call last):
... # lines missing here
  File "sqlalchemy/orm/session.py", line 1292, in execute