I create a Python object to encapsulate a link to existing postgreSQL database using SQLAlchemy under flask. I link to the SQLALCHEMY database using reflection.
The init code for the object is here:
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 ()
This code successfully loads a set of tables, including this one:
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)
Examining the database metadata indicates the program has loaded the foreign key definitions correctly:
locallanguage foreign_keys {ForeignKey('languages.idlanguage')} <class 'set'>
owner foreign_keys {ForeignKey('accounts.idaccounts')} <class 'set'>
However, when a select with join is executed, as follows:
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))
The following error is produced:
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
Can anyone tell me why the select routine fails to identify the join columns as mapped entities?
The join clauses should refer to ORM classes, rather than foreign key columns.
This should work:
select = (dbase.select (localStrings, languages, accounts).
join (accounts).
join (languages).
where (localStrings.category == category))
The generated SQL would be similar to this:
SELECT localstrings.idlabelstrings,
localstrings.category,
localstrings.locallanguage,
localstrings.owner,
languages.idlanguage,
accounts.idaccounts
FROM localstrings
JOIN accounts
ON accounts.idaccounts = localstrings.owner
JOIN languages
ON languages.idlanguage = localstrings.locallanguage
WHERE localstrings.category = ?
SQLAlchemy uses the foreign key definitions to create the ON clauses in the query. If localstrings.owner and localstrings.locallanguage where not defined as foreign keys then you would need to tell SQLAlchemy how to make the joins:
select = (
sa.select(localStrings, languages, accounts)
.join(accounts, localStrings.owner == accounts.idaccounts)
.join(languages, localStrings.locallanguage == languages.idlanguage)
.where(localStrings.category == category)
)
Finally, note that Automap will automatically construct relationships if it can detect them, so you can also use these relationships to express the join:
select = (
sa.select(localStrings, accounts, languages)
.join(localStrings.accounts)
.join(localStrings.languages)
.where(localStrings.category == category)
)