[Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key co
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2014-01-27 16:18:32
|
#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
----------------------+-----------------------------------------
Reporter: vrobin | Owner: zzzeek
Type: defect | Status: new
Priority: medium | Milestone:
Component: postgres | Severity: no triage selected yet
Keywords: | Progress State: awaiting triage
----------------------+-----------------------------------------
A program that worked nicely on SQLAlchemy 0.8.X have begun to fail with
0.9.X upgrade.
The code that fails is this one:
{{{
engine = create_engine("postgresql+psycopg2://xxxxxx")
metadata = MetaData(bind=engine, schema="mymainschema")
metadata.reflect()
}}}
Error thrown is:
{{{
packages/sqlalchemy/dialects/postgresql/base.py", line 1690, in
get_table_oid
raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: tableinsecondarychema
}}}
Relashionship looks like (simplified):
{{{
CREATE TABLE mymainschema.myfirsttable
(
name character varying(255),
city_id character varying(255),
CONSTRAINT myfirsttable_pkey PRIMARY KEY (name),
CONSTRAINT fkdc7e8de5969fc80 FOREIGN KEY (city_id)
REFERENCES secondaryschema.tableinsecondarychema (city_code) MATCH
SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
}}}
I managed to workaround this problem with a quick and very dirty change in
get_table_oid function ( dialects/postgresql/base.py ):
{{{
if schema is not None:
schema = util.text_type(schema)
# CHANGE BEGIN
if table_name == 'tableinsecondarychema':
schema = u'secondaryschema'
# CHANGE END
s = sql.text(query).bindparams(table_name=sqltypes.Unicode)
}}}
But this is very ugly, and the problem looks like a bug. What do you think
about it? If you need more information, just let me know.
Regards,
Robin
PS: detailed trace of the problem
{{{
Traceback (most recent call last):
File "import.py", line 10, in <module>
from loid.connectors.xls_parser_monobloc import XlsReader
File "/home/jerry/mygreatapp/loid/connectors/xls_parser_monobloc.py",
line 35, in <module>
from xls_schedule_parser_base import \
File "/home/jerry/mygreatapp/loid/connectors/xls_parser_base.py", line
328, in <module>
from loid.dao import *
File "/home/jerry/mygreatapp/loid/dao.py", line 237, in <module>
metadata.reflect()
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 3015, in reflect
Table(name, self, **reflect_opts)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 355, in __new__
table._init(name, metadata, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 429, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 446, in _autoload
self, include_columns, exclude_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1107, in run_callable
return callable_(self, *args, **kwargs)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 308, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
**reflection_options
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 355, in __new__
table._init(name, metadata, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 429, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 446, in _autoload
self, include_columns, exclude_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1107, in run_callable
return callable_(self, *args, **kwargs)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 308, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
**reflection_options
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 355, in __new__
table._init(name, metadata, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 429, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 446, in _autoload
self, include_columns, exclude_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1107, in run_callable
return callable_(self, *args, **kwargs)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 308, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
**reflection_options
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 355, in __new__
table._init(name, metadata, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 429, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 446, in _autoload
self, include_columns, exclude_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1107, in run_callable
return callable_(self, *args, **kwargs)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 308, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 563, in reflecttable
**reflection_options
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 355, in __new__
table._init(name, metadata, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 429, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/sql/schema.py", line 446, in _autoload
self, include_columns, exclude_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1107, in run_callable
return callable_(self, *args, **kwargs)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 308, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 464, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 277, in get_columns
**kw)
File "<string>", line 2, in get_columns
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 53, in cache
ret = fn(self, con, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/dialects/postgresql/base.py", line 1774, in
get_columns
info_cache=kw.get('info_cache'))
File "<string>", line 2, in get_table_oid
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 53, in cache
ret = fn(self, con, *args, **kw)
File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
packages/sqlalchemy/dialects/postgresql/base.py", line 1690, in
get_table_oid
raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: tableinsecondarychema
}}}
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|