sqlalchemy 多对多

简介

在 sqlalchemy 中多对多通过中间表来实现，和django的区别是， sqlalchemy 需要自己来实现中间表。

数据关系介绍：
小说 <-> 作者， 多对多
小说 <-> 类型， 多对多

建表示例：

base.py , 这个是一个基础表包含了基础字段和基础方法，所有数据模型表可以直接继承

from sqlalchemy import func
from novel import db, ma
from typing import List


class BaseModel(db.Model):
    """ 基础模型 """
    __abstract__ = True  # 这个声明为抽象表， sqlalchemy 不去创建表
    created_at = db.Column(db.DateTime, default=func.now(), comment="创建时间")
    updated_at = db.Column(db.DateTime, default=func.now(), comment="更新时间")
    deleted_at = db.Column(db.DateTime)

    def create(self):
        db.session.add(self)
        db.session.commit()

    def update(self):
        """
        更新数据
        :return:
        """
        self.updated_at = func.now()
        db.session.commit()

    def delete(self):
        """
        删除单条数据
        :return:
        """
        self.deleted_at = func.now()
        db.session.commit()

    @classmethod
    def delete_many(cls, ids: List[int]) -> int:
        """
        删除多条数据
        :param ids: 数据id
        :return: 成功数量
        """
        deleted_count = cls.__class__.query.filter(cls.id.in_(ids)).update({
            cls.__class__.deleted_at: func.now
        }, synchronize_session=False)
        return deleted_count

author.py 用户表

# -*- coding:utf-8 -*-
import sqlalchemy

from novel.model.base import BaseModel
from novel import ma, db


class Author(BaseModel):
    """
        小说作者信息
    """
    __tablename__ = "author"
    id = db.Column(db.BigInteger, autoincrement=True, primary_key=True)
    auth_name = db.Column(db.VARCHAR(50), comment="作者名称")

    def __init__(self, auth_name: str):
        self.auth_name = auth_name

    def __repr__(self):
        return f"id: {self.id} - name: {self.auth_name}"

# 序列化，可以不写
class AuthorSchema(ma.Schema):
    class Meta:
        model = Author
        # fields = ("id", "auth")
        # 这里是序列化字段， 写了一个简单匿名函数进行展示， fields = ("id", "auth_name", "created_at", "updated_at", "deleted_at")， 或者直接注释掉这一行，也可以达到同样的效果
        fields = tuple(
            filter(lambda x: isinstance(Author.__dict__[x], sqlalchemy.orm.attributes.InstrumentedAttribute),
                   Author.__dict__)
        )

novel_type.py 小说类型表

# -*- coding:utf-8 -*-
import sqlalchemy
from novel.model.base import BaseModel
from novel import ma, db


class NovelType(BaseModel):
    """ 小说分类 """
    __tablename__ = "novel_type"
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    type_name = db.Column(db.VARCHAR(20), unique=True, nullable=False, comment="分类名称")

    def __init__(self, type_name: str):
        self.type_name = type_name

    def __repr__(self):
        return f"id: {self.id} - type_name: {self.type_name}"


class NovelTypeSchema(ma.Schema):
    class Meta:
        model = NovelType
        fields = tuple(
            filter(lambda x: isinstance(NovelType.__dict__[x], sqlalchemy.orm.attributes.InstrumentedAttribute),
                   NovelType.__dict__)
        )
  # 序列化外键
    novel_type = ma.Nested(NovelTypeSchema, many=True)
    author = ma.Nested(AuthorSchema, many=True)

novel.py 小说表

# -*- coding:utf-8 -*-
import sqlalchemy
from novel.model.base import BaseModel
from novel import ma, db


novel_to_type = db.Table(
    'novel_to_type',  # 中间表 表名
    db.metadata,  # 元类的数据
    db.Column("novel_id", db.BigInteger, db.ForeignKey('novel.id')),
    db.Column("novel_type_id", db.Integer, db.ForeignKey("novel_type.id"))
)


novel_to_author = db.Table(
    'novel_to_author',  # 中间表 表名
    db.metadata,  # 元类的数据
    db.Column("novel_id", db.BigInteger, db.ForeignKey('novel.id')),
    db.Column("author_id", db.BigInteger, db.ForeignKey("author.id"))
)


class Novel(BaseModel):
    __tablename__ = "novel"
    id = db.Column(db.BigInteger, autoincrement=True, primary_key=True)
    novel_title = db.Column(db.VARCHAR(50), unique=True, nullable=False, comment="小说标题")
    cover = db.Column(db.VARCHAR(200), nullable=False, comment="小说封面, 存图片的存储路径")
    hot = db.Column(db.Integer, default=0, comment="热度，点击一次加一")
    novel_type = db.relationship("NovelType", backref='novel_type', secondary=novel_to_type)
    author = db.relationship("Author", backref='novel_author', secondary=novel_to_author)

    def __init__(self, novel_title: str, cover: str):
        self.novel_title = novel_title
        self.cover = cover

    def __repr__(self):
        return f"id: {self.id} - title: {self.novel_title}"


class NovelSchema(ma.Schema):
    class Meta:
        model = Novel
        fields = tuple(
            filter(lambda x: isinstance(Novel.__dict__[x], sqlalchemy.orm.attributes.InstrumentedAttribute),
                   Novel.__dict__)
        )

方法

1. 添加小说

      novel = Novel(novel_title="测试小说", cover="暂无")

2. 添加作者

     author1 = Author.query.first()
     novel.author.append(author1)

3. 添加类型

   noty= NovelType.query.first()
   novel.novel_type.append(noty)
 ```
4. 创建
 ```
   novel.create()  # 这里主要提交事务， db.session.commit(), create() 方法中就是调用提交事务的方法
 ```
5. 查询
 ```
   novel.author 
   novel.novel_type
 ```
6. 删除关系
 ```
   1. 获取作者对象 author1
   2. 删除
     novel.remove(author1)
   3. 提交事务
     novel.update()
 ```