sqlalchemy: one-to-one relationship with declarative

Question or problem about Python programming:

What is the best way to create a one-to-one relationship in SQLAlchemy using declarative?

I have two tables, foo and bar, and I want foo.bar_id to link to bar. The catch is that this is a one-way one-to-one relationship. bar must not know anything about foo. For every foo, there will be one and only one bar.

Ideally, after selecting a foo, I could do something like this:

myfoo.bar.whatever = 5 

What’s the best way to accomplish this using declarative?

How to solve the problem:

Solution 1:

If you want a true one-to-one relationship, you also have to use the “uselist=False” in your relationship definition.

bar_id = Column(Integer, ForeignKey(Bar.id))
bar = relationship(Bar, uselist=False)

Solution 2:

The documentation explains this nicely:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

OR

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref=backref("parent", uselist=False))

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Solution 3:

I think if it is a truly one to one relationship we should add a uniqueness constraint to foreign key so another parent can not have other parent child!! Like this:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'), unique=True)
    child = relationship("Child", backref=backref("parent", uselist=False))

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Solution 4:

It turns out this is actually quite easy. In your Foo model:

bar_id = Column(Integer, ForeignKey(Bar.id))
bar = relationship(Bar)

Hope this helps!