数据模型

# 数据模型

### 数据库回顾

- 分类：
  - 关系型数据库：MySQL、sqlite、...
  - 非关系型数据库：Redis、MongoDB、...
- 操作：
  - 执行原生SQL语句，没都需要拼接SQL语句，而且很容易出错。
  - ORM操作，使用ORM可以通过直接操作对象来完成对数据的操作。

### flask-sqlalchemy

- 说明：提供了绝大多数关系型数据库的支持，而且提供了ORM(对象关系映射)。

- 安装：`pip install flask-sqlalchemy`

- 连接地址配置：

  - 名称：`SQLALCHEMY_DATABASE_URI`
  - 格式：
    - MySQL：`dialect+driver://username:password@host:port/database`
    - sqlite：`sqlite:/// + 数据库文件地址`

- 使用：

  ```python
  from flask_sqlalchemy import SQLAlchemy
  import os

  # 当前文件所在目录
  base_dir = os.path.abspath(os.path.dirname(__file__))
  database_uri = 'sqlite:///' + os.path.join(base_dir, 'data.sqlite')
  # 配置数据库连接地址
  app.config['SQLALCHEMY_DATABASE_URI'] = database_uri

  # 创建数据库操作对象
  db = SQLAlchemy(app)

  # 设计数据模型
  class User(db.Model):
      # 表名默认是将数据模型类名转换为小写加下划线的风格
      # 如：UserModel => user_model
      # 用于指定表名
      __tablename__ = 'users'
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(20), unique=True)
      email = db.Column(db.String(50), unique=True)
  ```

- 管理：

  ```python
  @app.route('/create/')
  def create():
      # 创建所有的表
      db.create_all()
      return '数据表已创建'

  @app.route('/drop/')
  def drop():
      # 删除用户数据表
      db.drop_all()
      return '数据表已删除'

  # 向终端添加命令创建数据表
  @manager.command
  def createall():
      # 先删除原来的数据表
      db.drop_all()
      # 然后再创建
      db.create_all()
      return '数据表已创建'

  # 向终端添加命令删除数据表
  def dropall():
      # 删库前给出用户提示信息
      if prompt_bool('您确定要删库跑路吗?'):
          db.drop_all()
          return '数据表已删除'
      return '删库有风险，操作需谨慎'
  ```

  > 说明：通过装饰器(@manager.command)修改的函数名就是终端的命令。
  >
  > 使用：python manage.py createall，就可以根据数据模型创建数据表
  >
  > 提示：若创建时数据表已经存在则会失败，可以通过先删除再创建的方式解决，只是副作用有点大。

### 数据库迁移

- 说明：数据模型的更改应用到数据表中的操作叫数据库迁移。flask-migrate扩展就是专门用来数据库迁移的。

- 安装：`pip install flask-migrate`

- 使用：

  ```python
  # 导入类库
  from flask_migrate import Migrate, MigrateCommand

  # 创建数据库迁移对象
  migrate = Migrate(app, db)

  # 将数据库迁移命令添加到终端
  manager.add_command('db', MigrateCommand)
  ```

- 迁移：

  - 初始化，只需要一次，创建一个目录用于存放迁移脚本

  ```shell
  python manage.py db init
  ```

  - 根据模型与数据表，生成迁移脚本

  ```shell
  python manage.py db migrate
  ```

  - 执行迁移脚本

  ```shell
  python manage.py db upgrade
  ```

- 提示：

  - 初始化只需要一次，以后生成迁移脚本，然后执行迁移脚本循环操作即可。
  - 不是每次迁移都会成功，迁移出错时需要手动解决。

### 数据的CURD

- 增加数据

  ```python
  # 设置自动提交操作，每次请求结束会自动提交操作
  app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
  # 禁止追踪数据的更改，会销毁额外的性能
  app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

  @app.route('/insert/')
  def insert():
      # 创建对象
      # ming = User(name='ming', email='ming@163.com')
      # dong = User(name='dong', email='dong@163.com')
      # 保存单条数据到数据库
      # db.session.add(dong)

      yujie = User(name='yujie', email='yujie@163.com')
      baoxi = User(name='baoxi', email='baoxi@163.com')
      long = User(name='long', email='long@163.com')
      # 保存多条数据到数据库
      db.session.add_all([yujie, baoxi, long])

      # 提交操作
      # db.session.commit()
      return '数据已添加'
  ```

- 查询数据

  ```python
  @app.route('/select/<uid>/')
  def select(uid):
      # 根据主键查询，查到返回对象，没有查到返回None
      u = User.query.get(uid)
      if u:
          return u.name
      return '查无此人'
  ```

- 修改数据

  ```python
  @app.route('/update/<uid>/')
  def update(uid):
      u = User.query.get(uid)
      if u:
          u.email = 'xxx@163.com'
          # 下面这句是再次保存，ORM会自动区分更新和插入
          db.session.add(u)
          return '数据已修改'
      return '查无此人'
  ```

- 删除数据

  ```python
  @app.route('/delete/<uid>/')
  def delete(uid):
      u = User.query.get(uid)
      if u:
          # 删除数据
          db.session.delete(u)
          return '数据已删除'
      return '查无此人'
  ```

### 模型设计参考

- 常见字段类型

  | 类型名          | python类型           | 说明                   |
  | ------------ | ------------------ | -------------------- |
  | Integer      | int                | 整型(32)               |
  | SmallInteger | int                | 整型(16)               |
  | BigInteger   | int/long           | 整型(64)               |
  | Float        | float              | 浮点数                  |
  | String       | str                | 变长字符串                |
  | Text         | str                | 不受限制的文本              |
  | Boolean      | bool               | 布尔值，只有True/False     |
  | Date         | datetime.date      | 日期                   |
  | Time         | datetime.time      | 时间                   |
  | Datetime     | datetime.datetime  | 日期时间                 |
  | Interval     | datetime.timedelta | 时间间隔                 |
  | PickleType   | pikcle.dumps()     | 使用pickle处理后的python对象 |
  | LargeBinary  | bytes              | 任意大的二进制数据            |

- 常见字段选项

  | 选项            | 说明                |
  | ------------- | ----------------- |
  | primary_key   | 是否作为主键索引，默认为False |
  | autoincrement | 是否设置字段自增，默认为False |
  | unique        | 是否作为唯一索引，默认为False |
  | index         | 是否作为普通索引，默认为False |
  | nullable      | 字段是否可以为空，默认为True  |
  | default       | 设置默认值             |

- 总结：

  - 插入数据可以不传值的字段：自增的主键、有默认值的、可以为空的
  - 使用flask-sqlalchemy时要求每个模型都有一个主键，默认名字为id
  - 模型类名与数据表中的名字
    - 默认：将大驼峰格式的模型类名，转换为小写加下划线格式，如：`UserModel => user_model`
    - 指定：`__tablename__`，使用此类属性指定表名

### 各种查询

- 说明：绝大多数的数据库操作都是查询，这些操作都是通过方法来体现的。

- 常见操作：

  | 方法           | 说明                              |
  | ------------ | ------------------------------- |
  | get          | 根据主键进行查询，查到返回对象，没查到返回None       |
  | get_or_404   | 功能同上，查不到时，直接报404错               |
  | all          | 查询所有数据，返回一个列表(元素全是对象)           |
  | first        | 返回第一条数据，没有时返回None               |
  | first_or_404 | 功能同上，查不到时报404错                  |
  | limit        | 限制结果集数量，返回查询对象，可以继续进行链式查询操作     |
  | offset       | 设置偏移量，返回查询对象，可以继续进行链式查询操作       |
  | order_by     | 结果集排序，可以指定多个字段，asc升序(默认)，desc降序 |
  | count        | 统计总数                            |

- 聚合函数

  - 说明：`max、min、sum、avg、count`
  - 示例：

  ```python
  from sqlalchemy import func

  # 求最大值
  max_age = db.session.query(func.max(User.age)).scalar()
  return str(max_age)
  ```

- 指定条件查询

  ```python
  # 等值条件查询
  users = User.query.filter_by(age=18).all()
  # 指定任意条件查询
  users = User.query.filter(User.age > 20).all()
  return ','.join(u.name for u in users)
  ```

### filter条件查询

- 关系

  ```python
  >,    __gt__
  如：
      users = User.query.filter(User.age > 20).all()
      # 与上面等价
      users = User.query.filter(User.age.__gt__(20)).all()
  >=,    __ge__
  <,    __lt__
  <=, __le__
  ==,    __eq__
  !=, __ne__
  ```

- 范围

  ```python
  # users = User.query.filter(User.id.between(1, 3)).all()
  # users = User.query.filter(User.id.in_((1, 3, 5))).all()
  users = User.query.filter(User.id.notin_((1, 3, 5))).all()
  ```

- 内容

  ```
  startswith：以什么内容开头
  endswith：以什么内容结尾
  contains：包含什么内容
  like：模糊匹配
  notlike：模糊匹配相反的条件
  ```

- 逻辑

  ```python
  from sqlalchemy import and_, or_

  # 默认就是逻辑与
  # users = User.query.filter(User.id > 2, User.age > 20).all()
  # 与上式等价
  # users = User.query.filter(and_(User.id > 2, User.age > 20)).all()
  # 逻辑或
  users = User.query.filter(or_(User.id > 2, User.age > 20)).all()
  ```

  ​