from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyfrom flask_sqlalchemy.session import Sessionfrom sqlalchemy import *from sqlalchemy.orm import relationship
app = Flask(__name__)
username = "root"pwd = "123456"ip = "127.0.0.1"port = "3306"database = "first"app.config['SQLALCHEMY_DATABASE_URI'] = f'mysql+pymysql://{username}:{pwd}@{ip}:{port}/{database}?charset=utf8'app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)session: Session = db.session

# 创建一个中间表teacher_student_rel = db.Table(    # 中间表的名字，最好能体现哪两张表    "teacher_student",    # 其中一张表的描述，作为一个外键，指向 teacher 表的id    Column("teacher_id", Integer, \    ForeignKey("teacher.id"), \    nullable=False, primary_key=True),    # 其中一张表的描述，作为一个外键，指向 student 表的id    Column("student_id", Integer, ForeignKey("student.id"), \    nullable=False, primary_key=True))

# 创建老师的映射class Teacher(db.Model):    __tablename__ = "teacher"    id = Column(Integer, primary_key=True)    # name 老师的姓名    # nullable=False 不为空，unique=True 唯一约束，不允许重复    name = Column(String(100), nullable=False, unique=True)    # 参数一：指向另一张表，另外一张表的类名    # 参数二：secondary ，指向中间表的变量名    # 参数三：反向指向，backref  当前表的别名    students = relationship("Student",\     secondary=teacher_student_rel,                            backref= "teachers")
    def __repr__(self):        return "<Teacher id='%s' \        teacher_name='%s'>" % (self.id, self.name)

# 创建学生的映射class Student(db.Model):    __tablename__ = "student"    id = Column(Integer, primary_key=True, autoincrement=True)    name = Column(String(100),nullable=False,unique=True)
    def __repr__(self):        return f"<Student id='{self.id}' name='{self.name}'>"

if __name__ == '__main__':    with app.app_context():    #     db.create_all()
    # ------------添加数据-----------    #     stu1 = Student(name="学生A")    #     stu2 = Student(name="学生B")    #     stu3 = Student(name="学生C")    #    #     tea1 = Teacher(name='老师1')    #     tea2 = Teacher(name='老师2')    #     tea3 = Teacher(name='老师3')    #     session.add_all([stu1, stu2, stu3, tea1, tea2, tea3])    #    #     """中间表建立关联关系    #     老师1 -- 关联 -- 学生A，学生B    #     老师2 -- 关联 -- 学生B,学生C"""    #     tea1.students = [stu1, stu2]    #     tea2.students = [stu3, stu2]    #     session.commit()    #     session.close()
    # ------------查询数据-----------        # # 查询学生对应的老师        # stu = Student.query.filter_by(id=1).first()        # print(stu.teachers)        #        # # 查询老师对应的学生        # tea = Teacher.query.filter_by(id=1).first()        # print(tea.students)
    # ------------修改数据-----------        # # 查询学生id=1，修改对应的老师名称        # stu = Student.query.filter_by(id=1).first()        # print(stu.teachers)        # stu.teachers[0].name = '老师A'        # session.commit()        # session.close()
        # # 查询老师id=1，修改学生的姓名        # tea = Teacher.query.filter_by(id=1).first()        # print(tea.students)        # tea.students[0].name = '学生1'        # session.commit()        # session.close()
    # ------------删除数据-----------    #     # 在删除数据的时候，如果删除了有外键关系的数据，sqlachemy会自动删除关联关系    #     # 删除学生    #     stu1 = Student.query.filter_by(id=1).first()    #     db.session.delete(stu1)    #     db.session.commit()
        # # 删除老师        # tea1 = Teacher.query.filter_by(id=1).first()        # db.session.delete(tea1)        # db.session.commit()        # db.session.close()        pass