image.png
创建学生表(STUDENT)
CREATE TABLE STUDENT(
ID VARCHAR(20) primary key,
NAME VARCHAR(20),
AGE INT
);
创建选修课程表(SUBJECT)
CREATE TABLE SUBJECT(
ID VARCHAR(20) primary key,
NAME VARCHAR(40)
);
创建学生和选修课程的关联表(STU_REF_SUB)
CREATE TABLE STU_REF_SUB(
STUDENT_ID VARCHAR(20),
SUBJECT_ID VARCHAR(20)
);
添加外键
ALTER TABLE STU_REF_SUB ADD CONSTRAINT FK_STUDENT FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(ID);
ALTER TABLE STU_REF_SUB ADD CONSTRAINT FK_SUBJECT FOREIGN KEY(SUBJECT_ID) REFERENCES SUBJECT(ID);
添加联合主键
ALTER TABLE STU_REF_SUB ADD CONSTRAINT PK_STU_REF_SUB PRIMARY KEY(STUDENT_ID,SUBJECT_ID);
附：
一个数据库表只能有一个主键，不允许两个主键。
但是允许两个字段联合起来设置为主键，这叫联合主键。
创建主键的方式如下：
ALTER TABLE `选课表 `
ADD CONSTRAINT `pk_选课表` PRIMARY KEY(学号)
创建联合主键的方式如下：
ALTER TABLE `选课表 `
ADD CONSTRAINT `pk_选课表` PRIMARY KEY(学号,课程编号)
初始化数据
INSERT INTO STUDENT VALUES('STU001','李白',25);
INSERT INTO STUDENT VALUES('STU002','杜甫',26);
INSERT INTO STUDENT VALUES('STU003','欧阳修',27);
INSERT INTO STUDENT VALUES('STU004','岳飞',26);
INSERT INTO STUDENT VALUES('STU005','柳永',28);
INSERT INTO SUBJECT VALUES('SUB001','网球课');
INSERT INTO SUBJECT VALUES('SUB002','诗词课');
INSERT INTO SUBJECT VALUES('SUB003','计算机');
INSERT INTO SUBJECT VALUES('SUB004','乒乓球');
INSERT INTO SUBJECT VALUES('SUB005','篮球课');
INSERT INTO STU_REF_SUB VALUES('STU001','SUB001');
INSERT INTO STU_REF_SUB VALUES('STU001','SUB003');
INSERT INTO STU_REF_SUB VALUES('STU001','SUB004');
INSERT INTO STU_REF_SUB VALUES('STU002','SUB002');
INSERT INTO STU_REF_SUB VALUES('STU002','SUB004');
INSERT INTO STU_REF_SUB VALUES('STU004','SUB001');
INSERT INTO STU_REF_SUB VALUES('STU004','SUB005');
INSERT INTO STU_REF_SUB VALUES('STU005','SUB003');
查询
select STUDENT.name as "学生名称", SUBJECT.name as "课程名称"
from STUDENT,SUBJECT,STU_REF_SUB
where STUDENT.id = STU_REF_SUB.STUDENT_ID
and SUBJECT.id = STU_REF_SUB.SUBJECT_ID
flask的orm框架(SQLAlchemy)-一对多查询以及多对多查询
(venv) $ pip install flask-sqlalchemy
- 一对多，多对多是什么？
一对多。例如，班级与学生，一个班级对应多个学生，或者多个学生对应一个班级。
多对多。例如，学生与课程，可以有多个学生修同一门课，同时，一门课也有很多学生。
一对多查询
# 班级
CREATE TABLE `classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 学生
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cls_id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
foreign key (cls_id) references classes(id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
sqlalchemy 模板创建表的代码：
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 设置数据库连接属性
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://用户名:密码@IP:端口/数据库名?charset=utf8mb4"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 实例化 ORM 操作对象
db = SQLAlchemy(app)
# 班级表
class Classes(db.Model):
__tablename__ = "classes"
id = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(20),nullable=False,unique=True)
relate_student = db.relationship("Students", backref='relate_class', lazy='dynamic')
# 学生表
class Students(db.Model):
__tablename__ = "students"
id = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(40),nullable=False)
cls_id = db.Column(db.Integer,db.ForeignKey("classes.id")) # 注意要写成（表名.字段名）
# @app.route("/")
# def index():
# cls_id = Students.query.filter(Students.id == 1).first()
# cls = Classes.query.filter(Classes.id == cls_id.id).first()
# print(cls.name)
# return cls.name
# 根据学生姓名查班级的名称
@app.route("/studentName_to_className")
def studentName_to_className():
stu = Students.query.filter(Students.name == '赵小明').first()
# stu.relate_class.name # stu.relate_class 会跳到 classes 表id
print("1111111111",stu.relate_class.name)
return str(stu.relate_class.id)
# 根据班级名称查学生名字
@app.route("/className_to_studentName")
def className_to_studentName():
cls = Classes.query.filter(Classes.name == "高一(1)班").first()
print("22222222",cls.relate_student.all())
return str(cls.relate_student.all())
if __name__ == '__main__':
app.run(debug=True)
relate_student = db.relationship("Students", backref='relate_cla ss', lazy='dynamic')
> 其中realtionship描述了Students和Classes的关系。在此文中，第一个参数为对应参照的类"Students"
1.第二个参数backref为类Students申明新属性的方法
2.第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
如果设置为子查询方式(subquery)，
则会在加载完Classes对象后，就立即加载与其关联的对象，
这样会让总查询数量减少，但如果返回的条目数量很多，就会比较慢
另外,也可以设置为动态方式(dynamic)，
这样关联对象会在被使用的时候再进行加载，并且在返回前进行过滤，
如果返回的对象数很多，或者未来会变得很多，那最好采用这种方式
注意：在设置db.relationship的一方查询时需要用first()或者all()才能获取到对象，所以一对多的关系中尽可能把外键设置在多的一方，db.relationship设置在‘一’的一方。
多对多查询
假设一堆学生选了不同的课程，这就是多对多关系。
# 学生表
CREATE TABLE `students_m` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 课程表
CREATE TABLE `courses_m` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 中间表
CREATE TABLE `tb_student_course` (
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
添加外键
ALTER TABLE tb_student_course
ADD CONSTRAINT FK_STUDENT FOREIGN KEY(student_id)
REFERENCES students_m(ID);
ALTER TABLE tb_student_course
ADD CONSTRAINT FK_SUBJECT FOREIGN KEY(course_id)
REFERENCES courses_m(ID);
添加联合主键
ALTER TABLE tb_student_course ADD CONSTRAINT PK_STU_REF_SUB PRIMARY KEY(student_id,course_id);
sqlalchemy 模板创建表的代码：
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 设置数据库连接属性
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://用户名:密码@IP:端口/数据库名?charset=utf8mb4"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 实例化 ORM 操作对象
db = SQLAlchemy(app)
tb_student_course = db.Table('tb_student_course',
db.Column('student_id', db.Integer, db.ForeignKey('students_m.id')),
db.Column('course_id', db.Integer, db.ForeignKey('courses_m.id'))
)
class Student(db.Model):
__tablename__ = "students_m"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
# 关联属性，多对多的情况，可以写在任意一个模型类中
courses = db.relationship('Course', # 映射模型
secondary=tb_student_course, # 第二映射的模型
backref=db.backref('relate_student',lazy = "dynamic"),
lazy='dynamic'
# select 访问该字段时，加载所有的映射数据
# joined 对关联的两个表Course和tb_student_course进行join查询
# dynamic 访问该字段时，不加载数据
)
class Course(db.Model):
__tablename__ = "courses_m"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
@app.route("/insert_to_data")
def insert_to_data():
# 添加测试数据
stu1 = Student(name='张三')
stu2 = Student(name='李四')
stu3 = Student(name='王五')
cou1 = Course(name='物理')
cou2 = Course(name='化学')
cou3 = Course(name='生物')
#添加两者关系到中间表
stu1.courses = [cou2, cou3]
stu2.courses = [cou2]
stu3.courses = [cou1, cou2, cou3]
#或者
stu1.courses.append(cou2)
stu1.courses.append(cou3)
stu2.courses.append(cou2)
stu3.courses.append(cou1)
stu3.courses.append(cou2)
stu3.courses.append(cou3)
db.session.add_all([stu1, stu2, stu2])
db.session.add_all([cou1, cou2, cou3])
db.session.commit()
return "ok"
@app.route("/student_to_courses")
def student_to_courses():
stu1 = Student.query.filter(Student.name == "张三").first()
for course in stu1.courses.all():
print(course.name)
return "success1"
@app.route("/course_to_students")
def course_to_students():
cou2 = Course.query.filter(Course.name == "化学").first()
for student in cou2.relate_student.all():
print(student.name)
return "success2"
if __name__ == '__main__':
app.run(debug=True)
网友评论