LambdaElement not implemented

[FranzForstmayr]

• SQLAlchemy Easy Soft-Delete version: 0.8.3
• Python version: 3.12
• Operating System: Ubuntu 22

Description

I have a Litestar Application which usesadvanced-alchemy for my database modes.

advanced-alchemy utilizes lambda_stmt which does not work in combination with sqlalchemy-easy-softdelete.

What I Did

from advanced_alchemy.base import UUIDBase
from advanced_alchemy.filters import LimitOffset
from advanced_alchemy.repository import SQLAlchemySyncRepository
from advanced_alchemy.service import SQLAlchemySyncRepositoryService
from sqlalchemy import create_engine
from sqlalchemy.orm import Mapped, sessionmaker
from sqlalchemy_easy_softdelete.mixin import generate_soft_delete_mixin_class
from sqlalchemy_easy_softdelete.hook import IgnoredTable
from datetime import datetime

# Create a Class that inherits from our class builder
class SoftDeleteMixin(generate_soft_delete_mixin_class(
    # This table will be ignored by the hook
    # even if the table has the soft-delete column
    ignored_tables=[IgnoredTable(table_schema="public", name="cars"),]
)):
    # type hint for autocomplete IDE support
    deleted_at: datetime

class User(UUIDBase, SoftDeleteMixin):
    # you can optionally override the generated table name by manually setting it.
    __tablename__ = "user_account"  # type: ignore[assignment]
    email: Mapped[str]
    name: Mapped[str]

class UserRepository(SQLAlchemySyncRepository[User]):
    """User repository."""

    model_type = User

class UserService(SQLAlchemySyncRepositoryService[User]):
    """User repository."""

    repository_type = UserRepository

# use any compatible sqlalchemy engine.
engine = create_engine("duckdb:///:memory:")
session_factory = sessionmaker(engine, expire_on_commit=False)

# Initializes the database.
with engine.begin() as conn:
    User.metadata.create_all(conn)

with session_factory() as db_session:
    service = UserService(session=db_session)
    # 1) Create multiple users with `add_many`
    objs = service.create_many([
        {"email": 'cody@litestar.dev', 'name': 'Cody'},
        {"email": 'janek@litestar.dev', 'name': 'Janek'},
        {"email": 'peter@litestar.dev', 'name': 'Peter'},
        {"email": 'jacob@litestar.dev', 'name': 'Jacob'}
    ])
    print(objs)
    print(f"Created {len(objs)} new objects.")

    # 2) Select paginated data and total row count.  Pass additional filters as kwargs
    created_objs, total_objs = service.list_and_count(LimitOffset(limit=10, offset=0), name="Cody")
    print(f"Selected {len(created_objs)} records out of a total of {total_objs}.")

    # 3) Let's remove the batch of records selected.
    deleted_objs = service.delete_many([new_obj.id for new_obj in created_objs])
    print(f"Removed {len(deleted_objs)} records out of a total of {total_objs}.")

    # 4) Let's count the remaining rows
    remaining_count = service.count()
    print(f"Found {remaining_count} remaining records after delete.")

When I execute the file, I get the following traceback:

[<__main__.User object at 0x7f04231edd00>, <__main__.User object at 0x7f042331c650>, <__main__.User object at 0x7f04232eb5f0>, <__main__.User object at 0x7f04231cb620>]
Created 4 new objects.
Traceback (most recent call last):
  File "/home/franz/Workspaces/python/repro/test.py", line 61, in <module>
    created_objs, total_objs = service.list_and_count(LimitOffset(limit=10, offset=0), name="Cody")
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/advanced_alchemy/service/_sync.py", line 381, in list_and_count
    return self.repository.list_and_count(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/advanced_alchemy/repository/_sync.py", line 1458, in list_and_count
    return self._list_and_count_window(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/advanced_alchemy/repository/_sync.py", line 1551, in _list_and_count_window
    result = self._execute(statement, uniquify=loader_options_have_wildcard)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/advanced_alchemy/repository/_sync.py", line 2003, in _execute
    result = self.session.execute(statement)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2207, in _execute_internal
    fn_result: Optional[Result[Any]] = fn(orm_exec_state)
                                       ^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/sqlalchemy_easy_softdelete/handler/sqlalchemy_easy_softdelete.py", line 33, in soft_delete_execute
    adapted = global_rewriter.rewrite_statement(state.statement)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/franz/Workspaces/python/repro/.venv/lib/python3.12/site-packages/sqlalchemy_easy_softdelete/handler/rewriter/__init__.py", line 63, in rewrite_statement
    raise NotImplementedError(f"Unsupported statement type \"{(type(stmt))}\"!")
NotImplementedError: Unsupported statement type "<class 'sqlalchemy.sql.lambdas.LinkedLambdaElement'>"!

I was able to run the script when changing this line in SoftDeleteQueryRewriter, but I'm not sure if this is the right approach without any negative effects.

class SoftDeleteQueryRewriter:
    """Rewrites SQL statements based on configuration."""

    ...

    def rewrite_statement(self, stmt: Statement) -> Statement:
        """Rewrite a single SQL-like Statement."""
        # if isinstance(stmt, Select):
        if isinstance(stmt, (Select, LambdaElement)):
            return self.rewrite_select(stmt)

        ...

[flipbit03]

Thanks for reporting - we should try to build a reproducible test case for our test suite, and then we check for its correctness

[cofin]

@flipbit03 let me know if there's something we can do on the AA side to assist.