SQLAlchemy: lazy='raise' complaining about fields even though they are loaded

user_7404090 0
Viewed 687

We use the async version of sqlalchemy and we need to eager load every relationship (lazy loading does not work for async). Accordingly for every relationship in our models we have set lazy='raise'. The problem is that it seems overly aggressive on raising errors. Consider the following unit test:

async def test_user_group_self_allowed(self):
    privilege = await self.db.get(Privilege, 1, [joinedload(Privilege.role)])
    options = [joinedload(Item.privileges).joinedload(Privilege.role), joinedload(Item.item_group)]
    item = await self.db.get(Item, 1, options)
    item.privileges.append(privilege)
    await (self.db.commit())

    options = [joinedload(User.user_groups).joinedload(UserGroup.privileges), joinedload(User.privileges)]
    user = await self.db.get(User, 1, options)
    user.privileges = []
    item = await self.db.get(Item, 1, [joinedload(Item.privileges).joinedload(Privilege.role), joinedload(Item.item_group)])
    user_group = await self.db.get(UserGroup, 1, [joinedload(UserGroup.organization)])
    print('why?????', user_group.organization)
    self.assertTrue(await self.helper.is_authorized(self.db, user, 'edit', user_group))

Notice the print, it results in the following error:

Traceback (most recent call last):


File "/usr/lib/python3.9/unittest/async_case.py", line 65, in _callTestMethod
    self._callMaybeAsync(method)
  File "/usr/lib/python3.9/unittest/async_case.py", line 88, in _callMaybeAsync
    return self._asyncioTestLoop.run_until_complete(fut)
  File "/usr/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/usr/lib/python3.9/unittest/async_case.py", line 102, in _asyncioLoopRunner
    ret = await awaitable
  File "/src/backend-core/backend_core/tests/authorization.py", line 206, in test_user_group_self_allowed
    print('why?????', user_group.organization)
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/attributes.py", line 926, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/attributes.py", line 962, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/strategies.py", line 836, in _load_for_state
    self._invoke_raise_load(state, passive, "raise")
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/strategies.py", line 795, in _invoke_raise_load
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: 'UserGroup.organization' is not available due to lazy='raise'

As you can see it complains about the organization not being eager loaded while I clearly include it in the options with a joinedload. Now we can make this error go away by changing the options for the user query to:

options = [joinedload(User.user_groups).joinedload(UserGroup.privileges), joinedload(User.privileges), joinedload(User.user_groups).joinedload(UserGroup.organization)]

(the same options as before, only we add a joinedload for User -> UserGroups -> Organization)

This makes the error go away and everything is fine again. Now my question is, why does it complain about this to start with? I access user_group.organization not user.user_groups[x].organization.. I don't know how these queries work under the hood exactly, but not only do I have to write way too many joinedloads this way, I think it also results in needless querying.

sqlalchemy python
1 Answers

As it turns out, .get caches more than I would expect. Not only the main entity (in this case usergroup) but also stuff loaded through a joinedload (user.user_group.organization). So that means a direct get on the organization does not overwrite the cached one from user.user_group.organization or usergroup.organization. However it is possible to do e.g. db.get(Organization, 1, populate_existing=True) which will retrieve the entity again and update the cache.

From the docs:
If the given primary key identifier is present in the local identity map, the object is returned directly from this collection and no SQL is emitted, unless the object has been marked fully expired....
...
populate_existing – causes the method to unconditionally emit a SQL query and refresh the object with the newly loaded data, regardless of whether or not the object is already present.

As it tells, another way is to expire an object, read more about that here

user_74040900
Related