SQLAlchemy 多对多

参考官网： https://www.pythoncentral.io/sqlalchemy-orm-examples/  

员工部门两个表，中间为多对多关系，这种一般需要创建一个中间表。多对多转换成一对多

from sqlalchemy import Integer, Column, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, backref, sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()


class Department(Base):
    __tablename__ = ‘department‘
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(‘Employee‘, secondary=‘department_employee‘)


class Employee(Base):
    __tablename__ = ‘employee‘
    id = Column(Integer, primary_key=True)
    name = Column(String)
    departments = relationship(‘Department‘, secondary=‘department_employee‘)


class DepartmentEmployee(Base):
    __tablename__ = ‘department_employee‘
    department_id = Column(Integer, ForeignKey(‘department.id‘), primary_key=True)
    employee_id = Column(Integer, ForeignKey(‘employee.id‘), primary_key=True)


engine = create_engine("sqlite:///")
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

s = session()
john = Employee(name=‘Jhon‘)
s.add(john)
it_department = Department(name=‘IT‘)
it_department.employees.append(john)
s.add(it_department)
s.commit()
johnDB = s.query(Employee).filter(Employee.name == ‘Jhon‘).one()
print(johnDB.name)
print(johnDB.departments[0].name)