9-flask框架-关联查询

一、常用的SQLAlchemy关系选项

选项名	说明
backref	在关系的另一模型中添加反向引用,用于设置外键名称,在1查多的
primary join	明确指定两个模型之间使用的连表条件
lazy	指定如何加载关联模型数据的方式。参数值: select（立即加载，查询所有相关数据显示，相当于lazy=True） subquery（立即加载，但使用子查询） dynamic（不加载记录，但提供加载记录的查询对象）
uselist	如果为False，不使用列表，而使用标量值。一对一关系中，需要设置relationship中的uselist=Flase，其他数据库操作一样。
secondary	指定多对多关系中关系表的名字。多对多关系中，需建立关系表，设置 secondary=关系表
secondary join	在SQLAlchemy中无法自行决定时，指定多对多关系中的二级连表条件

1.1 三范式：逆范式

第一范式: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF

第二范式: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现

第三范式：数据不能冗余，把关联性不强的数据可以移除到另一个表中。使用外键进行管理。

1对1：把主表的主键放到附加表中作为外键存在。
1对多：把主表(1) 的主键放到附加表(多)作为外键存在。
多对多：把主表(多)的主键和附加表的(多)主键，放到第三方表(关系表)中作为外键。

二、模型之间的关联(一对一)

2.1 模型关联用法

关联属性定义在主模型中

class Student(db.Model):
    """个人信息主表"""
    ....
    # 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
    # 因为StudentInfo和Student是一对一的关系，所以uselist=False表示关联一个数据
    info = db.relationship("StudentInfo", uselist=False,backref="student")


class StudentInfo(db.Model):
    """个人信息附加表"""

    # 外键，
    # 如果是一对一，则外键放在附加表对应的模型中
    # 如果是一对多，则外键放在多的表对象的模型中    
    user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")

关联属性定义在模型中外键模型中

class Student(db.Model):
    """个人信息主表"""
    ....
  
    
from sqlalchemy.orm import backref
class StudentInfo(db.Model):
    """个人信息附加表"""

    # 外键，
    # 如果是一对一，则外键放在附加表对应的模型中
    # 如果是一对多，则外键放在多的表对象的模型中    
    user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
    # 因为StudentInfo和Student是一对一的关系，所以uselist=False表示关联一个数据
    student = db.relationship("Student", backref=backref("info",uselist=False))

2.2 连表后的增改查

关联属性定义在主模型中

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 # 等同于
 22 # db = SQLAlchemy()
 23 # db.init_app(app)
 24 
 25 class Student(db.Model):
 26     """学生信息模型"""
 27     # 声明与当前模型绑定的数据表名称
 28     __tablename__ = "db_students"
 29     # 字段定义
 30     """
 31     create table db_student(
 32       id int primary key auto_increment comment="主键",
 33       name varchar(15) comment="姓名",
 34     )
 35     """
 36     id = db.Column(db.Integer, primary_key=True,comment="主键")
 37     name = db.Column(db.String(15), comment="姓名")
 38     age = db.Column(db.SmallInteger, comment="年龄")
 39     sex = db.Column(db.Boolean, default=True, comment="性别")
 40     email = db.Column(db.String(128), comment="邮箱地址")
 41     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 42     # 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
 43     # info 可以代表与当前数据对应的外键模型对象
 44     info = db.relationship("StudentInfo", uselist=False,backref="student")
 45 
 46     def __repr__(self):
 47         return f"{self.name}<Student>"
 48 
 49     @classmethod
 50     def add(cls):
 51         student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
 52         db.session.add(student)
 53         db.session.commit()
 54         return student
 55 
 56     @property
 57     def to_dict(self):
 58         """把对象转化成字典"""
 59         return {
 60             "id": self.id,
 61             "name": self.name,
 62             "age": self.age,
 63             "sex": self.sex,
 64             "email": self.email,
 65             "money": float("%.2f" % self.money),
 66         }
 67 
 68 class StudentInfo(db.Model):
 69     """学生信息附加表"""
 70     __tablename__ = "db_student_info"
 71     id = db.Column(db.Integer, primary_key=True, comment="主键")
 72     address = db.Column(db.String(500), nullable=True, comment="地址")
 73     qq_num  = db.Column(db.String(15), nullable=True, comment="QQ号")
 74     # 外键设置[默认创建数据库物理外键]
 75     user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
 76 
 77 @app.route("/")
 78 def index():
 79     """添加数据"""
 80     # 添加主表信息的时候通过关联属性db.relationship同步添加附件表信息
 81     # student = Student(
 82     #     name="xiaolan02",
 83     #     age=16,
 84     #     sex=False,
 85     #     money=10000,
 86     #     email="xiaolan02@qq.com",
 87     #     info = StudentInfo(address="北京市昌平区百沙路204号", qq_num="100861000")
 88     # )
 89     # db.session.add(student)
 90     # db.session.commit()
 91 
 92     # 添加附加表数据，通过关联属性中db.relationshop的backref同步添加主表数据
 93     # info = StudentInfo(
 94     #     address="北京市昌平区百沙路204号",
 95     #     qq_num="100861220",
 96     #     student = Student(
 97     #         name="xiaolan02",
 98     #         age=16,
 99     #         sex=False,
100     #         money=10000,
101     #         email="xiaolan02@qq.com",
102     #     )
103     # )
104     #
105     # db.session.add(info)
106     # db.session.commit()
107 
108     """查询数据"""
109     # 正向关联----> 从主模型查询外键模型
110     # student = Student.query.get(1)
111     # print(student.info) # <StudentInfo 1>
112     # print(student.info.address) # 北京市昌平区百沙路204号
113     #
114     # # 反向关联----> 从外键模型查询主模型
115     # student_info = StudentInfo.query.filter(StudentInfo.qq_num=="100861220").first()
116     # print(student_info.student) #    xiaolan02<Student>
117     # print(student_info.student.name) #  xiaolan02
118     # print(student_info.user_id) # 2     仅仅获取了外键真实数据
119 
120     """修改数据"""
121     # 通过主表使用关联属性可以修改附加表的数据
122     student = Student.query.get(2)
123     student.info.address = "广州市天河区天河东路103号"
124     db.session.commit()
125 
126     # 也可以通过附加表模型直接修改主表的数据
127     student_info = StudentInfo.query.filter(StudentInfo.qq_num == "100861220").first()
128     print(student_info.student)
129     student_info.student.age = 22
130     db.session.commit()
131 
132     return "ok"
133 
134 if __name__ == '__main__':
135     with app.app_context():
136         # 检测数据库中是否存在和模型匹配的数据表。
137         # 如果没有，则根据模型转换的建表语句进行建表。
138         # 如果找到，则不会进行额外处理
139 
140         db.create_all()
141     app.run(debug=True)

View Code

关联属性定义在模型中外键模型中

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 # 等同于
 22 # db = SQLAlchemy()
 23 # db.init_app(app)
 24 
 25 class Student(db.Model):
 26     """学生信息模型"""
 27     # 声明与当前模型绑定的数据表名称
 28     __tablename__ = "db_students"
 29     # 字段定义
 30     """
 31     create table db_student(
 32       id int primary key auto_increment comment="主键",
 33       name varchar(15) comment="姓名",
 34     )
 35     """
 36     id = db.Column(db.Integer, primary_key=True,comment="主键")
 37     name = db.Column(db.String(15), comment="姓名")
 38     age = db.Column(db.SmallInteger, comment="年龄")
 39     sex = db.Column(db.Boolean, default=True, comment="性别")
 40     email = db.Column(db.String(128), comment="邮箱地址")
 41     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 42 
 43     def __repr__(self):
 44         return f"{self.name}<Student>"
 45 
 46     @classmethod
 47     def add(cls):
 48         student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
 49         db.session.add(student)
 50         db.session.commit()
 51         return student
 52 
 53     @property
 54     def to_dict(self):
 55         """把对象转化成字典"""
 56         return {
 57             "id": self.id,
 58             "name": self.name,
 59             "age": self.age,
 60             "sex": self.sex,
 61             "email": self.email,
 62             "money": float("%.2f" % self.money),
 63         }
 64 
 65 from sqlalchemy.orm import backref
 66 class StudentInfo(db.Model):
 67     """学生信息附加表"""
 68     __tablename__ = "db_student_info"
 69     id = db.Column(db.Integer, primary_key=True, comment="主键")
 70     address = db.Column(db.String(500), nullable=True, comment="地址")
 71     qq_num  = db.Column(db.String(15), nullable=True, comment="QQ号")
 72     # 外键设置[默认创建数据库物理外键]
 73     user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
 74     # 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
 75     # info 可以代表与当前数据对应的外键模型对象
 76     student = db.relationship("Student", backref=backref("info",uselist=False))
 77 
 78 @app.route("/")
 79 def index():
 80     """查询数据"""
 81     # 正向关联----> 从主模型查询外键模型
 82     student_info = StudentInfo.query.filter(StudentInfo.qq_num=="100861220").first()
 83     print(student_info.student) #    xiaolan02<Student>
 84     print(student_info.student.name) #  xiaolan02
 85     print(student_info.user_id) # 2     仅仅获取了外键真实数据
 86 
 87 
 88     # 反向关联----> 从外键模型查询主模型
 89     student = Student.query.get(1)
 90     print(student.info) # <StudentInfo 1>
 91     print(student.info.address) # 北京市昌平区百沙路204号
 92 
 93     """修改数据"""
 94     # 通过主表使用关联属性可以修改附加表的数据
 95     # student = Student.query.get(2)
 96     # student.info.address = "广州市天河区天河东路103号"
 97     # db.session.commit()
 98 
 99     # 也可以通过附加表模型直接修改主表的数据
100     # student_info = StudentInfo.query.filter(StudentInfo.qq_num == "100861220").first()
101     # print(student_info.student)
102     # student_info.student.age = 22
103     # db.session.commit()
104 
105     return "ok"
106 
107 if __name__ == '__main__':
108     with app.app_context():
109         # 检测数据库中是否存在和模型匹配的数据表。
110         # 如果没有，则根据模型转换的建表语句进行建表。
111         # 如果找到，则不会进行额外处理
112 
113         db.create_all()
114     app.run(debug=True)

View Code

三、模型之间的关联(一对多)

3.1 模型关联用法

关联属性定义在主模型中

class Student(db.Model):
    ...
    # 从Student        查询  StudentAddress:  Student.address_list = []
    # 从StudentAddress 查询  Student:         StudentAddress.student = 学生模型对象
    address_list = db.relationship("StudentAddress", uselist=True, backref="student")

class StudentAddress(db.Model):
    ...
    # 外键，多的一方模型中添加外间
    user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")

关联属性定义在模型中外键模型中

from sqlalchemy.orm import backref
class Student(db.Model):
    ...

class StudentAddress(db.Model):
    ...
    # 外键，多的一方模型中添加外间
    user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
    student = db.relationship("Student", uselist=False, backref=backref("address_list",uselist=True))

3.2 连表后的增改查

关联属性定义在主模型中

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 # 等同于
 22 # db = SQLAlchemy()
 23 # db.init_app(app)
 24 
 25 class Student(db.Model):
 26     """学生信息模型"""
 27     # 声明与当前模型绑定的数据表名称
 28     __tablename__ = "db_students"
 29     id = db.Column(db.Integer, primary_key=True,comment="主键")
 30     name = db.Column(db.String(15), comment="姓名")
 31     age = db.Column(db.SmallInteger, comment="年龄")
 32     sex = db.Column(db.Boolean, default=True, comment="性别")
 33     email = db.Column(db.String(128), comment="邮箱地址")
 34     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 35     # 从Student        查询  StudentAddress:  Student.address_list = []
 36     # 从StudentAddress 查询  Student:         StudentAddress.student = 学生模型对象
 37     address_list = db.relationship("StudentAddress", uselist=True, backref="student")
 38 
 39     def __repr__(self):
 40         return f"{self.name}<Student>"
 41 
 42 from sqlalchemy.orm import backref
 43 class StudentInfo(db.Model):
 44     """学生信息附加表"""
 45     __tablename__ = "db_student_info"
 46     id = db.Column(db.Integer, primary_key=True, comment="主键")
 47     address = db.Column(db.String(500), nullable=True, comment="默认地址")
 48     qq_num  = db.Column(db.String(15), nullable=True, comment="QQ号")
 49     # 外键设置[默认创建数据库物理外键]
 50     user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
 51     # 模型属性，不是数据库的字段，不会在数据表中出现，仅仅是SQLAlchemy为了方便开发者使用关联查询所提供的对象属性
 52     # info 可以代表与当前数据对应的外键模型对象
 53     student = db.relationship("Student", backref=backref("info",uselist=False))
 54 
 55 class StudentAddress(db.Model):
 56     """学生收货地址"""
 57     __tablename__ = "db_student_address"
 58     id = db.Column(db.Integer, primary_key=True, comment="主键")
 59     user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
 60     province = db.Column(db.String(20), comment="省份")
 61     city = db.Column(db.String(20), comment="城市")
 62     area = db.Column(db.String(20), comment="地区")
 63     address = db.Column(db.String(250), comment="详细接地")
 64     # 也可以在外键模型中声明关系熟悉
 65     # student = db.relationship("Student", uselist=False, backref=backref("address_list",uselist=True))
 66 
 67     def __repr__(self):
 68         return f"{self.province}-{self.city}-{self.area}-{self.address}"
 69 
 70 @app.route("/")
 71 def index():
 72     """添加数据"""
 73     # 添加主表信息的时候通过关联属性db.relationship同步添加附件表信息
 74     student = Student(
 75         name="xiaohei",
 76         age=18,
 77         sex=False,
 78         money=12000,
 79         email="xiaohei@qq.com",
 80         address_list = [
 81             StudentAddress(province="北京市", city="北京市", area="昌平区", address="百沙路204号"),
 82             StudentAddress(province="北京市", city="北京市", area="昌平区", address="百沙路205号"),
 83         ],
 84     )
 85     db.session.add(student)
 86     db.session.commit()
 87 
 88     # 添加附加表数据，通过关联属性中db.relationshop的backref同步添加主表数据
 89     # 1. 主模型没创建的情况
 90     stu_address = StudentAddress(
 91         province="北京市",
 92         city="北京市",
 93         area="昌平区",
 94         address="百沙路206号",
 95         student=Student(name="xiaobai",age=18,sex=False,money=12000,email="xiaobai@qq.com",)
 96     )
 97     db.session.add(stu_address)
 98     db.session.commit()
 99 
100     # 2.1 主模型已创建的情况[直接对物理外键的字段进行赋值]
101     stu_address = StudentAddress(
102         province="北京市",
103         city="北京市",
104         area="昌平区",
105         address="百沙路207号",
106         user_id=4,  # 如果主模型已经创建，则直接设置物理外键的值即可
107     )
108     db.session.add(stu_address)
109     db.session.commit()
110 
111     #2.2 主模型已创建的情况[使用查询出来的模型对象，通过关联属性进行赋值]
112     stu_address = StudentAddress(
113         province="北京市",
114         city="北京市",
115         area="昌平区",
116         address="百沙路208号",
117         student= Student.query.get(4),  # 如果需要通过关联属性设置，则需要先把模型对象查询出来
118     )
119     db.session.add(stu_address)
120     db.session.commit()
121 
122     """查询数据"""
123     # 正向关联----> 从主模型查询外键模型
124     student = Student.query.filter(Student.name=="xiaobai").first()
125     # 获取地址列表
126     print(student.address_list)
127     print(student.address_list[0].address)
128 
129     # 反向关联----> 从外键模型查询主模型
130     sa = StudentAddress.query.get(4)
131     print(sa.student)
132     print(sa.student.name)
133 
134     """修改数据"""
135     # 通过主表使用关联属性可以修改附加表的数据
136     student = Student.query.filter(Student.name == "xiaobai").first()
137     student.address_list[0].address = "南丰路103号"
138     db.session.commit()
139 
140     # 也可以通过附加表模型直接修改主表的数据
141     sa = StudentAddress.query.filter(StudentAddress.address=="南丰路103号").first()
142     # StudentAddress查找Student查找StudentInfo
143     sa.student.info.address = sa.address
144     db.session.commit()
145     return "ok"
146 
147 if __name__ == '__main__':
148     with app.app_context():
149         # 检测数据库中是否存在和模型匹配的数据表。
150         # 如果没有，则根据模型转换的建表语句进行建表。
151         # 如果找到，则不会进行额外处理
152 
153         db.create_all()
154     app.run(debug=True)

View Code

四、基于第三方关系表构建多对多

4.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)

# 多对多关系表
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>'

4.2、基于第三方关系表数据操作

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 
 22 # 多对多关系表
 23 achievement = db.Table(
 24     'db_achievement',
 25     db.Column('id', db.Integer, primary_key=True),
 26     db.Column('student_id', db.Integer, db.ForeignKey('db_students.id')),
 27     db.Column('course_id', db.Integer, db.ForeignKey('db_course.id')),
 28 )
 29 
 30 class Student(db.Model):
 31     """学生信息模型"""
 32     __tablename__ = "db_students"
 33     id = db.Column(db.Integer, primary_key=True,comment="主键")
 34     name = db.Column(db.String(15), comment="姓名")
 35     age = db.Column(db.SmallInteger, comment="年龄")
 36     sex = db.Column(db.Boolean, default=True, comment="性别")
 37     email = db.Column(db.String(128), comment="邮箱地址")
 38     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 39     course_list = db.relationship("Course",secondary=achievement, backref="student_list", lazy="dynamic")
 40 
 41     def __repr__(self):
 42         return f"{self.name}<Student>"
 43 
 44 class Course(db.Model):
 45     """课程数据模型"""
 46     __tablename__ = "db_course"
 47     id = db.Column(db.Integer, primary_key=True, comment="主键")
 48     name = db.Column(db.String(64), unique=True, comment="课程")
 49     price = db.Column(db.Numeric(7, 2))
 50 
 51     def __repr__(self):
 52         return f'{self.name}<Course>'
 53 
 54 @app.route("/")
 55 def index():
 56     """添加数据"""
 57 
 58     # student = Student(
 59     #     name="xiaozhao",
 60     #     age=13,
 61     #     sex=False,
 62     #     money=30000,
 63     #     email="100000@qq.com",
 64     #     course_list=[
 65     #         Course(name="python入门", price=99.99),
 66     #         Course(name="python初级", price=199.99),
 67     #         Course(name="python进阶", price=299.99),
 68     #     ]
 69     # )
 70     # db.session.add(student)
 71     # db.session.commit()
 72 
 73     # student = Student.query.get(4)
 74     # # student.course_list = [Course.query.get(2)]  #错误写法！！！ 如果数据中已经存在了课程列表了，则不要重新赋值，会变成删除操作的
 75     # student.course_list.append(Course.query.get(3))
 76     # db.session.commit()
 77 
 78     """查询操作"""
 79     # student = Student.query.get(4)
 80     # course_list = [{"name":item.name,"price":float(item.price)} for item in student.course_list]
 81 
 82     # 查询出2号课程，都有谁在读？
 83     # course = Course.query.get(2)
 84     # student_list = [{"name":item.name,"money":float(item.money)} for item in course.student_list]
 85     # print(student_list)
 86 
 87 
 88     """更新数据"""
 89     # 给报读了3号课程的同学，返现红包200块钱
 90     course = Course.query.get(3)
 91     for student in course.student_list:
 92         student.money+=200
 93 
 94     db.session.commit()
 95 
 96     return "ok"
 97 
 98 if __name__ == '__main__':
 99     with app.app_context():
100         db.create_all()
101     app.run(debug=True)

View Code

五、基于关系模型构建多对多

在SQLAlchemy中，基于db.Table创建的关系表，如果需要新增除了外键以外其他字段，无法操作。所以将来实现多对多的时候，除了上面db.Table方案以外，还可以把关系表声明成模型的方法，如果声明成模型，则原来课程和学生之间的多对多的关系，就会变成远程的1对多了

5.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="考试时间")

5.2、基于关系模型数据操作

 1 from flask import Flask,render_template,request
 2 from flask_sqlalchemy import SQLAlchemy
 3 
 4 app = Flask(__name__)
 5 
 6 class Config(object):
 7     DEBUG = True
 8     # 数据库连接配置
 9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
11     # 动态追踪修改设置，如未设置只会提示警告
12     SQLALCHEMY_TRACK_MODIFICATIONS = True
13     # 查询时会显示原始SQL语句
14     SQLALCHEMY_ECHO = True
15 
16 app.config.from_object(Config)
17 
18 
19 """模型类定义"""
20 db = SQLAlchemy(app=app)
21 
22 class Student(db.Model):
23     """学生信息模型"""
24     __tablename__ = "db_students"
25     id = db.Column(db.Integer, primary_key=True,comment="主键")
26     name = db.Column(db.String(15), comment="姓名")
27     age = db.Column(db.SmallInteger, comment="年龄")
28     sex = db.Column(db.Boolean, default=True, comment="性别")
29     email = db.Column(db.String(128), comment="邮箱地址")
30     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
31     achievement_list = db.relationship("Achievement", uselist=True, backref="student")
32     def __repr__(self):
33         return f"{self.name}<Student>"
34 
35 class Course(db.Model):
36     """课程数据模型"""
37     __tablename__ = "db_course"
38     id = db.Column(db.Integer, primary_key=True, comment="主键")
39     name = db.Column(db.String(64), unique=True, comment="课程")
40     price = db.Column(db.Numeric(7, 2))
41     achievement_list = db.relationship("Achievement", uselist=True, backref="course")
42     def __repr__(self):
43         return f'{self.name}<Course>'
44 
45 from datetime import datetime
46 class Achievement(db.Model):
47     __tablename__ = "db_achievement"
48     id = db.Column(db.Integer, primary_key=True, comment="主键")
49     student_id = db.Column(db.Integer, db.ForeignKey(Student.id))
50     course_id  = db.Column(db.Integer, db.ForeignKey(Course.id))
51     score = db.Column(db.Numeric(4,1), default=0, comment="成绩")
52     time  = db.Column(db.DateTime, default=datetime.now, comment="考试时间")
53 
54 @app.route("/")
55 def index():
56     """添加数据"""
57     # 记录xiaobai本次的python入门考试成绩: 88
58     # achievement = Achievement(
59     #     student= Student.query.filter(Student.name=="xiaobai").first(),
60     #     course = Course.query.filter(Course.name=="python初级").first(),
61     #     score  = 81
62     # )
63     # db.session.add(achievement)
64     # db.session.commit()
65 
66     """查询操作"""
67     # 查询xiaobai的成绩
68     student = Student.query.filter(Student.name=="xiaobai").first()
69     for achievement in student.achievement_list:
70         print(f"课程：{achievement.course.name}，成绩：{achievement.score}")
71 
72     return "ok"
73 
74 if __name__ == '__main__':
75     with app.app_context():
76         db.create_all()
77     app.run(debug=True)

View Code

六、逻辑外键

6.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="钱包")
    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="考试时间")

6.2 逻辑外键的数据操作

运用逻辑外键查询的基本用法：

模型.query.join("模型类","主模型.主键==外键模型.外键").with_entities("字段1","字段2".label("字段别名")).all()

 1 from flask import Flask,render_template,request
 2 from flask_sqlalchemy import SQLAlchemy
 3 
 4 app = Flask(__name__)
 5 
 6 class Config(object):
 7     DEBUG = True
 8     # 数据库连接配置
 9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
11     # 动态追踪修改设置，如未设置只会提示警告
12     SQLALCHEMY_TRACK_MODIFICATIONS = True
13     # 查询时会显示原始SQL语句
14     SQLALCHEMY_ECHO = True
15 
16 app.config.from_object(Config)
17 
18 
19 """模型类定义"""
20 db = SQLAlchemy(app=app)
21 
22 class Student(db.Model):
23     """学生信息模型"""
24     __tablename__ = "db_students"
25     id = db.Column(db.Integer, primary_key=True,comment="主键")
26     name = db.Column(db.String(15), comment="姓名")
27     age = db.Column(db.SmallInteger, comment="年龄")
28     sex = db.Column(db.Boolean, default=True, comment="性别")
29     email = db.Column(db.String(128), comment="邮箱地址")
30     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
31     def __repr__(self):
32         return f"{self.name}<Student>"
33 
34 class Course(db.Model):
35     """课程数据模型"""
36     __tablename__ = "db_course"
37     id = db.Column(db.Integer, primary_key=True, comment="主键")
38     name = db.Column(db.String(64), unique=True, comment="课程")
39     price = db.Column(db.Numeric(7, 2))
40     def __repr__(self):
41         return f'{self.name}<Course>'
42 
43 from datetime import datetime
44 class Achievement(db.Model):
45     __tablename__ = "db_achievement"
46     id = db.Column(db.Integer, primary_key=True, comment="主键")
47     student_id = db.Column(db.Integer, comment="学生ID")
48     course_id  = db.Column(db.Integer, comment="课程ID")
49     score = db.Column(db.Numeric(4,1), default=0, comment="成绩")
50     time  = db.Column(db.DateTime, default=datetime.now, comment="考试时间")
51 
52 @app.route("/")
53 def index():
54     """添加数据"""
55     # 记录xiaobai本次的python入门考试成绩: 88
56     # achievement = Achievement(
57     #     student_id = Student.query.filter(Student.name=="xiaobai").first().id,
58     #     course_id  = Course.query.filter(Course.name=="python入门").first().id,
59     #     score = 80,
60     #     time = datetime.now(),
61     # )
62     # db.session.add(achievement)
63     # db.session.commit()
64 
65     """查询操作"""
66     # 查询xiaobai的成绩
67     student = Student.query.join(
68         Achievement,
69         Achievement.student_id == Student.id
70     ).join(
71         Course,
72         Course.id == Achievement.course_id
73     ).filter(
74         Student.
75         =="xiaobai",
76         Achievement.score==88,
77     ).with_entities(
78         Student.name,
79         Course.name.label("course"),
80         Achievement.score.label("number")
81     ).first()
82 
83     # 当连表查询返回一个结果的时候，可以通过keys查看当前模型提供的字段
84     print(student) # 字段列表名
85     print(type(student)) # 返回值是一个SQLAlchemy内部封装的模型对象
86     print(student.keys()) # 字段列表
87     print(f"{student.name}的{student.course}成绩是：{student.number}")
88 
89     return "ok"
90 
91 if __name__ == '__main__':
92     with app.app_context():
93         db.create_all()
94     app.run(debug=True)

View Code

注意：django中也有虚拟外键的设置方案：在模型中设置db_constraint = False

七、lazy 懒加载的用法

7.1 懒加载简介

指定如何加载关联模型数据的方式。

参数值:

select（立即加载，查询所有相关数据显示，相当于lazy=True）
subquery（立即加载，但使用子查询）
dynamic（不立即加载，但提供加载记录的查询对象）

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

7.2 操作实例

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 # 等同于
 22 # db = SQLAlchemy()
 23 # db.init_app(app)
 24 
 25 class Student(db.Model):
 26     """学生信息模型"""
 27     # 声明与当前模型绑定的数据表名称
 28     __tablename__ = "db_students"
 29     id = db.Column(db.Integer, primary_key=True,comment="主键")
 30     name = db.Column(db.String(15), comment="姓名")
 31     age = db.Column(db.SmallInteger, comment="年龄")
 32     sex = db.Column(db.Boolean, default=True, comment="性别")
 33     email = db.Column(db.String(128), comment="邮箱地址")
 34     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 35     # 从Student        查询  StudentAddress:  Student.address_list = []
 36     # 从StudentAddress 查询  Student:         StudentAddress.student = 学生模型对象
 37     address_list = db.relationship("StudentAddress", uselist=True, backref="student",lazy="dynamic")
 38 
 39     def __repr__(self):
 40         return f"{self.name}<Student>"
 41 
 42 from sqlalchemy.orm import backref
 43 
 44 class StudentAddress(db.Model):
 45     """学生收货地址"""
 46     __tablename__ = "db_student_address"
 47     id = db.Column(db.Integer, primary_key=True, comment="主键")
 48     user_id = db.Column(db.ForeignKey("db_students.id"), comment="学生id")
 49     province = db.Column(db.String(20), comment="省份")
 50     city = db.Column(db.String(20), comment="城市")
 51     area = db.Column(db.String(20), comment="地区")
 52     address = db.Column(db.String(250), comment="详细接地")
 53     # 也可以在外键模型中声明关系熟悉
 54     # student = db.relationship("Student", uselist=False, backref=backref("address_list",uselist=True))
 55 
 56     def __repr__(self):
 57         return f"{self.province}-{self.city}-{self.area}-{self.address}"
 58 
 59 @app.route("/")
 60 def index():
 61     """查询数据"""
 62     # 正向关联----> 从主模型查询外键模型
 63     student = Student.query.filter(Student.name=="xiaobai").first()
 64     print(student)
 65     # # 获取地址列表[调用关联属性，直接把外建模型对应数据直接查询查来]
 66     address_list = student.address_list[0].address
 67     return "ok"
 68 
 69 """
 70 lazy="subquery"
 71 1. 只查询：student，但是同时使用子查询语句进行链表操作，把外键模型数据也查询出来了
 72 
 73     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 
 74     FROM db_students
 75 
 76     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 
 77     FROM (SELECT db_students.id AS db_students_id  FROM db_students  WHERE db_students.name = %s  LIMIT %s) AS anon_1 
 78     INNER JOIN db_student_address ON anon_1.db_students_id = db_student_address.user_id
 79 
 80 lazy="select"，
 81 1. 只查询：student
 82     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 
 83     FROM db_students WHERE db_students.name = %s  LIMIT %s
 84 
 85 2. 调用关联属性，直接连表查询
 86     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 
 87     FROM db_students WHERE db_students.name = %s  LIMIT %s
 88 
 89     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 
 90     FROM db_student_address WHERE %s = db_student_address.user_id
 91 
 92 lazy="dynamic"
 93 1. 只查询：student
 94    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 
 95     FROM db_students WHERE db_students.name = %s  LIMIT %s
 96 
 97 2. 调用关联属性，不连表，直到代码真的调用了外键模型的具体字段才会查询数据库
 98     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 
 99     FROM db_students WHERE db_students.name = %s  LIMIT %s
100     
101 """
102 
103 if __name__ == '__main__':
104     with app.app_context():
105         # 检测数据库中是否存在和模型匹配的数据表。
106         # 如果没有，则根据模型转换的建表语句进行建表。
107         # 如果找到，则不会进行额外处理
108 
109         db.create_all()
110     app.run(debug=True)

View Code