无法在flask-sqlalchemy中创建自引用外键 [英] Unable to create self referencing foreign key in flask-sqlalchemy

问题描述

我有一个模型Region，每个Region都可以有子区域.每个子区域都有一个字段parent_id，它是其父区域的ID.这是我的模型的样子

I have a model Region and each Region can have sub-regions. Each sub-region has a field parent_id which is the id of its parent region. Here is how my model looks like

class Region(db.Model):
    __tablename__ = 'regions'
    __table_args__ = {'schema': 'schema_name'}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    parent_id = db.Column(db.Integer, db.ForeignKey('regions.id'))
    parent = db.relationship('Region', primaryjoin=('Region.parent_id==Region.id'), backref='sub-regions')
    created_at = db.Column(db.DateTime, default=db.func.now())
    deleted_at = db.Column(db.DateTime)

我尝试执行db.create_all时遇到错误sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'regions.parent_id' could not find table 'regions' with which to generate a foreign key to target column 'id'

Bu when i try to do db.create_all i get this error sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'regions.parent_id' could not find table 'regions' with which to generate a foreign key to target column 'id'

为什么我在__tablename__中指定它时找不到regions?我正在使用flask-sqlalchemy 1.0版

Why cant it find regions when i am specifying it in __tablename__? I am using flask-sqlalchemy version 1.0

编辑- 我删除了线

__table_args__ = {'schema': 'schema_name'}

从我的代码开始，它可以工作.击败我.

from my code and it works. Beats the hell out of me.

推荐答案

您必须告诉SQLAlchemy关系的远程"是什么，以区分当前行和要连接的行.相关说明位于此部分的中间有关关系的文档.

You must tell SQLAlchemy what the "remote side" of the relationship is, to distinguish between the current row and the row being joined to. The relevant explanation is located partway into this section of the documentation on relationships.

这种关系可能看起来像这样:

The relationship might look like this:

parent = db.relationship('Region', remote_side=id, backref='sub_regions')

这是一个展示自我参照关系的示例:

This is an example demonstrating a self-referential relationship:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(engine)
Base = declarative_base(engine)

session = Session()


class Region(Base):
    __tablename__ = 'region'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    parent_id = Column(Integer, ForeignKey('region.id'), index=True)

    parent = relationship(lambda: Region, remote_side=id, backref='sub_regions')


Base.metadata.create_all()

r1 = Region(name='United States of America')
r2 = Region(name='California', parent=r1)

session.add_all((r1, r2))
session.commit()

ca = session.query(Region).filter_by(name='California').first()
print ca.parent.name

由于打开了echo，因此将有很多行SQL输出，脚本将在末尾打印美国".

There will be many lines of SQL output since echo is on, and the script will print 'United States of America' at the end.

这篇关于无法在flask-sqlalchemy中创建自引用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！