在没有外键的情况下在SQLalchemy ORM中指定连接条件 [英] Specify join condition in SQLalchemy ORM without foreign key

问题描述

我在SQLAlchemy中有两个模型，这些模型已自动加入外键，就像这样:

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')

这很好用，我可以很容易地从孩子的父母那里找到父母.现在，由于超出此问题范围的技术原因，我不得不摆脱数据库中的外键.我试图在SQLAlchemy中解决该问题，但是没有任何替换代码(使用primaryjoins或backrefs)起作用.我在此处看到了另一个答案，该答案只是对SQLAlchemy的谎言并告诉我我具有外键关系，但这使Alembic尝试在我自动生成的每个新修订版上创建外键关系，这确实很烦人.什么是正确的方法?

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?

推荐答案

要使relationship工作，可以指定显式连接条件:

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

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

要使与Child的连接有效，可以指定关系而不是实体:

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)

也可以使用SQLAlchemy.您可以通过指定 include_object 参数:

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

这篇关于在没有外键的情况下在SQLalchemy ORM中指定连接条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！