SQLAlchemy:镜像另一个表中的非主列

Question

我想要有一个表Goals，它与另一个表Hints是一对多关系，这意味着一个提示可以用于多个目标，但每个目标都有一个提示。现在，我希望Goals反映Hints中的两列。Hints有id (主)和penalty (非唯一)列，我希望Goals有hint_id和hint_penalty列，它们反映了链接提示的那些列。我理解如何镜像id，因为它是Hint中的主键，使用关系可以很好地在刷新时更新它，但penalty似乎不会被传播。

我的意思是：

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


Base = declarative_base()


class Hint(Base):
    __tablename__ = 'hint'
    id = Column(Integer, primary_key=True)
    penalty = Column(Integer, nullable=False)

class Goal(Base):
    __tablename__ = 'goal'
    id = Column(Integer, primary_key=True)
    hint_penalty = Column(
        Integer, ForeignKey('hint.penalty'))
    hint_id = Column(Integer, ForeignKey('hint.id'))
    hint = relationship(
        # this leaves hint_penalty blank
        Hint, foreign_keys=[hint_id],
        # this raises sqlalchemy.exc.AmbiguousForeignKeysError
        #  Hint, foreign_keys=[hint_id, hint_penalty],
        backref=backref('goals', uselist=True))


engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

g1 = Goal()
h1 = Hint(penalty=1, goals=[g1])
h2 = Hint(penalty=1)
g2 = Goal(hint=h2)

session.add(h1)
session.add(h2)
session.commit()

print((
    'g1.hint_id = {hid1}\n'
    'g1.hint_penalty = {hp1}\n'
    'g2.hint_id = {hid2}\n'
    'g2.hint_penalty = {hp2}\n'
).format(
    hid1=g1.hint_id,
    hp1=g1.hint_penalty,
    hid2=g2.hint_id,
    hp2=g2.hint_penalty))

无论我创建目标和提示的顺序是什么，goal.hint_penalty始终为None

g1.hint_id = 1
g1.hint_penalty = None
g2.hint_id = 2
g2.hint_penalty = None

如何在刷新时将hint.penalty传播到goal.hint_penalty？

我当然知道我可以通过goal.hint.penalty访问它，但对于我的情况来说，这是不可取的。

Answer 1

将适当的primaryjoin添加到关系中即可完成此任务：

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


Base = declarative_base()


class Hint(Base):
    __tablename__ = 'hint'
    id = Column(Integer, primary_key=True)
    penalty = Column(Integer, nullable=False)

class Goal(Base):
    __tablename__ = 'goal'
    id = Column(Integer, primary_key=True)
    hint_penalty = Column(
        Integer, ForeignKey('hint.penalty'))
    hint_id = Column(Integer, ForeignKey('hint.id'))
    hint = relationship(
        Hint,
        primaryjoin="and_(Hint.penalty == foreign(Goal.hint_penalty), "
                    "Hint.id == foreign(Goal.hint_id))",
        backref=backref('goals', uselist=True))