# db_init.py

from typing import List

from sqlalchemy import create_engine, Integer, ForeignKey, Table, Column
from sqlalchemy.orm import declarative_base, sessionmaker, Mapped, mapped_column, relationship

engine = create_engine('sqlite:///many_to_many.db', echo=True)
Base = declarative_base()

use_role_table = Table(
    'user_role',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('user.id'), primary_key=True),
    Column('role_id', Integer, ForeignKey('role.id'), primary_key=True)
)

class Role(Base):
    __tablename__ = 'role'

    id:Mapped[int] = mapped_column(primary_key=True)
    name:Mapped[str]

    # back_populates这个参数的意思就是当用户变化时自动同步User类的roles属性
    users:Mapped[List['User']] = relationship(secondary=use_role_table, lazy=True, back_populates='roles')

    def __repr__(self) -> str:
        return f'id:{self.id} name:{self.name}'

class User(Base):
    __tablename__ = 'user'

    id:Mapped[int] = mapped_column(primary_key=True)
    name:Mapped[str]

    # back_populates这个参数的意思就是当用户变化时自动同步Role类的users属性
    roles:Mapped[List['Role']] = relationship(secondary=use_role_table, lazy=False, back_populates='users')

    def __repr__(self) -> str:
        return f'id:{self.id}, name:{self.name}'

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

调用代码如下

#query.py
from db_init import Session, User, Role

def insert_user(s):
    r1 = Role(name='市场部')
    r2 = Role(name='研发部')

    u1 = User(name='张三')
    u2 = User(name='李四')
    u3 = User(name='王五')

    u1.roles.append(r1)
    u1.roles.append(r2)

    u2.roles.append(r1)

    u3.roles.append(r2)

    s.add_all([u1,u2,u3])

    s.commit()

def select_user(s):
    u = s.query(User).filter(User.id == 1).one()
    print(u)
    for r in u.roles:
        print(r)

def select_role(s):
    r = s.query(Role).filter(Role.id==1).one()
    print(r)
    for u in r.users:
        print(u)

session = Session()
insert_user(session)
# select_user(session)
# select_role(session)