SQLAlchemy multiple foreign keys in one mapped class to the same primary key

by

Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths

Changed in version 0.8: relationship() can resolve ambiguity between
foreign key targets on the basis of the foreign_keys argument alone;
the primaryjoin argument is no longer needed in this situation.

Self-contained code below works with sqlalchemy>=0.9:

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

engine = create_engine(u'sqlite:///:memory:', echo=True)
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()

#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)

class Stakeholder(Base):
    __tablename__ = 'stakeholder'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    company = relationship("Company", foreign_keys=[company_id])
    stakeholder = relationship("Company", foreign_keys=[stakeholder_id])

Base.metadata.create_all(engine)

# simple query test
q1 = session.query(Company).all()
q2 = session.query(Stakeholder).all()

More Related Contents:

  1. How to write DataFrame to postgres table
  2. CS50: LIKE operator, variable substitution with % expansion
  3. SQLAlchemy create_all() does not create tables
  4. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  5. How to create an SQL View with SQLAlchemy?
  6. Generate sql with subquery as a column in select statement using SQLAlchemy
  7. Updates to JSON field don’t persist to DB
  8. Complex foreign key constraint in SQLAlchemy
  9. Connection problems with SQLAlchemy and multiple processes
  10. How to upsert pandas DataFrame to PostgreSQL table?
  11. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  12. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  13. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  14. Creating partial unique index with sqlalchemy on Postgres
  15. SQLAlchemy func.count on boolean column
  16. How to set connection timeout in SQLAlchemy
  17. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  18. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  19. Python Postgres psycopg2 ThreadedConnectionPool exhausted
  20. SQLAlchemy: unexpected results when using `and` and `or`
  21. sqlalchemy: how to join several tables by one query?
  22. Serve image stored in SQLAlchemy LargeBinary column
  23. ImportError: No module named MySQLdb
  24. How do I select literal values in an sqlalchemy query?
  25. Get inserted key before commit session
  26. Create a Postgres database using python
  27. Flask-SQLAlchemy Constructor
  28. Is possible to mapping view with class using mapper in SqlAlchemy?
  29. How does COPY work and why is it so much faster than INSERT?
  30. SQLAlchemy ordering by count on a many to many relationship

Leave a Comment