时间戳

首页 > 解决方案 > SQL Alchemy - 在一对多关系中避免递归

python - SQL Alchemy - 在一对多关系中避免递归

问题描述

对于任何缺乏解释以及这篇文章的长度，我提前道歉。我认为这个问题比我想象的要简单得多。我有两个利用一对多关系的模型。对于我的 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",
            }
        }
    ]
}

我的 PodcastModel 代码是：

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_no_insights() 和 json_no_podcast() 方法来防止递归发生。但是，我敢肯定，阅读这段代码已经让你感到胃里有一种麻木的感觉，我迫切需要一种更好的方法来编写它。非常感谢您的任何见解，我再次为这篇文章的长度或任何缺乏解释表示歉意。

标签： pythonflasksqlalchemy

解决方案

让您的生活更轻松 - 使用棉花糖。

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)

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

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

推荐阅读