Relationship between classes with __bind_key__ defined throws InvalidRequestError

[graedo-ogd]

When defining a relationship between two classes via association class and query to one of them it raise the InvalidRequestError exception.

Define a minimal flask app, with six (three as default and three with bind key) classes and a second bind for the configuration.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy import ForeignKey, select
from typing import List

db = SQLAlchemy()
app = Flask(__name__)
app.config.from_mapping({
    'SECRET_KEY': 'dev',
    'SQLALCHEMY_DATABASE_URI': 'sqlite:///db.sqlite',
    'SQLALCHEMY_BINDS': {
        'auth': 'sqlite:///auth.sqlite'
    }
})


class ClassA(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manye: Mapped[List["ClassB"]] = relationship(
        "ClassE", secondary="class_f", back_populates="manya")


class ClassB(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manyc: Mapped[List["ClassC"]] = relationship(
        "ClassC", secondary="class_d", back_populates="manyb")


class ClassC(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manyb: Mapped[List["ClassB"]] = relationship(
        "ClassB", secondary="class_d", back_populates="manyc")


class ClassD(db.Model):
    __bind_key__ = "auth"
    b_id: Mapped[int] = mapped_column(
        ForeignKey('class_b.id'), primary_key=True)
    c_id: Mapped[int] = mapped_column(
        ForeignKey('class_c.id'), primary_key=True)


class ClassE(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manya: Mapped[List["ClassA"]] = relationship(
        "ClassA", secondary="class_f", back_populates="manye")


class ClassF(db.Model):
    a_id: Mapped[int] = mapped_column(
        ForeignKey('class_a.id'), primary_key=True)
    e_id: Mapped[int] = mapped_column(
        ForeignKey('class_e.id'), primary_key=True)

    db.init_app(app)


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


@app.route("/")
def index():
    return db.session.execute(select(ClassB)).scalars().all()


@app.route("/good")
def good():
    return db.session.execute(select(ClassA)).scalars().all()


if __name__ == "__main__":
    app.run(debug=True)

Traceback:

127.0.0.1 - - [09/Aug/2024 06:22:35] "GET /good HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/clsregistry.py", line 532, in __call__
    x = eval(self.arg, globals(), self._dict)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 1, in <module>
NameError: name 'class_d' is not defined

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 1498, in __call__
    return self.wsgi_app(environ, start_response)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 1476, in wsgi_app
    response = self.handle_exception(e)
               ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 1473, in wsgi_app
    response = self.full_dispatch_request()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 882, in full_dispatch_request
    rv = self.handle_user_exception(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/test_app.py", line 77, in good
    return db.session.execute(select(ClassA)).scalars().all()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/scoping.py", line 778, in execute
    return self._proxied.execute(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2247, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1632, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
                                                ^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 703, in _compile_w_cache
    compiled_sql = self._compiler(
                   ^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 316, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 1429, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 870, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
    return obj._compiler_dispatch(self, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 4679, in visit_select
    compile_state = select_stmt._compile_state_factory(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py", line 683, in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 1097, in create_for_statement
    _QueryEntity.to_compile_state(
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 2552, in to_compile_state
    _MapperEntity(
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 2632, in __init__
    entity._post_inspect
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 1253, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
                                           ^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2711, in _post_inspect
    self._check_configure()
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2388, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4204, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4245, in _do_configure_registries
    mapper._post_configure_properties()
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2405, in _post_configure_properties
    prop.init()
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/interfaces.py", line 584, in init
    self.do_init()
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1641, in do_init
    self._process_dependent_arguments()
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1682, in _process_dependent_arguments
    rel_arg._resolve_against_registry(self._clsregistry_resolvers[1])
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 270, in _resolve_against_registry
    self.resolved = clsregistry_resolver(
                    ^^^^^^^^^^^^^^^^^^^^^
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/clsregistry.py", line 539, in __call__
    self._raise_for_name(n.args[0], n)
  File "/path/to/project/.venv/lib/python3.12/site-packages/sqlalchemy/orm/clsregistry.py", line 500, in _raise_for_name
    raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper[ClassB(class_b)], expression 'class_d' failed to locate a name ("name 'class_d' is not defined"). If this is a class name, consider adding this relationship() to the <class '__main__.ClassB'> class after both dependent classes have been defined.

When removing the relationship from classes with bind key it works.

class ClassB(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    # manyc: Mapped[List["ClassC"]] = relationship(
    #     "ClassC", secondary="class_d", back_populates="manyb")


class ClassC(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    # manyb: Mapped[List["ClassB"]] = relationship(
    #     "ClassB", secondary="class_d", back_populates="manyc")

Also tested with creating tables.

Environment:

• Python version: 3.12.4
• Flask-SQLAlchemy version: 3.1.1
• SQLAlchemy version: 2.0.32

[graedo-ogd]

If you are doing something like this:

class_d = db.Table(
    'class_d',
    Column('b_id', Integer, ForeignKey(
        'class_b.id'), primary_key=True),
    Column('c_id', Integer, ForeignKey(
        'class_c.id'), primary_key=True),
    bind_key="auth"
)


class ClassB(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manyc: Mapped[List["ClassC"]] = relationship(
        secondary=class_d, back_populates="manyb")


class ClassC(db.Model):
    __bind_key__ = "auth"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str] = mapped_column(unique=True, nullable=False)
    manyb: Mapped[List["ClassB"]] = relationship(
        secondary=class_d, back_populates="manyc")

it works. Any suggestion? I mean it's a workaround, but with all classes defined without a bind key it works proper.

[mohammad-reza-rahbar]

I have the same problem. Using secondary=lambda: class_d works too. It is discussed here, but I expect the string version of it works too, but it does not.