Flask下SQLAlchemy关联查询报错：关联目标未指向映射实体

问题描述 #

在Flask框架下使用SQLAlchemy封装PostgreSQL数据库连接，采用反射方式关联现有数据库。初始化已成功加载包括localstrings在内的多张表，数据库元数据显示外键定义加载正确，但执行包含join的查询语句时，出现错误：

sqlalchemy.exc.ArgumentError: Join target localstrings.owner does not refer to a mapped entity

相关代码与信息 #

初始化代码 #

def __init__ (self, app, url) :
    
    app.config ['SQLALCHEMY_DATABASE_URI'] = url
    self.db = SQLAlchemy (app)
    with app.app_context () :
        self.db.reflect ()
        self.md = self.db.metadata
        self.base = automap_base (metadata = self.md)
        self.base.prepare ()

localstrings表结构 #

Column     |           Type           | Collation | Nullable 
     ---------------+--------------------------+-----------+---------
     idlabelstrings | integer                  |           | not null
     category       | character varying(4)     |           |         
     name           | character varying(45)    |           |         
     value          | character varying(16000) |           |         
     locallanguage  | integer                  |           | not null
     owner          | integer                  |           | not null 
    Indexes:
        "localstrings_pkey" PRIMARY KEY, btree (idlabelstrings)
        "localstrings_name_idx" btree (name)
        "localstrings_owner_idx" btree (owner)
    Foreign-key constraints:
        "fk_labelstrings_languages1" FOREIGN KEY (locallanguage) REFERENCES languages(idlanguage)
        "fk_localstrings_owner1" FOREIGN KEY (owner) REFERENCES accounts(idaccounts)

外键元数据 #

locallanguage   foreign_keys    {ForeignKey('languages.idlanguage')}    <class 'set'>
    owner           foreign_keys    {ForeignKey('accounts.idaccounts')} <class 'set'>

查询代码（报错版本） #

dbase = self.db
localStrings = self.base.classes.localstrings
accounts = self.base.classes.accounts
languages = self.base.classes.languages
select = (dbase.select (localStrings, languages, accounts).
          join (localStrings.owner).
          join (localStrings.locallanguage).
          where (localStrings.category == category))

完整报错信息 #

2023-10-11 18:59:41,072] ERROR in app: Exception on /liststrings [GET]
Traceback (most recent call last):
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 1792, in _join
    right = right.entity
            ^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 1327, in     __getattr__
    return self._fallback_getattr(key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 1296, in _fallback_getattr
    raise AttributeError(key)
AttributeError: entity

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

Traceback (most recent call last):
  File "endpoints\venv\Lib\site-packages\flask\app.py", line 2190, in wsgi_app
    response = self.full_dispatch_request()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\flask\app.py", line 1486, in full_dispatch_request
    rv = self.handle_user_exception(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\flask\app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\flask\app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\application\app.py", line 124, in stringLister
    result = dataSrc.getStrings ('en', 'wcs', 'public')
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\application\dblink.py", line 92, in getStrings
    return dbase.session.execute (select).scalars () #.mappings ()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\scoping.py", line 738, in execute
    return self._proxied.execute(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2262, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2144, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
    return meth(
           ^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1627, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
                                                ^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 704, in _compile_w_cache
    compiled_sql = self._compiler(
                   ^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 316, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\compiler.py", line 1426, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\compiler.py", line 867, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\compiler.py", line 912, in process
    return obj._compiler_dispatch(self, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\compiler.py", line 4585, in visit_select
    compile_state = select_stmt._compile_state_factory(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\sql\base.py", line 687, in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 1162, in create_for_statement
    self._setup_for_generate()
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 1197, in _setup_for_generate
    self._join(query._setup_joins, self._entities)
  File "endpoints\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 1794, in _join
    raise sa_exc.ArgumentError(
    sqlalchemy.exc.ArgumentError: Join target localstrings.owner does not refer to a mapped entity

问题原因与解决方法 #

原因分析 #

你当前的写法join(localStrings.owner)是错误的：localStrings.owner是表的列对象，而SQLAlchemy ORM的join()方法需要传入的是映射实体类，或者是实体间通过automap自动生成的关联属性，而非单个字段。

正确写法 #

以下三种方式任选其一即可：

方式1：自动关联实体类（推荐）

让SQLAlchemy根据外键约束自动推断关联条件：

dbase = self.db
localStrings = self.base.classes.localstrings
accounts = self.base.classes.accounts
languages = self.base.classes.languages
select = (dbase.select(localStrings, languages, accounts)
          .join(accounts)  # 自动匹配localStrings.owner -> accounts.idaccounts
          .join(languages)  # 自动匹配localStrings.locallanguage -> languages.idlanguage
          .where(localStrings.category == category))

方式2：显式指定关联条件

如果存在多外键等需要明确关联逻辑的场景，可手动指定关联条件：

dbase = self.db
localStrings = self.base.classes.localstrings
accounts = self.base.classes.accounts
languages = self.base.classes.languages
select = (dbase.select(localStrings, languages, accounts)
          .join(accounts, localStrings.owner == accounts.idaccounts)
          .join(languages, localStrings.locallanguage == languages.idlanguage)
          .where(localStrings.category == category))

方式3：使用automap生成的关联属性

Automap反射后会为实体生成关联属性（比如localStrings.accounts对应owner外键的关联实体），可直接用该属性join：

dbase = self.db
localStrings = self.base.classes.localstrings
accounts = self.base.classes.accounts
languages = self.base.classes.languages
select = (dbase.select(localStrings, languages, accounts)
          .join(localStrings.accounts)
          .join(localStrings.languages)
          .where(localStrings.category == category))

注：若关联属性未生成，可检查dir(localStrings)确认automap是否正确识别了外键关联。

内容的提问来源于stack exchange，提问作者John Spragge