SQL炼金术避免一对一关系中的递归

Question

我很抱歉在先进的任何缺乏解释，以及这篇文章的长度。我认为这个问题比我想象的要简单得多。我有两个模型利用一对多关系。对于InsightModel，我的json（）方法显示了以下内容：

{
    name: "insightname",
    start: 1,
    end: 3,
    podcast_id: 1,
    podcast: {
        name: "podcast1",
        wave_data: 1,
        length: 2,
        host: "Hosterman",
        category: "entertain",
        pub_date: "11/1",
        cover_art_url: "google.com"
    }
}

对于我的PodcastModel，json（）方法显示以下内容：

{
    name: "podcast1",
    wave_data: 1,
    length: 2,
    host: "Hosterman",
    category: "entertain",
    pub_date: "11/1",
    cover_art_url: "google.com",
    insights: [
        {
            name: "insightname",
            start: 1,
            end: 3,
            podcast_id: 1
        }
    ]
}

这是我所需要的，但为了让它工作，我必须为每个类创建两个json（）方法，以避免PodcastModel中出现如下递归：

{
    name: "podcast1",
    wave_data: 1,
    length: 2,
    host: "Hosterman",
    category: "entertain",
    pub_date: "11/1",
    cover_art_url: "google.com",
    insights: [
        {
            name: "insightname",
            start: 1,
            end: 3,
            podcast_id: 1,
            podcast: {
                name: "podcast1",
                wave_data: 1,
                length: 2,
                host: "Hosterman",
                category: "entertain",
                pub_date: "11/1",
                cover_art_url: "google.com",
            }
        }
    ]
}

我的播客模型代码是：

from db import db
from datetime import datetime

class PodcastModel(db.Model):

    __tablename__ = 'podcasts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    wave_data = db.Column(db.Float(precision=2))
    length = db.Column(db.Float(precision=2))
    host = db.Column(db.String(80))
    category = db.Column(db.String(80))
    pub_date = db.Column(db.String(50))
    cover_art_url = db.Column(db.String(200)) 

    insights = db.relationship('InsightModel', backref='podcast', lazy='dynamic')

    def __init__(self, name, wave_data, length, host, category, pub_date, cover_art_url):
        self.name = name
        self.wave_data = wave_data
        self.length = length
        self.host = host
        self.category = category
        self.pub_date = pub_date
        self.cover_art_url = cover_art_url

    def json(self):
        return {'name': self.name, 'wave_data': self.wave_data, 'length': self.length, 'host': self.host, 'category': self.category, 'pub_date': self.pub_date, 'cover_art_url': self.cover_art_url, 'insights': [insight.json_no_podcast() for insight in self.insights.all()]}

    def json_no_insight(self):
        return {'name': self.name, 'wave_data': self.wave_data, 'length': self.length, 'host': self.host, 'category': self.category, 'pub_date': self.pub_date, 'cover_art_url': self.cover_art_url}

    @classmethod
    def find_by_name(cls, name):
        # Select * FROM items WHERE name=name LIMIT 1
        return cls.query.filter_by(name=name).first()

    @classmethod
    def find_by_id(cls, _id):
        return cls.query.filter_by(id=_id)

InsightModel如下所示：

from db import db
from models.podcast import PodcastModel

class InsightModel(db.Model):

    __tablename__ = 'insights'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    start = db.Column(db.Float(precision=2))
    end = db.Column(db.Float(precision=2))

    podcast_id = db.Column(db.Integer, db.ForeignKey('podcasts.id'))
    #podcast = db.relationship('PodcastModel')

    def __init__(self, name, start, end, podcast_id):
        self.name = name
        self.start = start
        self.end = end
        self.podcast_id = podcast_id

    def json(self):
        podcast = PodcastModel.find_by_id(self.podcast_id).first().json_no_insight()
        return {'name': self.name, 'start': self.start, 'end': self.end,
                'podcast_id': self.podcast_id, 'podcast': podcast}

    def json_no_podcast(self):
        return {'name': self.name, 'start': self.start, 'end': self.end,
                'podcast_id': self.podcast_id}

    @classmethod
    def find_by_name(cls, name):
        # Select * FROM items WHERE name=name LIMIT 1
        return cls.query.filter_by(name=name).first()

如您所见，我添加了json \u no \u insights（）和json \u no \u podcast（）方法来防止发生递归。然而，我确信阅读这段代码已经给了你一种胃部凹陷的感觉，我迫切需要一个更好的方法来编写它。非常感谢您的任何见解，再次，我为这篇文章的篇幅或任何缺乏解释道歉。你知道吗

Answer 1

让你的生活更轻松-使用棉花糖。你知道吗

from marshmallow import Schema, fields
from flask import jsonify

class PodcastSchema(Schema):
    name = fields.Str()
    wave_data = fields.Float()
    length = fields.Float()
    host = fields.Str()
    category = fields.Str()
    pub_date = fields.Str()
    cover_art_url = fields.Str()
    insights = fields.Nested('InsightSchema')


class InsightSchema(Schema):
    name = fields.Str()
    start = fields.Float()
    end = fields.Float()
    podcast_id = fields.Integer()

然后简单地像这样转储数据：

podcast_schema = PodcastSchema()  # for dict (single)
podcasts_schema = PodcastSchema(many=True)  # for list (array)
jsonify(podcast_schema.dumps(your_json)

注意在PodcastSchema中缺少podcast字段-这将导致（不调整）无限递归。如果需要该字段，可以尝试以下操作：

class PodcastSchema(Schema):
        name = fields.Str()
        wave_data = fields.Float()
        length = fields.Float()
        host = fields.Str()
        category = fields.Str()
        pub_date = fields.Str()
        cover_art_url = fields.Str()
        # dump insights without podcast field
        insights = fields.Nested('InsightSchema', exclude=('podcast', ))

class InsightSchema(Schema):
        name = fields.Str()
        start = fields.Float()
        end = fields.Float()
        podcast = fields.Nested('PodcastSchema')