introduce built-in constructors for Base class, to simplify model instantation from nested objects

[scd75]

Is your feature request related to a problem? Please describe.
I started using SQLAlchemy, and while the ORM is super rich in terms of features, I found that it lacks a simple way to instantiate nested models from a dictionnary.

Describe the solution you'd like
I have a model "Parent", that contains a one-to-many relationship to another model "Child". I would like to be able to instantiate seamlessly a "Parent" class, from a dictionnary containing a list of "child" dictionnaries (say, a list named "childs" whose members are standards dictionnaries containing child attributes)

Describe alternatives you've considered
Alternative is to recursively construct the model for members of the "childs" list, but it is not super generic

Additional context
See this issue on this topic:
I suggested the use of the custom constructor as follows, which works for one-to-many relationships, but would be great to have built-in support for this, as it seems to be quite a standard feature:

import sys
from sqlalchemy.ext.declarative import declarative_base


def _declarative_constructor_auto_instantiate_nested(self, **kwargs):
    """A simple constructor that allows initialization from kwargs.

    Sets attributes on the constructed instance using the names and
    values in ``kwargs``.

    Only keys that are present as
    attributes of the instance's class are allowed. These could be,
    for example, any mapped columns or relationships.

    EDITED by @scd75 to auto_instantiate nested lists' elements as child classes
    """
    cls_ = type(self)
    relationships = self.__mapper__.relationships
    for k in kwargs:
        if not hasattr(cls_, k):
            raise TypeError(
                "%r is an invalid keyword argument for %s" % (k, cls_.__name__)
            )
        if k in relationships.keys():
            if relationships[k].direction.name == 'ONETOMANY':
                childclass = getattr(sys.modules[self.__module__], relationships[k].argument)
                setattr(self, k, [childclass(**elem) for elem in kwargs[k]])
        else:
            setattr(self, k, kwargs[k])


_declarative_constructor_auto_instantiate_nested.__name__ = "__init__"

Base = declarative_base(
    constructor=_declarative_constructor_auto_instantiate_nested)

[mjpieters]

This sounds a lot like you are reinventing the Marshmallow wheel, deserialising JSON data structures into Python objects. Take a look at Marshmallow-SQLAlchemy, have it auto-generate a schema from your model then use the schema to create your nested instances.

This is not really the job of SQLAlchemy itself though. Normal Python classes don’t behave this way either, so why should SQLAlchemy models?

[scd75]

Well in my case I use the framework FastAPI, which relies on Pydantic. I use Pydantic for serialization / deserialization, no problem with that. There is a similar package Pydantic-SQLAlchemy, that generates schemas from SQLAlchemy models, but this is not really my issue here. In any case the nested instances created by either Pydantic or Mashmallow are still Schema instances, and I need to insert then into DB at some point. I could not find any helper from either Pydantic or Mashmallow in that matter.

So my issue was more: how to insert in a single payload an object into DB, together with a bunch of related (possibly arbitrarily nested) objects, without having to recursively create all underlying object instances before insertion. Maybe it is not on SQLAlchemy side, but if not, I am not sure where it should be handled.
See this issue on the topic:
tiangolo/fastapi#2194

Thanks!

[CaselIT]

I think this would be solved if you could be able to instrument the pydantic classes to be used with the orm, but currently it's not possible.
IIRC @zzzeek looked into it, since SQLAlchemy 1.4 supports instrumenting decorated classes, like in the case of dataclasses, but it's currently impossible to do with how pydanting does it's instrumentation. Again IIRC the problem was mainly this https://github.com/samuelcolvin/pydantic/blob/5261fd05a0374b84ce2602d45990baf480fa2417/pydantic/main.py#L402

So for the time being, this is probably a pydantic issue

[scd75]

I think this would be solved if you could be able to instrument the pydantic classes to be used with the orm, but currently it's not possible.
IIRC @zzzeek looked into it, since SQLAlchemy 1.4 supports instrumenting decorated classes, like in the case of dataclasses, but it's currently impossible to do with how pydanting does it's instrumentation. Again IIRC the problem was mainly this https://github.com/samuelcolvin/pydantic/blob/5261fd05a0374b84ce2602d45990baf480fa2417/pydantic/main.py#L402

So for the time being, this is probably a pydantic issue

--> I am not sure I fully get what you are meaning. I don't think that it is possible with Marshmallow neither, or is it?

But I agree that could be a solution yes

[CaselIT]

Not sure if it's currently possible to instrument marshmallow classes so that they can be used with the orm. I guess that ideally it could be an option.

@zzzeek maybe, long term, this could be an extension point for other libraries to provide instrumentation support for their way of defining things? So we don't have to support them all directly

[zzzeek]

Not sure if it's currently possible to instrument marshmallow classes so that they can be used with the orm. I guess that ideally it could be an option.

@zzzeek maybe, long term, this could be an extension point for other libraries to provide instrumentation support for their way of defining things? So we don't have to support them all directly

this is about a constructor, right? you can use whatever constructor you'd like, either by using an explcit Base class or by passing https://docs.sqlalchemy.org/en/14/orm/mapping_api.html?highlight=declarative_base#sqlalchemy.orm.declarative_base.params.constructor . this is fully pluggable. the constructor notion as given is perfectly fine it's just something out of scope for SQLAlchemy itself, I would be fine having a recipe on the wiki showing how to introspect the relationships to design just such a constructor. if we actually made this a feature, then it would be a magnet for new user requests and issues that would be better solved if they just made their own constructor that does what they want.

[zzzeek]

welcome to our first "ideas" discussion! :)

[CaselIT]

@zzzeek maybe, long term, this could be an extension point for other libraries to provide instrumentation support for their way of defining things? So we don't have to support them all directly

sorry I was potentially unclear. I meant that all the inspection that we do to take a dataclass and instrument it to be a model could be provided as an api so that maybe pydantinc can write an extension to instrument its classes that's picked up by the registry; marshmallow could to the same, etc. This way we would not need to explicitly support library foo_bar, but it could be foo_bar to provide an extension for sqlalchemy.

Again I'm just making a suggestion, I have no idea of how feasible it is, but maybe it could be a nice addition for a future 1.4.x or 2.x

[zzzeek]

OK that would be the thing we do in util.dataclass_fields , util.local_dataclass_fields, as well as that we are looking in f.metadata which is a dataclasses concept.

the API used here can be generalized but I would say it was very tricky to get the dataclass thing to work correctly. If other explicit attribute systems like I guess marshmallow is (?) want to work with that we can help, but we would need good motivation to make it work.

pydantic is really the lib that gets in the way very directly because they explicitly make descriptors not work at all. it's pretty hard to work around that.