How do you specify the foreign key column value in SQLAlchemy?

Question

One of my models has the following relationship:

class User(Base):
    account     = relationship("Account")

I would like to set the account id manually.

My first attempt was this:

class User(Base):
    account     = relationship("Account")
    accounts_id = Column(Integer, ForeignKey("accounts.id"), nullable=True)

    @classmethod
    def from_json(cls, json):
        appointment = Appointment()
        appointment.account_id = json["account_id"]
        return appointment

The above dosen't work. We can't refer to this column because SQLAlchemy throws a fit. This is the exception:

sqlalchemy.exc.InvalidRequestError: Implicitly combining column users.accounts_id with column users.accounts_id under attribute 'accounts_id'.  Please configure one or more attributes for these same-named columns explicitly.

I've tried hunting through the docs and expermiented with getting to the attribute numerous ways but I haven't been able to find, much less set it.

  print(self.account.account_id)
  print(self.account.relationhip)
  print(self.account.properties)
  print(self.account.primaryjoin)

Any ideas?

[Edit- added exception above]

Answer 1

Use the Account class to define the relationship, and add the backref keyword argument:

from sqlalchemy.orm import relationship

class User(Base):

    accounts_id = Column(Integer, ForeignKey('account.id'))

class Account(Base):

    users = relationship('User', backref='account')

When the backref keyword is used on a single relationship, it’s exactly the same as if the above two relationships were created individually using back_populates on each.

References

• Linking Relationships with Backref

• Controlling Cascade on Backrefs

• SQLAlchemy ORM Examples