Could not locate any relevant foreign key columns with mixin and declared_attr

[Charles-Axel Dein]

The following code:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Boolean

Base = declarative_base()


class Transaction(Base):
    __tablename__ = 'transactions'
    id = Column(Integer, primary_key=True)
    invoice_id = Column(Integer, ForeignKey('invoices.id'))


class InvoiceMixin(object):

    __tablename__ = 'invoices'

    id = Column(Integer, primary_key=True)
    approved = Column(Boolean)

    @declared_attr
    def transactions(cls):
        return relationship(
            'Transaction',
            primaryjoin=lambda: Transaction.invoice_id == cls.id
            # primaryjoin=lambda: remote(Transaction.invoice_id) == cls.id
            # primaryjoin='Transaction.invoice_id == %s.id' % cls.__name__,
        )


class Invoice1(InvoiceMixin, Base):
    __table_args__ = {'extend_existing': True}


class Invoice2(InvoiceMixin, Base):
    __table_args__ = {'extend_existing': True}


def main():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(Invoice1).get(0)


if __name__ == '__main__':
    main()

Will fail with the following:

Traceback (most recent call last):
  File "reproduce_sqla_bug.py", line 53, in <module>
    main()
  File "reproduce_sqla_bug.py", line 49, in main
    session.query(Invoice1).get(0)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1151, in query
    return self._query_cls(entities, self, **kwargs)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 106, in __init__
    self._set_entities(entities)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 116, in _set_entities
    self._set_entity_selectables(self._entities)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 149, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2980, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1840, in _with_polymorphic_mappers
    configure_mappers()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2544, in configure_mappers
    mapper._post_configure_properties()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1657, in _post_configure_properties
    prop.init()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 143, in init
    self.do_init()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1512, in do_init
    self._setup_join_conditions()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1588, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1856, in __init__
    self._check_foreign_cols(self.primaryjoin, True)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2374, in _check_foreign_cols
    raise sa_exc.ArgumentError(err)
sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'transactions.invoice_id = invoices.id' on relationship Invoice1.transactions.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

If I don't specify the primaryjoin, it will be ok. If I remove one of the subclass (e.g. Invoice1), it will be ok. My guess is that this come from _setup_pairs's deannotation step. The first time the relationship, it's gonna get rid of the annotation and the next time it runs, it won't have access to them. Actually, after having a second look, maybe it's not what's happening there. Am I missing anything?

Thanks a lot Mike!

[Mike Bayer]

there's no bug here, this code attempts to ignore a very important error by mis-using "extend existing". The original "invoice.id" column is being removed from the Table entirely because, like the error pre-extend-existing says, it's being replaced. the following code illustrates:

class Invoice1(InvoiceMixin, Base):
    __table_args__ = {'extend_existing': True}

# here's the "id" column we added to Invoice1.__table__
id_number_one_a = Invoice1.__table__.c.id

# but wait, another class is here, and is calling the "extend_existing"
# flag for some odd reason, meaning it wishes to *replace all the columns
# on the table* with its own
class Invoice2(InvoiceMixin, Base):
   __table_args__ = {'extend_existing': True}

# so lets look at "id" again, on our Invoice1.__table__...
id_number_one_b = Invoice1.__table__.c.id

# and "id" on Invoice2.__table__, which is the *same* table
# as that of Invoice1
id_number_two = Invoice2.__table__.c.id

# These are *two different columns* !
assert id_number_one_a is not id_number_two

# but the new "Invoice1.__table__.c.id", *thats* the right
# column now
assert id_number_one_b is id_number_two

# but Invoice1.id, the mapping we made, *still points to the column
# that we replaced!*
assert Invoice1.id.property.columns[0] is id_number_one_a


# but it was booted from the Table, and is not referred to by Transaction.__table__.c.invoice_id
# hence, no foreign key is set up here.
assert not Transaction.__table__.c.invoice_id.references(id_number_one_a)


# that column *is not even correctly mapped!* it's been blown away from
# the Table altogehter, this mapping is entirely wrong.  The warning
# we had about "extend_existing" means something.  so there is *no way*
# to refer to this column in the primaryjoin
assert not Invoice1.__table__.c.contains_column(id_number_one_a)
assert Invoice1.__table__.c.contains_column(id_number_one_b)

[Mike Bayer]

any number of subclasses can be made from a declarative mapped class, just create the subclass and it is mapped as single table inheritance. in the absense of polymorphic, whichever class you refer to is the one you'll get.

class Invoice1(Invoice):
    pass

class Invoice2(Invoice):
    pass

configure_mappers()

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

i2 = Invoice2()
s.add(i2)
s.commit()
s.close()

print s.query(Invoice2).all()

[Charles-Axel Dein]

Thanks a lot!

I'm not sure what warning you refer to...

This is what we were trying to achieve:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Boolean

Base = declarative_base()


class Transaction(Base):
    __tablename__ = 'transactions'
    id = Column(Integer, primary_key=True)
    invoice_id = Column(Integer, ForeignKey('invoices.id'))


class InvoiceMixin(object):

    __tablename__ = 'invoices'

    id = Column(Integer, primary_key=True)
    approved = Column(Boolean)

    @declared_attr
    def transactions(cls):
        return relationship(
            'Transaction',
            primaryjoin=lambda: Transaction.invoice_id == cls.id
        )


class Invoice1(InvoiceMixin, Base):
    pass


class Invoice2(InvoiceMixin, Base):
    pass


def main():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(Invoice1).get(0)


if __name__ == '__main__':
    main()

This was a very naive way to set it up and we knew it would fail. The problem is that the traceback recommends using extend_existing:

Traceback (most recent call last):
  File "reproduce_sqla_bug.py", line 40, in <module>
    class Invoice2(InvoiceMixin, Base):
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py", line 53, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 251, in _as_declarative
    **table_kw)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 339, in __new__
    "existing Table object." % key)
sqlalchemy.exc.InvalidRequestError: Table 'invoices' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I know this is quite a naive way to do the setup, but still pretty intuitive... A better way is effectively to do:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Boolean

Base = declarative_base()


class Transaction(Base):
    __tablename__ = 'transactions'
    id = Column(Integer, primary_key=True)
    invoice_id = Column(Integer, ForeignKey('invoices.id'))


class Invoice(Base):

    __tablename__ = 'invoices'

    id = Column(Integer, primary_key=True)
    approved = Column(Boolean)

    @declared_attr
    def transactions(cls):
        return relationship(
            'Transaction',
            primaryjoin=lambda: Transaction.invoice_id == cls.id
        )


class V2Invoice(Invoice):
    pass


def main():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(V2Invoice).get(0)


if __name__ == '__main__':
    main()

[Mike Bayer]

what do you hope to achieve by mapping two different classes to the same table and why cant the base of the two classes simply be mapped as well?

also OK to close here? there's no bug being demonstrated.

[Charles-Axel Dein]

Invalid.

[Charles-Axel Dein]

Thanks for your help Mike! We figured it out, the solution is not really interesting as it's really specific to the structure of the object in this implementation.