Sqlalchemy可以使用额外的字段进行多对多的映射

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addresses = relationship('Address', secondary='user_addresses')

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    users = relationship('User', secondary='user_addresses')

class UserAddress(Base):
    __tablename__ = 'user_addresses'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    address_id = Column(Integer, ForeignKey('address.id'), primary_key=True)
    extra_field = Column(String)

engine = create_engine('sqlite:///sqlalchemy_example.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# 创建一个用户和地址
user1 = User(name='John Doe')
address1 = Address(email_address='john.doe@example.com')

# 添加多对多关系
user1.addresses.append(address1)
session.add(user1)
session.commit()

# 查询多对多关系
user = session.query(User).filter_by(name='John Doe').first()
addresses = user.addresses
for address in addresses:
    print(f"User: {user.name}, Address: {address.email_address}")

# 更新额外字段
user_address = session.query(UserAddress).filter_by(user_id=user1.id, address_id=address1.id).first()
user_address.extra_field = 'example field'
session.commit()