Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-08-06· OpenAIAi2 WildChat-4.8M (AI2)

import logging from collections import defaultdict from collections.abc import Sequence…

  1. User

    import logging from collections import defaultdict from collections.abc import Sequence

    from sqlalchemy import ( select, inspect, Select, asc, desc, delete, func, text, ) from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.orm import aliased, contains_eager, RelationshipProperty

    from .helpers import ( update_rows, prepare_where_clause, instance_to_model, prepare_update_data, get_record_count, ) from .decorators import session_manager, autologger from ..interface.crud import ( BaseCrudInterface, BaseDbModelType, BaseSchemaType, )

    from ..interface.session import SessionProviderInterface from ..models.schema import BaseSchema

    logger = logging.getLogger(name)

    class BaseCrudRepository(BaseCrudInterface): model: BaseDbModelType = NotImplemented # SQLAlchemy модель schema: BaseSchemaType = NotImplemented # Pydantic схема _FIELDS_TO_ALWAYS_EXCLUDE_FROM_CREATE_OR_UPDATE: set = set()

    def __init__(self, provider: SessionProviderInterface) -> None:
        self.session_provider = provider
        self._inspector = inspect(self.model)
        self.pk = {
            key.name: getattr(self.model, key.name)
            for key in self._inspector.primary_key
        }
        self.relations = {}
        self.joins = defaultdict(dict)
        self._add_unique = False
        self.aliases = {}
        self.fields = {
            column.key: getattr(self.model, column.key)
            for column in self._inspector.columns
        }
        for r in self._inspector.relationships.keys():
            # ищем поле с типом relation модели таблицы
            # заполним словарь outerjoin - поля для которых join(Х, isouter=True)
            # заполним словарь fields - поля модели (включая relation) для сортировки
            #
            relation = getattr(self.model, r)
            aliased_relation = aliased(relation.entity.class_)
            if hasattr(relation, "info") and relation.info.get("is_aliased", None):
                self.aliases[f"{relation}"] = aliased_relation
                self.joins[f"{relation}"]["onclause"] = [local_col == getattr(aliased_relation, remote_col.key) for local_col, remote_col in relation.prop.local_remote_pairs]
            self.relations[r] = aliased_relation
            self.joins[f"{relation}"]["isouter"] = not relation.property.innerjoin
    
            rel_mod_inspect = inspect(relation.entity.class_)
            for column in rel_mod_inspect.columns:
                self.fields[f"{r}.{column.key}"] = getattr(aliased_relation, column.key)
    
    def _prepare_options_for_model(
        self, schema: BaseSchemaType, options: dict | None = None
    ) -> dict:
        """Формирует словарь с опциями для select операции. Используется
         для загрузки relation:
         ключ словаря - поле для join,
         значение - поля помеченное как contains_eager
         В опциях используем contains_eager, чтобы можно было загрузить
         relation c помощью join
        Args:
            options: словарь полученный на этапе формирования условий where
        Returns:
            dict опций
        """
        if not options:
            options = dict()
        for field in schema.model_fields.keys():
            if not hasattr(self.model, field):
                continue
            column = getattr(self.model, field)
            if column in options:
                continue
            if isinstance(column.property, RelationshipProperty):
                if f"{column}" in self.aliases:
                    options[column] = contains_eager(column, alias=self.aliases[f"{column}"])
                else:
                    options[column] = contains_eager(column)
        return options
    
    def _prepare_order(
        self,
        order: tuple[str, ...],
    ) -> list:
        """Формирует список сортировки для order_by()
        Args:
            order: список полей для сортировки
             по умолчанию выполняется asc сортировка,
             если имя поля начинается с "-", то desc сортировка
        Returns:
            список колонок сортировки
        """
        order_statements = []
    
        for field in order:
            direction = asc
            if field.startswith("-"):
                field = field[1:]
                direction = desc
            order_statements.append(direction(text(field)))
        return order_statements
    
    def _process_join(self, statement: Select, options: dict) -> Select:
        for join_column in options.keys():
            if f"{join_column}" in self.aliases:
                statement = statement.join(
                    self.aliases[f"{join_column}"],
                    *self.joins[f"{join_column}"]["onclause"],
                    isouter=self.joins[f"{join_column}"]["isouter"]
                )
                self._add_unique = True
                continue
            statement = statement.join(
                join_column, isouter=self.joins[f"{join_column}"]["isouter"]
            )
        statement = statement.options(*list(options.values()))
        return statement
    
    # @autologger(logger)
    async def _filter_statement_prepare(
        self,
        schema: BaseSchemaType,
        filters: dict,
        sa_filters: list,
        order: tuple[str, ...],
        limit: int,
        offset: int,
        session: AsyncSession,
        statement: Select | None = None,
        with_count: bool = False,
    ) -> (Select, int):
        """Формирует Select: подготавливает и применяет where, join, options, limit, offset.
        Так же, при необходимости, возвращает общее количество записей для сформированного
        запроса без учета offset, limit.
        Args:
            schema: схема Pydantic для формирования полей необходимых в ответе
            filters: словарь условий из которых формируется where ("field": value, "model.field": value)
            sa_filters: список условий для where, загружаемый непосредственно в where, без обработки
            order: набор полей для сортировки
            session: сессия доступа к БД
            statement: базовое выражение для формирования Select
            with_count: подсчитать общее количество записей для запроса без учета offset, limit
        Returns:
            Select, общее количество записей
        """
        statement = statement or select(self.model)
        where, options = prepare_where_clause(model=self.model, filters=filters, aliases=self.aliases)
        options = self._prepare_options_for_model(schema, options)
        if sa_filters:
            where.extend(sa_filters)
        statement = statement.where(*where)
    
        count = await get_record_count(session, statement) if with_count else -1
        if options:
            statement = self._process_join(statement, options)
        if offset:
            statement = statement.offset(offset)
        if limit:
            statement = statement.limit(limit)
        if order:
            statement = statement.order_by(*self._prepare_order(order))
        return statement, count
    
    @session_manager
    @autologger(logger)
    async def create(
        self,
        instance: BaseSchemaType | Sequence[BaseSchemaType],
        *,
        exclude_fields: set | None = None,
        with_relations: bool = False,
        session: AsyncSession = None,
    ):
        """Создает запись в БД из экземпляра схемы
        Args:
            instance: экземпляр схемы данных
            exclude_fields: поля, которые исключаются из model_dump
            with_relations: если передано - то создаем записи в БД для relations
            session: сессия доступа к БД
        Returns:
            экземпляр схемы данных
        """
        db_instances = []
        exclude_fields = exclude_fields or set()
        exclude_fields.update(self._FIELDS_TO_ALWAYS_EXCLUDE_FROM_CREATE_OR_UPDATE)
        if not isinstance(instance, Sequence):
            instances = [
                instance,
            ]
        else:
            instances = instance
    
        for instance in instances:
            if with_relations:
                for field, value in inspect(self.model).relationships.items():
                    if value.viewonly:
                        continue
                    db_instances.append(
                        instance_to_model(
                            instance=getattr(instance, field),
                            model=value.mapper.class_,
                            exclude_fields=exclude_fields,
                        )
                    )
            db_instances.append(
                instance_to_model(
                    instance=instance, model=self.model, exclude_fields=exclude_fields
                )
            )
        session.add_all(db_instances)
        # await session.flush()
    
    async def _update(
        self,
        instance: BaseSchema | None = None,
        *,
        filters: dict | tuple[str, ...] | None = None,
        exclude_fields: tuple[str, ...] | None = None,
        update_fields: dict | tuple[str, ...] | None = None,
        session: AsyncSession | None = None,
    ) -> BaseSchemaType:
        """Обновляет запись в БД из экземпляра схемы.
        Args:
            instance: экземпляр схемы данных
            filters: поля (значения берутся из instance) или условия для поиска (словарь, поле:значение), если
              не передано - используются primary key модели
            exclude_fields: поля, которые исключаются из model_dump
            update_fields: если передано tuple - то обновляем только эти поля, если передан dict,
             то используем его для обновления значений
            session: сессия доступа к БД
        Returns:
            экземпляр схемы данных
        """
        if not instance and not isinstance(update_fields, dict):
            raise ValueError(
                "if instance is none update_fields must be dict for update or vice versa"
            )
        if not filters:
            filters = {pk_f: getattr(instance, pk_f) for pk_f in self.pk.keys()}
        if isinstance(filters, tuple):
            if not instance:
                raise ValueError(f"{instance} is None")
            filters = {f: getattr(instance, f) for f in filters}
    
        update_data = (
            update_fields
            if isinstance(update_fields, dict)
            else prepare_update_data(
                instance=instance,
                model=self.model,
                exclude_fields=exclude_fields,
                update_fields=update_fields,
            )
        )
    
        results = await self._update_many(
            filters=filters,
            update_data=update_data,
            session=session,
        )
        if len(results) != 1:
            raise ValueError(f"update return not one object: {results}")
        return results[0]
    
    @session_manager
    @autologger(logger)
    async def update(
        self,
        instance: BaseSchema | None = None,
        *,
        filters: dict | tuple[str, ...] | None = None,
        exclude_fields: tuple[str, ...] | None = None,
        update_fields: dict | tuple[str, ...] | None = None,
        session: AsyncSession | None = None,
    ) -> BaseSchemaType:
        """Обновляет запись в БД из экземпляра схемы.
        Args:
            instance: экземпляр схемы данных. Если в экземпляре присутствуют значения None,
              которые не должны попасть в БД, то нужно передать список полей в параметре
              update_fields
            filters: поля (значения берутся из instance) или условия для поиска (словарь, поле:значение),
              если не передано - используются primary key модели
            exclude_fields: поля, которые исключаются из model_dump
            update_fields: если передано tuple - то обновляем только эти поля, если передан dict,
             то используем его для обновления значений
            session: сессия доступа к БД
        Returns:
            экземпляр схемы данных
        """
        return await self._update(
            instance,
            filters=filters,
            exclude_fields=exclude_fields,
            update_fields=update_fields,
            session=session,
        )
    
    @session_manager
    @autologger(logger)
    async def update_many(
        self,
        *,
        update_data: dict,
        filters: dict,
        exclude_fields: tuple[str, ...] | None = None,
        session: AsyncSession | None = None,
    ) -> list[BaseSchemaType]:
        """Обновляет несколько строк в БД с использованием переданного словаря.
        Args:
            update_data: словарь полей и значений для обновления, для обновления
             отношений используются вложенные словари
            filters: поля (значения берутся из instance) или условия для поиска
             (словарь, поле:значение), если не передано - используются primary key модели
            exclude_fields: поля, которые исключаются из model_dump
            session: сессия доступа к БД
        Returns:
            экземпляр схемы данных
        """
    
        return await self._update_many(
            update_data=update_data, filters=filters, session=session
        )
    
    async def _update_many(
        self,
        *,
        update_data: dict,
        filters: dict,
        session: AsyncSession | None = None,
    ) -> list[BaseSchemaType]:
        """Обновляет несколько строк в БД с использованием переданного словаря.
        Args:
            update_data: словарь полей и значений для обновления, для обновления
             отношений используются вложенные словари
            filters: поля (значения берутся из instance) или условия для поиска
             (словарь, поле:значение), если не передано - используются primary key модели
            session: сессия доступа к БД
        Returns:
            экземпляр схемы данных
        """
        lookup_clause, options = prepare_where_clause(model=self.model, filters=filters, aliases=self.aliases)
        statement = select(self.model)
    
        options = self._prepare_options_for_model(schema=self.schema, options=options)
        statement = statement.where(*lookup_clause)
        if options:
            statement = self._process_join(statement, options)
        rows = (await session.execute(statement.with_for_update(of=self.model))).scalars()
        if self._add_unique:
            rows = rows.unique()
    
        results = update_rows(self.schema, rows.all(), update_data)
        return results
    
    @session_manager
    @autologger(logger)
    async def update_or_create(
        self,
        instance: BaseSchema,
        *,
        with_relations: bool = False,
        filters: dict | tuple[str, ...] | None = None,
        exclude_fields: tuple[str, ...] | None = None,
        update_fields: tuple[str, ...] | None = None,
        session: AsyncSession | None = None,
    ) -> tuple[bool, BaseSchemaType]:
        """Обновляет или создает запись.
        Args:
            instance: экземпляр схемы данных
            filters: поля (значения берутся из instance) или условия для поиска (словарь, поле:значение), если
              не передано - используются primary key модели
            exclude_fields: поля, которые исключаются из model_dump
            update_fields: если передано - то обновляем только эти поля
            session: сессия доступа к БД
        Returns:
            is_created, экземпляр схемы данных
        """
    
        try:
            # вызовем функцию без декоратора логирования
            # чтобы не загрязнять логи ошибкой обновления
            # если записи нет
            instance = await self._update(
                instance,
                filters=filters,
                exclude_fields=exclude_fields,
                update_fields=update_fields,
                session=session,
            )
            await session.flush()
            return False, instance
        except ValueError:
            await self.create(instance, with_relations=with_relations, session=session)
            return True, instance
    
    @session_manager
    @autologger(logger)
    async def delete(
        self, filters: dict, *, session: AsyncSession | None = None
    ) -> int:
        where, _ = prepare_where_clause(model=self.model, filters=filters)
        statement = delete(self.model).where(*where)
        result = await session.execute(statement)
        return result.rowcount
    
    @session_manager
    async def get_or_none(
        self,
        filters: dict,
        *,
        response_schema: BaseSchema = None,
        order: tuple[str, ...] = None,
        session: AsyncSession | None = None,
    ) -> BaseSchemaType | None:
        result = await self.filter(
            filters=filters,
            session=session,
            response_schema=response_schema,
            order=order,
        )
        return result[0] if len(result) else None
    
    @session_manager
    async def get(
        self,
        filters: dict,
        *,
        response_schema: BaseSchemaType = None,
        order: tuple[str, ...] = None,
        session: AsyncSession = None,
    ) -> BaseSchema:
        item = await self.get_or_none(
            response_schema=response_schema,
            filters=filters,
            session=session,
            order=order,
        )
        if not item:
            raise ValueError(f"Not found {self.model} for {filters}")
        return item
    
    @session_manager
    async def all(
        self,
        response_schema: BaseSchemaType = None,
        order: tuple[str, ...] = None,
        session: AsyncSession | None = None,
    ) -> list[BaseSchema]:
        return await self.filter(
            filters={}, session=session, response_schema=response_schema, order=order
        )
    
    @session_manager
    async def filter_statement(
        self,
        statement: Select,
        *,
        response_schema: BaseSchemaType = None,
        session: AsyncSession | None = None,
        is_use_scalars: bool = True,
    ) -> list[BaseSchema]:
        """Выбор из БД записей по фильтрам
        Args:
            statement: выражение select
            response_schema: схема в которую будут преобразованы данные из БД
            session: сессия доступа к БД
            is_use_scalars: выставляется в False при использовании в statement
             произвольных возвращаемых полей, а не SqlAlchemy модели
        Returns:
            список экземпляр схемы данных
        """
        schema = response_schema or self.schema
        query = await session.execute(statement)
        if is_use_scalars:
            query = query.scalars()
        if self._add_unique:
            query = query.unique()
        rows = query.all()
        return [schema.model_validate(row) for row in rows]
    
    @session_manager
    async def filter_statement_with_count(
        self,
        statement: Select,
        *,
        response_schema: BaseSchemaType = None,
        limit: int | None = None,
        offset: int | None = None,
        session: AsyncSession | None = None,
        count_stmt: Select | None = None,
        is_use_scalars: bool = True,
    ) -> tuple[int, list[BaseSchema]]:
        """Выбор из БД записей по фильтрам
        Args:
            statement: выражение select
            response_schema: схема в которую будут преобразованы данные из БД
            limit: max количество возвращаемых записей
            offset: сдвиг от начала в БД
            session: сессия доступа к БД
            count_stmt: отдельный запрос для подсчета общего количества строк,
             может потребоваться в случае сложного запроса с group_by, order_by
            is_use_scalars: выставляется в False при использовании в statement
             произвольных возвращаемых полей, а не SqlAlchemy модели
        Returns:
            список экземпляр схемы данных
        """
        schema = response_schema or self.schema
        #
        # Для отдельных запросов которые используют group_by, order_by
        # необходимо правильно сформировать название полей, сделать это
        # динамически не представляется возможным, поэтому в таких случаях
        # передаем отдельное выражение для общего подсчета строк
        #
        #
        if count_stmt is None:
            count_stmt = statement.with_only_columns(*list(self.pk.values()))
            count_stmt = select(func.count()).select_from(count_stmt.subquery())
        count = await get_record_count(session, count_stmt)
    
        if limit:
            statement = statement.limit(limit)
        if offset:
            statement = statement.offset(offset)
    
        query = await session.execute(statement)
        if is_use_scalars:
            query = query.scalars()
        rows = query.all()
        return count, [schema.model_validate(row) for row in rows]
    
    # @session_manager
    async def _filter(
        self,
        filters: dict,
        *,
        order: tuple[str, ...] = None,
        sa_filters: list = None,
        response_schema: BaseSchemaType = None,
        session: AsyncSession | None = None,
        limit: int = 0,
        offset: int = 0,
        statement: Select | None = None,
    ) -> list[BaseSchemaType]:
        """Выбор из БД записей по фильтрам
        Args:
            filters: условия для поиска (словарь, поле:значение)
            order: список полей для упорядочивания
            sa_filters: список условий для where в формате sqlalchemy (не требует обработки)
            response_schema: схема в которую будут преобразованы данные из БД
            session: сессия доступа к БД
            limit: max количество возвращаемых записей
            offset: сдвиг от начала в БД
        Returns:
            список экземпляр схемы данных
        """
        schema = response_schema or self.schema
        statement, _ = await self._filter_statement_prepare(
            schema=schema,
            filters=filters,
            sa_filters=sa_filters,
            order=order,
            limit=limit,
            offset=offset,
            session=session,
            statement=statement,
        )
    
        return await self.filter_statement(
            statement, response_schema=schema, session=session
        )
    
    @session_manager
    @autologger(logger)
    async def filter(
        self,
        filters: dict,
        *,
        order: tuple[str, ...] = None,
        response_schema: BaseSchemaType = None,
        session: AsyncSession | None = None,
        limit: int = 0,
        offset: int = 0,
        sa_filters: tuple = None,
    ) -> list[BaseSchema]:
        """Выбор из БД записей по фильтрам
        Args:
            filters: условия для поиска (словарь, поле:значение)
            order: список полей для упорядочивания
            response_schema: схема в которую будут преобразованы данные из БД
            session: сессия доступа к БД
            limit: max количество возвращаемых записей
            offset: сдвиг
            sa_filters: список условий для where в формате sqlalchemy (не требует обработки)
        Returns:
            список экземпляров схемы данных
        """
        return await self._filter(
            filters,
            order=order,
            response_schema=response_schema,
            session=session,
            limit=limit,
            offset=offset,
            sa_filters=sa_filters,
        )
    
    # @session_manager
    async def _filter_with_total_count(
        self,
        filters: dict,
        *,
        order: tuple[str, ...] = None,
        sa_filters: list = None,
        response_schema: BaseSchemaType = None,
        session: AsyncSession | None = None,
        limit: int = 0,
        offset: int = 0,
        statement: Select | None = None,
    ) -> (int, list[BaseSchema]):
        """Выбор из БД записей по фильтрам, при этом будет дополнительно возвращено
         общее количество записей, соответствующее условиям поиска, без учета limit & offset
        Args:
            filters: условия для поиска (словарь, поле:значение)
            order: список полей для упорядочивания
            sa_filters: список условий для where в формате sqlalchemy (не требует обработки)
            response_schema: схема в которую будут преобразованы данные из БД
            session: сессия доступа к БД
            limit: max количество возвращаемых записей
            offset: сдвиг
        Returns:
            список экземпляров схемы данных
        """
        schema = response_schema or self.schema
    
        statement, count = await self._filter_statement_prepare(
            schema=schema,
            filters=filters,
            sa_filters=sa_filters,
            order=order,
            limit=limit,
            offset=offset,
            session=session,
            with_count=True,
            statement=statement,
        )
    
        return count, await self.filter_statement(
            statement, response_schema=schema, session=session
        )
    
    @session_manager
    async def filter_with_total_count(
        self,
        filters: dict,
        *,
        order: Sequence[str, ...] = None,
        response_schema: BaseSchemaType = None,
        session: AsyncSession | None = None,
        limit: int = 0,
        offset: int = 0,
        sa_filters: Sequence = None,
    ) -> (int, list[BaseSchema]):
        """Выбор из БД записей по фильтрам, при этом будет дополнительно возвращено
         общее количество записей, соответствующее условиям поиска, без учета limit & offset
        Args:
            filters: условия для поиска (словарь, поле:значение)
            order: список полей для упорядочивания
            response_schema: схема в которую будут преобразованы данные из БД
            session: сессия доступа к БД
            limit: max количество возвращаемых записей
            offset: сдвиг
            sa_filters: список условий для where в формате sqlalchemy (не требует обработки)
        Returns:
            список экземпляр схемы данных
        """
    
        return await self._filter_with_total_count(
            filters,
            order=order,
            response_schema=response_schema,
            session=session,
            limit=limit,
            offset=offset,
            sa_filters=sa_filters,
        )
    
  2. Assistant

    The provided code is a comprehensive implementation of a base repository for performing CRUD operations in an asynchronous context using SQLAlchemy's AsyncSession. This class, BaseCrudRepository, extends a BaseCrudInterface and provides a consistent interface to work with database models by implementing common methods for filtering, updating, creating, and deleting database records.

    Here's a breakdown of what each part of the implementation does:

    1. Class Attributes and Initialization:

      • Specifies type hints for the model and schema used in the repository.
      • Initializes various attributes for managing relations, aliases, fields, and options related to the database model during instantiation of a repository object.
    2. Options and Joins:

      • Prepares SQLAlchemy select operation options, specifically for managing relations and eager loading (using contains_eager).
      • Handles joining of related models, accounting for both aliased and non-aliased relationships.
    3. Query Orders and Conditions:

      • Constructs a list of ordering conditions (ascending or descending), mapping string-based input to SQLAlchemy order criteria.
      • Prepares and processes SQLAlchemy join statements based on relationships specified in select options.
    4. CRUD Operations:

      • Create: Facilitates creating new records in the database from Pydantic schema instances, handling both single and batch creations.
      • Update: Supports updating records using provided filters or primary key defaults, accommodating both single-record updates and batch updates when passed a dictionary of update data.
      • Read/Filter: Effectively retrieves database records, supporting options for sorting, filtering, eager loading, and pagination. Offers methods to return either one or multiple results, along with count functionality.
      • Delete: Enables deletion of records based on specified filters, returning the count of affected rows.
    5. Utilities:

      • Implements helper methods for managing session-based operations and ensuring query safety within asynchronous contexts.
      • Incorporates external utilities and decorators from other modules (not provided), so functions like session_manager, autologger, prepare_where_clause, etc., indicate dependency handling or logging enhancements.
    6. Error Handling:

      • Raises ValueError when update operations result in unexpected results, such as multiple rows being affected where only one was expected, ensuring predictable outcomes and error transparency.

    The code serves as a flexible and reusable pattern for interacting with a database in a structured, asynchronous environment, making it suitable for services requiring concurrency management, such as web applications built with frameworks like FastAPI or similar setups. It can be further extended or adapted based on specific database interaction requirements or complex relationships in the underlying data model.

  3. User

    from collections import defaultdict from datetime import datetime from typing import Iterable, Type, Any

    from asyncache import cached from cachetools import TTLCache from cachetools.keys import hashkey from sqlalchemy import ( Column, BinaryExpression, ColumnOperators, Sequence, Row, RowMapping, func, Select, select, ) from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.orm import contains_eager

    from ..interface.crud import BaseDbModelType from ..interface.crud import BaseSchemaType, BaseSchema

    def remove_self(*argv, **kwarg): # убираем первый параметр - сессия return hashkey(*argv[1:], **kwarg)

    @cached(TTLCache(maxsize=1024, ttl=300), key=remove_self) async def get_record_count(session: AsyncSession, stmt: Select) -> int: stmt = select(func.count()).select_from(stmt) return (await session.scalars(stmt)).first()

    def clause_for_column(field: Column, val) -> BinaryExpression | ColumnOperators: """Формирует условие для where() Если передано единичное значение или строка, то Column == val Если итерируемый объект, то Column.in_(val) Args: field: колонка val: значение Returns: BinaryExpression | ColumnOperators """ if isinstance(val, Iterable) and not isinstance(val, str): vals = list(val) if ( len(vals) == 2 and isinstance(vals[0], datetime) and isinstance(vals[1], datetime) ): return field.between(vals[0], vals[1]) return field.in_(val) elif isinstance(val, str) and "%" in val: return field.ilike(val) else: return field == val

    def process_fields_to_model_and_relation(fields: tuple[str, ...]) -> (set, dict): """Преобразует набор полей в: - множество (set) полей непосредственно модели ("field") - словарь отношений [модель][поле] ("model.field") Args: fields: набор полей в формате "field" или "model.field" Returns: (множество полей модели, словарь отношений) """ model = set() relation = defaultdict(dict) for f in fields: if "." in f: model_, field = f.split(".", 1) relation[model_][field] = 1 else: model.add(f) return model, relation

    def instance_to_model( instance: BaseSchema, model: BaseDbModelType, exclude_fields: set | None = None, update_fields: set | None = None, ) -> BaseDbModelType: """Преобразует Pydantic экземпляр в модель SqlAlchemy Args: instance: Pydantic экземпляр model: модель таблицы SqlAlchemy exclude_fields: поля которые исключаются из model_dump update_fields: если передано - то только эти поля будут выгружены в модель SqlAlchemy

    Returns:
        экземпляр модели SqlAlchemy
    """
    if not exclude_fields:
        exclude_fields = {}
    # возьмем только те поля которые есть в модели SqlAlchemy
    include_fields: set = model.attribute_names()
    # если заданы update_fields (обновить только эти поля) - берем пересечение с полями модели
    if update_fields:
        include_fields.intersection(update_fields)
    instance = model(
        **instance.model_dump(exclude=exclude_fields, include=include_fields)
    )
    return instance
    

    def prepare_update_data( instance: BaseSchema, model: BaseDbModelType, exclude_fields: tuple[str, ...] | None = None, update_fields: tuple[str, ...] | None = None, ) -> dict: """Преобразует Pydantic экземпляр в словарь для метода update() используемый для обновления данных в БД Args: instance: Pydantic экземпляр model: модель таблицы SqlAlchemy exclude_fields: поля которые исключаются из model_dump() update_fields: если передано - то только эти поля будут выгружены в модель SqlAlchemy Returns: dict """

    update_data = instance.model_dump()
    for field, value in list(update_data.items()):
        if not hasattr(model, field):
            del update_data[field]
        if not isinstance(value, dict):
            continue
        submodel_ = getattr(model, field).entity.class_
        for subfield in list(update_data[field].keys()):
            if hasattr(submodel_, subfield):
                continue
            del update_data[field][subfield]
    
    result = defaultdict(dict)
    if update_fields:
        for f in update_fields:
            if "." in f:
                model_, field = f.split(".", 1)
                result[model_][field] = update_data[model_][field]
            else:
                result[f] = update_data[f]
        return result
    if exclude_fields:
        for f in exclude_fields:
            if "." in f:
                model_, field = f.split(".", 1)
                if model_ in update_data and field in update_data[model_]:
                    del update_data[model_][field]
            else:
                if f in update_data:
                    del update_data[f]
    
    return update_data
    

    def prepare_where_clause( model: BaseDbModelType, filters: dict, aliases: dict | None = None, ) -> (list, dict): """Формирует список условий поиска для where() Ищет поля, в том числе в relations модели SqlAlchemy Args: model: SqlAlchemy модель таблицы filters: список полей и значений для условий (словарь) или список полей aliases: словарь c alias для колонок relation Returns: список условий поиска """ clause = [] relationships = defaultdict(dict) options = dict()

    # Parse filters for direct attributes and relationships
    for attr, value in filters.items():
        if "." in attr:
            # It"s a relationship field
            rel_attr, rel_field = attr.split(".", 1)
            relationships[rel_attr][rel_field] = value
        else:
            # It"s a direct attribute of the model
            column = getattr(model, attr, None)
            clause.append(clause_for_column(column, value))
    
    # Для загрузки relation будем использовать
    # join совместно contains_eager
    for rel_attr, rel_filter in relationships.items():
        relation = getattr(model, rel_attr)
        if aliases and f"{relation}" in aliases:
            options[relation] = contains_eager(relation, alias=aliases[f"{relation}"])
        else:
            options[relation] = contains_eager(relation)
    
        for attr, value in rel_filter.items():
            column = getattr(relation.property.mapper.class_, attr)
            clause.append(relation.has(clause_for_column(column, value)))
    
    return clause, options
    

    def update_row( schema: BaseSchemaType, row: Row | RowMapping | Any, data: dict ) -> BaseSchemaType: for field, value in data.items(): # обновим зависимости if isinstance(value, dict): if not hasattr(row, field): raise ValueError(f"Row {row} has no relation {field} from {data=}") relation = getattr(row, field) for subfield, subvalue in data[field].items(): setattr(relation, subfield, subvalue) continue # обновим данные модели if not hasattr(row, field): raise ValueError(f"Row {row} has no field {field} from {data=}") setattr(row, field, value) return schema.model_validate(row)

    def update_rows( schema: BaseSchemaType, rows: Sequence[Row | RowMapping | Any], data: dict ) -> list[BaseSchema]: results = [] for row in rows: results.append(update_row(schema, row, data)) return results

  4. Assistant

    The provided code is a utility module designed to enhance and streamline interactions with SQLAlchemy's ORM, specifically focusing on operations that involve transforming and managing data between Pydantic models and SQLAlchemy models, along with handling common database operations. Here's an explanation of what's happening in each function and its purpose:

    1. Caching:

      • remove_self: Utility function to adjust function caching by removing 'self' from the cache key when caching methods of a class.
      • get_record_count: Uses async caching to compute the number of records for a given SQLAlchemy select statement, optimizing database load by caching results temporarily.
    2. Clause and Condition Handling:

      • clause_for_column: Constructs where conditions for SQLAlchemy queries. Depending on the type of val, it either checks equality, membership (in_ clause), or performs a range or ilike operation.
    3. Field Processing:

      • process_fields_to_model_and_relation: Divides fields into direct model fields and relationship fields, organizing these into a format that separates them for processing.
    4. Model and Instance Transformation:

      • instance_to_model: Converts a Pydantic model instance into an SQLAlchemy model instance, selectively including or excluding fields based on given parameters.
      • prepare_update_data: Generates a dictionary from a Pydantic model instance for updating SQLAlchemy models, allowing selective inclusion or exclusion of fields for updates.
    5. Where Clause Preparation:

      • prepare_where_clause: Generates where filter conditions for queries, accommodating both direct model attributes and relational fields. Also manages aliasing for efficiently eager-loading related data.
    6. Updating Database Rows:

      • update_row: Updates a single SQLAlchemy model instance (or row) using a data dictionary. Handles nested dictionaries for relational data, validating with the corresponding Pydantic schema.
      • update_rows: Applies update_row across a sequence of rows, returning validated and updated schema instances.

    The utilities in this code are intended to facilitate efficient CRUD operations in an asynchronous context, leveraging SQLAlchemy's ORM capabilities. It is designed to work seamlessly with Pydantic for data validation, ensuring that only appropriately structured data is processed. This makes it a powerful tool for applications that require robust database interactions, such as web services or data-centric backend systems.

  5. User

    create tests for this code

  6. Assistant

    To create tests for the given code, you would typically use a testing framework like pytest along with some test utilities provided by SQLAlchemy for working with asynchronous database sessions. The tests should cover scenarios such as correctly creating, updating, and querying models, as well as verifying utility functions like caching and clause building. The goal is to ensure that everything behaves as expected under normal conditions and edge cases.

    Below is a basic example of how you could start writing tests for these utilities. Note that this assumes you have some basic SQLAlchemy models and Pydantic schemas defined already. Additionally, you'd need to set up an actual database or use an SQLite in-memory database for testing purposes.

    Prerequisites

    1. Define SQLAlchemy Models: You'll need actual SQLAlchemy models that the functions interact with. Define these models based on your application's requirements if not already present.
    2. Define Pydantic Schemas: Similar to models, prepare Pydantic schemas corresponding to your models.

    Setup for Tests

    import pytest
    from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
    from sqlalchemy.orm import sessionmaker
    from myapp.models import MyModel  # replace with actual model
    from myapp.schemas import MySchema  # replace with actual schema
    from myapp.utils import (
        get_record_count,
        clause_for_column,
        process_fields_to_model_and_relation,
        instance_to_model,
        prepare_update_data,
        prepare_where_clause,
        update_row,
        update_rows,
    )
    
    # Setup in-memory database for testing
    DATABASE_URL = "sqlite+aiosqlite:///:memory:"
    engine = create_async_engine(DATABASE_URL, echo=True)
    AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    
    @pytest.fixture(scope="function", autouse=True)
    async def async_session():
        async with AsyncSessionLocal() as session:
            yield session
    
    @pytest.fixture(scope="function", autouse=True)
    async def setup_database():
        async with engine.begin() as conn:
            # import your models and then drop and create tables here
            await conn.run_sync(MyModel.metadata.drop_all)
            await conn.run_sync(MyModel.metadata.create_all)
        yield
    

    Test Cases

    @pytest.mark.asyncio
    async def test_get_record_count(async_session):
        # Assuming you have a populated database
        async with async_session.begin():
            count = await get_record_count(async_session, select(MyModel))
            assert isinstance(count, int)
    
    def test_clause_for_column():
        # Replace 'MyModel.column' with an actual column of your model
        clause = clause_for_column(MyModel.column, "value")
        assert str(clause) == "EXPECTED_CONDITION"
    
    def test_process_fields_to_model_and_relation():
        fields = ("model.field", "field")
        model_fields, relation_fields = process_fields_to_model_and_relation(fields)
        assert "field" in model_fields
        assert "field" in relation_fields["model"]
    
    def test_instance_to_model():
        instance = MySchema(field1="value1", field2="value2")
        model_instance = instance_to_model(instance, MyModel)
        assert isinstance(model_instance, MyModel)
    
    def test_prepare_update_data():
        instance = MySchema(field1="value1", field2="value2")
        update_data = prepare_update_data(instance, MyModel)
        assert "field1" in update_data
    
    def test_prepare_where_clause():
        filters = {"field": "value", "model.field": "value"}
        clause, options = prepare_where_clause(MyModel, filters)
        assert len(clause) > 0
        
    def test_update_row():
        instance = MySchema(field1="updated_value")
        model_instance = MyModel(field1="value1", field2="value2")
        updated_instance = update_row(MySchema, model_instance, {"field1": "updated_value"})
        assert updated_instance.field1 == "updated_value"
    
    @dataclasses.dataclass
    def test_update_rows():
        model_instances = [MyModel(field1="value1"), MyModel(field1="value2")]
        updated_instances = update_rows(MySchema, model_instances, {"field1": "new_value"})
        for instance in updated_instances:
            assert instance.field1 == "new_value"
    

    Notes

    • More comprehensive tests should be created based on your schema/model details.
    • SQL statements, expected conditions, and assertions must be adapted based on your particular schema and model attributes.
    • Ensure you cater test cases for edge conditions like empty field values, invalid inputs, and large data sets.
    • You might also want to test threading by running concurrent async operations if your application requires such use-cases.
  7. User

    for previous listing add tests to

  8. Assistant

    To test the previous implementation of the BaseCrudRepository class, you should write unit tests that cover CRUD operations including create, read, update, delete, and specific utility behaviors like joining, filtering, and sorting. These tests will ensure that your repository behaves as expected in various scenarios. Here are some illustrative test cases using pytest and SQLAlchemy for async testing:

    import pytest
    from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
    from sqlalchemy.orm import sessionmaker
    from myapp.models import MyModel  # Replace with actual model
    from myapp.repositories import BaseCrudRepository  # Make sure your repository is imported
    from myapp.schemas import MySchema  # Replace with actual schema
    
    # Setup in-memory database for testing
    DATABASE_URL = "sqlite+aiosqlite:///:memory:"
    engine = create_async_engine(DATABASE_URL, echo=True)
    AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    
    @pytest.fixture(scope="function", autouse=True)
    async def async_session():
        async with AsyncSessionLocal() as session:
            yield session
    
    @pytest.fixture(scope="function", autouse=True)
    async def setup_database():
        async with engine.begin() as conn:
            # Drop and create tables
            await conn.run_sync(MyModel.metadata.drop_all)
            await conn.run_sync(MyModel.metadata.create_all)
        yield
    
    @pytest.fixture
    def crud_repo(async_session):
        return BaseCrudRepository(session_provider=async_session, model=MyModel, schema=MySchema)
    
    @pytest.mark.asyncio
    async def test_create_instance(crud_repo, async_session):
        new_instance = MySchema(field1="value1", field2="value2")
        await crud_repo.create(new_instance, session=async_session)
        result = await async_session.execute(select(MyModel))
        inserted_instance = result.scalar()
        assert inserted_instance is not None
        assert inserted_instance.field1 == "value1"
    
    @pytest.mark.asyncio
    async def test_get_instance(crud_repo, async_session):
        # Pre-populate data in the database
        instance = MyModel(field1="value1", field2="value2")
        async_session.add(instance)
        await async_session.commit()
    
        fetched_instance = await crud_repo.get({"field1": "value1"}, session=async_session)
        assert fetched_instance.field1 == "value1"
    
    @pytest.mark.asyncio
    async def test_update_instance(crud_repo, async_session):
        # Pre-populate data in the database
        instance = MyModel(field1="value1", field2="value2")
        async_session.add(instance)
        await async_session.commit()
    
        # Create a schema with updated data
        instance_to_update = MySchema(field1="value1", field2="new_value")
        updated_instance = await crud_repo.update(instance_to_update, session=async_session)
        assert updated_instance.field2 == "new_value"
    
    @pytest.mark.asyncio
    async def test_delete_instance(crud_repo, async_session):
        # Pre-populate data in the database
        instance = MyModel(field1="value1", field2="value2")
        async_session.add(instance)
        await async_session.commit()
    
        # Delete the instance
        await crud_repo.delete({"field1": "value1"}, session=async_session)
        result = await async_session.execute(select(MyModel))
        remaining_instance = result.scalar()
        assert remaining_instance is None
    
    @pytest.mark.asyncio
    async def test_filter_instances(crud_repo, async_session):
        # Pre-populate data in the database
        instance1 = MyModel(field1="value1", field2="value2")
        instance2 = MyModel(field1="value3", field2="value4")
        async_session.add_all([instance1, instance2])
        await async_session.commit()
    
        # Fetch instances with a filter
        filtered_instances = await crud_repo.filter({"field1": "value1"}, session=async_session)
        assert len(filtered_instances) == 1
        assert filtered_instances[0].field1 == "value1"
    
    @pytest.mark.asyncio
    async def test_update_or_create(crud_repo, async_session):
        # Testing update_or_create logic
        new_instance = MySchema(field1="value5", field2="value6")
        created, instance = await crud_repo.update_or_create(new_instance, session=async_session)
        assert created is True
    
        # Try updating the same instance
        updated_instance = MySchema(field1="value5", field2="new_value6")
        created, instance = await crud_repo.update_or_create(updated_instance, session=async_session)
        assert created is False
        assert instance.field2 == "new_value6"
    

    Explanation:

    • Setup: The setup_database fixture initializes the in-memory SQLite database and sets up tables before each test.
    • CRUD Operations: The tests demonstrate creating, reading, updating, and deleting an instance of MyModel using the BaseCrudRepository.
    • Filter and Query: Tests checking filtering logic ensure that the repository retrieves correct records based on the filter provided.
    • update_or_create: Confirms that an instance can be either updated or created based on existing records.

    These tests ensure each repository function behaves as expected, and help in identifying issues when any of those functions encounter unexpected data or conditions. Adjust your fields, models, and schemas according to your specific setup.

  9. User

    add tests for sqlmodels with relation

  10. Assistant

    Testing SQLAlchemy models with relationships involves setting up and verifying the proper creation, querying, and integrity of related records. In the following example, I'll illustrate how to test SQLAlchemy models that include relationships. We will focus on a simple one-to-many relationship between hypothetical Author and Book models.

    Example Models

    Let's assume we have the following SQLAlchemy models with a one-to-many relationship:

    from sqlalchemy import Column, Integer, String, ForeignKey
    from sqlalchemy.orm import relationship
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    
    class Author(Base):
        __tablename__ = 'authors'
    
        id = Column(Integer, primary_key=True)
        name = Column(String, nullable=False)
    
        books = relationship("Book", back_populates="author")
    
    class Book(Base):
        __tablename__ = 'books'
    
        id = Column(Integer, primary_key=True)
        title = Column(String, nullable=False)
        author_id = Column(Integer, ForeignKey('authors.id'))
    
        author = relationship("Author", back_populates="books")
    

    Testing Setup

    You should have a testing environment configured similarly to the previous example, but ensure you handle the creation of both tables and their relationships.

    import pytest
    from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.future import select
    from yourapp.models import Author, Book  # Assuming you placed these in `yourapp.models`
    
    DATABASE_URL = "sqlite+aiosqlite:///:memory:"
    engine = create_async_engine(DATABASE_URL, echo=True)
    AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    
    @pytest.fixture(scope="function", autouse=True)
    async def async_session():
        async with AsyncSessionLocal() as session:
            yield session
    
    @pytest.fixture(scope="function", autouse=True)
    async def setup_database():
        async with engine.begin() as conn:
            # Drop and create tables for tests
            await conn.run_sync(Base.metadata.drop_all)
            await conn.run_sync(Base.metadata.create_all)
        yield
    

    Test Cases

    These tests cover creating objects and ensuring the relationships work as expected:

    @pytest.mark.asyncio
    async def test_create_author_with_books(async_session):
        author = Author(name="Author 1", books=[Book(title="Book 1"), Book(title="Book 2")])
    
        async_session.add(author)
        await async_session.commit()
    
        # Retrieve and verify
        result = await async_session.execute(select(Author).options(selectinload(Author.books)))
        authors = result.scalars().all()
    
        assert len(authors) == 1
        assert len(authors[0].books) == 2
        assert authors[0].books[0].title == "Book 1"
        assert authors[0].books[1].title == "Book 2"
    
    @pytest.mark.asyncio
    async def test_query_books_by_author(async_session):
        # Pre-populate data in the database
        author = Author(name="Author 2", books=[Book(title="Book 3"), Book(title="Book 4")])
        async_session.add(author)
        await async_session.commit()
    
        # Query books for a specific author
        result = await async_session.execute(select(Book).join(Book.author).where(Author.name == "Author 2"))
        books = result.scalars().all()
    
        assert len(books) == 2
        assert books[0].title == "Book 3"
        assert books[1].title == "Book 4"
    
    @pytest.mark.asyncio
    async def test_delete_author_cascades(async_session):
        # Pre-populate data
        author = Author(name="Author 3", books=[Book(title="Book 5")])
        async_session.add(author)
        await async_session.commit()
    
        # Delete the author
        await async_session.delete(author)
        await async_session.commit()
    
        # Verify cascade delete
        result = await async_session.execute(select(Book).filter_by(author_id=author.id))
        books = result.scalars().all()
    
        assert len(books) == 0
    

    Explanation

    • Test Creation: The first test checks that an Author with multiple Book entries can be created and retrieved correctly using relationships.
    • Test Query: Verifies books can be fetched based on their related author.
    • Test Deletion: Confirms cascading delete behavior, which should remove associated books when an author is deleted.

    Note: Ensure that your models' relationships are configured properly to enable cascading deletes or handle cascade behaviors as you intend in your application. Adjust tests as necessary for additional settings like cascading on delete or other complex relationship behaviors.