search

pallets-eco / flask-sqlalchemy

Adds SQLAlchemy support to Flask
https://flask-sqlalchemy.readthedocs.io
BSD 3-Clause "New" or "Revised" License
4.22k stars 899 forks source link

NoForeignKeysError when foreign key is pointing to different database #1330

Closed andreasc-kuehn closed 5 months ago

andreasc-kuehn commented 5 months ago

After upgrading from Flask-SQLAlchemy 2.5.1 to 3.0.0 with unchanged dependencies (same Flask 2.2.0, same SQLAlchemy 1.4.51, Python 3.9), I am getting NoForeignKeysError on formerly working foreign key definition in cases where the foreign key points to a table in a different database.

I am using Mariadb, where foreign keys can be defined across databases by using "database.table.field".

I would expect to not happen the exception.

I errantly posted the bug in SQLAlchemy and it was converted to a discussion: https://github.com/sqlalchemy/sqlalchemy/discussions/11333

This is a shortened model definition that used to work:

class DataResourceRole(db.Model): bind_key__ = 'resource_bind' tablename = "DATA_RESOURCE_ROLE" table_args__ = ( ForeignKeyConstraint(name="DRES_RLE_RLETYP", columns=["ROLE_TYPE_ID"], refcolumns=["contact.ROLE_TYPE.ROLE_TYPE_ID"], ondelete="RESTRICT", onupdate="CASCADE") ,{'schema': 'resource'} )
DATA_RESOURCE_ID = Column("DATA_RESOURCE_ID", Integer, nullable=False, primary_key=True, )
CONTACT_ID = Column("CONTACT_ID", Integer, nullable=False, primary_key=True, )
ROLE_TYPE_ID = Column("ROLE_TYPE_ID", String(length=20), nullable=False, primary_key=True, ) ROLE_TYPE = relationship("RoleType")

class RoleType(db.Model): bind_key__ = 'contact_bind' tablename = "ROLE_TYPE" __table_args = {'schema': 'contact'}
ROLE_TYPE_ID = Column("ROLE_TYPE_ID", String(length=20), nullable=False, primary_key=True, )

And this is the exception that occurs. I have tracked down the cause to the fact that SQLAlchemy cannot find the primary key when the __table_args__ = [..], {'schema': 'schemaname'} is involved.

Traceback (most recent call last): File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2754, in _determine_joins self.primaryjoin = join_condition( File "", line 2, in join_condition File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1229, in _join_condition raise exc.NoForeignKeysError( sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'DATA_RESOURCE_ROLE' and 'ROLE_TYPE'.

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

Traceback (most recent call last): File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2486, in call return self.wsgi_app(environ, start_response) File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2466, in wsgi_app response = self.handle_exception(e) File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2463, in wsgi_app response = self.full_dispatch_request() File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1760, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1758, in full_dispatch_request rv = self.dispatch_request() File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1734, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(view_args) File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 284, in decorated_view elif not current_user.is_authenticated: File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/werkzeug/local.py", line 316, in get obj = instance._get_current_object() # type: ignore[misc] File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/werkzeug/local.py", line 516, in _get_current_object return get_name(local()) # type: ignore File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 25, in current_user = LocalProxy(lambda: _get_user()) File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 372, in _get_user current_app.login_manager._load_user() File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/login_manager.py", line 364, in _load_user user = self._user_callback(user_id) File "/home/develop/Projects/Python/ibench1/app/auth/models_userlogin.py", line 185, in load_user return db.session.get(UserLogin, (int(uid))) File "", line 2, in get File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2853, in get return self._get_impl( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2975, in _get_impl return db_load_fn( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/loading.py", line 530, in load_on_pk_identity session.execute( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1717, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1569, in _execute_clauseelement compiled_sql, extracted_params, cache_hit = elem._compile_w_cache( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 532, in _compile_w_cache compiled_sql = self._compiler( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 567, in _compiler return dialect.statement_compiler(dialect, self, kw) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 809, in init Compiled.init(self, dialect, statement, kwargs) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 464, in init self.string = self.process(self.statement, compile_kwargs) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 499, in process return obj._compiler_dispatch(self, kwargs) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch return meth(self, kw) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 3403, in visit_select compile_state = select_stmt._compile_state_factory( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/base.py", line 510, in create_for_statement return klass.create_for_statement(statement, compiler, **kw) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 701, in create_for_statement _QueryEntity.to_compile_state( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 2458, in to_compile_state _MapperEntity( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 2531, in init entity._post_inspect File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1184, in get obj.dict[self.name] = result = self.fget(obj) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 2199, in _post_inspect self._check_configure() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1941, in _check_configure _configure_registries({self.registry}, cascade=True) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3527, in _configure_registries _do_configure_registries(registries, cascade) File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3566, in _do_configure_registries mapper._post_configure_properties() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1958, in _post_configure_properties prop.init() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init self.do_init() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2152, in do_init self._setup_join_conditions() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2248, in _setup_join_conditions self._join_condition = jc = JoinCondition( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2643, in init self._determine_joins() File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2776, in _determinejoins util.raise( File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship DataResourceRole.ROLE_TYPE - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.