async-fastapi-sqlalchemy Fails when

Have you ever tried to create a Notebook with include_notes set to False?

new = await cls.read_by_id(session, notebook.id, include_notes=False)

When I try it I get an ugly:

...
<clipped long stack_trace>
...
 File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2128, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 479, in execute
    self._adapt_connection.await_(
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 59, 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/14/xd2s)

that I do not know where it comes from.

Thanks for the great ideas in your repo anyways... :)

Nov 10 '22 14:11 stratosgear

@stratosgear Thank you for your interest.

This application is running in the acync world and all IO events need await keyword. However, many python orms like sqlalchemy have a lazy load feature and this is not fit well the async world. So, all records that may be used must be fetched in advance.

This is a good.

(venv) [rhoboro]async-fastapi-sqlalchemy % APP_CONFIG_FILE=local python3 -m asyncio
>>> import asyncio
>>> from app.db import AsyncSessionLocal
>>> from app.models import Notebook
>>> async with AsyncSessionLocal() as session:
...   notebook = await Notebook.read_by_id(session, 1, include_notes=True)
...   print(notebook)
...   print(notebook.notes)
...
<Notebook(title='my first notebook', id=1, notes=[<Note(title='my first note', content='content 1', notebook_id=1, id=1)>])>
[<Note(title='my first note', content='content 1', notebook_id=1, id=1)>]
>>>

This is not good, because notes were not previously fetched. And cannot be fetched on the fly too in the async world.

>>> async with AsyncSessionLocal() as session:
...   notebook = await Notebook.read_by_id(session, 1, include_notes=False)
...   print(notebook)
...   print(notebook.notes)
...
<Notebook(title='my first notebook', id=1)>
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/concurrent/futures/_base.py", line 446, in result
    return self.__get_result()
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/concurrent/futures/_base.py", line 391, in __get_result
    raise self._exception
  File "<console>", line 4, in <module>
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 941, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 977, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 911, in _load_for_state
    return self._emit_lazyload(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 1047, in _emit_lazyload
    result = session.execute(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2047, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 479, in execute
    self._adapt_connection.await_(
  File "/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 59, 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/14/xd2s)

Nov 12 '22 08:11 rhoboro

As you explain it it makes sense.

The confusion happens, since the include_notes is exposed as a parameter, and when I tried to use it as False it failed.

To be honest, this increases the complexity and mental overhead of such an approach. This is not a criticism against your very nice example, but it goes against the python async methodology overall.

I have to rethink if I want to access the DB in async mode.

Thanks for your very insightful reply!

Nov 16 '22 09:11 stratosgear

Can we make it contain an empty list or None, instead of throwing?

Oct 23 '23 08:10 Trolldemorted

yes we can! Specifying lazy='noload' on the relationship does the trick.

@rhoboro since lazy-loading should never work with async (?), you might want to set it on every relationship. I tested it on the notebook endpoint, you'll get an empty list of notes:

{
  "notebooks": [
    {
      "id": 1,
      "title": "bennibook",
      "notes": []
    }
  ]
}

Oct 23 '23 12:10 Trolldemorted

@Trolldemorted Thanks for the tip! It's a good idea to specify lazy='noload' when you want that behavior.

However, I'm concerned that we might be accessing an empty list when in fact there are records. If you really need access to the list, you may want to get it in advance. Throwing reminds you of that.

Nov 14 '23 00:11 rhoboro