from flask import Flask, render_template, request, flash, redirect
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__,static_folder="static",template_folder="templates")

# 設(shè)置數(shù)據(jù)庫(kù)連接屬性
app.config['SQLALCHEMY_DATABASE_URI'] = '×××'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 實(shí)例化 ORM 操作對(duì)象
db = SQLAlchemy(app)

# 班級(jí)表
class Classes(db.Model):
  __tablename__ = "classes"
  id = db.Column(db.Integer,primary_key=True)
  name = db.Column(db.String(20),nullable=False,unique=True)

# 學(xué)生表
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"))  # 注意要寫(xiě)成（表名.字段名）

cls_id = Students.query.filter(Student.id == 'xxx').first()
cls = Classes.query.filter(Classes.id == cls.id).first()
print(cls.name)

relate_student = db.relationship("Students",backref='relate_class',lazy='dynamic')

stu = Students.query.filter(Students.name == 'xxx').first()
stu.relate_class.name # stu.relate_class 會(huì)跳到 classes 表

cls = Classes.query.filter(Classes.name == 'xxx').first()
cls.relate_student.name # cls.relate_stu 會(huì)跳到 students 表

relate_student = db.relationship("Students",backref='relate_class',lazy='dynamic')

tb_student_course = db.Table('tb_student_course',
               db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
               db.Column('course_id', db.Integer, db.ForeignKey('courses.id'))
               )


class Student(db.Model):
  __tablename__ = "students"
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(64), unique=True)
　　 # 關(guān)聯(lián)屬性，多對(duì)多的情況，可以寫(xiě)在任意一個(gè)模型類(lèi)中
  relate_courses = db.relationship('Course', secondary=tb_student_course,
               backref='relate_student',
               lazy='dynamic')

class Course(db.Model):
  __tablename__ = "courses"
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(64), unique=True)

# 添加測(cè)試數(shù)據(jù)

  stu1 = Student(name='張三')
  stu2 = Student(name='李四')
  stu3 = Student(name='王五')

  cou1 = Course(name='物理')
  cou2 = Course(name='化學(xué)')
  cou3 = Course(name='生物')

  stu1.courses = [cou2, cou3]  # 記得要添加關(guān)系
  stu2.courses = [cou2]
  stu3.courses = [cou1, cou2, cou3]

  db.session.add_all([stu1, stu2, stu2])
  db.session.add_all([cou1, cou2, cou3])

  db.session.commit()

for course in stu1.relate_courses:
  print(course.name)
for student in cou2.relate_student:
  print(student)