flask-sqlalchemy中的一对多、多对多

一对多

Article 和 Content 一对多关系

class Article(db.Model):
    __tablename__ = 'articles'

    aid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50), nullable=False)
    content = db.Column(db.Text, nullable=False)
+   comments = db.relationship('Comment', backref=db.backref('articles'))
    createdDate = db.Column(db.DateTime, nullable=False, default=datetime.now(),
                            server_default=db.text('CURRENT_TIMESTAMP'))
    updatedDate = db.Column(db.DateTime, nullable=False, default=datetime.now(),
                            server_default=db.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
    
class Comment(db.Model):
    __tablename__ = 'comments'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    content = db.Column(db.Text, nullable=False)
    aid = db.Column(db.Integer, db.ForeignKey('articles.aid')) # 文章 id

article = Article.query.get(1) # 查询文章
article.comments.append(Comment(content='评论123')) # 添加评论，可为多个
db.session.add(article)
db.session.commit() # 提交

print(article.comments[0].content) # 评论123

comments 表中数据

id	content	aid
1	评论123	1

多对多

Article 和 Category 多对多关系

ac = db.Table('ac',
              db.Column('aid', db.Integer, db.ForeignKey('articles.aid')),
              db.Column('cid', db.Integer, db.ForeignKey('categories.cid'))
              )

class Article(db.Model):
    __tablename__ = 'articles'

    aid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50), nullable=False)
    content = db.Column(db.Text, nullable=False)
+   categories = db.relationship('Category', secondary=ac, backref=db.backref('articles'))
    createdDate = db.Column(db.DateTime, nullable=False, default=datetime.now(),
                            server_default=db.text('CURRENT_TIMESTAMP'))
    updatedDate = db.Column(db.DateTime, nullable=False, default=datetime.now(),
                            server_default=db.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
    
class Category(db.Model):
    __tablename__ = 'categories'

    cid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(30), nullable=False)

article.categories.append(Category(name='归档1'))
article.categories.append(Category(name='归档2'))
db.session.add(article)
db.session.commit()
print(article.categories) # [<Category 12>, <Category 13>]

需要新建一个关联表 ac，两个外键为 Article 和 Comment 的主键，其中一个模型使用 relationship 关联，与一对多不同的是需要增加一个字段 secondary ，值为关联表

ac 表

aid	cid
1	12
1	13

categories 表

cid	name
12	归档1
13	归档2

---

使用 relationship 还有一个比较方便的操作，可以直接使用映射属性名新增关联表数据

新建文章时

article = Article(title='cesi12', content='con123', categories=[Category(name='ca111'), Category(name='ca222')])
db.session.add(article)
db.session.commit()

等同于下面的操作

article = Article(title='cesi12', content='con123')
category1 = Category(name='ca111')
category2 = Category(name='ca222')
article.categories.append(category1)
article.categories.append(category2)
db.session.add(article)
db.session.add(actegory1)
db.session.add(category2)
db.session.commit()