Unable to create self referencing foreign key in flask-sqlalchemy

flask, flask-sqlalchemy, python, sqlalchemy

Solution

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

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

Problem

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) ``` 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'` Why cant it find `regions` when i am specifying it in `__tablename__`? I am using flask-sqlalchemy version 1.0 EDIT -- i removed the line ``` __table_args__ = {'schema': 'schema_name'} ``` from my code and it works. Beats the hell out of me.

Original source