# 数据模型
### 数据库回顾
- 分类：
- 关系型数据库：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()
```
