marshmallow-sqlalchemy v0.30 用户指南(2024中文版)

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

v0.30.0 变更日志

marshmallow-sqlalchemy 是粘合 SQLAlchemy 与 marshmallow 的一个（反）序列化库。

UPDATED BY YULIKE 2024-01-19

jupyter nbconvert --to markdown "marshmallow-sqlalchemy.ipynb"

Declare your models 声明你的模型 ¶

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship, backref

engine = sa.create_engine("sqlite:///:memory:")
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()


class Author(Base):
    __tablename__ = "authors"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, nullable=False)
    date_created = sa.Column(sa.DateTime, default=sa.func.now())

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


class Book(Base):
    __tablename__ = "books"
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String)
    author_id = sa.Column(sa.Integer, sa.ForeignKey("authors.id"))
    author = relationship("Author", backref=backref("books"))


Base.metadata.create_all(engine)

Generate marshmallow schemas 生成棉花糖模式 ¶

from marshmallow_sqlalchemy import SQLAlchemySchema, auto_field


class AuthorSchema(SQLAlchemySchema):
    class Meta:
        model = Author
        load_instance = True  # Optional: deserialize to model instances

    id = auto_field()
    name = auto_field()
    books = auto_field()


class BookSchema(SQLAlchemySchema):
    class Meta:
        model = Book
        load_instance = True

    id = auto_field()
    title = auto_field()
    author_id = auto_field()

您可以使用 SQLAlchemyAutoSchema 自动为模型的列生成字段。以下模式类与上面的模式类等效。

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema


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


class BookSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Book
        include_fk = True
        load_instance = True

确保在实例化 Schemas 之前声明 Models 。否则 sqlalchemy.orm.configure_mappers() 将运行得太快并失败。

笔记

模型上任何不直接派生自 Column 的 column_property （例如映射表达式）都将被检测并标记为 dump_only 。

hybrid_property 根本不会自动处理，需要显式声明为字段。

反）序列化你的数据 ¶

author = Author(name="Chuck Paluhniuk")
author

author_schema = AuthorSchema()

book = Book(title="Fight Club", author=author)
author, book

session.add(author)
session.add(book)

session.commit()
author,book

dump_data = author_schema.dump(author)
print(dump_data)
# {'id': 1, 'name': 'Chuck Paluhniuk', 'books': [1]}

load_data = author_schema.load(dump_data, session=session)
print(load_data)
# <Author(name='Chuck Paluhniuk')>

<Author(name='Chuck Paluhniuk')>






(<Author(name='Chuck Paluhniuk')>, <__main__.Book at 0x16573096890>)






(<Author(name='Chuck Paluhniuk')>, <__main__.Book at 0x16573096890>)



{'id': 1, 'name': 'Chuck Paluhniuk', 'date_created': '2024-01-18T08:14:04', 'books': [1]}
<Author(name='Chuck Paluhniuk')>

from sqlalchemy import select

book1 = session.scalars(select(Book)).first()
# book2 = Book.query.get(1)

book3 = session.query(Book).filter_by(id=1).first()
book3

book_schema = BookSchema()
print(book_schema.dump(book1))
print(book_schema.dump(book1), author_schema.dump(
    book1.author), book_schema.dump(book1.author.books[0]),)

<__main__.Book at 0x16573096890>



{'id': 1, 'title': 'Fight Club', 'author_id': 1}
{'id': 1, 'title': 'Fight Club', 'author_id': 1} {'id': 1, 'name': 'Chuck Paluhniuk', 'date_created': '2024-01-18T08:14:04', 'books': [1]} {'id': 1, 'title': 'Fight Club', 'author_id': 1}

立即获取 ¶

pip install -U marshmallow-sqlalchemy

Requires Python >= 3.8, marshmallow >= 3.0.0, and SQLAlchemy >= 1.4.40.

需要 Python >= 3.8、marshmallow >= 3.0.0 和 SQLAlchemy >= 1.4.40。

Recipes 食谱 ¶

Base Schema I 基本模式 I ¶

marshmallow 的常见模式是定义一个 Schema 基类，它具有应用程序的 Schemas 的通用配置和行为。

您可能想要定义一个公共会话对象，例如用于所有 Schemas 的 scoped_session 。

# 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 .db import Session
from marshmallow_sqlalchemy import SQLAlchemySchema

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

# models/User.py
class User(Base):
    __tablename__ = 'users'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(255))
    email = sa.Column(sa.String(255), unique=True)
    password = sa.Column(sa.String(255))

# 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 基本架构 II ¶

这是使用公共 Session 对象定义 BaseSchema 类的另一种方法。

# 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 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.

这允许您定义类 Meta 选项，而无需子类 BaseSchema.Meta 。

# myproject/users/schemas.py

# from ..schemas import BaseSchema
# from .models import User


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

Introspecting Generated Fields 内省生成的字段 ¶

反思为 SQLAlchemyAutoSchema 生成哪些字段通常很有用。

生成的字段将添加到 Schema's _declared_fields 属性中。

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

<fields.RelatedList(dump_default=<marshmallow.missing>, attribute=None, validate=[], required=False, load_only=False, dump_only=False, load_default=<marshmallow.missing>, allow_none=False, error_messages={'required': 'Missing data for required field.', 'null': 'Field may not be null.', 'validator_failed': 'Invalid value.', 'invalid': 'Not a valid list.'})>

You can also use marshmallow_sqlalchemy's conversion functions directly.

您还可以直接使用 marshmallow_sqlalchemy's 转换函数。

from marshmallow_sqlalchemy import property2field

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

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

<fields.Integer(dump_default=<marshmallow.missing>, attribute=None, validate=[], required=False, load_only=False, dump_only=False, load_default=<marshmallow.missing>, allow_none=False, error_messages={'required': 'Missing data for required field.', 'null': 'Field may not be null.', 'validator_failed': 'Invalid value.', 'invalid': 'Not a valid integer.', 'too_large': 'Number too large.'})>

Overriding Generated Fields 覆盖生成的字段 ¶

由 SQLAlchemyAutoSchema 生成的任何字段都可以被覆盖。

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",))

您可以使用 auto_field 函数根据单个模型属性生成棉花糖 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. 如果字段的外部数据键与模型的列名称不同，您可以将列名称传递给 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)

自动生成 SQLAlchemy 模型的模式 ¶

如果不覆盖上面详述的任何生成的字段，那么实现大量模式可能会很乏味。 SQLAlchemy 有一个钩子，可用于触发模式的创建，将它们分配给 SQLAlchemy 模型属性 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(): Update for SQLAlchemy 2.0 Update 2024-01-18 
        for class_ in Base.registry._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

An example of then using this: 然后使用这个的一个例子：

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base # Update for SQLAlchemy 2.0 Update 2024-01-18 
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import event
from sqlalchemy.orm import Mapper  # Update for SQLAlchemy 2.0 Update 2024-01-18

# Either import or declare setup_schema here

engine = sa.create_engine("sqlite:///:memory:")
session = scoped_session(sessionmaker(bind=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)
author = Author(name="宫泽贤治")

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))

{'id': 1, 'name': '宫泽贤治'}

这是受到 ColanderAlchemy 功能的启发。

Smart Nested 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(SmartNested, self).serialize(attr, obj, accessor)

An example of then using this: 然后使用这个的一个例子：

# session.rollback()
class Book(Base):
    __tablename__ = "books"
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String)
    author_id = sa.Column(sa.Integer, sa.ForeignKey("authors.id"))
    author = relationship("Author", backref=backref("books"))

Base.metadata.create_all(engine)

session.rollback()

from marshmallow_sqlalchemy import SQLAlchemySchema, auto_field
from sqlalchemy.orm import joinedload


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

    class Meta:
        model = Book
        sqla_session = Session


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

# book = Book.query.get(1)
book = session.execute(select(Book).where(Book.id == 10)).scalar()
# print(BookSchema().dump(book))
print(BookSchema().dump(book)["author"])
# {'id': 1}

{'id': 2}

joinedload

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

{'id': 2, '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. 有时可能需要反序列化瞬态（未附加到会话）的实例。在这些情况下，您可以在 SQLAlchemySchema 的 Meta 类中指定 transient 选项。

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')>

<Author(name='John Steinbeck')>

You may also explicitly specify an override by passing the same argument to load. 您还可以通过将相同的参数传递给 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')>

<Author(name='John Steinbeck')>

请注意，瞬态会传播到关系（即，嵌套项的自动生成模式也将是瞬态的）。

也可以看看

请参阅状态管理以了解会话状态管理。

Controlling Instance Loading 控制实例加载 ¶

您可以在创建架构实例时通过传入 load_instance 参数来覆盖架构 load_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"}

<Author(name='John Steinbeck')>
{'id': 1, 'name': 'John Steinbeck'}

API Reference API 参考 ¶

marshmallow-sqlalchemy.readthedocs.io/en/latest/a…

update by YULIKE 2024-01-18

附录 总结 SQLAlchemy 1.x和2.x语法区别：

• 1.x语法：模型类.query()是等同于db.session.query(模型类, <查询语法>)

  因为 Flask-SQLAlchemy 向每个模型添加一个 query 对象。用于查询给定模型的实例。如：User.query 是 db.session.query(User) 的快捷方式。

• 2.x语法：db.session.execute() 是sqlalchemy 2.x版本后的语法，flask-sqlalchemy3.x版本都是基于sqlalchemy 2.x的语法使用。

• 2.x语法更加接我们时使用sql语句去查询数据。特别要注意的是2.x的各个方法调用都要按顺序，和sql一样，如where要在group_by前调用。

• 使用模型类.query()语法最大的问题是没有比较完善的语法提示。

• 旧版本的flask-sqlalchemy是基于sqlalchemy 1.x版本的，但是新版本依旧可以使用旧版本的查询语法。