- edited description
SQLAlchemy not finding tables in two postgres separate schemas at same time
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
Comments (10)
-
reporter -
repo owner 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.
-
reporter My problem isn't so much with the
reflect
function but ratherpandas
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 inpandas
or is the failure in SQLAlchemy? -
repo owner -
repo owner 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.
-
@zzzeek the code snippet above (using
meta = MetaData(engine, schema=schema); meta.reflect(only=[table_name], views=True)
) is indeed from the pandas code insideread_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)
? -
repo owner 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.
-
repo owner - changed status to resolved
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:
#3716Change-Id: I3d24f99c22cded206c5379fd32a225e74edb7a8e→ <<cset c124fa36d5af>>
-
repo owner 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:
#3716Change-Id: I3d24f99c22cded206c5379fd32a225e74edb7a8e (cherry picked from commit c124fa36d5af2c85c87c51d24e92387adffbe3d2)→ <<cset 4b31736343f1>>
-
reporter Thank you very much for your help!
- Log in to comment