Re: [sqlalchemy] Temporarily disable/intercept ORM events on mutation

[Mike Bayer]

On Fri, Sep 15, 2023, at 8:59 PM, Mike Bayer wrote:
> 
> unfortunately no, that's a backref event handler, that's within the class 
> instrumentation and has no mechanism to be disabled on a per-class basis, not 
> to mention the backref handler is not the only aspect of things that expects 
> a certain kind of value to be present.  Marshmallow should have alternate 
> APIs that allow the attributes in question to be named by their string name 
> somewhere, rather than being assigned some out-of-band object; that's not 
> very good design in today's pep-484 typed Python.

sorry, I meant "no mechanism to be disabled on a per-instance basis". 

> 
> 
> 
> 
> 
>> 
>> 
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/34ee3121-e284-4fcb-9c1d-6191557e89bcn%40googlegroups.com
>>  
>> .
> 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0183d49d-e442-4512-bf62-8adae2c0e903%40app.fastmail.com.

Re: [sqlalchemy] Temporarily disable/intercept ORM events on mutation

[Mike Bayer]

On Fri, Sep 15, 2023, at 4:28 PM, 'Luna Lucadou' via sqlalchemy wrote:
> When customers call our JSON:API API, they can use an "include" parameter to 
> specify related objects to be appended to the response.
> 
> However, requests to include are not always satisfiable (e.g. if 
> job.supervisor=null, include=supervisor is ignored).
> In order to prevent Marshmallow from trying to load nonexistent related 
> objects to append to our API responses, we need to tell it when to ignore a 
> relationship attribute, such as via setting 
> job.supervisor=marshmallow.missing (if it sees job.supervisor=None, it will 
> attempt (and fail) to load the null supervisor object, so we cannot just 
> leave it as-is).
> 
> Unfortunately, this causes problems as SQLAlchemy attempts to handle the new 
> value:
> 
> Error Traceback (most recent call last): File 
> "/Users/lucadou/IdeaProjects/person-api/api/unit_tests/test_person_service.py",
>  line 601, in test_get_people_include_job response = 
> self.person_service.get_people(QueryParameters({"include": "jobs"})) 
>  File 
> "/Users/lucadou/IdeaProjects/person-api/api/src/person_service.py", line 61, 
> in get_people response = person_schema.dump(people, many=True) 
> ^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 557, in dump result = self._serialize(processed_obj, many=many) 
> ^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 519, in _serialize return [ ^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 520, in  self._serialize(d, many=False) File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 525, in _serialize value = field_obj.serialize(attr_name, obj, 
> accessor=self.get_attribute) 
>  File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
>  line 248, in serialize return super().serialize(attr, obj, accessor) 
> ^^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/fields.py",
>  line 344, in serialize return self._serialize(value, attr, obj, **kwargs) 
> ^^^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
>  line 274, in _serialize self._serialize_included(item) File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
>  line 280, in _serialize_included result = self.schema.dump(value) 
> ^^^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 551, in dump processed_obj = self._invoke_dump_processors( 
> ^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 1068, in _invoke_dump_processors data = self._invoke_processors( 
>  File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
>  line 1225, in _invoke_processors data = processor(data, many=many, **kwargs) 
>  File 
> "/Users/lucadou/IdeaProjects/person-api/api/src/model/schema/job_schema.py", 
> line 62, in set_null_supervisor job.supervisor = missing ^^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
>  line 536, in __set__ self.impl.set( File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
>  line 1466, in set value = self.fire_replace_event(state, dict_, value, old, 
> initiator)  File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
>  line 1505, in fire_replace_event value = fn( ^^^ File 
> "/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
>  line 2167, in emit_backref_from_scalar_set_event instance_state(child), 
> ^ AttributeError: '_Missing' object has no attribute 
> '_sa_instance_state'
> 
> Is there any way to temporarily disable ORM event listeners when we mutate 
> objects and have no intention of saving the changes/do not intend for the ORM 
> to act on them?

unfortunately no, that's a backref event handler, that's within the class 
instrumentation and has no mechanism to be 

[sqlalchemy] Temporarily disable/intercept ORM events on mutation

['Luna Lucadou' via sqlalchemy]

When customers call our JSON:API API, they can use an "include" parameter 
to specify related objects to be appended to the response.

However, requests to include are not always satisfiable (e.g. if 
job.supervisor=null, include=supervisor is ignored).
In order to prevent Marshmallow from trying to load nonexistent related 
objects to append to our API responses, we need to tell it when to ignore a 
relationship attribute, such as via setting 
job.supervisor=marshmallow.missing (if it sees job.supervisor=None, it will 
attempt (and fail) to load the null supervisor object, so we cannot just 
leave it as-is).

Unfortunately, this causes problems as SQLAlchemy attempts to handle the 
new value:

Error Traceback (most recent call last): File 
"/Users/lucadou/IdeaProjects/person-api/api/unit_tests/test_person_service.py", 
line 601, in test_get_people_include_job response = 
self.person_service.get_people(QueryParameters({"include": "jobs"})) 
 File 
"/Users/lucadou/IdeaProjects/person-api/api/src/person_service.py", line 
61, in get_people response = person_schema.dump(people, many=True) 
^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 557, in dump result = self._serialize(processed_obj, many=many) 
^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 519, in _serialize return [ ^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 520, in  self._serialize(d, many=False) File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 525, in _serialize value = field_obj.serialize(attr_name, obj, 
accessor=self.get_attribute) 
 File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
 
line 248, in serialize return super().serialize(attr, obj, accessor) 
^^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/fields.py",
 
line 344, in serialize return self._serialize(value, attr, obj, **kwargs) 
^^^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
 
line 274, in _serialize self._serialize_included(item) File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow_jsonapi/fields.py",
 
line 280, in _serialize_included result = self.schema.dump(value) 
^^^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 551, in dump processed_obj = self._invoke_dump_processors( 
^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 1068, in _invoke_dump_processors data = self._invoke_processors( 
 File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/marshmallow/schema.py",
 
line 1225, in _invoke_processors data = processor(data, many=many, **kwargs) 
 File 
"/Users/lucadou/IdeaProjects/person-api/api/src/model/schema/job_schema.py", 
line 62, in set_null_supervisor job.supervisor = missing ^^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
 
line 536, in __set__ self.impl.set( File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
 
line 1466, in set value = self.fire_replace_event(state, dict_, value, old, 
initiator)  File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
 
line 1505, in fire_replace_event value = fn( ^^^ File 
"/Users/lucadou/IdeaProjects/person-api/api/venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py",
 
line 2167, in emit_backref_from_scalar_set_event instance_state(child), 
^ AttributeError: '_Missing' object has no attribute 
'_sa_instance_state'

Is there any way to temporarily disable ORM event listeners when we mutate 
objects and have no intention of saving the changes/do not intend for the 
ORM to act on them?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the 

Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations

[Mike Bayer]

the recursion_depth feature on selectinload() is also very new and that is 
actually a very complex and not very mature feature.If you only wanted to 
apply additional options for N levels deep, you would build out separate 
options for that, like:

options(
   selectinload(Model.thing, recursion_depth=-1),
   
defaultload(Model.thing).selectinload(Model.otherthing).selectinload(Model.otherthing)
)

something like that

again, fairly esoteric stuff

On Fri, Sep 15, 2023, at 4:25 AM, Cornelis Poppema wrote:
> Hello Mike, thank you very much for the in-depth reply and providing a 
> solution :)
> 
> There is no specific reason that I posted in google groups, I did not visit 
> the support page, this was just something I decided after thinking where to 
> post first between googlegroups and stackoverflow, I will use github 
> discussions when I open a new discussion in the future!
> Your workaround will help me continue so many many thanks. I thought I tried 
> all variations, but I can only concede after seeing this work that I did not 
> attempt adding the selectinload to the root options().
> 
> It might warrant a new thread, but can you also tell me if there is a way to 
> control the options() that sqlalchemy uses for the recursion of next_step ?
> 
> Ie. I included this bit in the original post:
> ```
> .options(
> selectinload(StepModel.next_step, recursion_depth=-1),
> ...
> )
> ```
> 
> and it would be helpful to know if I can chain the same options to next_step, 
> so that step also has its actions and their relationships() eagerly available 
> etc.
> 
> PS
> FWIW the reason I included `raiseload("*")` in options() is because I am 
> running async queries, and personally the error I will be confronted with 
> trying to access lazy attributes is more helpful, so I've come to add it by 
> default. Without raisedload(*) I would see:
> ```
> 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)
> ```
> 
> With the help of raisedload(*) I get to see:
> ```
> sqlalchemy.exc.InvalidRequestError: 'ServiceActionModel.service' is not 
> available due to lazy='raise```
> ```
> 
> This helps me tackle those cases more easily one-by-one.
> 
> On Thursday, 14 September 2023 at 15:30:23 UTC+2 Mike Bayer wrote:
>> __
>> 
>> 
>> On Thu, Sep 14, 2023, at 7:36 AM, Cornelis Poppema wrote:
>>> Hi all,
>>> 
>>> I am new to sqlalchemy, I think the idea of what I am trying to achieve is 
>>> relatively simple, but I can't seem to figure out how to retrieve 
>>> `.service` in the same query. I failed to find an example in the 2.0 
>>> documentation for exactly this.
>>> 
>>> My attempts have been to simply chain a .selectinload after the 
>>> .selectin_polymorphic, ie.:
>>> 
>>> ```python
>>> .options(
>>> selectinload(StepModel.next_step, recursion_depth=-1),
>>> selectinload(StepModel.actionbases).selectin_polymorphic(
>>> [
>>> ServiceActionModel,
>>> ],
>>> )
>>> .selectinload(ServiceActionModel.service),
>>> raiseload("*"),
>>> )
>>> ```
>>> 
>>> This gives the error:
>>> 
>>> ```
>>> /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:2442:
>>>  in _raise_for_does_not_link
>>> raise sa_exc.ArgumentError(
>>> E   sqlalchemy.exc.ArgumentError: ORM mapped entity or attribute 
>>> "ServiceActionModel.service" does not link from relationship 
>>> "StepModel.actionbases".  Did you mean to use 
>>> "StepModel.actionbases.of_type(ServiceActionModel)"?
>>> ```
>>> 
>> 
>> Hi -
>> 
>> A few up front things, is it possible you can post these questions that have 
>> a lot of code on github discussions?   That's at 
>> https://github.com/sqlalchemy/sqlalchemy/discussions .I'm sort of 
>> wondering how new users are still arriving here at the mailing list, did you 
>> find this list via the support page at 
>> https://www.sqlalchemy.org/support.html ?   I would want to change the 
>> verbiage there to please refer people to GH discussions instead.
>> Especially with these advanced inheritance eager loading problems, which in 
>> the majority of cases end up being real bugs in SQLAlchemy, as seems to be 
>> the case here (at least, there is an inconsistency in the API that somehow 
>> needs to be documented, or something).
>> 
>> As for the question, first off this is really advanced usage and I've hardly 
>> ever seen people using selectin_polymorphic(), much less deep within a chain 
>> of loaders like this.
>> 
>> The correct form for this load would follow from how it's described at 
>> https://docs.sqlalchemy.org/en/20/orm/queryguide/inheritance.html#combining-additional-loader-options-with-selectin-polymorphic-subclass-loads
>>  , where the ORM allows the selectin_polymorphic(Target, [TargetSubclassA]) 

Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations

[Cornelis Poppema]

Hello Mike, thank you very much for the in-depth reply and providing a 
solution :)

There is no specific reason that I posted in google groups, I did not visit 
the support page, this was just something I decided after thinking where to 
post first between googlegroups and stackoverflow, I will use github 
discussions when I open a new discussion in the future!

Your workaround will help me continue so many many thanks. I thought I 
tried all variations, but I can only concede after seeing this work that I 
did not attempt adding the selectinload to the root options().

It might warrant a new thread, but can you also tell me if there is a way 
to control the options() that sqlalchemy uses for the recursion of 
next_step ?

Ie. I included this bit in the original post:
```
.options(
selectinload(StepModel.next_step, recursion_depth=-1),
...
)
```

and it would be helpful to know if I can chain the same options to 
next_step, so that step also has its actions and their relationships() 
eagerly available etc.

PS
FWIW the reason I included `raiseload("*")` in options() is because I am 
running async queries, and personally the error I will be confronted with 
trying to access lazy attributes is more helpful, so I've come to add it by 
default. Without raisedload(*) I would see:
```
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)
```

With the help of raisedload(*) I get to see:
```
sqlalchemy.exc.InvalidRequestError: 'ServiceActionModel.service' is not 
available due to lazy='raise```
```

This helps me tackle those cases more easily one-by-one.

On Thursday, 14 September 2023 at 15:30:23 UTC+2 Mike Bayer wrote:

>
>
> On Thu, Sep 14, 2023, at 7:36 AM, Cornelis Poppema wrote:
>
> Hi all,
>
>
> I am new to sqlalchemy, I think the idea of what I am trying to achieve is 
> relatively simple, but I can't seem to figure out how to retrieve 
> `.service` in the same query. I failed to find an example in the 2.0 
> documentation for exactly this.
>
> My attempts have been to simply chain a .selectinload after the 
> .selectin_polymorphic, ie.:
>
> ```python
> .options(
> selectinload(StepModel.next_step, recursion_depth=-1),
> selectinload(StepModel.actionbases).selectin_polymorphic(
> [
> ServiceActionModel,
> ],
> )
> .selectinload(ServiceActionModel.service),
> raiseload("*"),
> )
> ```
>
> This gives the error:
>
> ```
> /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:2442:
>  
> in _raise_for_does_not_link
> raise sa_exc.ArgumentError(
> E   sqlalchemy.exc.ArgumentError: ORM mapped entity or attribute 
> "ServiceActionModel.service" does not link from relationship 
> "StepModel.actionbases".  Did you mean to use 
> "StepModel.actionbases.of_type(ServiceActionModel)"?
> ```
>
>
> Hi -
>
> A few up front things, is it possible you can post these questions that 
> have a lot of code on github discussions?   That's at 
> https://github.com/sqlalchemy/sqlalchemy/discussions .I'm sort of 
> wondering how new users are still arriving here at the mailing list, did 
> you find this list via the support page at 
> https://www.sqlalchemy.org/support.html ?   I would want to change the 
> verbiage there to please refer people to GH discussions instead.
> Especially with these advanced inheritance eager loading problems, which in 
> the majority of cases end up being real bugs in SQLAlchemy, as seems to be 
> the case here (at least, there is an inconsistency in the API that somehow 
> needs to be documented, or something).
>
> As for the question, first off this is really advanced usage and I've 
> hardly ever seen people using selectin_polymorphic(), much less deep within 
> a chain of loaders like this.
>
> The correct form for this load would follow from how it's described at 
> https://docs.sqlalchemy.org/en/20/orm/queryguide/inheritance.html#combining-additional-loader-options-with-selectin-polymorphic-subclass-loads
>  
> , where the ORM allows the selectin_polymorphic(Target, [TargetSubclassA]) 
> to be a sibling to the appropriate relationship load, 
> selectinload(TargetSubclassA.elements).   The example there places both of 
> these options comma-separated within select().options().This is the 
> "inconsistent" part because I'm already surprised the ORM is allowing the 
> selectinload() to be present against TargetSubclassA when that's not one of 
> the primary entities in the select().
>
> However in your case, you are coming off of a parent loader option 
> already.  So following from this, the correct form based on a direct 
> reading of those docs would, *in theory*, be:
>
> select().options(
>  selectinload(Parent.target).options(
>  selectin_polymorphic(Target,