2.4.1 [多对多]表Model的建立

# 任务-用户 多对多关联表  这个表会被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.
字段加工 : 导入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)
func.strftime('%w', 日期字段)
取得 日期是星期几, 0-6的字符串, 0是周日
==前面有个str, so返回的全是字符串 !==
==要数字的话在构建 json时 int()一下即可==
%Y年数,四位字符串; 注意是大写
%m月数,两位字符串; 
%d当月几号,两位字符串;
%w星期几,一位字符串, 0是周日; 
%W是全年的第几周,字符串; 注意是大写
%%百分号
日期字段处理(extract)
先导入from sqlalchemy import extract
不推荐 (这个的功能 func.strftime()都能做到 )
extract('year', 日期字段)
取得 年, 整数
extract('month', 日期字段)
取得 月, 整数
extract('day', 日期字段)
取得 日, 整数
extract('hour', 日期字段)
取得 小时, 整数
extract('minute', 日期字段)
取得 分钟, 整数
extract('second', 日期字段)
取得 秒, 整数
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: 来执行
    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(), 然后再手工写代码把原数据中的数据一条一条的抄进去.
需要的模块
Flask-Migrate, 负责数据库升级

Flask-Script, 其中的MigrateCommand对象打包了Flask-Migrate的所有命令
Flask-Migrate (2.5.3)
Flask-Script (2.0.6)
彻底删除migrate, 重新init
有时候, 我们直接修改了数据库, 比如sqlite3, 然后用Flask-Migrate就会出错, 因为Flask-Migrate会自己记录数据库的修改过程. 此时, 最好的办法就是: 把 Flask-Migrate彻底删除, 然后我们重新 init
要彻底删除Flask-Migrate, 需要: ①把migrations文件夹删掉, ②别忘了, 还要把数据库中的alembic_version表也删掉, 才算彻底删除Flask-Migrate.
之后就可以重新init了
创建迁移控制文件: dbm.py
这个文件的主要作用是对整个迁移过程进行控制管理, 其内容是:

导入迁移所需的工具对象 + 导入本Flask程序的相关对象(app, db)之类的 + 设置Flask-Script的命令选项
后面所有的升级维护, 都是通过这个迁移控制文件实施的
文件中的 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()
创建仓库(其实就是个文件夹migrations)
这里面要装每一次升级时的数据库变化记录. 因为要装很多, 所以是个仓库(其实是一个文件夹)
命令: python dbm.py x init

其中: dbm.py是创建的文件名, x是dbm.py中设置的前缀, init是初始化(创建仓库)命令
这一步只需要执行一次, 仓库建立好后就可以直接用了
$ 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
数据库变化分析
这一步是用migrate记录的上一次的数据库结构(首次就是实际数据库的结构)与导入的Model进行对比分析, 看有没有变化. 如果没有变化就终止数据库迁移, 如果有变化就生成一个变化的清单(迁移文件), 里面包含了一个upgrade()函数和一个downgrade()函数,  用来具体执行升级或降级. 需要提醒的是: 用户需要自行检查迁移文件里面的upgrade函数 ! 看你对数据的修改被 Migrate识别出来了没有, 如果没有就要上网去查资料!
命令: python dbm.py x migrate -m '此次升级的文字说明', -m是message的意思
如果有变化, 会生成分析文件供后续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
数据库升级: python dbm.py x upgrade
就是按照分析文件对数据库进行升级: 修改表等. 需要提醒注意的是: 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, 删了一个字段
# 中间可能会报错
查看数据仓库中的升级历史: python dbm.py x history
可以查看到每一次升级的路径, 版本号, 升级的备注等信息
其中<base>就是init的时候数据库的样子, (head)就是目前最新的样子
$ py dbm.py x history
581a2cf27d86 -> faa8c1d4f357 (head), 删了一个字段
<base> -> 581a2cf27d86, 升级测试
数据库降级, 退回到升级前: python dbm.py x downgrade 版本号, 指定版本号就是为了确定执行versions文件夹中的哪一个*.py里的downgrade()函数.