Re: [sqlalchemy] Joining tables across schema in PostgreSQL

[Mike Bayer]

On 08/10/2016 12:06 PM, Demitri Muna wrote:

There's nothing like hitting a brick wall of a problem that you
definitively solved years ago.



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.

you should have search_path set to "public".   Since you're already 
familiar with this problem, look at the names that are in 
metadata.tables.keys(), that will show you the table names w/ schemas 
that it came up with.   Also set echo='debug' on create_engine() to see 
what it's reflecting and how it's getting it back.





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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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.