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__)
)
方法
- 添加小说
novel = Novel(novel_title="测试小说", cover="暂无")
- 添加作者
author1 = Author.query.first()
novel.author.append(author1)
- 添加类型
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()
```
