Python: ORM (many-to-many)

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, String

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:passw0rd@localhost/test?charset=utf8"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

program = db.Table('program', db.Model.metadata,
    db.Column('year', db.Integer, primary_key=True),
    db.Column('teacher_id', db.Integer, db.ForeignKey('teacher.id')),
    db.Column('student_id', db.Integer, db.ForeignKey('student.id'))
)

class Teacher(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30))
    course = db.Column(db.String(20))
    students = db.relationship('Student', secondary=program)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30))
    grade = db.Column(db.String(10))
    teachers = db.relationship('Teacher', secondary=program)

teacher1 = Teacher(name='Kent Beck', course='TDD')
teacher2 = Teacher(name='Martin Fowler', course='Design Patterns')
teacher3 = Teacher(name='Brendan Burns', course='Distributed Systems')

db.session.add_all([teacher1, teacher2, teacher3])
db.session.commit()

student1 = Student(name='Steven', grade='1')
student2 = Student(name='Candice', grade='1')
db.session.add_all([student1, student2])
db.session.commit()

statement = program.insert().values(year=2019, teacher_id=teacher1.id, student_id=student1.id)
db.session.execute(statement)
statement = program.insert().values(year=2019, teacher_id=teacher3.id, student_id=student1.id)
db.session.execute(statement)

statement = program.insert().values(year=2019, teacher_id=teacher1.id, student_id=student2.id)
db.session.execute(statement)
statement = program.insert().values(year=2019, teacher_id=teacher2.id, student_id=student2.id)
db.session.execute(statement)

db.session.commit()

print(student1.teachers)
print(student2.teachers)

前一篇「Python: ORM (one-to-many)」說明怎麼用 Flask-SQLAlchemy 建立一對多的關係，這篇說明怎麼建立多對多的關係。

如上程式碼，老師和學生存在著授課的關係，學生可以選擇多個課程，老師也可以有許多學生，program 這個 table 即是記錄學生與老師間的關係。

• line 20、26: 注意裡面的 secondary 屬性，這兩行除了使用
  relationship 建立起學生對老師的一對多關係、老師對學生的一對多關係，還用這個屬性指出另一個關係。 
• line 12、37: 前一篇有用到 add 一次加入一個 table，add_all 則是允許一次多個。