Dynamically generating marshmallow schemas for SQLAlchemy fails on column attribute lookup for Relationship

Question

I am automatically deriving marshmallow schemas for SQLAlchemy objects using the approach described in How to dynamically generate marshmallow schemas for SQLAlchemy models.

I am then decorating my model classes:

@derive_schema
class Foo(db.Model):
    id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sqlalchemy.text("uuid_generate_v4()"))
    name = db.Column(String, nullable=False)

    def __repr__(self):
        return self.name


@derive_schema
class FooSettings(db.Model):
    foo_id = Column(UUID(as_uuid=True), ForeignKey('foo.id'), primary_key=True, nullable=False)
    my_settings = db.Column(JSONB, nullable=True)
    foo = db.relationship('Foo', backref=db.backref('foo_settings'))

Where my derive_schema decorator is defined as follows:

import marshmallow
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema

def derive_schema(cls):
    class Schema(SQLAlchemyAutoSchema):
        class Meta:
            include_fk = True
            include_relationships = True
            load_instance = True
            model = cls

    marshmallow.class_registry.register(f'{cls.__name__}.Schema', Schema)
    cls.Schema = Schema
    return cls

This used to work fine with SQLAlchemy 1.4. While attempting to upgrade to 2.0.3, I am running into the following exception when changing my schema to inherit SQLAlchemyAutoSchema instead of ModelSchema:

Traceback (most recent call last):
    from foo.model import Foo, FooSettings
  File "foo/model.py", line 21, in <module>
    class SourceSettings(db.Model):
  File "schema_generation/__init__.py", line 18, in derive_schema
    class Schema(SQLAlchemyAutoSchema):
  File "python3.10/site-packages/marshmallow/schema.py", line 121, in __new__
    klass._declared_fields = mcs.get_declared_fields(
  File "python3.10/site-packages/marshmallow_sqlalchemy/schema/sqlalchemy_schema.py", line 91, in get_declared_fields
    fields.update(mcs.get_declared_sqla_fields(fields, converter, opts, dict_cls))
  File "python3.10/site-packages/marshmallow_sqlalchemy/schema/sqlalchemy_schema.py", line 130, in get_declared_sqla_fields
    converter.fields_for_model(
  File "python3.10/site-packages/marshmallow_sqlalchemy/convert.py", line 141, in fields_for_model
    field = base_fields.get(key) or self.property2field(prop)
  File "python3.10/site-packages/marshmallow_sqlalchemy/convert.py", line 180, in property2field
    field_class = field_class or self._get_field_class_for_property(prop)
  File "python3.10/site-packages/marshmallow_sqlalchemy/convert.py", line 262, in _get_field_class_for_property
    column = prop.columns[0]
  File "python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1329, in __getattr__
    return self._fallback_getattr(key)
  File "python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1298, in _fallback_getattr
    raise AttributeError(key)
AttributeError: columns

Looking internally in the stacktrace, it seems like the problem is here:

def _get_field_class_for_property(self, prop):
    if hasattr(prop, "direction"):
        field_cls = Related
    else:
        column = prop.columns[0]
        field_cls = self._get_field_class_for_column(column)
    return field_cls

There is a check for an attribute named direction which is specified on the SQLAlchemy Relationship object. However, this attribute seems to be dynamically loaded, which causes the conditional check to fail and fall back to prop.columns[0]. But since this object is a Relationship and not a ColumnProperty it has no columns attribute which causes the program to crash.

However, I have found a way to force load the direction property by adding the following code to the derive_schema method, before creating the generating Schema class:

import marshmallow
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
from sqlalchemy import inspect


def derive_schema(cls):
    mapper = inspect(cls)
    _ = [_ for _ in mapper.relationships]

    class Schema(SQLAlchemyAutoSchema):
        class Meta:
            include_fk = True
            include_relationships = True
            load_instance = True
            model = cls

    marshmallow.class_registry.register(f'{cls.__name__}.Schema', Schema)
    cls.Schema = Schema
    return cls

Enumerating the relationships and force loading them fixes the materialization of the direction property and thus the program loads fine.

Am I missing something in the model definition to make this work without force loading the relationships?

Answer 1

SQLAlchemy 2 is not officially supported by marshmallow-sqlalchemy at this moment.

Similar issues have been raised in the repository. The following comment is most insightful in your context.

As stated in the above link, an implicit call to registry.configure() that was done in previous SQLAlchemy versions is now removed. You can achieve a similar result manually.

After importing your models:

from sqlalchemy.orm import configure_mappers
configure_mappers()

Once you have imported the models, and called configure_mappers (in that order), you can import the schemas.

EDIT:

Refer to Jerome's comment below, and update to newest version as this has been patched.

Answer 2

This was a compatibility issue with SQLAlchemy 2.x.

• marshmallow-sqlalchemy 0.28 doesn't support SQLAlchemy 2.x but the "sqlalchemy<2.0" lock was only introduced in marshmallow-sqlalchemy 0.28.2 so before I released 0.28.2 people could end up with incompatible versions.

• marshmallow-sqlalchemy 0.29 supports SQLAlchemy 2.x and drops 1.3 support.

TL;DR

Update marshmallow-sqlalchemy and the issue should disappear.