There's nothing like hitting a brick wall of a problem that you definitively solved years ago.
I am trying to join two tables across two schema in PostgreSQL. Which was solved here: https://groups.google.com/d/msg/sqlalchemy/iLXMXBIkYiA/sHNyNwFui4kJ and has been used successfully since. But I'm now (with a new database) getting this error: sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'file.fits_file.dataset_release_pk = trillian.dataset_release.pk' on relationship FitsFile.datasetRelease. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. The (truncated) table definitions: CREATE TABLE file.fits_file ( pk bigint NOT NULL DEFAULT nextval('fits_file_pk_seq'::regclass), dataset_release_pk integer, CONSTRAINT fits_file_dataset_release_fk FOREIGN KEY (dataset_release_pk) REFERENCES trillian.dataset_release (pk) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE trillian.dataset_release ( pk serial NOT NULL, ); Great. My model classes are: class FitsFile(Base): __tablename__ = 'fits_file' __table_args__ = {'autoload' : True, 'schema' : 'file'} class DatasetRelease(Base): __tablename__ = 'dataset_release' __table_args__ = {'autoload' : True, 'schema' : 'trillian'} None of these variations work (where I'm only using one at a time, and expect the first to work): [1] FitsFile.datasetRelease = relationship(DatasetRelease, backref="fitsFiles") [2] DatasetRelease.fitsFiles = relationship(FitsFile, primaryjoin=FitsFile.dataset_release_pk==DatasetRelease.pk, backref="datasetRelease") [3] FitsFile.datasetRelease = relationship(DatasetRelease, primaryjoin=FitsFile.dataset_release_pk==DatasetRelease.pk, backref="fitsFiles") Case [3] gives the error above; case [1] returns with: sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship FitsFile.datasetRelease - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression. Right before any of these statements I print FitsFile.__table__.foreign_key_constraints and get the wall of text below where I *do* see the foreign key constraint, but SQLAlchemy isn't playing ball. Have I missed something obvious? I'm using the code at the end of the thread linked above to effectively remove all tables from the search_path to force SQLAlchemy to reference tables explicitly by schema name. I'm using SQLAlchemy 1.0.13. Any help appreciated! Cheers, Demitri --- Foreign keys: {ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x7f2eeba3ae10>, None, name='base_path_fk', onupdate='CASCADE', ondelete='RESTRICT', link_to_name=True, table=Table('fits_file', MetaData(bind=Engine(postgresql://trillian_admin:***@localhost:5432/trilliandb)), Column('pk', BIGINT(), table=<fits_file>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x7f2eeba331d0>, for_update=False)), Column('dataset_release_pk', INTEGER(), ForeignKey('trillian.dataset_release.pk'), table=<fits_file>), Column('filename', TEXT(), table=<fits_file>), Column('relative_path', TEXT(), table=<fits_file>), Column('base_path_pk', INTEGER(), ForeignKey('file.base_path.pk'), table=<fits_file>), Column('size', INTEGER(), table=<fits_file>), Column('file_kind_pk', INTEGER(), ForeignKey('file.file_kind.pk'), table=<fits_file>), Column('sha256_hash', TEXT(), table=<fits_file>), schema='file')), ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x7f2eeba3a940>, None, name='fits_file_dataset_release_fk', onupdate='CASCADE', ondelete='CASCADE', link_to_name=True, table=Table('fits_file', MetaData(bind=Engine(postgresql://trillian_admin:***@localhost:5432/trilliandb)), Column('pk', BIGINT(), table=<fits_file>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x7f2eeba331d0>, for_update=False)), Column('dataset_release_pk', INTEGER(), ForeignKey('trillian.dataset_release.pk'), table=<fits_file>), Column('filename', TEXT(), table=<fits_file>), Column('relative_path', TEXT(), table=<fits_file>), Column('base_path_pk', INTEGER(), ForeignKey('file.base_path.pk'), table=<fits_file>), Column('size', INTEGER(), table=<fits_file>), Column('file_kind_pk', INTEGER(), ForeignKey('file.file_kind.pk'), table=<fits_file>), Column('sha256_hash', TEXT(), table=<fits_file>), schema='file')), ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x7f2eeba33320>, None, name='file_kind_fk', onupdate='CASCADE', ondelete='RESTRICT', link_to_name=True, table=Table('fits_file', MetaData(bind=Engine(postgresql://trillian_admin:***@localhost:5432/trilliandb)), Column('pk', BIGINT(), table=<fits_file>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x7f2eeba331d0>, for_update=False)), Column('dataset_release_pk', INTEGER(), ForeignKey('trillian.dataset_release.pk'), table=<fits_file>), Column('filename', TEXT(), table=<fits_file>), Column('relative_path', TEXT(), table=<fits_file>), Column('base_path_pk', INTEGER(), ForeignKey('file.base_path.pk'), table=<fits_file>), Column('size', INTEGER(), table=<fits_file>), Column('file_kind_pk', INTEGER(), ForeignKey('file.file_kind.pk'), table=<fits_file>), Column('sha256_hash', TEXT(), table=<fits_file>), schema='file')) } -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
