Specify join condition in SQLalchemy ORM without foreign key

Question

I have two models in SQLAlchemy that I had automatically joining on a foreign key, like so:

class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    name = Column(String(300), nullable=False)
    metadata_id = Column(Integer, nullable=True, index=True)

class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    name = Column(String(300), nullable=False)
    parent_metadata_id = \
        Column(ForeignKey('parents.metadata_id'),
               nullable=True, primary_key=True)
    parent = relationship(u'Parent')

This worked fine and I could get easily access a parent from its children. Now, for technical reasons beyond the scope of this question, I had to get rid of the foreign key in my db. I've tried to get around that in SQLAlchemy, but none of the replacement code (using primaryjoins, or backrefs) has worked. I saw another answer here that just says to lie to SQLAlchemy and tell it I have the foreign key relationship, but this makes Alembic try to create the foreign key relationship on every new revision I autogenerate, which is really annoying. What's the right way to do this?

Downvoter, can you explain what your issue is with my question? — Eli, Mar 24 '16 at 20:36

score 7 · Accepted Answer · edited Dec 19 '19 at 18:48

7

To make the relationship work, you can specify the explicit join condition:

parent = relationship(Parent, primaryjoin=parent_metadata_id == Parent.metadata_id)

To make joins to Child work, you can specify the relationship instead of the entity:

session.query(Child).join(Child.parent)

Or, specify the join condition explicitly:

session.query(Child).join(Child, Child.parent_metadata_id == Parent.metadata_id)

Lying to SQLAlchemy also works. You can make alembic ignore the foreign key by specifying the include_object parameter:

class Child(Base):
    ...
    parent_metadata_id = Column(ForeignKey( ... ), info={"skip_autogenerate": True}, ...)

def include_object(object, name, type_, reflected, compare_to):
    if not reflected and object.info.get("skip_autogenerate", False):
        return False
    return True

edited Dec 19 '19 at 18:48

Saransh Singh

730
4
11

answered Mar 24 '16 at 21:13

univerio

19,548
3
66
68

This worked for me once I put the `info` param outside of ForeignKey( ... ). I edited your answer to reflect that. Thanks for the help! – Eli Mar 24 '16 at 21:51
@Eli That makes alembic skip the column altogether. Perhaps you're using an older version of alembic (it didn't use to support foreign keys in `include_object` before 0.7.1)? – univerio Mar 24 '16 at 21:58
you're right. I'm on Alembic 0.8.5 now and I can place `info` in ForeignKey( ... ), but it just gets ignored now. It works fine when placed in a column though, but then as you said, the whole column is ignored. – Eli Mar 24 '16 at 22:50

Specify join condition in SQLalchemy ORM without foreign key

1 Answers1

Linked