梗概

• ORM的核心是中间的R, 关系的建立以及关系的使用
• 一对多表, 一表是主表, 多表是从表
  1. 主表和从表的时间没有谁前谁后的问题(例如主表是称号, 从表是用户, 没有先后顺序), 则主表记录的删除, 不应同时删除从表数据
  2. 先有主表, 后有从表(例如主表是任务表, 从表是任务的log记录表), 则这种表主表记录删除后, 从表记录应当被一并删掉(联级删除)
  3. 主表删则从表删(看有没有指定cascade='all, delete'); 从表删主表永远不会被删
  4. 级联删除要设置relationship的cascade参数
• 一对一表
  1. 一对一表是在一对多基础上, 主表增加useist=False即可
  2. 主表删则从表删, 从表删但主表不能删
• 多对多表
  1. 在一对多表基础上, 增加一个关联表(dbTable), 主表的relationship再增加个参数secondary=这个关联表
  2. 多对多表不应联级删除(例如, 主表是任务, 从表是人员)
• 三种表都有增删改查的操作
  1. 增加, 简单, 关联增加就是用对象做字段的值
  2. 查, 关联查询, 也简单, 主要是filter()
  3. 改, 也简单, relationship里面可以用append()和remove()
  4. 删除, 要区分是否需要连接删除, 主要是relationship里面的cascade设置: 不设置就是从表不跟着一起被删; 设置了cascade='all, delete'就会从表跟着主表一起被删:
     1. delete: 父对象被删除时, 子对象一起被删除
     2. delete-orphan: 子对象失去关联的付对象时立即被删除
     3. save-update, merge: 默认值,
     4. all: save-update, merge, refresh-expire, expunge
     5. all, delete-orphan: 简直是删得只干净
• ORM使用的步骤
  1. 设计, 模型
  2. 建表, db.create_all()
  3. 使用, 上面的全都是使用
  4. 修改维护, 主要就是数据库的装修(迁移): 先把数据搬出来, 然后装修, 装修完了再搬回去

ORM操作就是把数据库的表及其关系(Relational)映射(Mapper)成python的Model类(Object), 然后通过操作这个类来间接操作数据库. 其实质是: 把SQL语句的编写转换称对类的操作, 其好处就是: 简化表间关系复杂的数据库操作, 避免写复杂的SQL语句 (集中体现在3.3.2节, 3.3.3节 ). 具体连接为:

• 常规: 业务逻辑 ——————————— 数据库驱动(可能有 ) —— 数据库
• ORM: 业务逻辑 —— py的Model类 —— 数据库驱动(可能有 ) —— 数据库

总结一句就是: 如果表之间的关系复杂, 外键很多, 用ORM会很简单
Flask-SQLAlchemy官方文档见: Flask-SQLAlchemy文档

• ORM用到的模块: 使用一个独立的(不光是Flask用, 其他web框架也可以用的 )数据库模块SQLAlchemy的Flask二次封装版Flask-SQLAlchemy把:

  1. 对Model类的操作翻译成数据库看得懂的SQL语句
  2. 数据库返回的结果翻译回Model类的相关属性

• 数据库驱动: 上面那家伙翻译出标准SQL语句后, 需要发送到数据库服务器上(通过TCP协议), 然后获取数据库的执行结果(收发室 )

  1. 如果是sqlite3数据库, 是一个单独的文件, 不是服务, 所以不需要数据库驱动
  2. 如果是mysql(或者其他), 就需要加pymysql这个数据库驱动来发送SQL语句以及获取结果(正常情况下mysql数据库是在单独一台服务器上的, 使用mysql驱动时要注意python的版本, 以及驱动实例的名字)

1.ORM文件结构

为了避免蓝图等py文件发生交叉循环引用, 推荐这样的文件结构. 建议把前面 1#py和 3#py文件以及 sqlite3数据库文件放到一个单独的文件夹 database里去, 好管理

1. db_ins.py: SQLAlchemy对象的实例文件
2. 主.py: 主py文件(创建Flask实例然后.run()的那个 ), 引用db_ins.py实例并初始化
3. db_model.py: 模型文件, 引用db_ins.py实例并创建数据模型(数据表)
4. bp_blueprintA.py: 各种蓝图文件, 引用db_ins.py实例并使用. (注意: 本文件本来只需要导入db_ins实例不需要导入Model的——实际上Model在主.py文件里已经导入进去了的, 蓝图也导入主.py, 所以这里原本不需要导入Model文件的——, 但是因为没有导入Model表, 语法检查时会提示没有找到Model的定义, 但是程序可以正常运行, 如果不想看到语法错误提示, 只需要在本文件中再导入一次Model表就可以了)
5. db_reset.py: 负责数据库重置的py程序, 包括使用model中定义的表创建数据库表, 删除数据库表, 创建数据库等. 用命令执行

文件样板如下:

1. SQLAlchemy实例:db_ins.py

from flask_sqlalchemy import SQLAlchemy  # 导入类

db = SQLAlchemy()  # 创建SQLAlchemy实例(只有这两句)

2. 主py文件入口: 主.py
   注意: sqlite的连接URI, 冒号后面是三个斜杠///, ①如果数据库路径用相对路径, 则路径前面可以不加或者加./都行, ②如果数据库路径用绝对路径, 则要在前面加一个/表示linux的根目录

# 导入SQLAlchemy实例, 建议把数据库相关文件放到一个database文件夹里去
from database.db_ins import db
# 这个可导入也可不导入. 本py文件用不上这个Model文件, 但是在主py里导入后蓝图里面就不用重复导入了, 可以节约代码(不过如果蓝图里不导入Model, 语法检查会出问题但是程序运行没问题, 建议还是在蓝图里导入吧)
# from database.model import *

# 创建的Flask实例名字是app

# Flask-SQLAlchemy数据库-配置参数，新建一个参数类, 然后导入
class config_SQLAlchemy(object):
    """SQLAlchemy配置类"""
    # sqlite3数据库相对路径
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + './database/my.db'  # 前面就是三个/
    # 数据库路径（相对 relative），加不加 ./ 都行
    # database_path_rel = 'database/my.db'
    # 数据库路径（绝对 absolute）,文件夹路径 + 数据库相对路径
    # database_path_abs = os.path.join(os.path.abspath(os.path.dirname(__file__)), database_path_rel)
    # SQLAlchemy连接数据库的协议和数据库路径，这里用的是相对路径（也可以用绝对路径）
    # SQLALCHEMY_DATABASE_URI = 'sqlite:///' + database_path_rel
    # 关闭数据库动态追踪
    SQLALCHEMY_TRACK_MODIFICATIONS = False  # 目标数据库被其他程序更高后, 本ORM模型对应进行修改(多个业务服务器时)
    # 这个参数是指, 如果你数据库中的数据被修改了, 那么我的py类模型也要相应修改, 其中,数据库中的数据被修改
    # 主要是指被其他的进程或者运行相同代码的其他服务器(负载均衡)给修改了.
    # 对于目前这种小的web程序: 只有一个程序一个服务器的情况,可以关闭,不会发生数据库被其他程序更改的情况(都是本程序修改的)
    # 关闭SQL语句的打印
    SQLALCHEMY_ECHO = False  # 调试时再开启, 查看SQLAlchemy翻译的SQL是咋写的
# 导入数据库参数类
app.config.from_object(config_SQLAlchemy)
# 这个from_object是把相关参数达导入app.config字典中
# 1. 对于已经存在的键, 覆盖之
# 2. 对于不存在的键, 新建之
# 3. 对于导入类中键名不是全大写的键, 忽略之
# 4. 对于字典中的其他键, 存续之

# 初始化SQLAlchemy数据库对象db
db.init_app(app)

3. 数据(表)模型: db_model.py

from database.db_ins import db  # 导入db对象，用来创建表的模型类（虽然model.py与db_ins.py是同一个文件夹database下的文件，但是不能在from里面直接写db_ins，因为：这个model.py会被导入到`主.py`里然后作为`主.py`的一部分运行，因此运行时`主.py`查找模块的路径是从自己所在位置开始的，所以前面要加[database.]db_ins）
import datetime

# 数模模型的基类, 其他模型在此基础上创建 class ModelA(ModelBase, db.Model):
class ModelBase(object):
    # ตอนนี้
    dornee = datetime.datetime.now()

    # 数据模型的默认字段
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True)
    insert_at = db.Column(db.DateTime, default=dornee)
    update_at = db.Column(db.DateTime, default=dornee, onupdate=dornee)

    # 定义一个将查询结果的一条记录自动字典化的函数  【字典的生成式】
    def toDict(self):
        # 注意: 只能返回db.Column创建的真实字段, ORM的逻辑字段返回不到, 因为ORM里面的是其他的class的实例
        return {key: getattr(self, key) for key in self.__table__.columns.keys()}

    # 定义一个Save的方法把当前实例instinct保存到数据库, 返回元组(成功与否True/False, 附加信息msg)
    def save(self, dornee=dornee):
        try:
            db.session.add(self)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            msg = f'[数据保存时发生错误][{dornee}]'
            print(msg)
            print(e)
            return False, msg
        else:
            # print(f'[数据保存成功][{dornee}]')
            return True, '数据保存成功'

    # 定义一个删除的方法, 返回元组(成功与否True/False, 附加信息msg)
    def delete(self, dornee=dornee):
        try:
            db.session.delete(self)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            msg = f'[数据删除时发生错误][{dornee}]'
            print(msg)
            print(e)
            return False, msg
        else:
            # print(f'[数据删除成功][{dornee}]')
            return True, 0

# 以下是定义各种表的类,详见2.数据模型(Model)部分
class ModelA(ModelBase, db.Model):
    __tablename__ = '×××'  # 表名字

    ...

4. 蓝图中调用SQLAlchemy实例操作数据库: bp_blueprintA.py

from database.db_ins import db  # 导入SQLAlchemy实例
from database.model import *  # 其实这个在`主.py`已经导入了，这里不必要导入。但如果这里不导入的话,语法检查会提示没有Model里定义的类，所以多就导入一次好了

# 下面在视图函数里直接使用db就可以了
# ...

5. 数据库重置等维护文件: db_reset.py

• 主要是创建所有表db.create_all(), 删除所有表db.drop_all()的使用, 很简单, 就不写了
• db.create_all()时, 如果Model中定义的表不存在, 则新建, 如果存在就算了, Model中没定义但数据库中已经存在的表不管他们
  1. 注意: 这个只涉及在数据中创建真实的表 (db.Column()创建的内容 ), 创建好以后就不能修改了, 要修改需要用到migrate.
  2. 但是ORM的其他方面是可以随时修改的, 比如.relationship()中的参数啊, ORM创建对象的方法啊之类的
• db.drop_all()也是, 只删除Model中定义了的表, 其他的表都不管

2. 数据模型(Model)

2.1 数据模型Model的定义

• 数据模型一般定义在./database/db_model.py文件中
• 数据模型是一个对应关系: ①一个py类class对应数据库中一张表table; ②类的属性对应表中的字段
• 类的属性有两种, ①一种是用.Column()创建的字段, 是数据库表中的真实存在的字段, ②另一种是用.relationship()创建的字段, 这个不是数据库表中真实存在的字段, 是ORM模型中存在的关系, 他表示: 这里的值是另外一张表的一个或多个记录
• 此外, 类可以添加一个初始化方法__init__, 用来提示在创建这个类实例的同时设置字段的值; 类还可以设置一个自定义方法比如toDict()用来把实例的属性全部输出成list, 以便于后续把读取的结果转换成json格式.

from database.db_ins import db  # 导入SQLAlchemy实例

class Role(ModelBase, db.Model):  # 创建一个名字叫Role的类, 记录角色数据
    __tablename__ = 'tbl_role'  # 对应的数据库中真实表的名字
    id = db.Column(db.Integer, primary_key=True)  # 字段id
    title = db.Column(db.String, unique=True)  # 字段title

    # 类初始化
    def __init__(self, title):  # 可以在创建对象时提示需要输入哪些字段
        # id设置了db.Integer和primary_key=True后自动转为自增字段, 不用管 
        self.title = title

类似于db.Integer, db.String的是SQLAlchemy字段类型, 可以有以下种类:

类似于primary_key=True的是字段属性, 可以有以下选项:

2.2[ 一对多]关系表

2.2.1 [一对多]关系表模型的建立

• 一对多关系表的核心与重点, 用得最多的. 理解的难点在于外键ForeignKey和反向引用backref(back reference)
• 一对多表是指: 1条主表(一)记录可以关联n条从表(多)记录, 但是1条从表(多)记录只能关联1条主表(一)记录: 主1 vs (从1, 从5, 从7) && 从1 vs 主1
• 主表上有一个用.relationship()创建的属性(不是真实的表的字段, 只是一个存在于ORM模型中的逻辑字段 ), ①这个属性保存的是从表的 n条记录 , 默认是一个实际为list的类似类型(如果不需要list, 则需要在.relationship()中用参数指定一下: uselist=False), ②其第一个参数表示保存哪一个类的实例们( 这里是从[类]的角度来写的, 不是从[数据库表]的角度来写的, 因为这个属性不是真实的数据库表 ), ③第二个参数backref='xxx'指定的'xxx'是主表为从表新建的一个虚拟 字段, 从表通过这个虚拟字段, 可以快速查询到从表的记录从属于主表的哪一条记录(查询到的是一个完整的类, 需要用属性名来读取这个类的各个属性值)而不用写SQL语句, 通过这种关联, 可以实现MongoDB那样的节点一级一级的展开下去
• ★★★★★级联删除 cascade: 主表 .relationship()的第三个参数cascade='all, delete'用于指定级联删除. 在默认情况下( 就是不加这个参数情况下 ) , 主表的数据删除后, 从表的数据是不会随着一起被删除的; 但是如果加了这个这个参数 , 那么主表的数据删除后, 从表关联的数据会被一起删除!
  1. 对于现有主表数据, 后才有从表数据的关系, 例如现有任务, 后有任务办理记录, 这类的关系就可以使用这种级联删除
  2. 对于其他的关系, 没有先后顺序的关系, 千万不要加入级联删除 啊!!!
• 从表 上有一个外键db.ForeignKey('tbl_role.id'), 指向主表的主键, 也是数据库表中的真实记录. 这里的参数是从数据库真实存在的表的角度来写的, 其中: tbl_role是主表的表名(数据库中真实的表名), id的这个表主键的字段名(数据库中真实的字段名)
• 通过主表.relationship()和从表.ForeignKey()的设置, 可以实现从主表到从表的顺查, 以及从从表都主表的倒查, 非常方便, 不用不停的写SQL语句然后调试调试调试了!

# db_model.py
from database.db_ins import db  # 导入SQLAlchemy实例

class Role(ModelBase, db.Model):  # 角色表
    __tablename__ = 'tbl_role'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, unique=True)
    users = db.relationship('User', backref='role')  # 记录[关系]的属性, 不是字段名; 指明从属的[类名]叫User, 并且给从表加一个反查虚拟字段名role

class User(ModelBase, db.Model):  # 用户表
    __tablename__ = 'tbl_users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True)
    male = db.Column(db.Boolean, default=True)
    birthday = db.Column(db.Date)
    role_id = db.Column(db.Integer, db.ForeignKey('tbl_role.id'))  # 外键, 是一个真实的数据库表的字段, 指向数据库表tbl_role的主键id字段
    contact = db.relationship('ContactInfo', backref='user')  #  记录[关系]的属性, 指明从属的[类名]叫ContactInfo, 并且给从表加一个反查虚拟字段名user

class ContactInfo(ModelBase, db.Model):  # 联系方式表
    __tablename__ = 'tbl_contact'
    id = db.Column(db.Integer, primary_key=True)
    ctcWay = db.Column(db.String, nullable=False)
    ctcValue = db.Column(db.String, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('tbl_users.id'))

2.2.2 [一对多]关系表的关联查询

2.2.2.1 顺查

• 以role查询为例, user和contactinfo类似

# 查询role, 扩展显示从属的user以及从属于user的contactinfo
from flask import Blueprint, jsonify
from database.db_ins import db
from database.model import *

@role_user.route('/roles')
def showroles():
    mlist = Role.query.all()
    r = [m.toDict(showUsers=True) for m in mlist]
    return jsonify(r)


# 查询结果json:
[
    {"id": 1, "title": "敬畏的寂静", "users": [  // 顺查的user信息
        {
            "birthday": "Mon, 04 May 2020 00:00:00 GMT",
            "contact": [  // 顺查的联系方式信息
                {"ctcValue": "31766533", "ctcWay": "wx"},
                {"ctcValue": "dfsdkf@dfa.com", "ctcWay": "email"}
            ], 
            "id": 3,
            "male": false,
            "name": "爱的封印",
            "role": {"id": 1, "title": "敬畏的寂静", "users": null},
            "role_id": 1
        },
        {
            "birthday": "Mon, 04 May 2020 00:00:00 GMT",
            "contact": [],
            "id": 4,
            "male": false,
            "name": "鸟语花香",
            "role": {"id": 1, "title": "敬畏的寂静", "users": null},
            "role_id": 1
        }]
    },
    {"id": 2, "title": "苍之风云", "users": [] },
    {"id": 3, "title": "呢喃的歌声", "users": [] },
    {"id": 4, "title": "水面上的小草", "users": [] },
    {"id": 5, "title": "路旁的落叶", "users": [] },
    {"id": 6, "title": "无名的旅人", "users": [] }
]

2.2.2.2 倒查

• 以User查询为例, 可以倒查role还可以顺差contactinfo

# 查询user, 扩展显示从属的contactinfo, 以及上一级的role
from flask import Blueprint, jsonify
from database.db_ins import db
from database.model import *

@role_user.route('/users')
def showusers():
    mlist = User.query.all()
    r = [m.toDict(showContact=True) for m in mlist]
    return jsonify(r)


# 查询结果json:
[
    {
        "birthday": "Mon, 04 May 2020 00:00:00 GMT",
        "contact": [  // 顺查的联系方式信息
            {"ctcValue": "31766533", "ctcWay": "wx"},
            {"ctcValue": "dfsdkf@dfa.com", "ctcWay": "email"}
        ],
        "id": 3,
        "male": false,
        "name": "爱的封印",
        "role": {"id": 1, "title": "敬畏的寂静", "users": null},  // 倒查的角色信息
        "role_id": 1
    },
    {
        "birthday": "Mon, 04 May 2020 00:00:00 GMT",
        "contact": [],
        "id": 4,
        "male": false,
        "name": "鸟语花香",
        "role": {"id": 1, "title": "敬畏的寂静", "users": null},  // 倒查的角色信息
        "role_id": 1
    }
]

2.3 [一对一]关系表

• 理解了一对多关系表, 那么一对一关系表就简单多了
• 主表的一个扩展逻辑字段 装的是从表的一个对象实例
• 从表的一个反向引用逻辑字段 装的, 也是主表的一个对象实例
• 只需要增加一个uselist=False参数, 避免逻辑字段的值是list就可了

2.3.1 [一对一]关系表的建立

# [一对一]跟[一对多]唯一的区别是, 多一个`uselist=False`
# 主表, 任务
class Task(ModelBase, db.Model):
    __tablename__ = 'tbl_task'

    title = db.Column(db.String)
    # 因为是 1vs. 1, 所以[扩展逻辑字段]的值不需要list, 直接对象就可以了
    log = db.relationship('Log', backref="task", cascade='all, delete', uselist=False)
    # 加了[cascade='all, delete']可以实现级联删除, 不需要的话就不加这个参数


# 从表, log
class Log(ModelBase, db.Model):
    __tablename__ = 'tbl_log'

    text = db.Column(db.Text)
    # 从表没有变化, 跟一对多表比起来
    task_id = db.Column(db.Integer, db.ForeignKey('tbl_task.id'))

2.3.2 [一对一]关系表的查询

1. 新增, 查询, 更新这些, 跟一对多表是一样的, 就是注意: 保持一对一的关系, 不要有多于1条的从表数据绑定到同一条主表数据上了
2. 删除也一样, 就是要看有没有设置级联删除:
   1. 设置了级联删除后, 从表数据会跟着一起被删除;
   2. 没设置的话从表数据不会被删除, 但是从表的 主表_id字段会变成null.

2.4 [多对多]关系表😋

[多对多]关系表的核心, 是把Model写好 ! 只要 Model关联对了, 后面的操作就跟[一对多]表完全一样了, 唯一的区别无非就是: [一对多]表的两张表各自的逻辑字段里, 一个是 list而另一个是 对象实例(即所谓的"标量", 那list是不是就该说是"向量"了呢 ? ), 而在[多对多]表中, 两个逻辑字段都是 list.

• [多对多]关系表是在[一对多]表的基础做了3个变化:
  1. 从表外键了没有, 转移到下面的关联表 中去了. 从表显得很干净, 看不出跟其他表的关系
  2. 多了一张绑定主表/从表id的关联表, 用db.Table()建立
  3. 主表的.relationship()多了一个参数secondary=关联表
• 建立好以后, 使用起来非常简单:
  1. 主表有.relationship()的扩展逻辑字段 里面装的, 是从表对象的list(实际上是list); 主表会给从表加一个反向引用逻辑字段
  2. 从表的反向引用逻辑字段里装的, 也是主表对象的list(实际上是list)

2.4.1 [多对多]表Model的建立

Task与User之间的多对多图示

# 任务-用户 多对多关联表  这个表会被ORM自动创建,自动维护的
Task_User = db.Table('tbl_Task_User',  # 第一个参数是表名
     # db.Column的第一个参数是字段名. 另外,注意: 设置外键的指向
     db.Column('task_id', db.Integer, db.ForeignKey('tbl_task.id')),  # 注意逗号隔开
     db.Column('user_id', db.Integer, db.ForeignKey('tbl_user.id'))
     )

# 主表, 任务
class Task(ModelBase, db.Model):  # 继承ModelBase的预制特性
    __tablename__ = 'tbl_task'

    title = db.Column(db.String)
    users = db.relationship('User', backref='tasks', secondary=Task_User)  # 核心就是这个secondary参数

# 从表, 用户. 没有外键, 干净得很
class User(ModelBase, db.Model):  # 继承ModelBase的预制特性
    __tablename__ = 'tbl_user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True)
    male = db.Column(db.Boolean, default=True)

2.4.2 [多对多]表的数据新增

单表数据新增

# 新建用户
u = ['奇奇乌布里', '乌云齐齐格', '中华小当家', '爱鉴定的小强']
for ui in u:
    User(name=ui).save()  # 用ModelBase内置的save()方法就可以了

关联数据新增 (因为之前的Model已经建立好了, 所以这里直接 操作数组 一样的操作就可以了 )

# 找到主表对象
task = Task.query.get(2)
# 更新主表扩展逻辑字段, 把User对象append进去即可
task.users.append(User.query.get(1))
# 提交
db.session.commit()
return jsonify('添加完毕!')

2.4.3 [多对多]表的数据查询, 修改

• 跟[一对多]表的查询, 修改是一样的
• 注意要把对象转成字典吧

查询 Task, 扩展逻辑字段 users得到的是User对象的 list

# 读取全部, py部分
r = Task.query.all()
# 注意用户信息的读取 u.toDict()
r = [{'id': m.id, 'title': m.title, 'users': [u.toDict() for u in m.users] if m.users else []} for m in r]
return jsonify(r)

----------------------------------------------
# 返回结果, 浏览器收到的json
[
    {
        "id": 1,
        "title": "敬畏的寂静",
        "users": [
            {
                "birthday": null,
                "id": 1,
                "insert_at": "Sun, 10 May 2020 00:53:26 GMT",
                "male": true,
                "name": "奇奇乌布里",
                "update_at": "Sun, 10 May 2020 00:53:26 GMT"
            },
            {
                "birthday": null,
                "id": 2,
                "insert_at": "Sun, 10 May 2020 00:53:26 GMT",
                "male": true,
                "name": "乌云齐齐格",
                "update_at": "Sun, 10 May 2020 00:53:26 GMT"
            }
        ]
    },
    ...
]

查询 User, 反向引用逻辑字段 tasks得到的也是task对象的 list

# py查询部分
r = User.query.all()
# 注意t.toDict()的[]
r = [{'id': m.id, 'name': m.name, 'male': m.male, 'tasks': [t.toDict() for t in m.tasks] if m.tasks else []} for m in r]
return jsonify(r)

-------------------------------------------------------
# 浏览器收到的json
[
    {
        "id": 1,
        "male": true,
        "name": "奇奇乌布里",
        "tasks": [
            {
                "id": 1,
                "insert_at": "Sun, 10 May 2020 00:53:26 GMT",
                "title": "敬畏的寂静",
                "update_at": "Sun, 10 May 2020 00:53:26 GMT"
            },
            {
                "id": 2,
                "insert_at": "Sun, 10 May 2020 00:53:26 GMT",
                "title": "苍之风云",
                "update_at": "Sun, 10 May 2020 00:53:26 GMT"
            }
        ]
    },
    ...
]

2.4.4 [多对多]表的数据删除

注意: 一般不应启用 [ 级联删除 ]!!!

• 只要不在.relationship()里加cascade='×××'参数, 则①删主表的数据从表中关联的数据不会被删, 只会删除关联表Tas_User的关联记录; 同样, ②删除从表数据, 关联的主表数据也不会被删除, 只会删除关联表Tas_User的关联记录.
• 这里是主表是写有.relationship()扩展逻辑字段的表, 另外那张很干净的表是从表.

3. ORM下的增、查删、查改

3.1 增加

导入模块那些就省了, 下同

@dbtest.route('/i/')
def root_i(txt='东瞧瞧，西看看'):
    m = TodoTitle(txt, datetime.now())  # 创建对象同时赋值
    try:  # 使用try, 以便在出错(比如值不符合条件)时回滚
        db.session.add(m)
        db.session.commit()
    except Exception as e:  # 出粗
        db.rollback()  # 回滚
        r = ' [SQLAlchemy错误][执行添加操作失败] %s' % e  # 输错错误
        print(r)
    else:
        r = '插入数据成功[%d]！' % m.id  # 反馈结果
    return jsonify(r)

3.2 查删

3.2.1 独立表(没外键关系)的查删

@dbtest.route('/d/<int:id>')
def root_d(id=None):
    if id is None:  # 如果没有指定要删除的id
        r = '[错误]都没有告诉人家要删除数据的id了啦[错误]'
    else:
        try:  # 使用try, 以便在出错时回滚
            m = TodoTitle.query.get(id)  # 查到要删的记录
            db.session.delete(m)  # 删除之
            db.session.commit()  # 提交, 使生效
        except Exception as e:
            r = ' [SQLAlchemy错误][删除数据失败] %s' % e  # 输错错误
            print(r)
        else:
            r = '删除成功!'
    return jsonify(r)

3.2.2 [一对多]表中主表 的查删

• 这个是联级删除问题
• 关键是看是否在主表的.relationship()中设置了参数cascade='all, delete', 设置了的话从表数据 会 跟着关联的主表数据的删除而删除 , 没设置的话从表数据 不会 跟着关联的主表数据的删除而删除 .
• 此外, 从表数据删除后, 主表数据都不会被删除

3.2.3 [一对多]表中从表 的查删

• 从表数据删除后, 主表数据都不会被删除 , 因为从表的Model上没有写参数cascade='all, delete'

3.2.4 [多对多]表的级联删除

• 多对多表通常都不具备两张表数据谁先谁后, 谁从属谁的情况, 因此不应设置级联删除 , 直接按默认方式设计Model(就是不写cascade参数 )就可以了.
• 多对多表应确保一个表的数据删除后, 另外一张表的数据不被删除

3.3 查改

3.3.1 对表的一般数据进行更改

• 可以查出来了再逐个修改
• 也可以用.update(字典)写到一行, 但是要注意 :
  1 此时需要用SQLAlchemy原生的db.session.query()查询
  2 不能用.get(), .all(), .first()这些取得结果
  3 .update()的参数是字典, 字典的键 要写成类名.属性名

# 写全
a = User.query.get(3)  # 查找要修改的对象
a.name = f'风起的日子'  # 修改对象属性
db.session.commit()  # 提交修改
return jsonify('ok!')

# update 简写
db.session.query(User).filter(User.id==3).update({User.name: '爱的封印'})
db.session.commit()
return jsonify('ok2!')

3.3.2 对主表 的ORM扩展逻辑字段 进行更改

• 所谓ORM扩展逻辑字段, 就是主表中的relationship字段, 这个字段的值其实是一个list, 里面装的是另外一张表的记录
• 对这些字段的更改, 就是直接把这个字段值当成一个list, 跟更改list是一模一样的: 直接.append(从表对象)或者.remove(从表对象)即可
• 这里就体现出ORM操作的优越性了. 举例如下:

# 从list中减少
a = User.query.get(4)  # 查询从表对象
b = Role.query.get(1)  # 查询主表对象
b.users.remove(a)  # 把从表对象从主表的users逻辑字段中移除
db.session.commit()  # 提交更改
return jsonify(f'更新完毕!')

# 向list中添加
a = User.query.get(4)  # 查询从表对象
b = Role.query.get(2) # 查询主表对象
b.users.append(a)  # 把从表对象添加到主表users逻辑字段中
db.session.commit()  # 提交更改
return jsonify(f'更新完毕2!')

3.3.3 对从表 中反向引用逻辑字段 进行更改

• 所谓反向引用逻辑字段, 就是在主表.relationship()中用backref="×××"给从表加的一个逻辑字段, 来指向主表中的一个对象的
• 这个逻辑字段可以直接作为从表对象的一个属性 (里面装的是一个主表的对象, 不是list), 直接更改就行了, 至于数据库的表怎么修改, 那就交给SQLAlchemy好了
• 注意 : 这个属性是一个单独的对象, 不是list, 所以不能用list的.append()或者.remove()方法去操作 !
• 这里又体现出ORM操作的优越性了. 举例如下:

a = User.query.get(4)  # 查询从表对象
b = Role.query.get(3)  # 查询主表对象
a.role = b  # 把从表对象的反向引用逻辑字段的值给改了
db.session.commit()  # 提交更改
return jsonify(f'更新完毕3!')

# 可以简写如下:
User.query.get(4).role = Role.query.get(3)
db.session.commit()

4. ORM下的查询

SQLAlchemy的原生操作是db.session.query(User.id, User.name).filter(User.name.like('%鸟%')).first()，如果只在一张表里查询，则可以用Flask-SQLAlchemy封装的简化版：User.query.filter(User.name.like('%鸟%')).first()

• 原生的可以选表和选字段，可以使用.group_by()和聚合函数 ，但就是写起来长一点
• 原生选中单一字段是：db.session.query(User.id, User.name)，选中全部字段是：db.session.query(User)？？🚉

4.1 通用查询

主要使用Flask-SQLAlchemy封装的形式来写。
假设: 有个表类是 User.

4.2 字段值的加工

• func对象 : 需要先导入func: from sqlalchemy import func
• 字段加工 : 导入func对象后, 可以在查询时先对字段原始值进行加工, 将加工后的数据作为查询结果, 输出或者过滤
• 原生 : 一般需要用原生SQLAlchemy加工字段, 如:
  r = db.session.query(func.round(User.points)).all()
• 字段名或表达式 : ()中可以是字段名, 也可以是表达式, 如: .query(func.round(User.point*(-1))).
• 通用查询写法 : 为了更灵活的实现查询以及字段值的加工, 建议采取如下查询及返回结果写法

# 字段值的加工更加灵活, 还可以 User.id*(-1) 这样
l = db.session.query(User.id*(-1), User.name, User.birthday).all()
# 返回结果也更好控制
r = [{'id': m[0], 'name': m[1], 'birthday': m[2]} for m in l]
return jsonify(r)

4.3 分组聚合

• 分组聚合只能用SQLAlchemy的原生表达式：db.session.query(func.count(User.id), User.male).group_by(User.male)
• 分组后，得到的结果是一个<class 'flask_sqlalchemy.BaseQuery'>对象(实为list)，可以用for遍历。里面装的是<class 'sqlalchemy.util._collections.result'>对象(实为tuple)，这个对象可以用索引号[i ]来读取数据，顺序与query(func.count(User.id), User.male)中的顺序一致

  mlist = db.session.query(func.count(User.id), \
  User.male).group_by(User.male)
  
  r = [{'数量': m[0], '性别': m[1]} for m in mlist]
  return jsonify(r)
  

• 使用group_by()后就不再使用.all()了，.all()查出来的是实例的list
• 聚合后可以重新写字段名(标签), 但是好像也没啥必要
  .filter(func.count(User.id).label('count')).
• 使用聚合函数count()等之前，需要先从sqlalchemy导入func对象(聚合关键字 ), 然后把函数作为这个关键字的方法来用:

  from sqlalchemy import func
  

• 关于这个聚合关键字 func, 是这样的:
  • SQLAlchemy的作用就是把对象化的查询操作翻译成SQL语句，遇到SQL的函数的时候，旁比说count()函数，SQLAlchemy会要求在函数前面加一个func.前缀，然后SQLAlchemy就认得到这个后面加的是一个SQL函数了。 在翻译的时候，SQLAlchemy就直接把func.去掉来翻译，比如说ORM里写的是func.sum(User.money)，翻译成标准SQL语句喃就成了SUM(id)这个样子
  • 所以说，占是SQL中可以用的函数，SQLAlchemy都认得到
  • 具体可以看两个官方文档的介绍：关于func对象的说明、关于SQL函数使用的说明

• 案例 : 把一个月的记录找出来, 按日期分组, 聚合求每天的count, 如下:

r = db.session.query(func.count(User.id), func.strftime('%d', User.birthday))\
.filter(func.strftime('%m', User.birthday) == '05').group_by(func.strftime('%d', User.birthday))
r = [{'day': int(m[1]), 'count': m[0]} for m in r]
return jsonify(r)

5. SQLAlchemy中直接使用SQL语句

• 除select查询外, 其他SQL动词均要db.session.commit()提交一下(跟ORM的操作也一样)
• select查询结果用for读取, 然后再转为dict对象

# 直接执行sql语句
@role_user.route('/roles')
def showroles():
    # 插入操作
    db.session.execute('insert into tbl_role (title) values ("摇曳的金星")')
    db.session.commit()
    # 查询操作
    mlist = db.session.execute('select * from tbl_role')
    r = [dict(m) for m in mlist]  # for循环提取, dict()转为字典类型
    return jsonify(r)


# 查询结果
[
    {"id": 1, "title": "敬畏的寂静"},
    {"id": 2, "title": "苍之风云"},
    {"id": 3, "title": "呢喃的歌声"},
    {"id": 4, "title": "水面上的小草"},
    {"id": 5, "title": "路旁的落叶"},
    {"id": 6, "title": "无名的旅人"},
    {"id": 7, "title": "摇曳的金星"}
]

• 如何要检查是否成功, 可以通过 try:, except Exception as e:, else: 来执行

try:
    m = TodoTitle.query.get(id)
    m.title = '走起路来摇摇晃晃'
    db.session.commit()
except Exception as e:
    r = ' [SQLAlchemy错误][数据更新错误] %s' % e
    print(r)
else:
    r = '更新成功'

6. 数据库装修改造 (数据库迁移 )

在数据库用到一半的时候突然需要增加个字段或者增加个表什么的, 这时候就需要把数据库拿来装修升级改造了.
——1. 使用 Flask-Migrate不要抱有太多幻想, 各种坑层出不穷, 自己检查[分析文件]里的 upgrade()函数才是王道 !
——2. 私以为, 如果使用sqlite3, 除了增加字段可以用这个migrate一下, 其他对数据库的修改还是直接把数据库拿来编辑算了, 或者直接.create_all(), 然后再手工写代码把原数据中的数据一条一条的抄进去.

• 需要的模块

  1. Flask-Migrate, 负责数据库升级
  2. Flask-Script, 其中的MigrateCommand对象打包了Flask-Migrate的所有命令

  Flask-Migrate (2.5.3)
  Flask-Script (2.0.6)
  

• 彻底删除migrate, 重新init

  1. 有时候, 我们直接修改了数据库, 比如sqlite3, 然后用Flask-Migrate就会出错, 因为Flask-Migrate会自己记录数据库的修改过程. 此时, 最好的办法就是: 把 Flask-Migrate彻底删除, 然后我们重新 init
  2. 要彻底删除Flask-Migrate, 需要: ①把migrations文件夹删掉, ②别忘了, 还要把数据库中的alembic_version表也删掉, 才算彻底删除Flask-Migrate.
  3. 之后就可以重新init了

• 实际步骤

  1. 创建迁移控制文件: dbm.py

     1. 这个文件的主要作用是对整个迁移过程进行控制管理, 其内容是:
        导入迁移所需的工具对象 + 导入本Flask程序的相关对象(app, db)之类的 + 设置Flask-Script的命令选项
     2. 后面所有的升级维护, 都是通过这个迁移控制文件实施的
     3. 文件中的 manager.add_command('x', MigrateCommand)设置了执行迁移命令的前缀, 也就是x (execute的意思呗 ):

     # coding:utf-8
     # 这是一个数据库装修文件
     # 导入数据库迁移所必需的的工具对象
     from flask_migrate import Migrate, MigrateCommand
     from flask_script import Shell, Manager
     # 导入本Flask的相关对象: Flask对象, SQLAlchemy对象, Model对象
     from run import app, db  # 从主文件run.py导入app实例, db实例
     from database.model import *  # 导入数据模型, 注意model文件的位置, 类1+2, 关联表1
     
     
     # 创建 Flask-Script的 manager实例, 执行具体命令
     manager = Manager(app)
     # 为manager命令添加前缀 x
     manager.add_command('x', MigrateCommand)
     # 创建 migrate 实例
     migrate = Migrate(app, db)  # 第一个参数是Flask的实例，第二个参数是Sqlalchemy数据库实例
     
     if __name__ == '__main__':
         manager.run()
     

  2. 创建仓库(其实就是个文件夹migrations)

     1. 这里面要装每一次升级时的数据库变化记录. 因为要装很多, 所以是个仓库(其实是一个文件夹)
     2. 命令: python dbm.py x init
        其中: dbm.py是创建的文件名, x是dbm.py中设置的前缀, init是初始化(创建仓库)命令
     3. 这一步只需要执行一次, 仓库建立好后就可以直接用了

     $ py dbm.py x init
     Creating directory /home/xqq/web/venv8089/flaskr/migrations ...  done
     Creating directory /../flaskr/migrations/versions ...  done
     Generating /../flaskr/migrations/README ...  done
     Generating /../flaskr/migrations/alembic.ini ...  done
     Generating /../flaskr/migrations/script.py.mako ...  done
     Generating /../flaskr/migrations/env.py ...  done
     Please edit configuration/connection/logging settings in
     '/../flaskr/migrations/alembic.ini' before proceeding.
     
     $ tree
     ├── migrations
     │   ├── alembic.ini
     │   ├── env.py
     │   ├── README
     │   ├── script.py.mako
     │   └── versions
     

  3. 数据库变化分析

     1. 这一步是用migrate记录的上一次的数据库结构(首次就是实际数据库的结构)与导入的Model进行对比分析, 看有没有变化. 如果没有变化就终止数据库迁移, 如果有变化就生成一个变化的清单(迁移文件), 里面包含了一个upgrade()函数和一个downgrade()函数, 用来具体执行升级或降级. 需要提醒的是: 用户需要自行检查迁移文件里面的upgrade函数 ! 看你对数据的修改被 Migrate识别出来了没有, 如果没有就要上网去查资料!
     2. 命令: python dbm.py x migrate -m '此次升级的文字说明', -m是message的意思
     3. 如果有变化, 会生成分析文件供后续upgrade执行, 文件名为: 版本号_备注.py. 例如下面生成的分析文件名为: 581a2cf27d86_升级测试.py, 如果不写-m备注, 则会得到文件名581a2cf27d86_.py

     # 检查后发现数据库与Model一致, 没有修改时显示如下:
     $ py dbm.py x migrate -m '升级测试'
     INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
     INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
     INFO  [alembic.env] No changes in schema detected.
     $
     
     # 检查后发现数据库与Model不一致, 有修改时显示如下:
     $ py dbm.py x migrate -m '升级测试'
     INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
     INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
     INFO  [alembic.autogenerate.compare] Detected added column 'tbl_user.password'
       Generating /home/xqq/web/venv8089/flaskr/migrations/versions/581a2cf27d86_升级测试.py ...  done
     $
     

  4. 数据库升级: python dbm.py x upgrade

     1. 就是按照分析文件对数据库进行升级: 修改表等. 需要提醒注意的是: sqlite不支持删除和修改列, migrate使用的alembic库默认不比较字段类型的变化和默认值的变化.

     $ py dbm.py x upgrade
     INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
     INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
     INFO  [alembic.runtime.migration] Running upgrade 581a2cf27d86 -> faa8c1d4f357, 删了一个字段
     # 中间可能会报错
     $
     

  5. 查看数据仓库中的升级历史: python dbm.py x history

     1. 可以查看到每一次升级的路径, 版本号, 升级的备注等信息
     2. 其中<base>就是init的时候数据库的样子, (head)就是目前最新的样子

     $ py dbm.py x history
     581a2cf27d86 -> faa8c1d4f357 (head), 删了一个字段
     <base> -> 581a2cf27d86, 升级测试
     $
     

  6. 数据库降级, 退回到升级前: python dbm.py x downgrade 版本号, 指定版本号就是为了确定执行versions文件夹中的哪一个*.py里的downgrade()函数.

终于写完了, 感觉工作量好大, 这篇写了我一个多星期. 理解的难点在于 "一对多表的模型 ", "查询 ", 以及"日期字段的处理 " .
—— 奇奇乌布里