如何在SQLAlchemy中将自定义模型ClassB作为ClassA的列存储？

Hey there! I get the confusion—both approaches have their place, and the right choice depends entirely on what you need from ClassB in your application. Let’s walk through clear examples for each, plus when to use which.

1. Using TypeDecorator: Store ClassB as a Serialized Column #

This is ideal when ClassB is a value object—it doesn’t need its own database table, has no independent identity, and is just a piece of data that belongs exclusively to ClassA. Think things like a user’s address, a coordinate pair, or a settings object that only makes sense in the context of ClassA.

Example Code #

First, define your ClassB and a custom type to serialize/deserialize it (we’ll use JSON here, but you could also use pickle or another format):

from sqlalchemy import TypeDecorator, VARCHAR
import json

class ClassB:
    def __init__(self, field1: str, field2: int):
        self.field1 = field1
        self.field2 = field2

    # Helper methods to convert to/from dict for serialization
    def to_dict(self):
        return {"field1": self.field1, "field2": self.field2}
    
    @classmethod
    def from_dict(cls, data):
        return cls(data["field1"], data["field2"])

class ClassBType(TypeDecorator):
    impl = VARCHAR

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        # Serialize ClassB instance to JSON string
        return json.dumps(value.to_dict())

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        # Deserialize JSON string back to ClassB instance
        return ClassB.from_dict(json.loads(value))

Now use this custom type in ClassA:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ClassA(Base):
    __tablename__ = "class_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Store ClassB as a serialized column
    b_data = Column(ClassBType)

Pros & Cons #

• Pros: Simple schema (only one table), easy to read/write ClassB directly as an instance in your code, no extra joins needed when querying ClassA.
• Cons: Can’t query individual fields of ClassB directly in SQL (you’d have to fetch the whole ClassA record and parse it), harder to update just part of ClassB, and serialization adds a tiny overhead.

2. Using relationship: Link ClassB as a Separate Entity #

This is the way to go if ClassB is an entity—it has its own identity, might be referenced by multiple ClassA instances, or you need to query, modify, or delete ClassB records independently. Examples could be a user’s order, a blog post’s comment, or a product’s category.

Example Code #

First, define both ClassB and ClassA as separate tables, with a foreign key linking them (we’ll use a one-to-one relationship here):

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

class ClassB(Base):
    __tablename__ = "class_b"
    id = Column(Integer, primary_key=True)
    field1 = Column(String)
    field2 = Column(Integer)
    # Link back to ClassA (if one-to-one)
    class_a_id = Column(Integer, ForeignKey("class_a.id"), unique=True)
    class_a = relationship("ClassA", back_populates="b_data")

class ClassA(Base):
    __tablename__ = "class_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Relationship to ClassB
    b_data = relationship("ClassB", back_populates="class_a", uselist=False)  # uselist=False makes it one-to-one

Pros & Cons #

• Pros: Full SQL queryability for ClassB fields, easy to update individual parts of ClassB, supports relationships like one-to-many or many-to-many, and ClassB can exist independently of ClassA.
• Cons: Adds an extra table to your schema, requires joins when fetching ClassA with ClassB (though SQLAlchemy handles this seamlessly), and is slightly more complex to set up.

Which Should You Choose? #

• Go with TypeDecorator if:

  • ClassB is a simple, dependent object that never needs to be accessed outside of ClassA.
  • You want a minimal database schema with no extra tables.
  • You don’t need to query ClassB fields directly in SQL.

• Go with relationship if:

  • ClassB has its own lifecycle (e.g., you might create/delete ClassB without touching ClassA).
  • You need to filter or sort ClassA records based on ClassB fields.
  • Multiple ClassA instances might reference the same ClassB (or vice versa).

内容的提问来源于stack exchange，提问作者Jason Cromer