CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
lovesh
lovesh

Reputation: 5401

Unable to create self referencing foreign key in flask-sqlalchemy

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.

Upvotes: 12

Views: 18437

Answers (5)

bmakan
bmakan

Reputation: 431

Here's a solution for SQLA 2.0+ and type hints:

from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Node(Base):
    __tablename__ = 'node'

    id: Mapped[int] = mapped_column(autoincrement=True, primary_key=True)
    parent_id: Mapped[Optional[int]] = mapped_column(sa.ForeignKey('node.id'))
    # Other columns ...

    # --- Relationships ---
    children: Mapped[list['Node']] = relationship(back_populates='parent', remote_side=[id])
    parent: Mapped['Node'] = relationship(back_populates='children')

It's important to set the remote_side so the SQLA knows how to link children. Then it's the usual reference via back_referencing (backref doesn't work well with type hints).

Upvotes: 1

davidism
davidism

Reputation: 127200

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.

Upvotes: 15

dbokers
dbokers

Reputation: 920

If you use a schema for any table, other tables that have foreign keys referencing those schema tables must provide the name of the schema. See the docs here 

class Table(db.Model):
    __tablename__ = 'table_1_name'
    __table_args__ = {'schema': 'my_schema'}

    id = Column('id', Integer, primary_key=True)
    ...

class AnotherTable(db.Model):
    __tablename__ = 'table_2_name'
    # Doesn't matter if this belongs to the same or different schema
    # __table_args__ = {'schema': 'my_schema'}

    id = Column('id', Integer, primary_key=True)
    t1_id = Column(Integer, ForeignKey('my_schema.table_1_name.id'))
    ...

Works for both SQLAlchemy and Flask-SQLAlchemy. Hope this helps. :D

Upvotes: 8

Jobu
Jobu

Reputation: 631

I only see slight differences from @davidism, but here's what works for me in straight SQLAlchemy. 

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import backref

class Region(Base):
  __tablename__ = 'region'

  id = Column(Integer, primary_key=True)
  parent_id = Column(Integer, ForeignKey('region.id'), index=True)
  sub_regions = relationship('Region', backref=backref('parent', remote_side='Region.id'))

As he points out I'm guessing you wont need the imports, but should prefix them with db, so something like:

class Region(db.Model):
  __tablename__ = 'region'

  id = db.Column(db.Integer, primary_key=True)
  parent_id = db.Column(db.Integer, db.ForeignKey('region.id'), index=True)
  sub_regions = db.relationship('Region', backref=db.backref('parent', remote_side='Region.id'))

Upvotes: 4

braaksman
braaksman

Reputation: 91

I had the same issue with the schema name argument. What I changed to get it to work was to reference the table class directly in ForeignKey and relationships instead of using a string. Example:

parent_id = Column(Integer, ForeignKey(Region.id), index=True)

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

Upvotes: 9

Related Questions