flask_sqlalchemy

# pip install flask-sqlalchemy
from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)

# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/test'

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + "/home/lmp/test.db"

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'xxx'
db = SQLAlchemy(app)


# 学生表
class Student(db.Model):
    __tablename__ = "student"
    id = db.Column(db.Integer, primary_key=True)  # id号(独一无二的)
    name = db.Column(db.String(64), nullable=False)  # 学生姓名
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 学生性别
    phone = db.Column(db.String(11), unique=True, nullable=False)  # 学生手机号
    courses = db.relationship("Course", secondary="student_course", backref="students")  # 关系关联
    grades = db.relationship("Grade", backref="student")  # 成绩关系关联


# 学生-课程中间表
class StudentCourse(db.Model):
    __tablename__ = "student_course"
    id = db.Column(db.Integer, primary_key=True)  # id号(独一无二的)
    students_id = db.Column(db.Integer, db.ForeignKey("student.id"))  # 学生的id
    courses_id = db.Column(db.Integer, db.ForeignKey("course.id"))  # 课程的id


# 课程表
class Course(db.Model):
    __tablename__ = "course"
    id = db.Column(db.Integer, primary_key=True)  # id号(独一无二的)
    name = db.Column(db.String(32), unique=True)  # 课程名字
    teacher_id = db.Column(db.Integer, db.ForeignKey("teacher.id"))  # 所属老师的id
    grades = db.relationship("Grade", backref="course")  # 成绩关系关联


# 教师表
class Teacher(db.Model):
    __tablename__ = "teacher"
    id = db.Column(db.Integer, primary_key=True)  # id号(独一无二的)
    name = db.Column(db.String(32), unique=True)  # 姓名
    phone = db.Column(db.String(11), unique=True, nullable=False)  # 手机号
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 性别
    course = db.relationship("Course", backref="teacher")  # 所教课程


# 成绩表
class Grade(db.Model):
    __tablename__ = "grade"
    id = db.Column(db.Integer, primary_key=True)  # id号(独一无二的)
    my_grade = db.Column(db.String(32), unique=True)  # 分数
    course_id = db.Column(db.Integer, db.ForeignKey("course.id"))  # 所属课程
    students_id = db.Column(db.Integer, db.ForeignKey("student.id"))  # 所属学生


if __name__ == "__main__":
    db.create_all()
    # db.drop_all()

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

# 增

# s = Student(name="张三", gender="男", phone="12345678900")

# s1 = Student(name="kk", gender="女")
# s2 = Student(name="张蛋", gender="女")
# s3 = Student(name="李四", gender="男")
# s4 = Student(name="李鬼", gender="男", phone="12345678900")

# 语句 第一种
# db.session.add(s)
# db.session.commit()
#
# db.session.add_all([s1, s2, s3, s4])
# db.session.commit()

# 查

# get(id) 查 单一个
# stu = Student.query.get(1)
# print(stu)
# print(stu.name)
# print(stu.gender)
# print(stu.phone)

# all() 查全部
# stu = Student.query.all()
# print(stu)
# for i in stu:
#     print(i.name, i.gender, i.phone)

# filter() 条件查询
# stu = Student.query.filter(Student.gender == "女")
# print(stu)
# for i in stu:
#     print(i.name, i.id,i.gender)


# filter_by() 比较类似SQL的查询  first() 查询到的第一个
# stu = Student.query.filter_by(name="张三").first()
# print(stu)
# print(stu.name, stu.id, stu.gender)


# stu = Student.query.filter_by(name="张三").filter(Student.id <= 2)
# print(stu)
# for i in stu:
#     print(i.name, i.id)

# 改
# 第一种
# stu = Student.query.filter(Student.id == 1).update({"name": "张毅1"})# 返回动了多少条数据
# db.session.commit()
# print(stu)


# stu = Student.query.filter(Student.gender == "男").update({"gender": "女"})  # 返回动了多少条数据
# print(stu)
# db.session.commit()

# 第二种
# 匹配一个出来，修改
# stu = Student.query.get(1)
# print(stu.name,stu.gender)
# stu.gender = "男"
# db.session.add(stu)
# db.session.commit()
# print(stu.name,stu.gender)

# 匹配第一个修改
# stu = Student.query.filter(Student.gender == "男").first()
# print(stu.name,stu.gender)
# stu.gender = "女"
# db.session.add(stu)
# db.session.commit()
# print(stu.name,stu.gender)

# 修改全部
# stu = Student.query.filter(Student.gender == "女").all()
# for i in stu:
#     i.gender = "男"
#     db.session.add(i)
# db.session.commit()


# 删
#
# stu = Student.query.filter(Student.id >= 5).delete()  # 返回动了多少条数据
# print(stu)
# db.session.commit()

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

# print("--------------------------")

# grade1 = Grade(grade=100, student_id=1)
# grade2 = Grade(grade=95, student_id=1)
#
# db.session.add(grade1)
# db.session.add(grade2)
# db.session.commit()

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

# grade = Grade.query.filter(Grade.student_id == 1).all()
# for i in grade:
#     print(i.student_id, i.student, i.student.name)

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


# 通过 多 访问 一

# grade = Grade.query.filter(Grade.grade == "100").all()
# for i in grade:
#     print(i.student.name,i.student.gender)


# 多对多
# student0 = Student(name="李玲", gender="女", phone="12345678900")
# student1 = Student(name="李依", gender="女", phone="12345678901")
# student2 = Student(name="李贰", gender="男", phone="12345678902")
# student3 = Student(name="李叁", gender="男", phone="12345678903")
# student4 = Student(name="李斯", gender="男", phone="12345678904")
# student5 = Student(name="李舞", gender="女", phone="12345678905")
# student6 = Student(name="李榴", gender="男", phone="12345678906")
# student7 = Student(name="李淇", gender="女", phone="12345678907")
# student8 = Student(name="李巴", gender="男", phone="12345678908")
# student9 = Student(name="李玖", gender="男", phone="12345678909")
#
# teacher0 = Teacher(name="老数", gender="男", phone="12345678910")
# teacher1 = Teacher(name="老语", gender="女", phone="12345678911")
# teacher2 = Teacher(name="老英", gender="女", phone="12345678912")
# teacher3 = Teacher(name="老物", gender="男", phone="12345678913")
# teacher4 = Teacher(name="老化", gender="男", phone="12345678914")
# teacher5 = Teacher(name="老生", gender="男", phone="12345678915")
#
# course0 = Course(name="数学")
# course1 = Course(name="语文")
# course2 = Course(name="英语")
# course3 = Course(name="物理")
# course4 = Course(name="化学")
# course5 = Course(name="生物")
#
# db.session.add_all(
#     [student0, student1, student2, student3, student4, student5, student6, student7, student8, student9, teacher0,
#      teacher1, teacher2, teacher3, teacher4, teacher5, course0, course1, course2, course3, course4, course5])
# db.session.commit()

# for i in range(1, 7):
#     c = Course.query.filter(Course.id == i).update({"teacher_id": i})
# db.session.commit()


# 查询课程表
# 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)
# print(stu.courses)
#
# print("===================")

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