import logging from collections import defaultdict from collections.abc import Sequence…
- 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, ) - 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 aBaseCrudInterfaceand 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:
-
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.
-
Options and Joins:
- Prepares SQLAlchemy
selectoperation options, specifically for managing relations and eager loading (usingcontains_eager). - Handles joining of related models, accounting for both aliased and non-aliased relationships.
- Prepares SQLAlchemy
-
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
joinstatements based on relationships specified in select options.
-
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.
-
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.
-
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.
-
- 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 instancedef 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_datadef 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, optionsdef 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
- 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:
-
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.
-
Clause and Condition Handling:
- clause_for_column: Constructs
whereconditions for SQLAlchemy queries. Depending on the type ofval, it either checks equality, membership (in_clause), or performs a range orilikeoperation.
- clause_for_column: Constructs
-
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.
-
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.
-
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.
-
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_rowacross 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.
-
- User
create tests for this code
- Assistant
To create tests for the given code, you would typically use a testing framework like
pytestalong 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
- 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.
- 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) yieldTest 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.
- User
for previous listing add tests to
- Assistant
To test the previous implementation of the
BaseCrudRepositoryclass, 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 usingpytestandSQLAlchemyfor 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_databasefixture 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
MyModelusing theBaseCrudRepository. - 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.
- Setup: The
- User
add tests for sqlmodels with relation
- 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
AuthorandBookmodels.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) yieldTest 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) == 0Explanation
- Test Creation: The first test checks that an
Authorwith multipleBookentries 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.
- Test Creation: The first test checks that an