flask-数据库操作 / ORM/Flask-SQLAlchemy/数据表操作/数据操作/数据库迁移
1. 数据库操作
1.1 ORM
ORM 全拼Object-Relation Mapping，中文意为 对象-关系映射。主要实现模型对象到关系数据库数据的映射
优点 :
- 只需要面向对象编程, 不需要面向数据库编写代码.
- 对数据库的操作都转化成对类/对象的属性和方法的操作. 字段--->属性, 关键字-> 操作方法
- 不用编写各种数据库的
sql语句.
- 实现了数据模型与数据库的解耦, 屏蔽了不同数据库操作上的差异.
- 不再需要关注当前项目使用的是哪种数据库。
- 通过简单的配置就可以轻松更换数据库, 而不需要修改代码.
缺点:
- 相比较直接使用SQL语句操作数据库,ORM需要把操作转换成SQL语句，所以有性能损失.
- 根据对象的操作转换成SQL语句,根据查询的结果转化成对象, 在映射过程中有性能损失.
- 增加了学习成本，不同的ORM提供的操作不一样
1.2 Flask-SQLAlchemy
flask默认提供模型操作，但是并没有提供ORM，所以一般开发的时候我们会采用flask-SQLAlchemy模块来实现ORM操作。
SQLAlchemy是一个关系型数据库框架，它提供了高层的 ORM 和底层的原生数据库的操作。flask-sqlalchemy 是一个简化了 SQLAlchemy 操作的flask扩展。
SQLAlchemy: https://www.sqlalchemy.org/
安装 flask-sqlalchemy【清华源】 pip install flask-sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
如果连接的是 mysql 数据库，需要安装 mysqldb 驱动 pip install flask-mysqldb -i https://pypi.tuna.tsinghua.edu.cn/simple
安装flask-mysqldb时，注意
安装 flask-mysqldb的时候，python底层依赖于一个底层的模块 mysql-client 模块
如果没有这个模块，则会报错如下：
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-install-21hysnd4/mysqlclient/
解决方案：
sudo apt-get install libmysqlclient-dev python3-dev
运行上面的安装命令如果再次报错如下：
dpkg 被中断，您必须手工运行 ‘sudo dpkg --configure -a’ 解决此问题。
则根据提示执行命令以下命令，再次安装mysqlclient
sudo dpkg --configure -a
apt-get install libmysqlclient-dev python3-dev
解决了mysqlclient问题以后，重新安装 flask-mysqldb即可。
pip install flask-mysqldb -i https://pypi.tuna.tsinghua.edu.cn/simple
1.2.1 数据库连接设置
- 在 Flask-SQLAlchemy 中，数据库使用URL指定，而且程序使用的数据库必须保存到Flask配置对象的 SQLALCHEMY_DATABASE_URI 键中
config.py，配置文件代码：
class Config(object):
DEBUG = True
SECRET_KEY = "*(%#4sxcz(^(#$#8423"
# 数据库链接配置 = 数据库名称://登录账号:登录密码@数据库主机IP:数据库访问端口/数据库名称?charset=编码类型 dsn
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
- 其他设置：
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
#查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
- 配置完成需要去 MySQL 中创建项目所使用的数据库
mysql -uroot -p123
create database students charset=utf8mb4;
1.2.2 常用的SQLAlchemy字段类型
| 模型字段类型名 | python中数据类型 | 说明 |
|---|---|---|
| Integer | int | 普通整数,一般32位 |
| SmallInterer | int | 取值范围小的整数,一般16位 |
| BigIntager | int或long | 不限制精度的整数 |
| Float | float | 浮点数 |
| Numeric | decimal.Decim | 普通数值,一般32位 |
| String | str | 变长字符串 |
| Text | str | 变长字符串,对较长或不限长度的字符串做了优化 |
| Unicode | unicode | 变长Unicode字符串 |
| UnicodeText | unicode | 变长Unicode字符串，对较长或不限长度的字符串做了优化 |
| Boolean | bool | 布尔值 |
| Date | datetime.date | 日期 |
| Time | datetime.datetime | 日期和时间 |
| LargeBinary | str | 二进制文件内容 |
1.2.3 常用的SQLAlchemy列约束选项
| 选项名 | 说明 |
|---|---|
| primary_key | 如果为True，代表表的主键 |
| unique | 如果为True，为这列创建唯一 索引，代表这列不允许出现重复的值 |
| index | 果为True，为这列创建普通索引，提高查询效率 |
| nullable | 如果为True，允许有空值，如果为False，不允许有空值 |
| default | 为这列定义默认值 |
1.3 数据库的基本操作
- 在Flask-SQLAlchemy中，添加、修改、删除操作，均由数据库会话(sessionSM)管理。
- 会话用 db.session 表示。在准备把数据写入数据库前，要先将数据添加到会话中然后调用 db.commit() 方法提交会话。
- 在 Flask-SQLAlchemy 中，查询操作是通过 query 对象操作数据。
- 最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。
- 最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。
1.4 模型类定义
后面会把模型创建到单独的文件中，但是现在先把模型类写在main.py文件中
from flask import Flask
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
# 字段定义
"""
create table db_student(
id int primary key auto_increment comment="主键",
name varchar(15) comment="姓名",
)
"""
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), unique=True, comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
# 所有的模型必须直接或间接继承于db.Model
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
# repr()方法类似于django的__str__，用于打印模型对象时显示的字符串信息
def __repr__(self):
return f'{self.name}<Course>'
class Teacher(db.Model):
"""老师数据模型"""
__tablename__ = "db_teacher"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="姓名")
option = db.Column(db.Enum("讲师", "助教", "班主任"), default="讲师")
def __repr__(self):
return f"{self.name}< Teacher >"
@app.route("/")
def index():
return "ok！"
if __name__ == '__main__':
with app.app_context():
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
2. 数据表操作
2.1 创建
with app.app_context():
# create_all()方法执行的时候，需要放在模型的后面
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
2.2 删除
db.drop_all() # 慎用，很给力的！！
代码:
from flask import Flask
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
# 字段定义
"""
create table db_student(
id int primary key auto_increment comment="主键",
name varchar(15) comment="姓名",
)
"""
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), unique=True, comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
# 所有的模型必须直接或间接继承于db.Model
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
# repr()方法类似于django的__str__，用于打印模型对象时显示的字符串信息
def __repr__(self):
return f'{self.name}<Course>'
class Teacher(db.Model):
"""老师数据模型"""
__tablename__ = "db_teacher"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="姓名")
option = db.Column(db.Enum("讲师", "助教", "班主任"), default="讲师")
def __repr__(self):
return f"{self.name}< Teacher >"
@app.route("/")
def index():
return "ok！"
if __name__ == '__main__':
with app.app_context():
# db.drop_all()
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
3. 数据操作
添加一条数据
student1 = Student(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
db.session.add(student1)
db.session.commit()
#再次插入 一条数据
student2 = Student(name='小红', sex=False, age=13, email="16565666@qq.com", money=600)
db.session.add(student2)
db.session.commit()
一次插入多条数据
st1 = Student(name='wang',email='wang@163.com',age=22)
st2 = Student(name='zhang',email='zhang@189.com',age=22)
st3 = Student(name='chen',email='chen@126.com',age=22)
st4 = Student(name='zhou',email='zhou@163.com',age=22)
st5 = Student(name='tang',email='tang@163.com',age=22)
st6 = Student(name='wu',email='wu@gmail.com',age=22)
st7 = Student(name='qian',email='qian@gmail.com',age=22)
st8 = Student(name='liu',email='liu@163.com',age=22)
st9 = Student(name='li',email='li@163.com',age=22)
st10 = Student(name='sun',email='sun@163.com',age=22)
db.session.add_all([st1,st2,st3,st4,st5,st6,st7,st8,st9,st10])
db.session.commit()
删除数据
# 方法1[先查询后删除，2条语句]
# 先查询出来
student = Student.query.first()
print(student)
# 再进行删除
db.session.delete(student)
db.session.commit()
# 方法2【一条语句执行，性能更好更高效，在数据改动时添加条件才进行操作，这种用法就是乐观锁】
# 乐观锁和悲观锁
Student.query.filter(Student.id > 5).delete()
db.session.commit()
更新数据
# 先查询数据，然后进行更新，2条语句
stu = Student.query.first()
stu.name = 'dong'
db.session.commit()
# 直接根据条件更新,一条语句[乐观锁]
Student.query.filter(Student.name == 'chen').update({'money': 1998})
db.session.commit()
# 字段引用[利用当前一条数据的字典值进行辅助操作,实现类似django里面F函数的效果]
Student.query.filter(Student.name == "zhang").update({"money":Student.money+1000 * Student.age})
db.session.commit()
3.1 基本查询
3.1.1 常用的SQLAlchemy查询过滤器
| 过滤器 | 说明 |
|---|---|
| filter() | 把过滤器添加到原查询上，返回一个新查询 |
| filter_by() | 把等值过滤器添加到原查询上，返回一个新查询 |
| limit() | 使用指定的值限定原查询返回的结果数量 |
| offset() | 设置结果范围的开始位置，偏移原查询返回的结果，返回一个新查询 |
| order_by() | 根据指定条件对原查询结果进行排序，返回一个新查询 |
| group_by() | 根据指定条件对原查询结果进行分组，返回一个新查询 |
3.1.2 常用的SQLAlchemy查询结果的方法
| 方法 | 说明 |
|---|---|
| all() | 以列表形式返回查询的所有结果 |
| first() | 返回查询的第一个结果，如果未查到，返回None |
| first_or_404() | 返回查询的第一个结果，如果未查到，返回404 |
| get() | 返回指定主键对应的行，如不存在，返回None |
| get_or_404() | 返回指定主键对应的行，如不存在，返回404 |
| count() | 返回查询结果的数量 |
| paginate() | 返回一个Paginate分页器对象，它包含指定范围内的结果 |
| having | 返回结果中符合条件的数据，必须跟在group by后面，其他地方无法使用。 |
get():参数为数字，表示根据主键查询数据，如果主键不存在返回None
Student.query.get()
# # 根据主键获取一条数据
# student = Student.query.get(4)
# print(student)
all()返回查询到的所有对象
Student.query.all()
# # 返回所有结果数据数据
# student_list = Student.query.all()
# print(student_list)
count 返回结果的数量
# 返回结果的数量
ret = Student.query.filter(Student.id<5).count()
print(ret)
first()返回查询到的第一个对象【first获取一条数据,all获取多条数据】
Student.query.first()
# # 返回第一个结果数据
# first_student = Student.query.filter(Student.id<5).first()
# print(first_student)
filter条件查询，支持各种运算符和查询方法或者模糊查询方法。
返回名字结尾字符为g的所有数据。
# 模糊查询
# 使用163邮箱的所有用户
student_list = Student.query.filter(Student.email.endswith("@163.com")).all()
print(student_list)
# 姓名以"zh"开头的
student_list = Student.query.filter(Student.name.startswith("zh")).all()
print(student_list)
# 名字中带有"a"字母的数据
student_list = Student.query.filter(Student.name.contains("a")).all()
print(student_list)
# 也可以使用filter进行精确查找，
# 则需要指定条件格式为: 模型.字段 比较运算符 值。
# 运算符可以是: ==表示相等,!=不相等，> 表示大于 < 表示小于，>=大于等于，<=小于等于
# 单条件比较
student_list = Student.query.filter(Student.age>18).all()
print(student_list)
# 多条件比较
# 要求多个条件都要满足
student_list = Student.query.filter(Student.age>18, Student.sex==True).all()
print(student_list)
# 另一种写法的查询方式
# db.session.query(Student) 相当于 Student.query
# ret = db.session.query(Student).filter(Student.age==22).all()
filter_by精确查询，只支持字段的值是否相等这种条件
例如：返回age等于22的学生
# 单条件
student_list = Student.query.filter_by(age=22).all()
print(student_list)
# 多条件
student_list = Student.query.filter_by(age=22,sex=True).all()
print(student_list)
3.1.3 多条件查询
逻辑非，返回名字不等于wang的所有数据
Student.query.filter(Student.name!='wang').all()
not_ 相当于取反
from sqlalchemy import not_
Student.query.filter(not_(Student.name=='wang')).all()
# # 查询年龄不等于22
# student_list = Student.query.filter(Student.age != 22).all()
# print(student_list)
# student_list = Student.query.filter(not_(Student.age==22)).all()
# print(student_list)
逻辑与，需要导入and，返回and()条件满足的所有数据
from sqlalchemy import and_
Student.query.filter(and_(Student.name!='wang',Student.email.endswith('163.com'))).all()
# # and_(条件1,条件2,....) 等价于 filter(条件1,条件2,.....)
# # age > 18 and email like "%163.com"
# # student_list = Student.query.filter(Student.age > 18, Student.email.endswith("163.com")).all()
#
# student_list = Student.query.filter(
# and_(
# Student.age > 18,
# Student.email.endswith("163.com")
# )
# ).all()
逻辑或，需要导入or_
from sqlalchemy import or_
Student.query.filter(or_(Student.name!='wang',Student.email.endswith('163.com'))).all()
# # 查询性别为True，或者年龄大于18
# sex = 1 or age > 18
# student_list = Student.query.filter(
# or_(
# Student.sex==True,
# Student.age>18
# )
# ).all()
# print(student_list)
# 复合条件的查询情况
# 查询18岁的女生或者22岁的男生
# (age=18 and sex=0) or (age = 22 and sex=1)
# student_list = Student.query.filter(
# or_(
# and_(Student.age==18, Student.sex==False),
# and_(Student.age==22, Student.sex==True),
# )
# ).all()
# print( student_list )
in_范围查询
"""查询id为2, 3, 5, 7, 8这几个学生信息"""
# 查询id是 1 3 5 的学生信息
student_list = Student.query.filter(Student.id.in_([1, 3, 5])).all()
print(student_list)
# 查询id不是 1 3 5 的学生信息
student_list = Student.query.filter(not_(Student.id.in_([1, 3, 5]))).all()
print( student_list )
order_by 排序
# 倒序[值从大到小]
student_list = Student.query.order_by(Student.id.desc()).all()
# 升序[值从小到大]
student_list = Student.query.order_by(Student.id.asc()).all()
# 多字段排序[第一个字段值一样时，比较第二个字段，进行排序]
student_list = Student.query.order_by(Student.age.desc(), Student.id.asc() ).all()
print(student_list)
count统计
# 查询age>=19的男生的数量
from sqlalchemy import and_
# ret = Student.query.filter( and_(Student.age>=19,Student.sex==True) ).count()
ret = Student.query.filter( Student.age>=19, Student.sex==True ).count()
对结果进行偏移量和数量的限制
# 查询年龄最大的3个学生
# student_list = Student.query.limit(2).all()
# print(student_list)
# 查询年龄排第4到第7名的学生
student_list = Student.query.offset(0).limit(2).all()
print(student_list)
student_list = Student.query.limit(2).offset(2).all()
print(student_list)
3.1.4 分页器
run.py
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
# 字段定义
"""
create table db_student(
id int primary key auto_increment comment="主键",
name varchar(15) comment="姓名",
)
"""
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), unique=True, comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
@classmethod
def add(cls):
student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
db.session.add(student)
db.session.commit()
return student
@property
def to_dict(self):
"""把对象转化成字典"""
return {
"id": self.id,
"name": self.name,
"age": self.age,
"sex": self.sex,
"email": self.email,
"money": float("%.2f" % self.money),
}
# 所有的模型必须直接或间接继承于db.Model
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
# repr()方法类似于django的__str__，用于打印模型对象时显示的字符串信息
def __repr__(self):
return f'{self.name}<Course>'
class Teacher(db.Model):
"""老师数据模型"""
__tablename__ = "db_teacher"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="姓名")
option = db.Column(db.Enum("讲师", "助教", "班主任"), default="讲师")
def __repr__(self):
return f"{self.name}< Teacher >"
@app.route("/")
def index():
# 分页器
page = int(request.args.get("page",1)) # 页码
size = int(request.args.get("size",5)) # 每一页数据量
pagination = Student.query.paginate(page=page,per_page=size)
data = {}
data["pagination"] = pagination
return render_template("list.html",**data)
if __name__ == '__main__':
with app.app_context():
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
list.html，代码：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<style>
.page a,.page span{
padding: 2px 6px;
color: #fff;
background: #6666ff;
text-decoration: none;
}
.page span{
color: #fff;
background: orange;
}
</style>
</head>
<body>
<table border="1" align="center" width="600">
<tr>
<th>ID</th>
<th>age</th>
<th>name</th>
<th>sex</th>
<th>money</th>
</tr>
{% for student in pagination.items %}
<tr>
<td>{{ student.id }}</td>
<td>{{ student.age }}</td>
<td>{{ student.name }}</td>
<td>{{ "男" if student.sex else "女" }}</td>
<td>{{ student.money }}</td>
</tr>
{% endfor %}
<tr align="center">
<td colspan="5" class="page">
{% if pagination.has_prev %}
<a href="?page=1">首 页</a>
<a href="?page={{ pagination.page-1 }}">上一页</a>
<a href="?page={{ pagination.page-1 }}">{{ pagination.page-1 }}</a>
{% endif %}
<span>{{ pagination.page }}</span>
{% if pagination.has_next %}
<a href="?page={{ pagination.page+1 }}">{{ pagination.page+1 }}</a>
<a href="?page={{ pagination.page+1 }}">下一页</a>
<a href="?page={{ pagination.pages }}">尾 页</a>
{% endif %}
</td>
</tr>
</table>
</body>
</html>
3.1.5 分组查询
分组查询和分组查询结果过滤
一般分组都会结合聚合函数来一起使用。SQLAlchemy中所有的聚合函数都在func模块中声明的
from sqlalchemy import func
| 函数名 | 说明 |
|---|---|
| func.count | 统计总数 |
| func.avg | 平均值 |
| func.min | 最小值 |
| func.max | 最大值 |
| func.sum | 和 |
代码：
# 查询当前所有男生女生的数量
# ret = db.session.query(Student.sex,func.count(Student.id)).group_by(Student.sex).all()
# print(ret)
# 查看当前学生中各个年龄段的学生人数
# ret = db.session.query(Student.age, func.count(Student.id)).group_by(Student.age).all()
# print(ret)
# 查看当前男生女生的平均年龄
# ret = db.session.query(Student.sex, func.avg(Student.age)).group_by(Student.sex).all()
# ret = [{"sex":"男" if item[0] else "女","age":float(item[1])} for item in ret]
# print(ret)
# 分组后的过滤操作 having
# 在所有学生中，找出各个年龄中拥有最多钱的同学，并在这些同学里面筛选出money > 2000的数据
subquery = func.max(Student.money)
ret = db.session.query(Student.age, subquery).group_by(Student.age).having(subquery > 2000).all()
print(ret) # [(18, Decimal('1000.00')), (22, Decimal('26000.00')), (23, Decimal('1998.00'))]
SQL方法中的关键字顺序：
模型.query. // db.session.query.
filter
group by
having
order_by
limit offset
all / get / first
3.1.6 执行原生SQL语句
# # 查询多条数据
# ret = db.session.execute("select * from db_students").fetchall()
# # 查询一条数据
# ret = db.session.execute("select * from db_students").fetchone()
#
# # 添加/删除/更新
# db.session.execute("UPDATE db_students SET money=(db_students.money + %s) WHERE db_students.age = %s" % (200, 22))
# db.session.commit()
# db.session.execute("insert db_students (name,age,sex,email,money) select name,age,sex,concat(now(),email),money from db_students")
# db.session.commit()
return "ok"
3.2 关联查询
3.2.1 常用的SQLAlchemy关系选项
| 选项名 | 说明 |
|---|---|
| backref | 在关系的另一模型中添加反向引用,用于设置外键名称,在1查多的 |
| primary join | 明确指定两个模型之间使用的连表条件 |
| lazy | 指定如何加载关联模型数据的方式。参数值: select（立即加载，查询所有相关数据显示，相当于lazy=True） subquery（立即加载，但使用子查询） dynamic（不加载记录，但提供加载记录的查询对象） |
| uselist | 如果为False，不使用列表，而使用标量值。 一对一关系中，需要设置relationship中的uselist=Flase，其他数据库操作一样。 |
| secondary | 指定多对多关系中关系表的名字。 多对多关系中，需建立关系表，设置 secondary=关系表 |
| secondary join | 在SQLAlchemy中无法自行决定时，指定多对多关系中的二级连表条件 |
三范式：逆范式
第三范式：数据不能冗余，把关联性不强的数据可以移除到另一个表中。使用外键进行管理。
1对1：把主表的主键放到附加表中作为外键存在。
1对多：把主表(1) 的主键放到附加表(多)作为外键存在。
多对多：把主表(多)的主键和附加表的(多)主键，放到第三方表(关系表)中作为外键。
3.2.2 模型之间的关联
一对一
class Student(db.Model):
"""个人信息主表"""
....
# 关联属性，这个不会被视作表字段，只是模型的属性。
# 因为StudentInfo和Student是一对一的关系，所以uselist=False表示关联一个数据
info = db.relationship("StudentInfo",uselist=False,backref="own")
class StudentInfo(db.Model):
"""个人信息附加表"""
# 外键，
# 如果是一对一，则外键放在附加表对应的模型中
# 如果是一对多，则外键放在多的表对象的模型中
uid = db.Column(db.Integer, db.ForeignKey(Student.id),comment="外键")
关联属性声明在主模型中【最常用】
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
# 字段定义
"""
create table db_student(
id int primary key auto_increment comment="主键",
name varchar(15) comment="姓名",
)
"""
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
# 关联属性不是数据库的字段不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询提供的对象属性
# info 可以代表与当前数据对应的外键模型对象
# 在主模型中声明关联属性
info = db.relationship("StudentInfo", uselist=False,backref="student")
def __repr__(self):
return f"{self.name}<Student>"
@classmethod
def add(cls):
student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
db.session.add(student)
db.session.commit()
return student
@property
def to_dict(self):
"""把对象转化成字典"""
return {
"id": self.id,
"name": self.name,
"age": self.age,
"sex": self.sex,
"email": self.email,
"money": float("%.2f" % self.money),
}
class StudentInfo(db.Model):
"""学生信息附加表"""
__tablename__ = "db_student_info"
id = db.Column(db.Integer, primary_key=True, comment="主键")
address = db.Column(db.String(500), nullable=True, comment="地址")
qq_num = db.Column(db.String(15), nullable=True, comment="QQ号")
# 外键设置[默认创建数据库物理外键]
user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
@app.route("/")
def index():
"""添加数据"""
# 添加主表信息的时候通过关联属性db.relationship同步添加附件表信息
# student = Student(
# name="xiaolan02",
# age=16,
# sex=False,
# money=10000,
# email="xiaolan02@qq.com",
# info = StudentInfo(address="北京市昌平区百沙路204号", qq_num="100861000")
# )
# db.session.add(student)
# db.session.commit()
# 添加附加表数据，通过关联属性中db.relationshop的backref同步添加主表数据
# info = StudentInfo(
# address="北京市昌平区百沙路204号",
# qq_num="100861220",
# student = Student(
# name="xiaolan02",
# age=16,
# sex=False,
# money=10000,
# email="xiaolan02@qq.com",
# )
# )
#
# db.session.add(info)
# db.session.commit()
"""查询数据"""
# 正向关联----> 从主模型查询外键模型
# student = Student.query.get(1)
# print(student.info) # <StudentInfo 1>
# print(student.info.address) # 北京市昌平区百沙路204号
#
# # 反向关联----> 从外键模型查询主模型
# student_info = StudentInfo.query.filter(StudentInfo.qq_num=="100861220").first()
# print(student_info.student) # xiaolan02<Student>
# print(student_info.student.name) # xiaolan02
# print(student_info.user_id) # 2 仅仅获取了外键真实数据
"""修改数据"""
# 通过主表使用关联属性可以修改附加表的数据
student = Student.query.get(2)
student.info.address = "广州市天河区天河东路103号"
db.session.commit()
# 也可以通过附加表模型直接修改主表的数据
student_info = StudentInfo.query.filter(StudentInfo.qq_num == "100861220").first()
print(student_info.student)
student_info.student.age = 22
db.session.commit()
return "ok"
if __name__ == '__main__':
with app.app_context():
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
在外键模型中声明关联属性
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
# 字段定义
"""
create table db_student(
id int primary key auto_increment comment="主键",
name varchar(15) comment="姓名",
)
"""
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
@classmethod
def add(cls):
student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
db.session.add(student)
db.session.commit()
return student
@property
def to_dict(self):
"""把对象转化成字典"""
return {
"id": self.id,
"name": self.name,
"age": self.age,
"sex": self.sex,
"email": self.email,
"money": float("%.2f" % self.money),
}
from sqlalchemy.orm import backref
class StudentInfo(db.Model):
"""学生信息附加表"""
__tablename__ = "db_student_info"
id = db.Column(db.Integer, primary_key=True, comment="主键")
address = db.Column(db.String(500), nullable=True, comment="地址")
qq_num = db.Column(db.String(15), nullable=True, comment="QQ号")
# 外键设置[默认创建数据库物理外键]
user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
# 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
# info 可以代表与当前数据对应的外键模型对象
student = db.relationship("Student", backref=backref("info",uselist=False))
@app.route("/")
def index():
"""查询数据"""
# 正向关联----> 从主模型查询外键模型
student_info = StudentInfo.query.filter(StudentInfo.qq_num=="100861220").first()
print(student_info.student) # xiaolan02<Student>
print(student_info.student.name) # xiaolan02
print(student_info.user_id) # 2 仅仅获取了外键真实数据
# 反向关联----> 从外键模型查询主模型
student = Student.query.get(1)
print(student.info) # <StudentInfo 1>
print(student.info.address) # 北京市昌平区百沙路204号
"""修改数据"""
# 通过主表使用关联属性可以修改附加表的数据
# student = Student.query.get(2)
# student.info.address = "广州市天河区天河东路103号"
# db.session.commit()
# 也可以通过附加表模型直接修改主表的数据
# student_info = StudentInfo.query.filter(StudentInfo.qq_num == "100861220").first()
# print(student_info.student)
# student_info.student.age = 22
# db.session.commit()
return "ok"
if __name__ == '__main__':
with app.app_context():
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
一对多
class Teacher(db.Model):
...
# 关联属性，一的一方添加模型关联属性
course = db.relationship("Course", uselist=True, backref="teacher",lazy='dynamic')
class Course(db.Model):
...
# 外键，多的一方模型中添加外间
teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id))
- 其中realtionship描述了Course和Teacher的关系。第一个参数为对应参照的类"Course"
- 第二个参数backref为类Teacher申明新属性的方法
- 第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
- lazy='subquery'，查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了。
- lazy=True或lazy='select'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询数据[执行SQL]
- lazy='dynamic'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性并操作外键模型具体属性时，才进行连表查询数据[执行SQL]
- 常用的lazy选项：dynamic和select
代码：
models2.py，代码：
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
# 等同于
# db = SQLAlchemy()
# db.init_app(app)
class Student(db.Model):
"""学生信息模型"""
# 声明与当前模型绑定的数据表名称
__tablename__ = "db_students"
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
# 从Student 查询 StudentAddress: Student.address_list = []
# 从StudentAddress 查询 Student: StudentAddress.student = 学生模型对象
address_list = db.relationship("StudentAddress", uselist=True, backref="student",lazy="dynamic")
def __repr__(self):
return f"{self.name}<Student>"
from sqlalchemy.orm import backref
class StudentInfo(db.Model):
"""学生信息附加表"""
__tablename__ = "db_student_info"
id = db.Column(db.Integer, primary_key=True, comment="主键")
address = db.Column(db.String(500), nullable=True, comment="默认地址")
qq_num = db.Column(db.String(15), nullable=True, comment="QQ号")
# 外键设置[默认创建数据库物理外键]
user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
# 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
# info 可以代表与当前数据对应的外键模型对象
student = db.relationship("Student", backref=backref("info",uselist=False))
class StudentAddress(db.Model):
"""学生收货地址"""
__tablename__ = "db_student_address"
id = db.Column(db.Integer, primary_key=True, comment="主键")
user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
province = db.Column(db.String(20), comment="省份")
city = db.Column(db.String(20), comment="城市")
area = db.Column(db.String(20), comment="地区")
address = db.Column(db.String(250), comment="详细接地")
# 也可以在外键模型中声明关系熟悉
# student = db.relationship("Student", uselist=False, backref=backref("address_list",uselist=True))
def __repr__(self):
return f"{self.province}-{self.city}-{self.area}-{self.address}"
@app.route("/")
def index():
"""查询数据"""
# 正向关联----> 从主模型查询外键模型
student = Student.query.filter(Student.name=="xiaobai").first()
print(student)
# # 获取地址列表[调用关联属性，直接把外建模型对应数据直接查询查来]
address_list = student.address_list[0].address
return "ok"
"""
lazy="subquery"
1. 只查询：student，但是同时使用子查询语句进行链表操作，把外键模型数据也查询出来了
SELECT db_students.id AS db_students_id, db_students.name AS db_students_name, db_students.age AS db_students_age, db_students.sex AS db_students_sex, db_students.email AS db_students_email, db_students.money AS db_students_money
FROM db_students
SELECT db_student_address.id AS db_student_address_id, db_student_address.user_id AS db_student_address_user_id, db_student_address.province AS db_student_address_province, db_student_address.city AS db_student_address_city, db_student_address.area AS db_student_address_area, db_student_address.address AS db_student_address_address, anon_1.db_students_id AS anon_1_db_students_id
FROM (SELECT db_students.id AS db_students_id FROM db_students WHERE db_students.name = %s LIMIT %s) AS anon_1
INNER JOIN db_student_address ON anon_1.db_students_id = db_student_address.user_id
lazy="select"，
1. 只查询：student
SELECT db_students.id AS db_students_id, db_students.name AS db_students_name, db_students.age AS db_students_age, db_students.sex AS db_students_sex, db_students.email AS db_students_email, db_students.money AS db_students_money
FROM db_students WHERE db_students.name = %s LIMIT %s
2. 调用关联属性，直接连表查询
SELECT db_students.id AS db_students_id, db_students.name AS db_students_name, db_students.age AS db_students_age, db_students.sex AS db_students_sex, db_students.email AS db_students_email, db_students.money AS db_students_money
FROM db_students WHERE db_students.name = %s LIMIT %s
SELECT db_student_address.id AS db_student_address_id, db_student_address.user_id AS db_student_address_user_id, db_student_address.province AS db_student_address_province, db_student_address.city AS db_student_address_city, db_student_address.area AS db_student_address_area, db_student_address.address AS db_student_address_address
FROM db_student_address WHERE %s = db_student_address.user_id
lazy="dynamic"
1. 只查询：student
SELECT db_students.id AS db_students_id, db_students.name AS db_students_name, db_students.age AS db_students_age, db_students.sex AS db_students_sex, db_students.email AS db_students_email, db_students.money AS db_students_money
FROM db_students WHERE db_students.name = %s LIMIT %s
2. 调用关联属性，不连表，直到代码真的调用了外键模型的具体字段才会查询数据库
SELECT db_students.id AS db_students_id, db_students.name AS db_students_name, db_students.age AS db_students_age, db_students.sex AS db_students_sex, db_students.email AS db_students_email, db_students.money AS db_students_money
FROM db_students WHERE db_students.name = %s LIMIT %s
"""
if __name__ == '__main__':
with app.app_context():
# 检测数据库中是否存在和模型匹配的数据表。
# 如果没有，则根据模型转换的建表语句进行建表。
# 如果找到，则不会进行额外处理
db.create_all()
app.run(debug=True)
多对多
achievement = db.Table('tb_achievement',
db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')),
db.Column('course_id', db.Integer, db.ForeignKey('tb_course.id')),
)
class Course(db.Model):
...
student_list = db.relationship('Student',secondary=achievement,
backref='course_list',
lazy='dynamic')
class Student(db.Model):
...
多对多，也可以拆解成3个模型，其中tb_achievement作为单独模型存在。
基于第三方关系表构建多对多
代码：
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
# 多对多关系表
achievement = db.Table(
'db_achievement',
db.Column('id', db.Integer, primary_key=True),
db.Column('student_id', db.Integer, db.ForeignKey('db_students.id')),
db.Column('course_id', db.Integer, db.ForeignKey('db_course.id')),
)
class Student(db.Model):
"""学生信息模型"""
__tablename__ = "db_students"
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
course_list = db.relationship("Course",secondary=achievement, backref="student_list", lazy="dynamic")
def __repr__(self):
return f"{self.name}<Student>"
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
def __repr__(self):
return f'{self.name}<Course>'
@app.route("/")
def index():
"""添加数据"""
# student = Student(
# name="xiaozhao",
# age=13,
# sex=False,
# money=30000,
# email="100000@qq.com",
# course_list=[
# Course(name="python入门", price=99.99),
# Course(name="python初级", price=199.99),
# Course(name="python进阶", price=299.99),
# ]
# )
# db.session.add(student)
# db.session.commit()
# student = Student.query.get(4)
# # student.course_list = [Course.query.get(2)] #错误写法！！！ 如果数据中已经存在了课程列表了，则不要重新赋值，会变成删除操作的
# student.course_list.append(Course.query.get(3))
# db.session.commit()
"""查询操作"""
# student = Student.query.get(4)
# course_list = [{"name":item.name,"price":float(item.price)} for item in student.course_list]
# 查询出2号课程，都有谁在读？
# course = Course.query.get(2)
# student_list = [{"name":item.name,"money":float(item.money)} for item in course.student_list]
# print(student_list)
"""更新数据"""
# 给报读了3号课程的同学，返现红包200块钱
course = Course.query.get(3)
for student in course.student_list:
student.money+=200
db.session.commit()
return "ok"
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run(debug=True)
基于关系模型构建多对多
在SQLAlchemy中，基于db.Table创建的关系表，如果需要新增除了外键以外其他字段，无法操作。所以将来实现多对多的时候，除了上面db.Table方案以外，还可以把关系表声明成模型的方法，如果声明成模型，则原来课程和学生之间的多对多的关系，就会变成远程的1对多了。
代码：
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
class Student(db.Model):
"""学生信息模型"""
__tablename__ = "db_students"
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
achievement_list = db.relationship("Achievement", uselist=True, backref="student")
def __repr__(self):
return f"{self.name}<Student>"
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
achievement_list = db.relationship("Achievement", uselist=True, backref="course")
def __repr__(self):
return f'{self.name}<Course>'
from datetime import datetime
class Achievement(db.Model):
__tablename__ = "db_achievement"
id = db.Column(db.Integer, primary_key=True, comment="主键")
student_id = db.Column(db.Integer, db.ForeignKey(Student.id))
course_id = db.Column(db.Integer, db.ForeignKey(Course.id))
score = db.Column(db.Numeric(4,1), default=0, comment="成绩")
time = db.Column(db.DateTime, default=datetime.now, comment="考试时间")
@app.route("/")
def index():
"""添加数据"""
# 记录xiaobai本次的python入门考试成绩: 88
# achievement = Achievement(
# student= Student.query.filter(Student.name=="xiaobai").first(),
# course = Course.query.filter(Course.name=="python初级").first(),
# score = 81
# )
# db.session.add(achievement)
# db.session.commit()
"""查询操作"""
# 查询xiaobai的成绩
student = Student.query.filter(Student.name=="xiaobai").first()
for achievement in student.achievement_list:
print(f"课程：{achievement.course.name}，成绩：{achievement.score}")
return "ok"
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run(debug=True)
relationship还有一个设置外键级联的属性：cascade="all, delete, delete-orphan"
3.2.3 逻辑外键
模型.query.join("模型类","主模型.主键==外键模型.外键").with_entities("字段1","字段2".label("字段别名")).all()
代码:
from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
"""模型类定义"""
db = SQLAlchemy(app=app)
class Student(db.Model):
"""学生信息模型"""
__tablename__ = "db_students"
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
class Course(db.Model):
"""课程数据模型"""
__tablename__ = "db_course"
id = db.Column(db.Integer, primary_key=True, comment="主键")
name = db.Column(db.String(64), unique=True, comment="课程")
price = db.Column(db.Numeric(7, 2))
def __repr__(self):
return f'{self.name}<Course>'
from datetime import datetime
class Achievement(db.Model):
__tablename__ = "db_achievement"
id = db.Column(db.Integer, primary_key=True, comment="主键")
student_id = db.Column(db.Integer, comment="学生ID")
course_id = db.Column(db.Integer, comment="课程ID")
score = db.Column(db.Numeric(4,1), default=0, comment="成绩")
time = db.Column(db.DateTime, default=datetime.now, comment="考试时间")
@app.route("/")
def index():
"""添加数据"""
# 记录xiaobai本次的python入门考试成绩: 88
# achievement = Achievement(
# student_id = Student.query.filter(Student.name=="xiaobai").first().id,
# course_id = Course.query.filter(Course.name=="python入门").first().id,
# score = 80,
# time = datetime.now(),
# )
# db.session.add(achievement)
# db.session.commit()
"""查询操作"""
# 查询xiaobai的成绩
student = Student.query.join(
Achievement,
Achievement.student_id == Student.id
).join(
Course,
Course.id == Achievement.course_id
).filter(
Student.name=="xiaobai",
Achievement.score==88,
).with_entities(
Student.name,
Course.name.label("course"),
Achievement.score.label("number")
).first()
# 当连表查询返回一个结果的时候，可以通过keys查看当前模型提供的字段
print(student) # 字段列表名
print(type(student)) # 返回值是一个SQLAlchemy内部封装的模型对象
print(student.keys()) # 字段列表
print(f"{student.name}的{student.course}成绩是：{student.number}")
return "ok"
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run(debug=True)
注意：django中也有虚拟外键的设置方案：在模型中设置db_constraint = False
4. 数据库迁移
- 在开发过程中，需要修改数据库模型，而且还要在修改之后更新数据库。最直接的方式就是删除旧表，但这样会丢失数据。
- 更好的解决办法是使用数据库迁移框架，它可以追踪数据库模式的变化，然后把变动应用到数据库中。
- 在Flask中可以使用Flask-Migrate扩展，来实现数据迁移。并且集成到Flask-Script中，所有操作通过命令就能完成。
- 为了导出数据库迁移命令，Flask-Migrate提供了一个MigrateCommand类，可以附加到flask-script的manager对象上。
首先要在虚拟环境中安装Flask-Migrate。
pip install flask-migrate
代码
from flask import Flask
from config import Config
from flask_migrate import Migrate
app = Flask(__name__,template_folder='templates')
app.config.from_object(Config)
"""模型的创建"""
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
#第一个参数是Flask的实例，第二个参数是Sqlalchemy数据库实例
migrate = Migrate(app,db)
# 多对多的关系
# 关系表的声明方式
achieve = db.Table('tb_achievement',
db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')),
db.Column('course_id', db.Integer, db.ForeignKey('tb_course.id'))
)
class Course(db.Model):
# 定义表名
__tablename__ = 'tb_course'
# 定义字段对象
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
price = db.Column(db.Numeric(6,2))
teacher_id = db.Column(db.Integer, db.ForeignKey('tb_teacher.id'))
students = db.relationship('Student', secondary=achieve, backref='courses', lazy='subquery')
# repr()方法类似于django的__str__，用于打印模型对象时显示的字符串信息
def __repr__(self):
return 'Course:%s'% self.name
class Student(db.Model):
__tablename__ = 'tb_student'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
email = db.Column(db.String(64),unique=True)
age = db.Column(db.SmallInteger,nullable=False)
sex = db.Column(db.Boolean,default=1)
def __repr__(self):
return 'Student:%s' % self.name
class Teacher(db.Model):
__tablename__ = 'tb_teacher'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
# 课程与老师之间的多对一关联
courses = db.relationship('Course', backref='teacher', lazy='subquery')
def __repr__(self):
return 'Teacher:%s' % self.name
@app.route("/")
def index():
return "ok"
if __name__ == '__main__':
manage.run()
4.1 创建迁移版本仓库
# 切换到项目根目录下
cd ~/Desktop/flaskdemo
# 设置flask项目的启动脚本位置
# 新版本的flask1.1，会在系统终端下提供一个类似django-admin的终端命令工具, 可以实现类似 flask_scipt 的功能
export FLASK_APP=manage.py
# flask run 启动flask项目
# 数据库迁移初始化，这个命令会在当前项目根目录下创建migrations文件夹，所有数据表相关的迁移文件都放在里面。
flask db init
4.2 创建迁移版本
- 自动创建迁移版本有两个函数
- upgrade()：函数把迁移中的改动应用到数据库中。
- downgrade()：函数则将改动删除。
- 自动创建的迁移脚本会根据模型定义和数据库当前状态的差异，生成upgrade()和downgrade()函数的内容。
- 对比不一定完全正确，有可能会遗漏一些细节，需要进行检查
# 根据flask项目的模型生成迁移文件
flask db migrate -m 'initial migration'
# 这里等同于django里面的 makemigrations，生成迁移版本文件
# 完成2件事情：
# 1. 在migrations/versions生成一个数据库迁移文件
# 2. 如果是首次生成迁移文件的项目，则迁移工具还会在数据库创建一个记录数据库版本的version表
4.3 升级版本库的版本
# 从migations目录下的versions中根据迁移文件upgrade方法把数据表的结构同步到数据库中。
flask db upgrade
4.4 降级版本库的版本
# 从migations目录下的versions中根据迁移文件downgrade把数据表的结构同步到数据库中。
python main.py db downgrade
4.5 版本库的历史管理
可以根据history命令找到版本号,然后传给downgrade命令:
python manage.py db history
输出格式：<base> -> 版本号 (head), initial migration
回滚到指定版本
flask db downgrade # 默认返回上一个版本
flask db downgrade 版本号 # 回滚到指定版本号对应的版本
flask db upgrade 版本号 # 升级到指定版本号对应的版本
数据迁移的步骤：
1. 初始化数据迁移的目录
export FLASK_APP=manage.py
flask db init
2. 数据库的数据迁移版本初始化
flask db migrate -m 'initial migration'
3. 升级版本[创建表/创建字段/修改字段]
flask db upgrade
4. 降级版本[删除表/删除字段/恢复字段]
flask db downgrade
4.6 模块推荐
文档: https://faker.readthedocs.io/en/master/locales/zh_CN.html
批量生成测试数据: https://github.com/joke2k/faker
pip install faker -i https://pypi.douban.com/simple
代码：
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
class Config(object):
DEBUG = True
# 数据库连接配置
# SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
db = SQLAlchemy(app=app)
class Student(db.Model):
"""学生信息模型"""
__tablename__ = "db_students"
id = db.Column(db.Integer, primary_key=True,comment="主键")
name = db.Column(db.String(15), comment="姓名")
age = db.Column(db.SmallInteger, comment="年龄")
sex = db.Column(db.Boolean, default=True, comment="性别")
email = db.Column(db.String(128), comment="邮箱地址")
money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
def __repr__(self):
return f"{self.name}<Student>"
from flask_script import Command,Manager,Option
manage = Manager()
manage.app = app
# 自定义批量生成学生
import random
from faker import Faker
class FakerStudentCommand(Command):
"""生成测试学生信息"""
Name = "student"
option_list = (
Option("-n","--num",dest="num", type=int),
)
def run(self,num):
faker = Faker(locale="ZH_CN")
for i in range(num):
sex = bool( random.randint(0,1) )
student = Student(
name= faker.name_male() if sex else faker.name_female(),
age=random.randint(15,60),
sex=sex,
email=faker.free_email(),
money= float( random.randint(100,100000) / 100 ),
)
db.session.add(student)
# 在循环外面统一提交
db.session.commit()
manage.add_command(FakerStudentCommand.Name, FakerStudentCommand)
@app.route("/")
def index():
return "ok"
if __name__ == '__main__':
manage.run()
