CODEWITHSUNDEEP
pythonsqlalchemymarshmallow
mtndoe
mtndoe

Reputation: 444

Nested Marshmallow fields and Sqlalchemy relationships

In my sqlalchemy classes I have the following classes:

class FooBar(Model):
    __tablename__ = ‘foobar’
    id = Column('id', Integer, primary_key=True)

    foonr = Column('foonr', Integer, ForeignKey('foo.nr'), nullable=False)
    barnr = Column('barnr', String, ForeignKey('bar.nr'), nullable=False)

class Foo(Model):
    __tablename__ = ‘foo’
    nr = Column('nr', Integer, primary_key=True)
    foo_name = Column(‘name’,String)


class Bar(Model):
   __tablename__ = ‘bar’
   nr = Column('nr', Integer, primary_key=True)
   bar_name = Column(‘name’,String)
   foo_bar = relationship('foobar', uselist=False)

When I try to nest the classes Foo or Bar in a Marshmallow Schema for FooBar I’m not getting any results (the dictionaries don't have any references to the classes Foo or Bar).

class FooBarSchema(Schema):
   id = fields.Int()
   foo = fields.Nested('FooSchema', many=False)
   bar = fields.Nested('BarSchema', many=False)

How can I get the Foo and Bar classes in the results of the FooBarSchema?

Upvotes: 3

Views: 4542

Answers (1)

Jair Perrut
Jair Perrut

Reputation: 1370

Ok... I'll give you the solution to your problem.

class FooBar(Model):
    __tablename__ = 'foobar'
    id = Column('id', Integer, primary_key=True)
    foonr = Column('foonr', Integer, ForeignKey('foo.nr'), nullable=False)
    barnr = Column('barnr', String, ForeignKey('bar.nr'), nullable=False)
    foo = relationship("Foo", uselist=False)
    bar = relationship("Bar", uselist=False)

class FooBarSchema(Schema):
    id = fields.Int()   
    foo = fields.Nested('FooSchema', many=False)
    bar = fields.Nested('BarSchema', many=False)

But analyzing your code I think we can make it more pythonic.

If, and only if, you do not have extra data in the association table, we can change some things.

Looking at SQLAlchemy doc's for an Many To Many relationship, we can to use the secondary parameter of the relationship(). We have to keep class as you currently have and the class Bar like that:

class Bar(Model):
    __tablename__ = 'bar'
    nr = Column('nr', Integer, primary_key=True)
    bar_name = Column('name',String)
    foos = relationship("Foo", secondary="foobar", backref="bars")

So in Bar.foos we have a list of Foo objects, and the backref also makes it possible to have an Bar list in Foo.bars.

Now we have to configure the BarSchema and FooSchema classes.

class FooSchema(Schema):
    nr = fields.Int()   
    foo_name = fields.Str()
    bars = fields.Nested('BarSchema', exclude=('foos',), many=True)

class BarSchema(Schema):
    nr = fields.Int()   
    bar_name = fields.Str()
    foos = fields.Nested('FooSchema', exclude=('bars',), many=True)

The exclude is to avoid recursive problems.

Upvotes: 1

Related Questions