I have the following models:
class A(Base):
__tablename__="tableA"
id = Column(Integer, primary_key=True, index=True)
name = Column(String, unique=True)
coordinates= Column(Geometry(srid=4326,geometry_type="POINT"))
b_rel = relationship("B")
class B(Base):
__tablename__="tableB"
id = Column(Integer, primary_key=True, index=True)
b_rel = Column(ForeignKey(parking_lot.ParkingLot.id),nullable=False)
I am using geoalchemy2 to for spatial coordinates so a query would look like this
stmt = select(A.id,A.name,A.coordinates.ST_AsGeoJson().label('coordinates'))
While i would normaly load relationship with the following statement:
stmt = select(A.id,A.name,A.coordinates.ST_AsGeoJson().label('coordinates')).options(selectinload(A.b_rel))
in this case i get the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/uvicorn/protocols/http/httptools_impl.py", line 404, in run_asgi
result = await app( # type: ignore[func-returns-value]
File "/usr/local/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
return await self.app(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/fastapi/applications.py", line 270, in __call__
await super().__call__(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
await self.middleware_stack(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
raise exc
File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
await self.app(scope, receive, _send)
File "/usr/local/lib/python3.10/site-packages/starlette/middleware/cors.py", line 84, in __call__
await self.app(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
raise exc
File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
await self.app(scope, receive, sender)
File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
raise e
File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
await self.app(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 680, in __call__
await route.handle(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 275, in handle
await self.app(scope, receive, send)
File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
response = await func(request)
File "/usr/local/lib/python3.10/site-packages/fastapi/routing.py", line 231, in app
raw_response = await run_endpoint_function(
File "/usr/local/lib/python3.10/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
return await dependant.call(**values)
File "/app/./routers/parking_lot_crud.py", line 28, in get_parking_lot
return await parking_lot_dal.get(name)
File "/app/./database/dal/parking_lot_dal.py", line 52, in get
obj = (await self.db_session.execute(stmt)).one()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/session.py", line 215, in execute
result = await greenlet_spawn(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 128, in greenlet_spawn
result = context.switch(value)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1564, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 544, in _compile_w_cache
compiled_sql = self._compiler(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 790, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 463, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 498, in process
return obj._compiler_dispatch(self, **kwargs)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3301, in visit_select
compile_state = select_stmt._compile_state_factory(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 510, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 734, in create_for_statement
opt.process_compile_state(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/strategy_options.py", line 263, in process_compile_state
self._process(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/strategy_options.py", line 908, in _process
val._bind_loader(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/strategy_options.py", line 1064, in _bind_loader
entity = self._find_entity_prop_comparator(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/strategy_options.py", line 1149, in _find_entity_prop_comparator
raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Query has only expression-based entities, which do not apply to relationship property "ParkingLot.floors"
If execute the query with select(A) instead of individual columns, everything works fine. However i do need to run the queries with the geoalchemy functions.
Thanks to anyone who can give some pointers
