SQLA alchemy fails to import if database is not connected

[albireox]

In this example if you import directly the schema, for a connection that initially does not connect to the database you get an error

In [1]: from sdssdb.sqlalchemy.sdss5db import catalogdb
----------------------------------------------------------------------
NoSuchTableError                     Traceback (most recent call last)
<ipython-input-1-5d093eb6f97f> in <module>
----> 1 from sdssdb.sqlalchemy.sdss5db import catalogdb

~/Documents/Code/sdssv/sdssdb/python/sdssdb/sqlalchemy/sdss5db/catalogdb.py in <module>
    227
    228
--> 229 database.add_base(Base)

~/Documents/Code/sdssv/sdssdb/python/sdssdb/connection.py in add_base(self, base, prepare)
    458
    459             if prepare and self.connected:
--> 460                 self.prepare_bases(base=base)
    461
    462         def prepare_bases(self, base=None):

~/Documents/Code/sdssv/sdssdb/python/sdssdb/connection.py in prepare_bases(self, base)
    473
    474             for base in do_bases:
--> 475                 base.prepare(self.engine)
    476
    477                 # If the base has an attribute _relations that's the function

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in prepare(cls, engine)
    756         to_map = _DeferredMapperConfig.classes_for_base(cls)
    757         for thingy in to_map:
--> 758             cls._sa_decl_prepare(thingy.local_table, engine)
    759             thingy.map()
    760             mapper = thingy.cls.__mapper__

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in _sa_decl_prepare(cls, local_table, engine)
    788         # into the existing Table object.
    789         if local_table is not None:
--> 790             cls._reflect_table(local_table, engine)
    791
    792     @classmethod

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in _reflect_table(cls, table, engine)
    809             autoload=True,
    810             autoload_with=engine,
--> 811             schema=table.schema,
    812         )
    813

<string> in __new__(cls, *args, **kw)

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py in warned(fn, *args, **kwargs)
    126                     )
    127
--> 128             return fn(*args, **kwargs)
    129
    130         doc = fn.__doc__ is not None and fn.__doc__ or ""

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/sql/schema.py in __new__(cls, *args, **kw)
    480             table = metadata.tables[key]
    481             if extend_existing:
--> 482                 table._init_existing(*args, **kw)
    483             return table
    484         else:

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/sql/schema.py in _init_existing(self, *args, **kwargs)
    696                 exclude_columns,
    697                 resolve_fks,
--> 698                 _extend_on=_extend_on,
    699             )
    700

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/sql/schema.py in _autoload(self, metadata, autoload_with, include_columns, exclude_columns, resolve_fks, _extend_on)
    607                 exclude_columns,
    608                 resolve_fks,
--> 609                 _extend_on=_extend_on,
    610             )
    611         else:

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs)
   2158         """
   2159         with self._contextual_connect() as conn:
-> 2160             return conn.run_callable(callable_, *args, **kwargs)
   2161
   2162     def execute(self, statement, *multiparams, **params):

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs)
   1610
   1611         """
-> 1612         return callable_(self, *args, **kwargs)
   1613
   1614     def _run_visitor(self, visitorcallable, element, **kwargs):

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/default.py in reflecttable(self, connection, table, include_columns, exclude_columns, resolve_fks, **opts)
    429         insp = reflection.Inspector.from_engine(connection)
    430         return insp.reflecttable(
--> 431             table, include_columns, exclude_columns, resolve_fks, **opts
    432         )
    433

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py in reflecttable(self, table, include_columns, exclude_columns, resolve_fks, _extend_on)
    644
    645         for col_d in self.get_columns(
--> 646             table_name, schema, **table.dialect_kwargs
    647         ):
    648             found_table = True

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py in get_columns(self, table_name, schema, **kw)
    371
    372         col_defs = self.dialect.get_columns(
--> 373             self.bind, table_name, schema, info_cache=self.info_cache, **kw
    374         )
    375         for col_def in col_defs:

<string> in get_columns(self, connection, table_name, schema, **kw)

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw)
     54     ret = info_cache.get(key)
     55     if ret is None:
---> 56         ret = fn(self, con, *args, **kw)
     57         info_cache[key] = ret
     58     return ret

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py in get_columns(self, connection, table_name, schema, **kw)
   2788
   2789         table_oid = self.get_table_oid(
-> 2790             connection, table_name, schema, info_cache=kw.get("info_cache")
   2791         )
   2792         SQL_COLS = """

<string> in get_table_oid(self, connection, table_name, schema, **kw)

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw)
     54     ret = info_cache.get(key)
     55     if ret is None:
---> 56         ret = fn(self, con, *args, **kw)
     57         info_cache[key] = ret
     58     return ret

~/.pyenv/versions/3.7.4/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py in get_table_oid(self, connection, table_name, schema, **kw)
   2702         table_oid = c.scalar()
   2703         if table_oid is None:
-> 2704             raise exc.NoSuchTableError(table_name)
   2705         return table_oid
   2706

NoSuchTableError: sdss_dr14_cannonstar

But if you do

In [1]: from sdssdb.sqlalchemy.sdss5db import database

In [2]: database.connect(user='sdss', port=6667)
Out[2]: True

then you can do

from sdssdb.sqlalchemy.sdss5db import catalogdb

without additional problems.

[havok2063]

I can't recreate this particular error. For example this command to import the sas schema from the archive database, which I do not have locally, imports fine.

from sdssdb.sqlalchemy.archive import sas

However I do get probably related errors. I do encounter a problem when I import the schema for a database that fails to connect and then try to import a database and schema that I do have.

from sdssdb.sqlalchemy.archive import database, sas
database
<ArchiveDatabaseConnection (dbname='archive_20200302', profile='local', connected=False)>

from sdssdb.sqlalchemy.mangadb import database as mangadb, datadb
mangadb
<MANGAdbDatabaseConnection (dbname='manga', profile='local', connected=True)>

session = mangadb.Session()
session.query(datadb.Cube).first()
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/_collections.py in __getattr__(self, key)
    209         try:
--> 210             return self._data[key]
    211         except KeyError:

KeyError: 'type'

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
<ipython-input-10-5f15a5829806> in <module>
----> 1 session.query(datadb.Cube).first()

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py in query(self, *entities, **kwargs)
   1551         :class:`.Session`."""
   1552
-> 1553         return self._query_cls(entities, self, **kwargs)
   1554
   1555     @property

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py in __init__(self, entities, session)
    189         self.session = session
    190         self._polymorphic_adapters = {}
--> 191         self._set_entities(entities)
    192
    193     def _set_entities(self, entities, entity_wrapper=None):

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py in _set_entities(self, entities, entity_wrapper)
    217                 entity_wrapper(self, ent)
    218
--> 219             self._set_entity_selectables(self._entities)
    220
    221     def _set_entity_selectables(self, entities):

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py in _set_entity_selectables(self, entities)
    248
    249                     d[entity] = (ext_info, aliased_adapter)
--> 250                 ent.setup_entity(*d[entity])
    251
    252     def _mapper_loads_polymorphically_with(self, mapper, adapter):

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py in setup_entity(self, ext_info, aliased_adapter)
   4169         self.selectable = ext_info.selectable
   4170         self.is_aliased_class = ext_info.is_aliased_class
-> 4171         self._with_polymorphic = ext_info.with_polymorphic_mappers
   4172         self._polymorphic_discriminator = ext_info.polymorphic_on
   4173         self.entity_zero = ext_info

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py in __get__(self, obj, cls)
    853         if obj is None:
    854             return self
--> 855         obj.__dict__[self.__name__] = result = self.fget(obj)
    856         return result
    857

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py in _with_polymorphic_mappers(self)
   2133     def _with_polymorphic_mappers(self):
   2134         if Mapper._new_mappers:
-> 2135             configure_mappers()
   2136         if not self.with_polymorphic:
   2137             return []

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py in configure_mappers()
   3218             has_skip = False
   3219
-> 3220             Mapper.dispatch._for_class(Mapper).before_configured()
   3221             # initialize properties on all mappers
   3222             # note that _mapper_registry is unordered, which

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    259
    260         for fn in self.parent_listeners:
--> 261             fn(*args, **kw)
    262
    263     def __len__(self):

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/events.py in wrap(*arg, **kw)
    644                     arg[target_index] = arg[target_index].obj()
    645                 if not retval:
--> 646                     fn(*arg, **kw)
    647                     return interfaces.EXT_CONTINUE
    648                 else:

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py in before_configured()
    210             @event.listens_for(mapper, "before_configured")
    211             def before_configured():
--> 212                 self.cls.__declare_first__()
    213
    214     def _scan_attributes(self):

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in __declare_first__(cls)
    617     @classmethod
    618     def __declare_first__(cls):
--> 619         cls._sa_decl_prepare_nocascade()
    620
    621     @classmethod

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in _sa_decl_prepare_nocascade(cls)
    662         to_map.mapper_args_fn = mapper_args
    663
--> 664         m = to_map.map()
    665
    666         for scls in cls.__subclasses__():

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py in map(self)
    763     def map(self):
    764         self._configs.pop(self._cls, None)
--> 765         return super(_DeferredMapperConfig, self).map()
    766
    767

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py in map(self)
    685
    686     def map(self):
--> 687         self._prepare_mapper_arguments()
    688         if hasattr(self.cls, "__mapper_cls__"):
    689             mapper_cls = util.unbound_method_to_callable(

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py in _prepare_mapper_arguments(self)
    627         properties = self.properties
    628         if self.mapper_args_fn:
--> 629             mapper_args = self.mapper_args_fn()
    630         else:
    631             mapper_args = {}

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/ext/declarative/api.py in mapper_args()
    657         def mapper_args():
    658             args = m_args()
--> 659             args["polymorphic_on"] = pjoin.c.type
    660             return args
    661

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/_collections.py in __getattr__(self, key)
    210             return self._data[key]
    211         except KeyError:
--> 212             raise AttributeError(key)
    213
    214     def __contains__(self, key):

AttributeError: type

I only import the database itself without the schema, then everything works fine

from sdssdb.sqlalchemy.archive import database
from sdssdb.sqlalchemy.mangadb import database as mangadb, datadb
session = mangadb.Session()
session.query(datadb.Cube).first()
<Cube (pk=7767, plateifu='7995-6102', version='v1_5_1')>

I've tried resetting the engine and clearing out bases on the disconnected database but that doesn't help. So it might have something to do with how we're calling declarative_base and DeferredReflection. Maybe all models end up being uber-connected. I searched around a bit but couldn't find an obvious answer or fix at the moment.

This might be related to #6.