sqlalchemy relationship 两个字段指向同一个表，如何设置别名？

Z时代
2024-02-08
分类：IT

请看示例代码：

# coding=utf-8
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class User(Base):
    """用户模型"""
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    pid = Column(Integer, nullable=False, default=0)
    name = Column(String(30), nullable=False, unique=True)
class Trade(Base):
    """订单模型"""
    __tablename__ = 'trade'
    id = Column(Integer, primary_key=True)
    enterprise_id = Column(Integer, nullable=False, default=0)
    salesman_id = Column(Integer, nullable=False, default=0)
    enterprise = relationship(
        'User',
        primaryjoin='foreign(Trade.enterprise_id) == User.id',
    )
    salesman = relationship(
        'User',
        primaryjoin='foreign(Trade.salesman_id) == User.id',
    )
# 初始化数据库链接
uri = "mysql+pymysql://root:rootpassword@192.168.10.181:3306/test2"
engine = create_engine(uri)
DBSession = sessionmaker(bind=engine)
Base.metadata.create_all(engine)
# 插入模拟数据
session = DBSession()
if session.query(User).get(1) is None:
    user = User(id=1, pid=1, name='企业用户')
    session.add(user)
    session.commit()
if session.query(User).get(2) is None:
    session = DBSession()
    user = User(id=2, pid=2, name='销售用户')
    session.add(user)
    session.commit()
if session.query(Trade).get(1) is None:
    session = DBSession()
    trade = Trade(id=1, enterprise_id=1, salesman_id=2)
    session.add(trade)
    session.commit()
session = DBSession()
query_stmt = session.query(Trade)
query_stmt = query_stmt.join(Trade.enterprise, isouter=True)
query_stmt = query_stmt.add_columns(User)
# 以下代码开启会报错
# query_stmt = query_stmt.join(Trade.salesman, isouter=True)
# query_stmt = query_stmt.add_columns(User)
rows = query_stmt.all()
print(rows[0])
# 输出 (<Trade 1>, <User 1>)# 期望输出 (<Trade 1>, <Enterprise 1>, <Salesman 1>)