foreign key complaints with sdss5db targetdb tables

[havok2063]

Describe the bug
I am getting complaints about the sqlalchemy targetdb tables Assignment and ProgramToTarget not having foreign keys whenever I try to load these models and perform a simple query. I can import the database and models ok but running a query triggers the failure.

AttributeError: type object 'Assignment' has no attribute 'foreign_keys'
AttributeError: type object 'ProgramToTarget' has no attribute 'foreign_keys'

The full traceback is down below.

To Reproduce

from sdssdb.sqlalchemy.sdss5db import database as sdss5db, targetdb
[WARNING]: support for catalogdb in SQLalchemy is incomplete. Use Peewee instead. (SdssdbWarning)

sdss5db
<SDSS5dbDatabaseConnection (dbname='sdss5db', profile='local', connected=True)>

session=sdss5db.Session()
session.query(targetdb.Program).first()

Additional context
I dumped my existing targetdb schema and reloaded the latest one from at https://github.com/sdss/sdssdb/blob/master/schema/sdss5db/targetdb/targetdb.sql

Full traceback

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-4-591e4c97997d> in <module>
----> 1 session.query(targetdb.Program).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()
   3247                 if not mapper.configured:
   3248                     try:
-> 3249                         mapper._post_configure_properties()
   3250                         mapper._expire_memoizations()
   3251                         mapper.dispatch.mapper_configured(

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py in _post_configure_properties(self)
   1945
   1946             if prop.parent is self and not prop._configure_started:
-> 1947                 prop.init()
   1948
   1949             if prop._configure_finished:

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/interfaces.py in init(self)
    194         """
    195         self._configure_started = True
--> 196         self.do_init()
    197         self._configure_finished = True
    198

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py in do_init(self)
   1913         self._check_conflicts()
   1914         self._process_dependent_arguments()
-> 1915         self._setup_join_conditions()
   1916         self._check_cascade_settings(self._cascade)
   1917         self._post_init()

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py in _setup_join_conditions(self)
   1993             prop=self,
   1994             support_sync=not self.viewonly,
-> 1995             can_be_synced_fn=self._columns_are_mapped,
   1996         )
   1997         self.primaryjoin = jc.primaryjoin

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py in __init__(self, parent_persist_selectable, child_persist_selectable, parent_local_selectable, child_local_selectable, primaryjoin, secondary, secondaryjoin, parent_equivalents, child_equivalents, consider_as_foreign_keys, local_remote_pairs, remote_side, self_referential, prop, support_sync, can_be_synced_fn)
   2337         self.support_sync = support_sync
   2338         self.can_be_synced_fn = can_be_synced_fn
-> 2339         self._determine_joins()
   2340         self._sanitize_joins()
   2341         self._annotate_fks()

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py in _determine_joins(self)
   2437                         self.secondary,
   2438                         a_subset=self.child_local_selectable,
-> 2439                         consider_as_foreign_keys=consider_as_foreign_keys,
   2440                     )
   2441                 if self.primaryjoin is None:

<string> in join_condition(a, b, ignore_nonexistent_tables, a_subset, consider_as_foreign_keys)

<string> in _join_condition(cls, a, b, ignore_nonexistent_tables, a_subset, consider_as_foreign_keys)

~/anaconda3/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 ""

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py in _join_condition(cls, a, b, ignore_nonexistent_tables, a_subset, consider_as_foreign_keys)
    926         """
    927         constraints = cls._joincond_scan_left_right(
--> 928             a, a_subset, b, consider_as_foreign_keys
    929         )
    930

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py in _joincond_scan_left_right(cls, a, a_subset, b, consider_as_foreign_keys)
    980                 continue
    981             for fk in sorted(
--> 982                 b.foreign_keys, key=lambda fk: fk.parent._creation_order
    983             ):
    984                 if (

AttributeError: type object 'Assignment' has no attribute 'foreign_keys'

[albireox]

I'm not really maintaining the SQLA part of sdss5db and I'm not surprised it does not work. Since things are in flux right now I would not put a lot of effort in fixing something that can change again later. It may be better to reduce the confusion by removing the schema from sqlalchemy.

[albireox]

I ended up removing the SQLA implementation of sdss5db. The schema is quite in flux and I prefer to not give the option of using it in SQLA if it's not correct. We can reintroduce it later once things are fixed.