我在FASK下使用SQLAlChemy创建了一个Python对象来封装到现有PostgreSQL数据库的链接.我使用反射链接到SQLALCHEMY数据库.
该对象的init代码如下:
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 ()
此代码成功加载了一组表，其中包括以下表:
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'>
但是，在执行SELECT WITH 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.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
谁能告诉我为什么SELECT routine 无法将连接列标识为映射实体？