NoReferencedTableError with schema

[halfdan]

Similar issues:

• NoForeignKeysError with polymorphism and schemas #512, here it's claimed the issue is fixed with rewrite tablename generation again #541
• specifying schema name in model gives error #172, looks similar but I'm unclear if it's actually the same problem

Traceback (most recent call last):
  File "app.py", line 63, in <module>
    db.create_all()
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 963, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 955, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
    tables=tables)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 736, in visit_metadata
    [t for t in tables if self._can_create_table(t)])
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 1101, in sort_tables_and_constraints
    dependent_on = fkc.referred_table
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 3003, in referred_table
    return self.elements[0].column.table
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/halfdan/flsqla-schema/.venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 1892, in column
    tablekey)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'company_list_item.company_id' could not find table 'companies' with which to generate a foreign key to target column 'id'

Code that reproduces the error:

from flask import Flask
from sqlalchemy import (
    Column,
    Integer,
    Text,
    func,
    String,
    Boolean,
    DateTime,
    Date,
    TIMESTAMP,
    ForeignKey,
    or_,
    and_,
    UniqueConstraint,
)
from sqlalchemy.orm import relationship, backref
from sqlalchemy.sql import expression
from sqlalchemy.ext.declarative import AbstractConcreteBase
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://localhost/flsqla"
db = SQLAlchemy()

class List(db.Model):
    __tablename__ = "lists"
    __table_args__ = {"schema": "public"}

    id = Column(Integer, primary_key=True)
    label = Column(String(50), nullable=False)
    items = relationship("ListItem", back_populates="llist")


class Company(db.Model):
    __tablename__ = "companies"
    __table_args__ = {"schema": "public"}

    id = Column(Integer, primary_key=True)
    label = Column(String(50), nullable=False)
    tracked = Column(Boolean, server_default=expression.true(), nullable=False)


class ListItem(db.Model):
    __abstract__ = True
    id = Column(Integer, primary_key=True)


class CompanyListItem(ListItem):
    __tablename__ = "company_list_item"
    __table_args__ = {"schema": "public"}

    company_id = Column(ForeignKey("companies.id", deferrable=True, initially="DEFERRED"), nullable=False)
    company = relationship("Company", primaryjoin="CompanyListItem.company_id == Company.id")

    __mapper_args__ = {"polymorphic_identity": "company_item", "concrete": True}


with app.app_context():
    db.init_app(app)
    db.create_all()

Removing the table args fixes the problem but it's clearly a bug that even though all models live in the same schema it can't resolve the foreign keys.

[davidism]

Does this work in plain SQLAlchemy?

According to https://stackoverflow.com/a/47465718/400617 you must reference the other schema in the ForeignKey, even if the models belong to the same schema.

[davidism]

Closing, I'm assuming it was solved by specifying the schema in the foreign key.