Sqlalchemy是否真的具有一对一关系 [英] Does Sqlalchemy really have One to one relationships

问题描述

我有以下行为.警报可以更改状态，并且只能更改一个. 状态更改只能有一个警报.状态变化可能有一个原因，也可能是一些状态变化中的一个原因 我尝试了以下模式

I have the following scemantic. An alert can have a status change and only one. A status change can have only one alert. A status change can have one reason also a reason can be in maney status changes I tried the following schema

class Alert(BaseDb):
    __tablename__ = 'alerts'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='pk_alerts'),
    )

    id = Column(Integer)
    alert_text = Column(Text)

class AlertStateChange(BaseDb):
    __tablename__ = 'alert_state_change'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='pk_alert_state_change'),
        ForeignKeyConstraint(
            ['reason_id'],
            ['reasons.id'],
            name='fk_alert_status_change_reason_id__reasons'
        ),

        ForeignKeyConstraint(
            ['alert_id'],
            ['alerts.id'],
            name='fk_alert_status_change_alert_id__alert'
        ),


    )

    id = Column(Integer)
    reason_id = Column(Integer)
    alert_id = Column(Integer)
    reason = relationship('Reason', backref='status')
    alert = relationship('Alert',
                         backref=backref('status', uselist=False))
    status = Column(Text)

但是sqlalchemy允许我将同一警报(相同的alert_id)添加到AlertStateChange对象.它通常使用新的ID提交.在为数据库中的同一警报放置两个AlertStatusChange对象之后，尝试以下操作

but sqlalchemy lets me add to AlertStateChange objects for the same alert (same alert_id). It commits normally with a new id. After putting two AlertStatusChange objects for the same alert in the db trying the following

alert.status

给我以下警告

SAWarning: Multiple rows returned with uselist=False for lazily-loaded attribute 'Alert.status' % self.parent_property)

，返回的对象是添加的第一个AlertStateChange对象.第二个在数据库中，但被忽略.难道不应该引发例外情况吗?这不是真正的OneToOne关系.我可能应该将alert_id添加为主键或正确的唯一值?

and the object returned is the first AlertStateChange object added. The second is in the db but ignored. Shouldn't there be an exception raised? This isn't a real OneToOne relation. I should probably add the alert_id as a primary key or as a unique value correct?

推荐答案

执行以下操作:

• 在关系上设置uselist=False
• 在子unique=True
中设置引用列
• 您也可以在孩子上设置nullable=False
• 并且您可以添加到父级自定义__init__以实现严格的一对一

• Set uselist=False on relationship
• Set the referencing column in child unique=True
• You can also set nullable=False on child
• And you can add to Parent custom __init__ for strict one-to-one

现在它可以工作了.

class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    Child = relationship("Child", uselist=False, backref="Parent")

    def __init__(self,**kwargs):
        if 'Child' not in kwargs:
            raise RuntimeError('Need value')
        ...

class Child(Base):
    __tablename__ = 'childs'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parents.id'), unique=True)    

Base.metadata.create_all(engine)
session = Session(bind=engine)

ch1 = Child(Parent=Parent())
session.add(ch1)

p1 = Parent(Child=Child())
session.add(p1)

session.commit()

for row in session.query(Parent):
    print row.Child.id
for row in session.query(Child):
    print row.Parent.id

这篇关于Sqlalchemy是否真的具有一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！