SQLAlchemy not finding tables in two postgres separate schemas at same time

[Alex Petralia]

SQLAlchemy version: 1.0.12

I originally raised this issue as a bug with pandas but I believe it is actually an issue with SQLAlchemy: https://github.com/pydata/pandas/issues/13210#issuecomment-219881388

I have a SQLAlchemy Engine object:

In[11]: SQLALCHEMY_CONN
Out[11]: Engine(postgresql://***:***@***:***/***)

In the schema a, I have a table named ads_skus which has a foreign key reference to a table sku in the public schema.

In[12]: SQLALCHEMY_CONN.has_table('ads_skus', schema='a')
Out[12]: True

SQLAlchemy can find the table in the same schema, but not the foreign table in the public schema:

In[13]: 
from sqlalchemy.schema import MetaData
meta = MetaData(SQLALCHEMY_CONN, schema='a')
meta.reflect(only=['ads_skus'], views=True)
Out[13]: NoSuchTableError: sku

[Mike Bayer]

workaround:

m = MetaData(c)

m.reflect(schema='test_schema')

e.g. don't use the 'schema' argument in MetaData, as the reflection use case was not considered when this argument was added.

[Alex Petralia]

My problem isn't so much with the reflect function but rather pandas not being able to see the foreign table from a different schema.

The only true workaround I can think of is to remove the foreign key constraint on my ads_skus table. Do you think this a tweak that should be changed in pandas or is the failure in SQLAlchemy?

[Mike Bayer]

https://gerrit.sqlalchemy.org/#/q/I3d24f99c22cded206c5379fd32a225e74edb7a8e

[Mike Bayer]

My problem isn't so much with the reflect function but rather pandas not being able to see the foreign table from a different schema.

I don't understand the problem, unless it is that pandas is hardcoded to pass the "schema" parameter to the MetaData object and you can't change that. If that parameter is omitted, and "schema" is instead passed to the reflect() function, there is no issue.

[Joris Van den Bossche]

@zzzeek the code snippet above (using meta = MetaData(engine, schema=schema); meta.reflect(only=[table_name], views=True)) is indeed from the pandas code inside read_sql_table (see https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L355)

So we should better change this to meta = MetaData(engine); meta.reflect(only=[table_name], views=True, schema=schema)?

[Mike Bayer]

that would work a lot better, yes, because you want to point the reflection process at that schema, but not unconditionally set every Table associated with that MetaData to that schema. The fix coming in is a special symbol BLANK_SCHEMA which allows Table to force itself to have "None" for a schema even when "schema" is set on MetaData and the reflection process will use this internally now as appropriate.

[Mike Bayer]

Support "blank" schema when MetaData.schema is set

Previously, it was impossible to have a Table that has None for a schema name when the "schema" parameter on MetaData was set. A new symbol sqlalchemy.schema.BLANK_SCHEMA is added which indicates that the schema name should unconditionally be set to None. In particular, this value must be passed within cross-schema foreign key reflection, so that a Table which is in the "default" schema can be represented properly.

Fixes: #3716 Change-Id: I3d24f99c22cded206c5379fd32a225e74edb7a8e

→ <<cset c124fa36d5af>>

[Mike Bayer]

Support "blank" schema when MetaData.schema is set

Previously, it was impossible to have a Table that has None for a schema name when the "schema" parameter on MetaData was set. A new symbol sqlalchemy.schema.BLANK_SCHEMA is added which indicates that the schema name should unconditionally be set to None. In particular, this value must be passed within cross-schema foreign key reflection, so that a Table which is in the "default" schema can be represented properly.

Fixes: #3716 Change-Id: I3d24f99c22cded206c5379fd32a225e74edb7a8e (cherry picked from commit c124fa36d5af2c85c87c51d24e92387adffbe3d2)

→ <<cset 4b31736343f1>>

[Alex Petralia]

Thank you very much for your help!