flask+flask_sqlalchemy增删改查

虚拟环境: python3 -m venv venv
激活虚拟环境 source venv/Scripts/activate

使用flask run启动项目 $env:FLASK_APP=‘app.py’
热更新 $env:FLASK_DEBUG=1

镜像：
清华：https://pypi.tuna.tsinghua.edu.cn/simple
阿里云：http://mirrors.aliyun.com/pypi/simple/
中国科技大学 https://pypi.mirrors.ustc.edu.cn/simple/
华中理工大学：http://pypi.hustunique.com/
山东理工大学：http://pypi.sdutlinux.org/
豆瓣：http://pypi.douban.com/simple/

learn_sql.py

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///first.db'  # 数据库位置
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'test'

db = SQLAlchemy(app)  # 实例化的数据库

# 学生表
class Student(db.Model):
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)  # 主键
    name = db.Column(db.String(64), nullable=False)  # 学生姓名 nullable能否为空
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 性别 Enum枚举 不能为空
    phone = db.Column(db.String(11))  # 手机号 可以为空
    grades = db.relationship('Grade', backref='student')  # 成绩关系关联
    courses = db.relationship('Course', secondary="student_to_course", backref='student')

# 课程表
class Course(db.Model):
    __tablename__ = 'course'
    id = db.Column(db.Integer, primary_key=True)  # 主键
    name = db.Column(db.String(64), nullable=False)  # 课名
    grades = db.relationship('Grade', backref='course')  # 成绩关系关联
    teacher_id = db.Column(db.Integer, db.ForeignKey('teacher.id'))  # 所属教师

# 教师表
class Teacher(db.Model):
    __tablename__ = 'teacher'
    id = db.Column(db.Integer, primary_key=True)  # 主键
    name = db.Column(db.String(64), nullable=False)  # 教师姓名 nullable能否为空
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 性别 Enum枚举 不能为空
    phone = db.Column(db.String(11))  # 手机号 可以为空
    course = db.relationship('Course', backref='teacher')  # 成绩关系关联

# 成绩表
class Grade(db.Model):
    __tablename__ = 'grade'
    id = db.Column(db.Integer, primary_key=True)  # 主键
    grade = db.Column(db.Integer, nullable=False)  # 成绩 nullable能否为空
    student_id = db.Column(db.Integer, db.ForeignKey('student.id')) # 学生的id,外键指向所属学生
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))  # 课程id

# 中间表
class StudentToCourse(db.Model):
    __tablename__ = 'student_to_course'
    id = db.Column(db.Integer, primary_key=True)  # 主键
    student_id = db.Column(db.Integer, db.ForeignKey('student.id'))  # 学生的id,外键指向所属学生
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))  # 课程id


if __name__ == '__main__':
    with app.app_context():
        db.create_all() # 创建表
        # db.drop_all() # 删库

operate_new_sql.py文章来源地址https://uudwc.com/A/5Lp1

from learn_sql import db, Student, Grade, Course, Teacher, app

with app.app_context():
    # 增
    # s1 = Student(name="张三", gender="男", phone="12345678900")
    # s2 = Student(name="李姐", gender="女", phone="13245678900")
    # s3 = Student(name="王妹", gender="女", phone="13345678900")
    # s4 = Student(name="陈总", gender="男", phone="13445678900")
    # 添加语句 第一种
    # db.session.add(s1)
    # db.session.commit()
    # 批量添加语句 第二种
    # db.session.add_all([s1, s2, s3, s4])
    # db.session.commit()

    # 查
    # 第一种: get id查询
    # stu = Student.query.get(1)
    # print(stu.name)
    # print(stu.gender)
    # 第二种：查全部
    # stu = Student.query.all()
    # for item in stu:
    #     print(item.name, item.gender, item.phone)
    # print(stu)
    # 第二种：条件查询
    # stu = Student.query.filter(Student.name == '王妹')
    # for item in stu:
    #     print(item.id, item.name)
    # 第四种：filter_by() first() all() 比较类似SQL的查询
    #     stu = Student.query.filter_by(name='张三').filter(Student.id==5)
    #     for item in stu:
    #         print(item.id, item.name)

    # 改
    # 第一种
    # stu = Student.query.filter(Student.gender=='男').update({"gender": "女"}) # 返回动了多少条数据
    # print(stu)
    # db.session.commit()
    # 第二种
    # stu = Student.query.filter(Student.gender == '女').first()
    # stu.gender = '男'
    # db.session.add(stu)
    # db.session.commit()
    # 第三种
    # stu = Student.query.all()
    # for item in stu:
    #     item.gender = '男'
    #     db.session.add(item)
    # db.session.commit()

    # stu = Student.query.all()
    # for item in stu:
    #     print(item.gender)
    # 删
    #     stu = Student.query.filter(Student.gender=='男').delete() # 返回动了多少条数据
    #     print(stu)
    #     db.session.commit()

    # stu = Student.query.all()
    # print(stu)

    print('-------------以下是一对多---------------')
    # grade1 = Grade(grade=100,student_id=1)
    # grade2 = Grade(grade=95, student_id=1)
    # db.session.add_all([grade1, grade2])
    # db.session.commit()

    # stu = Student.query.all()
    # for item in stu:
    #     print(item.gender, item.name)

    # grade = Grade.query.filter(Grade.student_id==1).all()
    # for item in grade:
    #     print(item.grade)

    # 通过 一访问多
    # stu = Student.query.get(1)
    # for item in stu.grades:
    #     print(stu.name,item.grade)

    # 通过 多 访问 一
    # grade = Grade.query.filter(Grade.grade == '100').all()
    # for item in grade:
    #     print(item.student.name)

    # 多对多
    # s1 = Student(name="张1", gender="男", phone="12345678900")
    # s2 = Student(name="张2", gender="女", phone="12345678900")
    # s3 = Student(name="张3", gender="男", phone="12345678900")
    # s4 = Student(name="张4", gender="女", phone="12345678900")
    # s5 = Student(name="张5", gender="男", phone="12345678900")
    # s6 = Student(name="张6", gender="女", phone="12345678900")
    # s7 = Student(name="张7", gender="男", phone="12345678900")
    # s8 = Student(name="张8", gender="女", phone="12345678900")
    # s9 = Student(name="张9", gender="男", phone="12345678900")
    # db.session.add_all([s1,s2,s3,s4,s5,s6,s7,s8,s9])
    # db.session.commit()
    #
    # t1 = Teacher(name="老数", gender="男", phone="12345678900")
    # t2 = Teacher(name="老英", gender="男", phone="12345678900")
    # t3 = Teacher(name="老王", gender="男", phone="12345678900")
    # t4 = Teacher(name="老语", gender="男", phone="12345678900")
    # t5 = Teacher(name="老体育", gender="男", phone="12345678900")
    # db.session.add_all([t1,t2,t3,t4,t5])
    # db.session.commit()
    #
    # c1 = Course(name="数学")
    # c2 = Course(name="语文")
    # c3 = Course(name="英语")
    # c4 = Course(name="物理")
    # c5 = Course(name="化学")
    # c6 = Course(name="生物")
    # db.session.add_all([c1, c2, c3, c4, c5, c6])
    # db.session.commit()
    # for i in range(1, 5):
    #     c = Course.query.filter(Course.id == i).update({"teacher_id": 1})
    # db.session.commit()

    # 查询学生
    # stu = Student.query.all()
    #
    # # 查询课程表
    # cs = Course.query.filter(Course.id >= 2).all()
    # print(cs)
    # stu = Student.query.filter(Student.id >=2).all()
    # for s in stu:
    #     s.courses = cs
    #     db.session.add(s)
    #     db.session.commit()

    # 学生查询课程
    # stu = Student.query.get(1)
    # for s in stu.courses:
    #     print(s.name)
    #
    # # 课程查询学生
    # stu = Course.query.get(2)
    # for s in stu.students:
    #     print(s.name)