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

请看示例代码:

# 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>)

我期望在 relationship 上设置别名,输出 Enterprise 和 Salesman。

程序的关键,在于如何设置 enterprise 和 salesman 的别名。

请问这种方式如何设置别名?

阅读 1.3k
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题