Recipes

Base Schema I

A common pattern with marshmallow is to define a base Schema class which has common configuration and behavior for your application’s schemas.

You may want to define a common session object, e.g. a scoped_session to use for all Schemas.

# myproject/db.py
import sqlalchemy as sa
from sqlalchemy import orm

Session = orm.scoped_session(orm.sessionmaker())
Session.configure(bind=engine)

# myproject/schemas.py

from marshmallow_sqlalchemy import SQLAlchemySchema

from .db import Session


class BaseSchema(SQLAlchemySchema):
    class Meta:
        sqla_session = Session

# myproject/users/schemas.py

from ..schemas import BaseSchema
from .models import User


class UserSchema(BaseSchema):
    # Inherit BaseSchema's options
    class Meta(BaseSchema.Meta):
        model = User

Base Schema II

Here is an alternative way to define a BaseSchema class with a common Session object.

# myproject/schemas.py

from marshmallow_sqlalchemy import SQLAlchemySchemaOpts, SQLAlchemySchema
from .db import Session


class BaseOpts(SQLAlchemySchemaOpts):
    def __init__(self, meta, ordered=False):
        if not hasattr(meta, "sqla_session"):
            meta.sqla_session = Session
        super(BaseOpts, self).__init__(meta, ordered=ordered)


class BaseSchema(SQLAlchemySchema):
    OPTIONS_CLASS = BaseOpts

This allows you to define class Meta options without having to subclass BaseSchema.Meta.

# myproject/users/schemas.py

from ..schemas import BaseSchema
from .models import User


class UserSchema(BaseSchema):
    class Meta:
        model = User

Using Related to serialize relationships

The Related field can be used to serialize a SQLAlchemy relationship as a nested dictionary.

import sqlalchemy as sa
from sqlalchemy.orm import DeclarativeBase, relationship

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema, auto_field
from marshmallow_sqlalchemy.fields import Related


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user"
    id = sa.Column(sa.Integer, primary_key=True)
    full_name = sa.Column(sa.String(255))


class BlogPost(Base):
    __tablename__ = "blog_post"
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String(255), nullable=False)

    author_id = sa.Column(sa.Integer, sa.ForeignKey(User.id), nullable=False)
    author = relationship(User)


class BlogPostSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = BlogPost

    id = auto_field()
    # Blog's author will be serialized as a dictionary with
    # `id` and `name` pulled from the related User.
    author = Related(["id", "full_name"])

Serialization will look like this:

from pprint import pprint

from sqlalchemy.orm import sessionmaker

engine = sa.create_engine("sqlite:///:memory:")
Session = sessionmaker(engine)

Base.metadata.create_all(engine)

with Session() as session:
    user = User(full_name="Freddie Mercury")
    post = BlogPost(title="Bohemian Rhapsody Revisited", author=user)

    session.add_all([user, post])
    session.commit()

    blog_post_schema = BlogPostSchema()
    data = blog_post_schema.dump(post)
    pprint(data, indent=2)
    # { 'author': {'full_name': 'Freddie Mercury', 'id': 1},
    #   'id': 1,
    #   'title': 'Bohemian Rhapsody Revisited'}

Introspecting generated fields

It is often useful to introspect what fields are generated for a SQLAlchemyAutoSchema.

Generated fields are added to a Schema's _declared_fields attribute.

AuthorSchema._declared_fields["books"]
# <fields.RelatedList(default=<marshmallow.missing>, ...>

You can also use marshmallow-sqlalchemy’s conversion functions directly.

from marshmallow_sqlalchemy import property2field

id_prop = Author.__mapper__.attrs.get("id")

property2field(id_prop)
# <fields.Integer(default=<marshmallow.missing>, ...>

Overriding generated fields

Any field generated by a SQLAlchemyAutoSchema can be overridden.

from marshmallow import fields
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
from marshmallow_sqlalchemy.fields import Nested


class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author

    # Override books field to use a nested representation rather than pks
    books = Nested(BookSchema, many=True, exclude=("author",))

You can use the auto_field function to generate a marshmallow Field based on single model property. This is useful for passing additional keyword arguments to the generated field.

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema, field_for


class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author

    # Generate a field, passing in an additional dump_only argument
    date_created = auto_field(dump_only=True)

If a field’s external data key differs from the model’s column name, you can pass a column name to auto_field.

class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author
        # Exclude date_created because we're aliasing it below
        exclude = ("date_created",)

    # Generate "created_date" field from "date_created" column
    created_date = auto_field("date_created", dump_only=True)

Automatically generating schemas for SQLAlchemy models

It can be tedious to implement a large number of schemas if not overriding any of the generated fields as detailed above. SQLAlchemy has a hook that can be used to trigger the creation of the schemas, assigning them to Model.__marshmallow__.

from marshmallow_sqlalchemy import ModelConversionError, SQLAlchemyAutoSchema


def setup_schema(Base, session):
    # Create a function which incorporates the Base and session information
    def setup_schema_fn():
        for class_ in Base._decl_class_registry.values():
            if hasattr(class_, "__tablename__"):
                if class_.__name__.endswith("Schema"):
                    raise ModelConversionError(
                        "For safety, setup_schema can not be used when a"
                        "Model class ends with 'Schema'"
                    )

                class Meta(object):
                    model = class_
                    sqla_session = session

                schema_class_name = "%sSchema" % class_.__name__

                schema_class = type(
                    schema_class_name, (SQLAlchemyAutoSchema,), {"Meta": Meta}
                )

                setattr(class_, "__marshmallow__", schema_class)

    return setup_schema_fn

Usage:

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy import event
from sqlalchemy.orm import mapper

# Either import or declare setup_schema here

engine = sa.create_engine("sqlite:///:memory:")
Session = sessionmaker(engine)
Base = declarative_base()


class Author(Base):
    __tablename__ = "authors"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

    def __repr__(self):
        return "<Author(name={self.name!r})>".format(self=self)


# Listen for the SQLAlchemy event and run setup_schema.
# Note: This has to be done after Base and session are setup
event.listen(mapper, "after_configured", setup_schema(Base, session))

Base.metadata.create_all(engine)

with Session() as session:
    author = Author(name="Chuck Paluhniuk")
    session.add(author)
    session.commit()

    # Model.__marshmallow__ returns the Class not an instance of the schema
    # so remember to instantiate it
    author_schema = Author.__marshmallow__()

    print(author_schema.dump(author))

This is inspired by functionality from ColanderAlchemy.

Smart Nested field

To serialize nested attributes to primary keys unless they are already loaded, you can use this custom field.

from marshmallow_sqlalchemy.fields import Nested


class SmartNested(Nested):
    def serialize(self, attr, obj, accessor=None):
        if attr not in obj.__dict__:
            return {"id": int(getattr(obj, attr + "_id"))}
        return super().serialize(attr, obj, accessor)

An example of then using this:

from marshmallow_sqlalchemy import SQLAlchemySchema, auto_field


class BookSchema(SQLAlchemySchema):
    id = auto_field()
    author = SmartNested(AuthorSchema)

    class Meta:
        model = Book
        sqla_session = Session


book = Book(id=1)
book.author = Author(name="Chuck Paluhniuk")
session.add(book)
session.commit()

book = Book.query.get(1)
print(BookSchema().dump(book)["author"])
# {'id': 1}

book = Book.query.options(joinedload("author")).get(1)
print(BookSchema().dump(book)["author"])
# {'id': 1, 'name': 'Chuck Paluhniuk'}

Transient object creation

Sometimes it might be desirable to deserialize instances that are transient (not attached to a session). In these cases you can specify the transient option in the Meta class of a SQLAlchemySchema.

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema


class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author
        load_instance = True
        transient = True


dump_data = {"id": 1, "name": "John Steinbeck"}
print(AuthorSchema().load(dump_data))
# <Author(name='John Steinbeck')>

You may also explicitly specify an override by passing the same argument to load.

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema


class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author
        sqla_session = Session
        load_instance = True


dump_data = {"id": 1, "name": "John Steinbeck"}
print(AuthorSchema().load(dump_data, transient=True))
# <Author(name='John Steinbeck')>

Note that transience propagates to relationships (i.e. auto-generated schemas for nested items will also be transient).

See also

See State Management to understand session state management.

Controlling instance loading

You can override the schema load_instance flag by passing in a load_instance argument when creating the schema instance. Use this to switch between loading to a dictionary or to a model instance:

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema


class AuthorSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Author
        sqla_session = Session
        load_instance = True


dump_data = {"id": 1, "name": "John Steinbeck"}
print(AuthorSchema().load(dump_data))  # loading an instance
# <Author(name='John Steinbeck')>
print(AuthorSchema(load_instance=False).load(dump_data))  # loading a dict
# {"id": 1, "name": "John Steinbeck"}