Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# orm/_orm_constructors.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10import typing
11from typing import Any
12from typing import Callable
13from typing import Collection
14from typing import Iterable
15from typing import NoReturn
16from typing import Optional
17from typing import overload
18from typing import Type
19from typing import TYPE_CHECKING
20from typing import Union
22from . import mapperlib as mapperlib
23from ._typing import _O
24from .descriptor_props import Composite
25from .descriptor_props import Synonym
26from .interfaces import _AttributeOptions
27from .properties import MappedColumn
28from .properties import MappedSQLExpression
29from .query import AliasOption
30from .relationships import _RelationshipArgumentType
31from .relationships import _RelationshipBackPopulatesArgument
32from .relationships import _RelationshipDeclared
33from .relationships import _RelationshipSecondaryArgument
34from .relationships import RelationshipProperty
35from .session import Session
36from .util import _ORMJoin
37from .util import AliasedClass
38from .util import AliasedInsp
39from .util import LoaderCriteriaOption
40from .. import sql
41from .. import util
42from ..exc import InvalidRequestError
43from ..sql._typing import _no_kw
44from ..sql.base import _NoArg
45from ..sql.base import SchemaEventTarget
46from ..sql.schema import _InsertSentinelColumnDefault
47from ..sql.schema import SchemaConst
48from ..sql.selectable import FromClause
49from ..util.typing import Annotated
50from ..util.typing import Literal
52if TYPE_CHECKING:
53 from ._typing import _EntityType
54 from ._typing import _ORMColumnExprArgument
55 from .descriptor_props import _CC
56 from .descriptor_props import _CompositeAttrType
57 from .interfaces import PropComparator
58 from .mapper import Mapper
59 from .query import Query
60 from .relationships import _LazyLoadArgumentType
61 from .relationships import _ORMColCollectionArgument
62 from .relationships import _ORMOrderByArgument
63 from .relationships import _RelationshipJoinConditionArgument
64 from .relationships import ORMBackrefArgument
65 from .session import _SessionBind
66 from ..sql._typing import _AutoIncrementType
67 from ..sql._typing import _ColumnExpressionArgument
68 from ..sql._typing import _FromClauseArgument
69 from ..sql._typing import _InfoType
70 from ..sql._typing import _OnClauseArgument
71 from ..sql._typing import _TypeEngineArgument
72 from ..sql.elements import ColumnElement
73 from ..sql.schema import _ServerDefaultArgument
74 from ..sql.schema import _ServerOnUpdateArgument
75 from ..sql.selectable import Alias
76 from ..sql.selectable import Subquery
79_T = typing.TypeVar("_T")
82@util.deprecated(
83 "1.4",
84 "The :class:`.AliasOption` object is not necessary "
85 "for entities to be matched up to a query that is established "
86 "via :meth:`.Query.from_statement` and now does nothing.",
87 enable_warnings=False, # AliasOption itself warns
88)
89def contains_alias(alias: Union[Alias, Subquery]) -> AliasOption:
90 r"""Return a :class:`.MapperOption` that will indicate to the
91 :class:`_query.Query`
92 that the main table has been aliased.
94 """
95 return AliasOption(alias)
98def mapped_column(
99 __name_pos: Optional[
100 Union[str, _TypeEngineArgument[Any], SchemaEventTarget]
101 ] = None,
102 __type_pos: Optional[
103 Union[_TypeEngineArgument[Any], SchemaEventTarget]
104 ] = None,
105 /,
106 *args: SchemaEventTarget,
107 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
108 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
109 default: Optional[Any] = _NoArg.NO_ARG,
110 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
111 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
112 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
113 nullable: Optional[
114 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
115 ] = SchemaConst.NULL_UNSPECIFIED,
116 primary_key: Optional[bool] = False,
117 deferred: Union[_NoArg, bool] = _NoArg.NO_ARG,
118 deferred_group: Optional[str] = None,
119 deferred_raiseload: Optional[bool] = None,
120 use_existing_column: bool = False,
121 name: Optional[str] = None,
122 type_: Optional[_TypeEngineArgument[Any]] = None,
123 autoincrement: _AutoIncrementType = "auto",
124 doc: Optional[str] = None,
125 key: Optional[str] = None,
126 index: Optional[bool] = None,
127 unique: Optional[bool] = None,
128 info: Optional[_InfoType] = None,
129 onupdate: Optional[Any] = None,
130 insert_default: Optional[Any] = _NoArg.NO_ARG,
131 server_default: Optional[_ServerDefaultArgument] = None,
132 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
133 active_history: bool = False,
134 quote: Optional[bool] = None,
135 system: bool = False,
136 comment: Optional[str] = None,
137 sort_order: Union[_NoArg, int] = _NoArg.NO_ARG,
138 **kw: Any,
139) -> MappedColumn[Any]:
140 r"""declare a new ORM-mapped :class:`_schema.Column` construct
141 for use within :ref:`Declarative Table <orm_declarative_table>`
142 configuration.
144 The :func:`_orm.mapped_column` function provides an ORM-aware and
145 Python-typing-compatible construct which is used with
146 :ref:`declarative <orm_declarative_mapping>` mappings to indicate an
147 attribute that's mapped to a Core :class:`_schema.Column` object. It
148 provides the equivalent feature as mapping an attribute to a
149 :class:`_schema.Column` object directly when using Declarative,
150 specifically when using :ref:`Declarative Table <orm_declarative_table>`
151 configuration.
153 .. versionadded:: 2.0
155 :func:`_orm.mapped_column` is normally used with explicit typing along with
156 the :class:`_orm.Mapped` annotation type, where it can derive the SQL
157 type and nullability for the column based on what's present within the
158 :class:`_orm.Mapped` annotation. It also may be used without annotations
159 as a drop-in replacement for how :class:`_schema.Column` is used in
160 Declarative mappings in SQLAlchemy 1.x style.
162 For usage examples of :func:`_orm.mapped_column`, see the documentation
163 at :ref:`orm_declarative_table`.
165 .. seealso::
167 :ref:`orm_declarative_table` - complete documentation
169 :ref:`whatsnew_20_orm_declarative_typing` - migration notes for
170 Declarative mappings using 1.x style mappings
172 :param __name: String name to give to the :class:`_schema.Column`. This
173 is an optional, positional only argument that if present must be the
174 first positional argument passed. If omitted, the attribute name to
175 which the :func:`_orm.mapped_column` is mapped will be used as the SQL
176 column name.
177 :param __type: :class:`_types.TypeEngine` type or instance which will
178 indicate the datatype to be associated with the :class:`_schema.Column`.
179 This is an optional, positional-only argument that if present must
180 immediately follow the ``__name`` parameter if present also, or otherwise
181 be the first positional parameter. If omitted, the ultimate type for
182 the column may be derived either from the annotated type, or if a
183 :class:`_schema.ForeignKey` is present, from the datatype of the
184 referenced column.
185 :param \*args: Additional positional arguments include constructs such
186 as :class:`_schema.ForeignKey`, :class:`_schema.CheckConstraint`,
187 and :class:`_schema.Identity`, which are passed through to the constructed
188 :class:`_schema.Column`.
189 :param nullable: Optional bool, whether the column should be "NULL" or
190 "NOT NULL". If omitted, the nullability is derived from the type
191 annotation based on whether or not ``typing.Optional`` is present.
192 ``nullable`` defaults to ``True`` otherwise for non-primary key columns,
193 and ``False`` for primary key columns.
194 :param primary_key: optional bool, indicates the :class:`_schema.Column`
195 would be part of the table's primary key or not.
196 :param deferred: Optional bool - this keyword argument is consumed by the
197 ORM declarative process, and is not part of the :class:`_schema.Column`
198 itself; instead, it indicates that this column should be "deferred" for
199 loading as though mapped by :func:`_orm.deferred`.
201 .. seealso::
203 :ref:`orm_queryguide_deferred_declarative`
205 :param deferred_group: Implies :paramref:`_orm.mapped_column.deferred`
206 to ``True``, and set the :paramref:`_orm.deferred.group` parameter.
208 .. seealso::
210 :ref:`orm_queryguide_deferred_group`
212 :param deferred_raiseload: Implies :paramref:`_orm.mapped_column.deferred`
213 to ``True``, and set the :paramref:`_orm.deferred.raiseload` parameter.
215 .. seealso::
217 :ref:`orm_queryguide_deferred_raiseload`
219 :param use_existing_column: if True, will attempt to locate the given
220 column name on an inherited superclass (typically single inheriting
221 superclass), and if present, will not produce a new column, mapping
222 to the superclass column as though it were omitted from this class.
223 This is used for mixins that add new columns to an inherited superclass.
225 .. seealso::
227 :ref:`orm_inheritance_column_conflicts`
229 .. versionadded:: 2.0.0b4
231 :param default: Passed directly to the
232 :paramref:`_schema.Column.default` parameter if the
233 :paramref:`_orm.mapped_column.insert_default` parameter is not present.
234 Additionally, when used with :ref:`orm_declarative_native_dataclasses`,
235 indicates a default Python value that should be applied to the keyword
236 constructor within the generated ``__init__()`` method.
238 Note that in the case of dataclass generation when
239 :paramref:`_orm.mapped_column.insert_default` is not present, this means
240 the :paramref:`_orm.mapped_column.default` value is used in **two**
241 places, both the ``__init__()`` method as well as the
242 :paramref:`_schema.Column.default` parameter. While this behavior may
243 change in a future release, for the moment this tends to "work out"; a
244 default of ``None`` will mean that the :class:`_schema.Column` gets no
245 default generator, whereas a default that refers to a non-``None`` Python
246 or SQL expression value will be assigned up front on the object when
247 ``__init__()`` is called, which is the same value that the Core
248 :class:`_sql.Insert` construct would use in any case, leading to the same
249 end result.
251 .. note:: When using Core level column defaults that are callables to
252 be interpreted by the underlying :class:`_schema.Column` in conjunction
253 with :ref:`ORM-mapped dataclasses
254 <orm_declarative_native_dataclasses>`, especially those that are
255 :ref:`context-aware default functions <context_default_functions>`,
256 **the** :paramref:`_orm.mapped_column.insert_default` **parameter must
257 be used instead**. This is necessary to disambiguate the callable from
258 being interpreted as a dataclass level default.
260 .. seealso::
262 :ref:`defaults_default_factory_insert_default`
264 :paramref:`_orm.mapped_column.insert_default`
266 :paramref:`_orm.mapped_column.default_factory`
268 :param insert_default: Passed directly to the
269 :paramref:`_schema.Column.default` parameter; will supersede the value
270 of :paramref:`_orm.mapped_column.default` when present, however
271 :paramref:`_orm.mapped_column.default` will always apply to the
272 constructor default for a dataclasses mapping.
274 .. seealso::
276 :ref:`defaults_default_factory_insert_default`
278 :paramref:`_orm.mapped_column.default`
280 :paramref:`_orm.mapped_column.default_factory`
282 :param sort_order: An integer that indicates how this mapped column
283 should be sorted compared to the others when the ORM is creating a
284 :class:`_schema.Table`. Among mapped columns that have the same
285 value the default ordering is used, placing first the mapped columns
286 defined in the main class, then the ones in the super classes.
287 Defaults to 0. The sort is ascending.
289 .. versionadded:: 2.0.4
291 :param active_history=False:
293 When ``True``, indicates that the "previous" value for a
294 scalar attribute should be loaded when replaced, if not
295 already loaded. Normally, history tracking logic for
296 simple non-primary-key scalar values only needs to be
297 aware of the "new" value in order to perform a flush. This
298 flag is available for applications that make use of
299 :func:`.attributes.get_history` or :meth:`.Session.is_modified`
300 which also need to know the "previous" value of the attribute.
302 .. versionadded:: 2.0.10
305 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
306 specifies if the mapped attribute should be part of the ``__init__()``
307 method as generated by the dataclass process.
308 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
309 specifies if the mapped attribute should be part of the ``__repr__()``
310 method as generated by the dataclass process.
311 :param default_factory: Specific to
312 :ref:`orm_declarative_native_dataclasses`,
313 specifies a default-value generation function that will take place
314 as part of the ``__init__()``
315 method as generated by the dataclass process.
317 .. seealso::
319 :ref:`defaults_default_factory_insert_default`
321 :paramref:`_orm.mapped_column.default`
323 :paramref:`_orm.mapped_column.insert_default`
325 :param compare: Specific to
326 :ref:`orm_declarative_native_dataclasses`, indicates if this field
327 should be included in comparison operations when generating the
328 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
330 .. versionadded:: 2.0.0b4
332 :param kw_only: Specific to
333 :ref:`orm_declarative_native_dataclasses`, indicates if this field
334 should be marked as keyword-only when generating the ``__init__()``.
336 :param \**kw: All remaining keyword arguments are passed through to the
337 constructor for the :class:`_schema.Column`.
339 """
341 return MappedColumn(
342 __name_pos,
343 __type_pos,
344 *args,
345 name=name,
346 type_=type_,
347 autoincrement=autoincrement,
348 insert_default=insert_default,
349 attribute_options=_AttributeOptions(
350 init, repr, default, default_factory, compare, kw_only
351 ),
352 doc=doc,
353 key=key,
354 index=index,
355 unique=unique,
356 info=info,
357 active_history=active_history,
358 nullable=nullable,
359 onupdate=onupdate,
360 primary_key=primary_key,
361 server_default=server_default,
362 server_onupdate=server_onupdate,
363 use_existing_column=use_existing_column,
364 quote=quote,
365 comment=comment,
366 system=system,
367 deferred=deferred,
368 deferred_group=deferred_group,
369 deferred_raiseload=deferred_raiseload,
370 sort_order=sort_order,
371 **kw,
372 )
375def orm_insert_sentinel(
376 name: Optional[str] = None,
377 type_: Optional[_TypeEngineArgument[Any]] = None,
378 *,
379 default: Optional[Any] = None,
380 omit_from_statements: bool = True,
381) -> MappedColumn[Any]:
382 """Provides a surrogate :func:`_orm.mapped_column` that generates
383 a so-called :term:`sentinel` column, allowing efficient bulk
384 inserts with deterministic RETURNING sorting for tables that don't
385 otherwise have qualifying primary key configurations.
387 Use of :func:`_orm.orm_insert_sentinel` is analogous to the use of the
388 :func:`_schema.insert_sentinel` construct within a Core
389 :class:`_schema.Table` construct.
391 Guidelines for adding this construct to a Declarative mapped class
392 are the same as that of the :func:`_schema.insert_sentinel` construct;
393 the database table itself also needs to have a column with this name
394 present.
396 For background on how this object is used, see the section
397 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
398 section :ref:`engine_insertmanyvalues`.
400 .. seealso::
402 :func:`_schema.insert_sentinel`
404 :ref:`engine_insertmanyvalues`
406 :ref:`engine_insertmanyvalues_sentinel_columns`
409 .. versionadded:: 2.0.10
411 """
413 return mapped_column(
414 name=name,
415 default=(
416 default if default is not None else _InsertSentinelColumnDefault()
417 ),
418 _omit_from_statements=omit_from_statements,
419 insert_sentinel=True,
420 use_existing_column=True,
421 nullable=True,
422 )
425@util.deprecated_params(
426 **{
427 arg: (
428 "2.0",
429 f"The :paramref:`_orm.column_property.{arg}` parameter is "
430 "deprecated for :func:`_orm.column_property`. This parameter "
431 "applies to a writeable-attribute in a Declarative Dataclasses "
432 "configuration only, and :func:`_orm.column_property` is treated "
433 "as a read-only attribute in this context.",
434 )
435 for arg in ("init", "kw_only", "default", "default_factory")
436 }
437)
438def column_property(
439 column: _ORMColumnExprArgument[_T],
440 *additional_columns: _ORMColumnExprArgument[Any],
441 group: Optional[str] = None,
442 deferred: bool = False,
443 raiseload: bool = False,
444 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
445 init: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
446 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
447 default: Optional[Any] = _NoArg.NO_ARG,
448 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
449 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
450 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
451 active_history: bool = False,
452 expire_on_flush: bool = True,
453 info: Optional[_InfoType] = None,
454 doc: Optional[str] = None,
455) -> MappedSQLExpression[_T]:
456 r"""Provide a column-level property for use with a mapping.
458 With Declarative mappings, :func:`_orm.column_property` is used to
459 map read-only SQL expressions to a mapped class.
461 When using Imperative mappings, :func:`_orm.column_property` also
462 takes on the role of mapping table columns with additional features.
463 When using fully Declarative mappings, the :func:`_orm.mapped_column`
464 construct should be used for this purpose.
466 With Declarative Dataclass mappings, :func:`_orm.column_property`
467 is considered to be **read only**, and will not be included in the
468 Dataclass ``__init__()`` constructor.
470 The :func:`_orm.column_property` function returns an instance of
471 :class:`.ColumnProperty`.
473 .. seealso::
475 :ref:`mapper_column_property_sql_expressions` - general use of
476 :func:`_orm.column_property` to map SQL expressions
478 :ref:`orm_imperative_table_column_options` - usage of
479 :func:`_orm.column_property` with Imperative Table mappings to apply
480 additional options to a plain :class:`_schema.Column` object
482 :param \*cols:
483 list of Column objects to be mapped.
485 :param active_history=False:
487 Used only for Imperative Table mappings, or legacy-style Declarative
488 mappings (i.e. which have not been upgraded to
489 :func:`_orm.mapped_column`), for column-based attributes that are
490 expected to be writeable; use :func:`_orm.mapped_column` with
491 :paramref:`_orm.mapped_column.active_history` for Declarative mappings.
492 See that parameter for functional details.
494 :param comparator_factory: a class which extends
495 :class:`.ColumnProperty.Comparator` which provides custom SQL
496 clause generation for comparison operations.
498 :param group:
499 a group name for this property when marked as deferred.
501 :param deferred:
502 when True, the column property is "deferred", meaning that
503 it does not load immediately, and is instead loaded when the
504 attribute is first accessed on an instance. See also
505 :func:`~sqlalchemy.orm.deferred`.
507 :param doc:
508 optional string that will be applied as the doc on the
509 class-bound descriptor.
511 :param expire_on_flush=True:
512 Disable expiry on flush. A column_property() which refers
513 to a SQL expression (and not a single table-bound column)
514 is considered to be a "read only" property; populating it
515 has no effect on the state of data, and it can only return
516 database state. For this reason a column_property()'s value
517 is expired whenever the parent object is involved in a
518 flush, that is, has any kind of "dirty" state within a flush.
519 Setting this parameter to ``False`` will have the effect of
520 leaving any existing value present after the flush proceeds.
521 Note that the :class:`.Session` with default expiration
522 settings still expires
523 all attributes after a :meth:`.Session.commit` call, however.
525 :param info: Optional data dictionary which will be populated into the
526 :attr:`.MapperProperty.info` attribute of this object.
528 :param raiseload: if True, indicates the column should raise an error
529 when undeferred, rather than loading the value. This can be
530 altered at query time by using the :func:`.deferred` option with
531 raiseload=False.
533 .. versionadded:: 1.4
535 .. seealso::
537 :ref:`orm_queryguide_deferred_raiseload`
539 :param init:
541 :param default:
543 :param default_factory:
545 :param kw_only:
547 """
548 return MappedSQLExpression(
549 column,
550 *additional_columns,
551 attribute_options=_AttributeOptions(
552 False if init is _NoArg.NO_ARG else init,
553 repr,
554 default,
555 default_factory,
556 compare,
557 kw_only,
558 ),
559 group=group,
560 deferred=deferred,
561 raiseload=raiseload,
562 comparator_factory=comparator_factory,
563 active_history=active_history,
564 expire_on_flush=expire_on_flush,
565 info=info,
566 doc=doc,
567 _assume_readonly_dc_attributes=True,
568 )
571@overload
572def composite(
573 _class_or_attr: _CompositeAttrType[Any],
574 /,
575 *attrs: _CompositeAttrType[Any],
576 group: Optional[str] = None,
577 deferred: bool = False,
578 raiseload: bool = False,
579 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
580 active_history: bool = False,
581 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
582 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
583 default: Optional[Any] = _NoArg.NO_ARG,
584 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
585 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
586 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
587 info: Optional[_InfoType] = None,
588 doc: Optional[str] = None,
589 **__kw: Any,
590) -> Composite[Any]: ...
593@overload
594def composite(
595 _class_or_attr: Type[_CC],
596 /,
597 *attrs: _CompositeAttrType[Any],
598 group: Optional[str] = None,
599 deferred: bool = False,
600 raiseload: bool = False,
601 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
602 active_history: bool = False,
603 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
604 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
605 default: Optional[Any] = _NoArg.NO_ARG,
606 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
607 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
608 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
609 info: Optional[_InfoType] = None,
610 doc: Optional[str] = None,
611 **__kw: Any,
612) -> Composite[_CC]: ...
615@overload
616def composite(
617 _class_or_attr: Callable[..., _CC],
618 /,
619 *attrs: _CompositeAttrType[Any],
620 group: Optional[str] = None,
621 deferred: bool = False,
622 raiseload: bool = False,
623 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
624 active_history: bool = False,
625 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
626 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
627 default: Optional[Any] = _NoArg.NO_ARG,
628 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
629 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
630 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
631 info: Optional[_InfoType] = None,
632 doc: Optional[str] = None,
633 **__kw: Any,
634) -> Composite[_CC]: ...
637def composite(
638 _class_or_attr: Union[
639 None, Type[_CC], Callable[..., _CC], _CompositeAttrType[Any]
640 ] = None,
641 /,
642 *attrs: _CompositeAttrType[Any],
643 group: Optional[str] = None,
644 deferred: bool = False,
645 raiseload: bool = False,
646 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
647 active_history: bool = False,
648 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
649 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
650 default: Optional[Any] = _NoArg.NO_ARG,
651 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
652 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
653 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
654 info: Optional[_InfoType] = None,
655 doc: Optional[str] = None,
656 **__kw: Any,
657) -> Composite[Any]:
658 r"""Return a composite column-based property for use with a Mapper.
660 See the mapping documentation section :ref:`mapper_composite` for a
661 full usage example.
663 The :class:`.MapperProperty` returned by :func:`.composite`
664 is the :class:`.Composite`.
666 :param class\_:
667 The "composite type" class, or any classmethod or callable which
668 will produce a new instance of the composite object given the
669 column values in order.
671 :param \*attrs:
672 List of elements to be mapped, which may include:
674 * :class:`_schema.Column` objects
675 * :func:`_orm.mapped_column` constructs
676 * string names of other attributes on the mapped class, which may be
677 any other SQL or object-mapped attribute. This can for
678 example allow a composite that refers to a many-to-one relationship
680 :param active_history=False:
681 When ``True``, indicates that the "previous" value for a
682 scalar attribute should be loaded when replaced, if not
683 already loaded. See the same flag on :func:`.column_property`.
685 :param group:
686 A group name for this property when marked as deferred.
688 :param deferred:
689 When True, the column property is "deferred", meaning that it does
690 not load immediately, and is instead loaded when the attribute is
691 first accessed on an instance. See also
692 :func:`~sqlalchemy.orm.deferred`.
694 :param comparator_factory: a class which extends
695 :class:`.Composite.Comparator` which provides custom SQL
696 clause generation for comparison operations.
698 :param doc:
699 optional string that will be applied as the doc on the
700 class-bound descriptor.
702 :param info: Optional data dictionary which will be populated into the
703 :attr:`.MapperProperty.info` attribute of this object.
705 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
706 specifies if the mapped attribute should be part of the ``__init__()``
707 method as generated by the dataclass process.
708 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
709 specifies if the mapped attribute should be part of the ``__repr__()``
710 method as generated by the dataclass process.
711 :param default_factory: Specific to
712 :ref:`orm_declarative_native_dataclasses`,
713 specifies a default-value generation function that will take place
714 as part of the ``__init__()``
715 method as generated by the dataclass process.
717 :param compare: Specific to
718 :ref:`orm_declarative_native_dataclasses`, indicates if this field
719 should be included in comparison operations when generating the
720 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
722 .. versionadded:: 2.0.0b4
724 :param kw_only: Specific to
725 :ref:`orm_declarative_native_dataclasses`, indicates if this field
726 should be marked as keyword-only when generating the ``__init__()``.
728 """
729 if __kw:
730 raise _no_kw()
732 return Composite(
733 _class_or_attr,
734 *attrs,
735 attribute_options=_AttributeOptions(
736 init, repr, default, default_factory, compare, kw_only
737 ),
738 group=group,
739 deferred=deferred,
740 raiseload=raiseload,
741 comparator_factory=comparator_factory,
742 active_history=active_history,
743 info=info,
744 doc=doc,
745 )
748def with_loader_criteria(
749 entity_or_base: _EntityType[Any],
750 where_criteria: Union[
751 _ColumnExpressionArgument[bool],
752 Callable[[Any], _ColumnExpressionArgument[bool]],
753 ],
754 loader_only: bool = False,
755 include_aliases: bool = False,
756 propagate_to_loaders: bool = True,
757 track_closure_variables: bool = True,
758) -> LoaderCriteriaOption:
759 """Add additional WHERE criteria to the load for all occurrences of
760 a particular entity.
762 .. versionadded:: 1.4
764 The :func:`_orm.with_loader_criteria` option is intended to add
765 limiting criteria to a particular kind of entity in a query,
766 **globally**, meaning it will apply to the entity as it appears
767 in the SELECT query as well as within any subqueries, join
768 conditions, and relationship loads, including both eager and lazy
769 loaders, without the need for it to be specified in any particular
770 part of the query. The rendering logic uses the same system used by
771 single table inheritance to ensure a certain discriminator is applied
772 to a table.
774 E.g., using :term:`2.0-style` queries, we can limit the way the
775 ``User.addresses`` collection is loaded, regardless of the kind
776 of loading used::
778 from sqlalchemy.orm import with_loader_criteria
780 stmt = select(User).options(
781 selectinload(User.addresses),
782 with_loader_criteria(Address, Address.email_address != 'foo'))
783 )
785 Above, the "selectinload" for ``User.addresses`` will apply the
786 given filtering criteria to the WHERE clause.
788 Another example, where the filtering will be applied to the
789 ON clause of the join, in this example using :term:`1.x style`
790 queries::
792 q = session.query(User).outerjoin(User.addresses).options(
793 with_loader_criteria(Address, Address.email_address != 'foo'))
794 )
796 The primary purpose of :func:`_orm.with_loader_criteria` is to use
797 it in the :meth:`_orm.SessionEvents.do_orm_execute` event handler
798 to ensure that all occurrences of a particular entity are filtered
799 in a certain way, such as filtering for access control roles. It
800 also can be used to apply criteria to relationship loads. In the
801 example below, we can apply a certain set of rules to all queries
802 emitted by a particular :class:`_orm.Session`::
804 session = Session(bind=engine)
806 @event.listens_for("do_orm_execute", session)
807 def _add_filtering_criteria(execute_state):
809 if (
810 execute_state.is_select
811 and not execute_state.is_column_load
812 and not execute_state.is_relationship_load
813 ):
814 execute_state.statement = execute_state.statement.options(
815 with_loader_criteria(
816 SecurityRole,
817 lambda cls: cls.role.in_(['some_role']),
818 include_aliases=True
819 )
820 )
822 In the above example, the :meth:`_orm.SessionEvents.do_orm_execute`
823 event will intercept all queries emitted using the
824 :class:`_orm.Session`. For those queries which are SELECT statements
825 and are not attribute or relationship loads a custom
826 :func:`_orm.with_loader_criteria` option is added to the query. The
827 :func:`_orm.with_loader_criteria` option will be used in the given
828 statement and will also be automatically propagated to all relationship
829 loads that descend from this query.
831 The criteria argument given is a ``lambda`` that accepts a ``cls``
832 argument. The given class will expand to include all mapped subclass
833 and need not itself be a mapped class.
835 .. tip::
837 When using :func:`_orm.with_loader_criteria` option in
838 conjunction with the :func:`_orm.contains_eager` loader option,
839 it's important to note that :func:`_orm.with_loader_criteria` only
840 affects the part of the query that determines what SQL is rendered
841 in terms of the WHERE and FROM clauses. The
842 :func:`_orm.contains_eager` option does not affect the rendering of
843 the SELECT statement outside of the columns clause, so does not have
844 any interaction with the :func:`_orm.with_loader_criteria` option.
845 However, the way things "work" is that :func:`_orm.contains_eager`
846 is meant to be used with a query that is already selecting from the
847 additional entities in some way, where
848 :func:`_orm.with_loader_criteria` can apply it's additional
849 criteria.
851 In the example below, assuming a mapping relationship as
852 ``A -> A.bs -> B``, the given :func:`_orm.with_loader_criteria`
853 option will affect the way in which the JOIN is rendered::
855 stmt = select(A).join(A.bs).options(
856 contains_eager(A.bs),
857 with_loader_criteria(B, B.flag == 1)
858 )
860 Above, the given :func:`_orm.with_loader_criteria` option will
861 affect the ON clause of the JOIN that is specified by
862 ``.join(A.bs)``, so is applied as expected. The
863 :func:`_orm.contains_eager` option has the effect that columns from
864 ``B`` are added to the columns clause::
866 SELECT
867 b.id, b.a_id, b.data, b.flag,
868 a.id AS id_1,
869 a.data AS data_1
870 FROM a JOIN b ON a.id = b.a_id AND b.flag = :flag_1
873 The use of the :func:`_orm.contains_eager` option within the above
874 statement has no effect on the behavior of the
875 :func:`_orm.with_loader_criteria` option. If the
876 :func:`_orm.contains_eager` option were omitted, the SQL would be
877 the same as regards the FROM and WHERE clauses, where
878 :func:`_orm.with_loader_criteria` continues to add its criteria to
879 the ON clause of the JOIN. The addition of
880 :func:`_orm.contains_eager` only affects the columns clause, in that
881 additional columns against ``b`` are added which are then consumed
882 by the ORM to produce ``B`` instances.
884 .. warning:: The use of a lambda inside of the call to
885 :func:`_orm.with_loader_criteria` is only invoked **once per unique
886 class**. Custom functions should not be invoked within this lambda.
887 See :ref:`engine_lambda_caching` for an overview of the "lambda SQL"
888 feature, which is for advanced use only.
890 :param entity_or_base: a mapped class, or a class that is a super
891 class of a particular set of mapped classes, to which the rule
892 will apply.
894 :param where_criteria: a Core SQL expression that applies limiting
895 criteria. This may also be a "lambda:" or Python function that
896 accepts a target class as an argument, when the given class is
897 a base with many different mapped subclasses.
899 .. note:: To support pickling, use a module-level Python function to
900 produce the SQL expression instead of a lambda or a fixed SQL
901 expression, which tend to not be picklable.
903 :param include_aliases: if True, apply the rule to :func:`_orm.aliased`
904 constructs as well.
906 :param propagate_to_loaders: defaults to True, apply to relationship
907 loaders such as lazy loaders. This indicates that the
908 option object itself including SQL expression is carried along with
909 each loaded instance. Set to ``False`` to prevent the object from
910 being assigned to individual instances.
913 .. seealso::
915 :ref:`examples_session_orm_events` - includes examples of using
916 :func:`_orm.with_loader_criteria`.
918 :ref:`do_orm_execute_global_criteria` - basic example on how to
919 combine :func:`_orm.with_loader_criteria` with the
920 :meth:`_orm.SessionEvents.do_orm_execute` event.
922 :param track_closure_variables: when False, closure variables inside
923 of a lambda expression will not be used as part of
924 any cache key. This allows more complex expressions to be used
925 inside of a lambda expression but requires that the lambda ensures
926 it returns the identical SQL every time given a particular class.
928 .. versionadded:: 1.4.0b2
930 """
931 return LoaderCriteriaOption(
932 entity_or_base,
933 where_criteria,
934 loader_only,
935 include_aliases,
936 propagate_to_loaders,
937 track_closure_variables,
938 )
941def relationship(
942 argument: Optional[_RelationshipArgumentType[Any]] = None,
943 secondary: Optional[_RelationshipSecondaryArgument] = None,
944 *,
945 uselist: Optional[bool] = None,
946 collection_class: Optional[
947 Union[Type[Collection[Any]], Callable[[], Collection[Any]]]
948 ] = None,
949 primaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
950 secondaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
951 back_populates: Optional[_RelationshipBackPopulatesArgument] = None,
952 order_by: _ORMOrderByArgument = False,
953 backref: Optional[ORMBackrefArgument] = None,
954 overlaps: Optional[str] = None,
955 post_update: bool = False,
956 cascade: str = "save-update, merge",
957 viewonly: bool = False,
958 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
959 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
960 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
961 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
962 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
963 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
964 lazy: _LazyLoadArgumentType = "select",
965 passive_deletes: Union[Literal["all"], bool] = False,
966 passive_updates: bool = True,
967 active_history: bool = False,
968 enable_typechecks: bool = True,
969 foreign_keys: Optional[_ORMColCollectionArgument] = None,
970 remote_side: Optional[_ORMColCollectionArgument] = None,
971 join_depth: Optional[int] = None,
972 comparator_factory: Optional[
973 Type[RelationshipProperty.Comparator[Any]]
974 ] = None,
975 single_parent: bool = False,
976 innerjoin: bool = False,
977 distinct_target_key: Optional[bool] = None,
978 load_on_pending: bool = False,
979 query_class: Optional[Type[Query[Any]]] = None,
980 info: Optional[_InfoType] = None,
981 omit_join: Literal[None, False] = None,
982 sync_backref: Optional[bool] = None,
983 **kw: Any,
984) -> _RelationshipDeclared[Any]:
985 """Provide a relationship between two mapped classes.
987 This corresponds to a parent-child or associative table relationship.
988 The constructed class is an instance of :class:`.Relationship`.
990 .. seealso::
992 :ref:`tutorial_orm_related_objects` - tutorial introduction
993 to :func:`_orm.relationship` in the :ref:`unified_tutorial`
995 :ref:`relationship_config_toplevel` - narrative documentation
997 :param argument:
998 This parameter refers to the class that is to be related. It
999 accepts several forms, including a direct reference to the target
1000 class itself, the :class:`_orm.Mapper` instance for the target class,
1001 a Python callable / lambda that will return a reference to the
1002 class or :class:`_orm.Mapper` when called, and finally a string
1003 name for the class, which will be resolved from the
1004 :class:`_orm.registry` in use in order to locate the class, e.g.::
1006 class SomeClass(Base):
1007 # ...
1009 related = relationship("RelatedClass")
1011 The :paramref:`_orm.relationship.argument` may also be omitted from the
1012 :func:`_orm.relationship` construct entirely, and instead placed inside
1013 a :class:`_orm.Mapped` annotation on the left side, which should
1014 include a Python collection type if the relationship is expected
1015 to be a collection, such as::
1017 class SomeClass(Base):
1018 # ...
1020 related_items: Mapped[List["RelatedItem"]] = relationship()
1022 Or for a many-to-one or one-to-one relationship::
1024 class SomeClass(Base):
1025 # ...
1027 related_item: Mapped["RelatedItem"] = relationship()
1029 .. seealso::
1031 :ref:`orm_declarative_properties` - further detail
1032 on relationship configuration when using Declarative.
1034 :param secondary:
1035 For a many-to-many relationship, specifies the intermediary
1036 table, and is typically an instance of :class:`_schema.Table`.
1037 In less common circumstances, the argument may also be specified
1038 as an :class:`_expression.Alias` construct, or even a
1039 :class:`_expression.Join` construct.
1041 :paramref:`_orm.relationship.secondary` may
1042 also be passed as a callable function which is evaluated at
1043 mapper initialization time. When using Declarative, it may also
1044 be a string argument noting the name of a :class:`_schema.Table`
1045 that is
1046 present in the :class:`_schema.MetaData`
1047 collection associated with the
1048 parent-mapped :class:`_schema.Table`.
1050 .. warning:: When passed as a Python-evaluable string, the
1051 argument is interpreted using Python's ``eval()`` function.
1052 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1053 See :ref:`declarative_relationship_eval` for details on
1054 declarative evaluation of :func:`_orm.relationship` arguments.
1056 The :paramref:`_orm.relationship.secondary` keyword argument is
1057 typically applied in the case where the intermediary
1058 :class:`_schema.Table`
1059 is not otherwise expressed in any direct class mapping. If the
1060 "secondary" table is also explicitly mapped elsewhere (e.g. as in
1061 :ref:`association_pattern`), one should consider applying the
1062 :paramref:`_orm.relationship.viewonly` flag so that this
1063 :func:`_orm.relationship`
1064 is not used for persistence operations which
1065 may conflict with those of the association object pattern.
1067 .. seealso::
1069 :ref:`relationships_many_to_many` - Reference example of "many
1070 to many".
1072 :ref:`self_referential_many_to_many` - Specifics on using
1073 many-to-many in a self-referential case.
1075 :ref:`declarative_many_to_many` - Additional options when using
1076 Declarative.
1078 :ref:`association_pattern` - an alternative to
1079 :paramref:`_orm.relationship.secondary`
1080 when composing association
1081 table relationships, allowing additional attributes to be
1082 specified on the association table.
1084 :ref:`composite_secondary_join` - a lesser-used pattern which
1085 in some cases can enable complex :func:`_orm.relationship` SQL
1086 conditions to be used.
1088 :param active_history=False:
1089 When ``True``, indicates that the "previous" value for a
1090 many-to-one reference should be loaded when replaced, if
1091 not already loaded. Normally, history tracking logic for
1092 simple many-to-ones only needs to be aware of the "new"
1093 value in order to perform a flush. This flag is available
1094 for applications that make use of
1095 :func:`.attributes.get_history` which also need to know
1096 the "previous" value of the attribute.
1098 :param backref:
1099 A reference to a string relationship name, or a :func:`_orm.backref`
1100 construct, which will be used to automatically generate a new
1101 :func:`_orm.relationship` on the related class, which then refers to this
1102 one using a bi-directional :paramref:`_orm.relationship.back_populates`
1103 configuration.
1105 In modern Python, explicit use of :func:`_orm.relationship`
1106 with :paramref:`_orm.relationship.back_populates` should be preferred,
1107 as it is more robust in terms of mapper configuration as well as
1108 more conceptually straightforward. It also integrates with
1109 new :pep:`484` typing features introduced in SQLAlchemy 2.0 which
1110 is not possible with dynamically generated attributes.
1112 .. seealso::
1114 :ref:`relationships_backref` - notes on using
1115 :paramref:`_orm.relationship.backref`
1117 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1118 presents an overview of bi-directional relationship configuration
1119 and behaviors using :paramref:`_orm.relationship.back_populates`
1121 :func:`.backref` - allows control over :func:`_orm.relationship`
1122 configuration when using :paramref:`_orm.relationship.backref`.
1125 :param back_populates:
1126 Indicates the name of a :func:`_orm.relationship` on the related
1127 class that will be synchronized with this one. It is usually
1128 expected that the :func:`_orm.relationship` on the related class
1129 also refer to this one. This allows objects on both sides of
1130 each :func:`_orm.relationship` to synchronize in-Python state
1131 changes and also provides directives to the :term:`unit of work`
1132 flush process how changes along these relationships should
1133 be persisted.
1135 .. seealso::
1137 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1138 presents an overview of bi-directional relationship configuration
1139 and behaviors.
1141 :ref:`relationship_patterns` - includes many examples of
1142 :paramref:`_orm.relationship.back_populates`.
1144 :paramref:`_orm.relationship.backref` - legacy form which allows
1145 more succinct configuration, but does not support explicit typing
1147 :param overlaps:
1148 A string name or comma-delimited set of names of other relationships
1149 on either this mapper, a descendant mapper, or a target mapper with
1150 which this relationship may write to the same foreign keys upon
1151 persistence. The only effect this has is to eliminate the
1152 warning that this relationship will conflict with another upon
1153 persistence. This is used for such relationships that are truly
1154 capable of conflicting with each other on write, but the application
1155 will ensure that no such conflicts occur.
1157 .. versionadded:: 1.4
1159 .. seealso::
1161 :ref:`error_qzyx` - usage example
1163 :param cascade:
1164 A comma-separated list of cascade rules which determines how
1165 Session operations should be "cascaded" from parent to child.
1166 This defaults to ``False``, which means the default cascade
1167 should be used - this default cascade is ``"save-update, merge"``.
1169 The available cascades are ``save-update``, ``merge``,
1170 ``expunge``, ``delete``, ``delete-orphan``, and ``refresh-expire``.
1171 An additional option, ``all`` indicates shorthand for
1172 ``"save-update, merge, refresh-expire,
1173 expunge, delete"``, and is often used as in ``"all, delete-orphan"``
1174 to indicate that related objects should follow along with the
1175 parent object in all cases, and be deleted when de-associated.
1177 .. seealso::
1179 :ref:`unitofwork_cascades` - Full detail on each of the available
1180 cascade options.
1182 :param cascade_backrefs=False:
1183 Legacy; this flag is always False.
1185 .. versionchanged:: 2.0 "cascade_backrefs" functionality has been
1186 removed.
1188 :param collection_class:
1189 A class or callable that returns a new list-holding object. will
1190 be used in place of a plain list for storing elements.
1192 .. seealso::
1194 :ref:`custom_collections` - Introductory documentation and
1195 examples.
1197 :param comparator_factory:
1198 A class which extends :class:`.Relationship.Comparator`
1199 which provides custom SQL clause generation for comparison
1200 operations.
1202 .. seealso::
1204 :class:`.PropComparator` - some detail on redefining comparators
1205 at this level.
1207 :ref:`custom_comparators` - Brief intro to this feature.
1210 :param distinct_target_key=None:
1211 Indicate if a "subquery" eager load should apply the DISTINCT
1212 keyword to the innermost SELECT statement. When left as ``None``,
1213 the DISTINCT keyword will be applied in those cases when the target
1214 columns do not comprise the full primary key of the target table.
1215 When set to ``True``, the DISTINCT keyword is applied to the
1216 innermost SELECT unconditionally.
1218 It may be desirable to set this flag to False when the DISTINCT is
1219 reducing performance of the innermost subquery beyond that of what
1220 duplicate innermost rows may be causing.
1222 .. seealso::
1224 :ref:`loading_toplevel` - includes an introduction to subquery
1225 eager loading.
1227 :param doc:
1228 Docstring which will be applied to the resulting descriptor.
1230 :param foreign_keys:
1232 A list of columns which are to be used as "foreign key"
1233 columns, or columns which refer to the value in a remote
1234 column, within the context of this :func:`_orm.relationship`
1235 object's :paramref:`_orm.relationship.primaryjoin` condition.
1236 That is, if the :paramref:`_orm.relationship.primaryjoin`
1237 condition of this :func:`_orm.relationship` is ``a.id ==
1238 b.a_id``, and the values in ``b.a_id`` are required to be
1239 present in ``a.id``, then the "foreign key" column of this
1240 :func:`_orm.relationship` is ``b.a_id``.
1242 In normal cases, the :paramref:`_orm.relationship.foreign_keys`
1243 parameter is **not required.** :func:`_orm.relationship` will
1244 automatically determine which columns in the
1245 :paramref:`_orm.relationship.primaryjoin` condition are to be
1246 considered "foreign key" columns based on those
1247 :class:`_schema.Column` objects that specify
1248 :class:`_schema.ForeignKey`,
1249 or are otherwise listed as referencing columns in a
1250 :class:`_schema.ForeignKeyConstraint` construct.
1251 :paramref:`_orm.relationship.foreign_keys` is only needed when:
1253 1. There is more than one way to construct a join from the local
1254 table to the remote table, as there are multiple foreign key
1255 references present. Setting ``foreign_keys`` will limit the
1256 :func:`_orm.relationship`
1257 to consider just those columns specified
1258 here as "foreign".
1260 2. The :class:`_schema.Table` being mapped does not actually have
1261 :class:`_schema.ForeignKey` or
1262 :class:`_schema.ForeignKeyConstraint`
1263 constructs present, often because the table
1264 was reflected from a database that does not support foreign key
1265 reflection (MySQL MyISAM).
1267 3. The :paramref:`_orm.relationship.primaryjoin`
1268 argument is used to
1269 construct a non-standard join condition, which makes use of
1270 columns or expressions that do not normally refer to their
1271 "parent" column, such as a join condition expressed by a
1272 complex comparison using a SQL function.
1274 The :func:`_orm.relationship` construct will raise informative
1275 error messages that suggest the use of the
1276 :paramref:`_orm.relationship.foreign_keys` parameter when
1277 presented with an ambiguous condition. In typical cases,
1278 if :func:`_orm.relationship` doesn't raise any exceptions, the
1279 :paramref:`_orm.relationship.foreign_keys` parameter is usually
1280 not needed.
1282 :paramref:`_orm.relationship.foreign_keys` may also be passed as a
1283 callable function which is evaluated at mapper initialization time,
1284 and may be passed as a Python-evaluable string when using
1285 Declarative.
1287 .. warning:: When passed as a Python-evaluable string, the
1288 argument is interpreted using Python's ``eval()`` function.
1289 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1290 See :ref:`declarative_relationship_eval` for details on
1291 declarative evaluation of :func:`_orm.relationship` arguments.
1293 .. seealso::
1295 :ref:`relationship_foreign_keys`
1297 :ref:`relationship_custom_foreign`
1299 :func:`.foreign` - allows direct annotation of the "foreign"
1300 columns within a :paramref:`_orm.relationship.primaryjoin`
1301 condition.
1303 :param info: Optional data dictionary which will be populated into the
1304 :attr:`.MapperProperty.info` attribute of this object.
1306 :param innerjoin=False:
1307 When ``True``, joined eager loads will use an inner join to join
1308 against related tables instead of an outer join. The purpose
1309 of this option is generally one of performance, as inner joins
1310 generally perform better than outer joins.
1312 This flag can be set to ``True`` when the relationship references an
1313 object via many-to-one using local foreign keys that are not
1314 nullable, or when the reference is one-to-one or a collection that
1315 is guaranteed to have one or at least one entry.
1317 The option supports the same "nested" and "unnested" options as
1318 that of :paramref:`_orm.joinedload.innerjoin`. See that flag
1319 for details on nested / unnested behaviors.
1321 .. seealso::
1323 :paramref:`_orm.joinedload.innerjoin` - the option as specified by
1324 loader option, including detail on nesting behavior.
1326 :ref:`what_kind_of_loading` - Discussion of some details of
1327 various loader options.
1330 :param join_depth:
1331 When non-``None``, an integer value indicating how many levels
1332 deep "eager" loaders should join on a self-referring or cyclical
1333 relationship. The number counts how many times the same Mapper
1334 shall be present in the loading condition along a particular join
1335 branch. When left at its default of ``None``, eager loaders
1336 will stop chaining when they encounter a the same target mapper
1337 which is already higher up in the chain. This option applies
1338 both to joined- and subquery- eager loaders.
1340 .. seealso::
1342 :ref:`self_referential_eager_loading` - Introductory documentation
1343 and examples.
1345 :param lazy='select': specifies
1346 How the related items should be loaded. Default value is
1347 ``select``. Values include:
1349 * ``select`` - items should be loaded lazily when the property is
1350 first accessed, using a separate SELECT statement, or identity map
1351 fetch for simple many-to-one references.
1353 * ``immediate`` - items should be loaded as the parents are loaded,
1354 using a separate SELECT statement, or identity map fetch for
1355 simple many-to-one references.
1357 * ``joined`` - items should be loaded "eagerly" in the same query as
1358 that of the parent, using a JOIN or LEFT OUTER JOIN. Whether
1359 the join is "outer" or not is determined by the
1360 :paramref:`_orm.relationship.innerjoin` parameter.
1362 * ``subquery`` - items should be loaded "eagerly" as the parents are
1363 loaded, using one additional SQL statement, which issues a JOIN to
1364 a subquery of the original statement, for each collection
1365 requested.
1367 * ``selectin`` - items should be loaded "eagerly" as the parents
1368 are loaded, using one or more additional SQL statements, which
1369 issues a JOIN to the immediate parent object, specifying primary
1370 key identifiers using an IN clause.
1372 * ``noload`` - no loading should occur at any time. The related
1373 collection will remain empty. The ``noload`` strategy is not
1374 recommended for general use. For a general use "never load"
1375 approach, see :ref:`write_only_relationship`
1377 * ``raise`` - lazy loading is disallowed; accessing
1378 the attribute, if its value were not already loaded via eager
1379 loading, will raise an :exc:`~sqlalchemy.exc.InvalidRequestError`.
1380 This strategy can be used when objects are to be detached from
1381 their attached :class:`.Session` after they are loaded.
1383 * ``raise_on_sql`` - lazy loading that emits SQL is disallowed;
1384 accessing the attribute, if its value were not already loaded via
1385 eager loading, will raise an
1386 :exc:`~sqlalchemy.exc.InvalidRequestError`, **if the lazy load
1387 needs to emit SQL**. If the lazy load can pull the related value
1388 from the identity map or determine that it should be None, the
1389 value is loaded. This strategy can be used when objects will
1390 remain associated with the attached :class:`.Session`, however
1391 additional SELECT statements should be blocked.
1393 * ``write_only`` - the attribute will be configured with a special
1394 "virtual collection" that may receive
1395 :meth:`_orm.WriteOnlyCollection.add` and
1396 :meth:`_orm.WriteOnlyCollection.remove` commands to add or remove
1397 individual objects, but will not under any circumstances load or
1398 iterate the full set of objects from the database directly. Instead,
1399 methods such as :meth:`_orm.WriteOnlyCollection.select`,
1400 :meth:`_orm.WriteOnlyCollection.insert`,
1401 :meth:`_orm.WriteOnlyCollection.update` and
1402 :meth:`_orm.WriteOnlyCollection.delete` are provided which generate SQL
1403 constructs that may be used to load and modify rows in bulk. Used for
1404 large collections that are never appropriate to load at once into
1405 memory.
1407 The ``write_only`` loader style is configured automatically when
1408 the :class:`_orm.WriteOnlyMapped` annotation is provided on the
1409 left hand side within a Declarative mapping. See the section
1410 :ref:`write_only_relationship` for examples.
1412 .. versionadded:: 2.0
1414 .. seealso::
1416 :ref:`write_only_relationship` - in the :ref:`queryguide_toplevel`
1418 * ``dynamic`` - the attribute will return a pre-configured
1419 :class:`_query.Query` object for all read
1420 operations, onto which further filtering operations can be
1421 applied before iterating the results.
1423 The ``dynamic`` loader style is configured automatically when
1424 the :class:`_orm.DynamicMapped` annotation is provided on the
1425 left hand side within a Declarative mapping. See the section
1426 :ref:`dynamic_relationship` for examples.
1428 .. legacy:: The "dynamic" lazy loader strategy is the legacy form of
1429 what is now the "write_only" strategy described in the section
1430 :ref:`write_only_relationship`.
1432 .. seealso::
1434 :ref:`dynamic_relationship` - in the :ref:`queryguide_toplevel`
1436 :ref:`write_only_relationship` - more generally useful approach
1437 for large collections that should not fully load into memory
1439 * True - a synonym for 'select'
1441 * False - a synonym for 'joined'
1443 * None - a synonym for 'noload'
1445 .. seealso::
1447 :ref:`orm_queryguide_relationship_loaders` - Full documentation on
1448 relationship loader configuration in the :ref:`queryguide_toplevel`.
1451 :param load_on_pending=False:
1452 Indicates loading behavior for transient or pending parent objects.
1454 When set to ``True``, causes the lazy-loader to
1455 issue a query for a parent object that is not persistent, meaning it
1456 has never been flushed. This may take effect for a pending object
1457 when autoflush is disabled, or for a transient object that has been
1458 "attached" to a :class:`.Session` but is not part of its pending
1459 collection.
1461 The :paramref:`_orm.relationship.load_on_pending`
1462 flag does not improve
1463 behavior when the ORM is used normally - object references should be
1464 constructed at the object level, not at the foreign key level, so
1465 that they are present in an ordinary way before a flush proceeds.
1466 This flag is not not intended for general use.
1468 .. seealso::
1470 :meth:`.Session.enable_relationship_loading` - this method
1471 establishes "load on pending" behavior for the whole object, and
1472 also allows loading on objects that remain transient or
1473 detached.
1475 :param order_by:
1476 Indicates the ordering that should be applied when loading these
1477 items. :paramref:`_orm.relationship.order_by`
1478 is expected to refer to
1479 one of the :class:`_schema.Column`
1480 objects to which the target class is
1481 mapped, or the attribute itself bound to the target class which
1482 refers to the column.
1484 :paramref:`_orm.relationship.order_by`
1485 may also be passed as a callable
1486 function which is evaluated at mapper initialization time, and may
1487 be passed as a Python-evaluable string when using Declarative.
1489 .. warning:: When passed as a Python-evaluable string, the
1490 argument is interpreted using Python's ``eval()`` function.
1491 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1492 See :ref:`declarative_relationship_eval` for details on
1493 declarative evaluation of :func:`_orm.relationship` arguments.
1495 :param passive_deletes=False:
1496 Indicates loading behavior during delete operations.
1498 A value of True indicates that unloaded child items should not
1499 be loaded during a delete operation on the parent. Normally,
1500 when a parent item is deleted, all child items are loaded so
1501 that they can either be marked as deleted, or have their
1502 foreign key to the parent set to NULL. Marking this flag as
1503 True usually implies an ON DELETE <CASCADE|SET NULL> rule is in
1504 place which will handle updating/deleting child rows on the
1505 database side.
1507 Additionally, setting the flag to the string value 'all' will
1508 disable the "nulling out" of the child foreign keys, when the parent
1509 object is deleted and there is no delete or delete-orphan cascade
1510 enabled. This is typically used when a triggering or error raise
1511 scenario is in place on the database side. Note that the foreign
1512 key attributes on in-session child objects will not be changed after
1513 a flush occurs so this is a very special use-case setting.
1514 Additionally, the "nulling out" will still occur if the child
1515 object is de-associated with the parent.
1517 .. seealso::
1519 :ref:`passive_deletes` - Introductory documentation
1520 and examples.
1522 :param passive_updates=True:
1523 Indicates the persistence behavior to take when a referenced
1524 primary key value changes in place, indicating that the referencing
1525 foreign key columns will also need their value changed.
1527 When True, it is assumed that ``ON UPDATE CASCADE`` is configured on
1528 the foreign key in the database, and that the database will
1529 handle propagation of an UPDATE from a source column to
1530 dependent rows. When False, the SQLAlchemy
1531 :func:`_orm.relationship`
1532 construct will attempt to emit its own UPDATE statements to
1533 modify related targets. However note that SQLAlchemy **cannot**
1534 emit an UPDATE for more than one level of cascade. Also,
1535 setting this flag to False is not compatible in the case where
1536 the database is in fact enforcing referential integrity, unless
1537 those constraints are explicitly "deferred", if the target backend
1538 supports it.
1540 It is highly advised that an application which is employing
1541 mutable primary keys keeps ``passive_updates`` set to True,
1542 and instead uses the referential integrity features of the database
1543 itself in order to handle the change efficiently and fully.
1545 .. seealso::
1547 :ref:`passive_updates` - Introductory documentation and
1548 examples.
1550 :paramref:`.mapper.passive_updates` - a similar flag which
1551 takes effect for joined-table inheritance mappings.
1553 :param post_update:
1554 This indicates that the relationship should be handled by a
1555 second UPDATE statement after an INSERT or before a
1556 DELETE. This flag is used to handle saving bi-directional
1557 dependencies between two individual rows (i.e. each row
1558 references the other), where it would otherwise be impossible to
1559 INSERT or DELETE both rows fully since one row exists before the
1560 other. Use this flag when a particular mapping arrangement will
1561 incur two rows that are dependent on each other, such as a table
1562 that has a one-to-many relationship to a set of child rows, and
1563 also has a column that references a single child row within that
1564 list (i.e. both tables contain a foreign key to each other). If
1565 a flush operation returns an error that a "cyclical
1566 dependency" was detected, this is a cue that you might want to
1567 use :paramref:`_orm.relationship.post_update` to "break" the cycle.
1569 .. seealso::
1571 :ref:`post_update` - Introductory documentation and examples.
1573 :param primaryjoin:
1574 A SQL expression that will be used as the primary
1575 join of the child object against the parent object, or in a
1576 many-to-many relationship the join of the parent object to the
1577 association table. By default, this value is computed based on the
1578 foreign key relationships of the parent and child tables (or
1579 association table).
1581 :paramref:`_orm.relationship.primaryjoin` may also be passed as a
1582 callable function which is evaluated at mapper initialization time,
1583 and may be passed as a Python-evaluable string when using
1584 Declarative.
1586 .. warning:: When passed as a Python-evaluable string, the
1587 argument is interpreted using Python's ``eval()`` function.
1588 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1589 See :ref:`declarative_relationship_eval` for details on
1590 declarative evaluation of :func:`_orm.relationship` arguments.
1592 .. seealso::
1594 :ref:`relationship_primaryjoin`
1596 :param remote_side:
1597 Used for self-referential relationships, indicates the column or
1598 list of columns that form the "remote side" of the relationship.
1600 :paramref:`_orm.relationship.remote_side` may also be passed as a
1601 callable function which is evaluated at mapper initialization time,
1602 and may be passed as a Python-evaluable string when using
1603 Declarative.
1605 .. warning:: When passed as a Python-evaluable string, the
1606 argument is interpreted using Python's ``eval()`` function.
1607 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1608 See :ref:`declarative_relationship_eval` for details on
1609 declarative evaluation of :func:`_orm.relationship` arguments.
1611 .. seealso::
1613 :ref:`self_referential` - in-depth explanation of how
1614 :paramref:`_orm.relationship.remote_side`
1615 is used to configure self-referential relationships.
1617 :func:`.remote` - an annotation function that accomplishes the
1618 same purpose as :paramref:`_orm.relationship.remote_side`,
1619 typically
1620 when a custom :paramref:`_orm.relationship.primaryjoin` condition
1621 is used.
1623 :param query_class:
1624 A :class:`_query.Query`
1625 subclass that will be used internally by the
1626 ``AppenderQuery`` returned by a "dynamic" relationship, that
1627 is, a relationship that specifies ``lazy="dynamic"`` or was
1628 otherwise constructed using the :func:`_orm.dynamic_loader`
1629 function.
1631 .. seealso::
1633 :ref:`dynamic_relationship` - Introduction to "dynamic"
1634 relationship loaders.
1636 :param secondaryjoin:
1637 A SQL expression that will be used as the join of
1638 an association table to the child object. By default, this value is
1639 computed based on the foreign key relationships of the association
1640 and child tables.
1642 :paramref:`_orm.relationship.secondaryjoin` may also be passed as a
1643 callable function which is evaluated at mapper initialization time,
1644 and may be passed as a Python-evaluable string when using
1645 Declarative.
1647 .. warning:: When passed as a Python-evaluable string, the
1648 argument is interpreted using Python's ``eval()`` function.
1649 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1650 See :ref:`declarative_relationship_eval` for details on
1651 declarative evaluation of :func:`_orm.relationship` arguments.
1653 .. seealso::
1655 :ref:`relationship_primaryjoin`
1657 :param single_parent:
1658 When True, installs a validator which will prevent objects
1659 from being associated with more than one parent at a time.
1660 This is used for many-to-one or many-to-many relationships that
1661 should be treated either as one-to-one or one-to-many. Its usage
1662 is optional, except for :func:`_orm.relationship` constructs which
1663 are many-to-one or many-to-many and also
1664 specify the ``delete-orphan`` cascade option. The
1665 :func:`_orm.relationship` construct itself will raise an error
1666 instructing when this option is required.
1668 .. seealso::
1670 :ref:`unitofwork_cascades` - includes detail on when the
1671 :paramref:`_orm.relationship.single_parent`
1672 flag may be appropriate.
1674 :param uselist:
1675 A boolean that indicates if this property should be loaded as a
1676 list or a scalar. In most cases, this value is determined
1677 automatically by :func:`_orm.relationship` at mapper configuration
1678 time. When using explicit :class:`_orm.Mapped` annotations,
1679 :paramref:`_orm.relationship.uselist` may be derived from the
1680 whether or not the annotation within :class:`_orm.Mapped` contains
1681 a collection class.
1682 Otherwise, :paramref:`_orm.relationship.uselist` may be derived from
1683 the type and direction
1684 of the relationship - one to many forms a list, many to one
1685 forms a scalar, many to many is a list. If a scalar is desired
1686 where normally a list would be present, such as a bi-directional
1687 one-to-one relationship, use an appropriate :class:`_orm.Mapped`
1688 annotation or set :paramref:`_orm.relationship.uselist` to False.
1690 The :paramref:`_orm.relationship.uselist`
1691 flag is also available on an
1692 existing :func:`_orm.relationship`
1693 construct as a read-only attribute,
1694 which can be used to determine if this :func:`_orm.relationship`
1695 deals
1696 with collections or scalar attributes::
1698 >>> User.addresses.property.uselist
1699 True
1701 .. seealso::
1703 :ref:`relationships_one_to_one` - Introduction to the "one to
1704 one" relationship pattern, which is typically when an alternate
1705 setting for :paramref:`_orm.relationship.uselist` is involved.
1707 :param viewonly=False:
1708 When set to ``True``, the relationship is used only for loading
1709 objects, and not for any persistence operation. A
1710 :func:`_orm.relationship` which specifies
1711 :paramref:`_orm.relationship.viewonly` can work
1712 with a wider range of SQL operations within the
1713 :paramref:`_orm.relationship.primaryjoin` condition, including
1714 operations that feature the use of a variety of comparison operators
1715 as well as SQL functions such as :func:`_expression.cast`. The
1716 :paramref:`_orm.relationship.viewonly`
1717 flag is also of general use when defining any kind of
1718 :func:`_orm.relationship` that doesn't represent
1719 the full set of related objects, to prevent modifications of the
1720 collection from resulting in persistence operations.
1722 .. seealso::
1724 :ref:`relationship_viewonly_notes` - more details on best practices
1725 when using :paramref:`_orm.relationship.viewonly`.
1727 :param sync_backref:
1728 A boolean that enables the events used to synchronize the in-Python
1729 attributes when this relationship is target of either
1730 :paramref:`_orm.relationship.backref` or
1731 :paramref:`_orm.relationship.back_populates`.
1733 Defaults to ``None``, which indicates that an automatic value should
1734 be selected based on the value of the
1735 :paramref:`_orm.relationship.viewonly` flag. When left at its
1736 default, changes in state will be back-populated only if neither
1737 sides of a relationship is viewonly.
1739 .. versionadded:: 1.3.17
1741 .. versionchanged:: 1.4 - A relationship that specifies
1742 :paramref:`_orm.relationship.viewonly` automatically implies
1743 that :paramref:`_orm.relationship.sync_backref` is ``False``.
1745 .. seealso::
1747 :paramref:`_orm.relationship.viewonly`
1749 :param omit_join:
1750 Allows manual control over the "selectin" automatic join
1751 optimization. Set to ``False`` to disable the "omit join" feature
1752 added in SQLAlchemy 1.3; or leave as ``None`` to leave automatic
1753 optimization in place.
1755 .. note:: This flag may only be set to ``False``. It is not
1756 necessary to set it to ``True`` as the "omit_join" optimization is
1757 automatically detected; if it is not detected, then the
1758 optimization is not supported.
1760 .. versionchanged:: 1.3.11 setting ``omit_join`` to True will now
1761 emit a warning as this was not the intended use of this flag.
1763 .. versionadded:: 1.3
1765 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
1766 specifies if the mapped attribute should be part of the ``__init__()``
1767 method as generated by the dataclass process.
1768 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
1769 specifies if the mapped attribute should be part of the ``__repr__()``
1770 method as generated by the dataclass process.
1771 :param default_factory: Specific to
1772 :ref:`orm_declarative_native_dataclasses`,
1773 specifies a default-value generation function that will take place
1774 as part of the ``__init__()``
1775 method as generated by the dataclass process.
1776 :param compare: Specific to
1777 :ref:`orm_declarative_native_dataclasses`, indicates if this field
1778 should be included in comparison operations when generating the
1779 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
1781 .. versionadded:: 2.0.0b4
1783 :param kw_only: Specific to
1784 :ref:`orm_declarative_native_dataclasses`, indicates if this field
1785 should be marked as keyword-only when generating the ``__init__()``.
1788 """
1790 return _RelationshipDeclared(
1791 argument,
1792 secondary=secondary,
1793 uselist=uselist,
1794 collection_class=collection_class,
1795 primaryjoin=primaryjoin,
1796 secondaryjoin=secondaryjoin,
1797 back_populates=back_populates,
1798 order_by=order_by,
1799 backref=backref,
1800 overlaps=overlaps,
1801 post_update=post_update,
1802 cascade=cascade,
1803 viewonly=viewonly,
1804 attribute_options=_AttributeOptions(
1805 init, repr, default, default_factory, compare, kw_only
1806 ),
1807 lazy=lazy,
1808 passive_deletes=passive_deletes,
1809 passive_updates=passive_updates,
1810 active_history=active_history,
1811 enable_typechecks=enable_typechecks,
1812 foreign_keys=foreign_keys,
1813 remote_side=remote_side,
1814 join_depth=join_depth,
1815 comparator_factory=comparator_factory,
1816 single_parent=single_parent,
1817 innerjoin=innerjoin,
1818 distinct_target_key=distinct_target_key,
1819 load_on_pending=load_on_pending,
1820 query_class=query_class,
1821 info=info,
1822 omit_join=omit_join,
1823 sync_backref=sync_backref,
1824 **kw,
1825 )
1828def synonym(
1829 name: str,
1830 *,
1831 map_column: Optional[bool] = None,
1832 descriptor: Optional[Any] = None,
1833 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
1834 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
1835 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
1836 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
1837 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
1838 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
1839 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
1840 info: Optional[_InfoType] = None,
1841 doc: Optional[str] = None,
1842) -> Synonym[Any]:
1843 """Denote an attribute name as a synonym to a mapped property,
1844 in that the attribute will mirror the value and expression behavior
1845 of another attribute.
1847 e.g.::
1849 class MyClass(Base):
1850 __tablename__ = 'my_table'
1852 id = Column(Integer, primary_key=True)
1853 job_status = Column(String(50))
1855 status = synonym("job_status")
1858 :param name: the name of the existing mapped property. This
1859 can refer to the string name ORM-mapped attribute
1860 configured on the class, including column-bound attributes
1861 and relationships.
1863 :param descriptor: a Python :term:`descriptor` that will be used
1864 as a getter (and potentially a setter) when this attribute is
1865 accessed at the instance level.
1867 :param map_column: **For classical mappings and mappings against
1868 an existing Table object only**. if ``True``, the :func:`.synonym`
1869 construct will locate the :class:`_schema.Column`
1870 object upon the mapped
1871 table that would normally be associated with the attribute name of
1872 this synonym, and produce a new :class:`.ColumnProperty` that instead
1873 maps this :class:`_schema.Column`
1874 to the alternate name given as the "name"
1875 argument of the synonym; in this way, the usual step of redefining
1876 the mapping of the :class:`_schema.Column`
1877 to be under a different name is
1878 unnecessary. This is usually intended to be used when a
1879 :class:`_schema.Column`
1880 is to be replaced with an attribute that also uses a
1881 descriptor, that is, in conjunction with the
1882 :paramref:`.synonym.descriptor` parameter::
1884 my_table = Table(
1885 "my_table", metadata,
1886 Column('id', Integer, primary_key=True),
1887 Column('job_status', String(50))
1888 )
1890 class MyClass:
1891 @property
1892 def _job_status_descriptor(self):
1893 return "Status: %s" % self._job_status
1896 mapper(
1897 MyClass, my_table, properties={
1898 "job_status": synonym(
1899 "_job_status", map_column=True,
1900 descriptor=MyClass._job_status_descriptor)
1901 }
1902 )
1904 Above, the attribute named ``_job_status`` is automatically
1905 mapped to the ``job_status`` column::
1907 >>> j1 = MyClass()
1908 >>> j1._job_status = "employed"
1909 >>> j1.job_status
1910 Status: employed
1912 When using Declarative, in order to provide a descriptor in
1913 conjunction with a synonym, use the
1914 :func:`sqlalchemy.ext.declarative.synonym_for` helper. However,
1915 note that the :ref:`hybrid properties <mapper_hybrids>` feature
1916 should usually be preferred, particularly when redefining attribute
1917 behavior.
1919 :param info: Optional data dictionary which will be populated into the
1920 :attr:`.InspectionAttr.info` attribute of this object.
1922 :param comparator_factory: A subclass of :class:`.PropComparator`
1923 that will provide custom comparison behavior at the SQL expression
1924 level.
1926 .. note::
1928 For the use case of providing an attribute which redefines both
1929 Python-level and SQL-expression level behavior of an attribute,
1930 please refer to the Hybrid attribute introduced at
1931 :ref:`mapper_hybrids` for a more effective technique.
1933 .. seealso::
1935 :ref:`synonyms` - Overview of synonyms
1937 :func:`.synonym_for` - a helper oriented towards Declarative
1939 :ref:`mapper_hybrids` - The Hybrid Attribute extension provides an
1940 updated approach to augmenting attribute behavior more flexibly
1941 than can be achieved with synonyms.
1943 """
1944 return Synonym(
1945 name,
1946 map_column=map_column,
1947 descriptor=descriptor,
1948 comparator_factory=comparator_factory,
1949 attribute_options=_AttributeOptions(
1950 init, repr, default, default_factory, compare, kw_only
1951 ),
1952 doc=doc,
1953 info=info,
1954 )
1957def create_session(
1958 bind: Optional[_SessionBind] = None, **kwargs: Any
1959) -> Session:
1960 r"""Create a new :class:`.Session`
1961 with no automation enabled by default.
1963 This function is used primarily for testing. The usual
1964 route to :class:`.Session` creation is via its constructor
1965 or the :func:`.sessionmaker` function.
1967 :param bind: optional, a single Connectable to use for all
1968 database access in the created
1969 :class:`~sqlalchemy.orm.session.Session`.
1971 :param \*\*kwargs: optional, passed through to the
1972 :class:`.Session` constructor.
1974 :returns: an :class:`~sqlalchemy.orm.session.Session` instance
1976 The defaults of create_session() are the opposite of that of
1977 :func:`sessionmaker`; ``autoflush`` and ``expire_on_commit`` are
1978 False.
1980 Usage::
1982 >>> from sqlalchemy.orm import create_session
1983 >>> session = create_session()
1985 It is recommended to use :func:`sessionmaker` instead of
1986 create_session().
1988 """
1990 kwargs.setdefault("autoflush", False)
1991 kwargs.setdefault("expire_on_commit", False)
1992 return Session(bind=bind, **kwargs)
1995def _mapper_fn(*arg: Any, **kw: Any) -> NoReturn:
1996 """Placeholder for the now-removed ``mapper()`` function.
1998 Classical mappings should be performed using the
1999 :meth:`_orm.registry.map_imperatively` method.
2001 This symbol remains in SQLAlchemy 2.0 to suit the deprecated use case
2002 of using the ``mapper()`` function as a target for ORM event listeners,
2003 which failed to be marked as deprecated in the 1.4 series.
2005 Global ORM mapper listeners should instead use the :class:`_orm.Mapper`
2006 class as the target.
2008 .. versionchanged:: 2.0 The ``mapper()`` function was removed; the
2009 symbol remains temporarily as a placeholder for the event listening
2010 use case.
2012 """
2013 raise InvalidRequestError(
2014 "The 'sqlalchemy.orm.mapper()' function is removed as of "
2015 "SQLAlchemy 2.0. Use the "
2016 "'sqlalchemy.orm.registry.map_imperatively()` "
2017 "method of the ``sqlalchemy.orm.registry`` class to perform "
2018 "classical mapping."
2019 )
2022def dynamic_loader(
2023 argument: Optional[_RelationshipArgumentType[Any]] = None, **kw: Any
2024) -> RelationshipProperty[Any]:
2025 """Construct a dynamically-loading mapper property.
2027 This is essentially the same as
2028 using the ``lazy='dynamic'`` argument with :func:`relationship`::
2030 dynamic_loader(SomeClass)
2032 # is the same as
2034 relationship(SomeClass, lazy="dynamic")
2036 See the section :ref:`dynamic_relationship` for more details
2037 on dynamic loading.
2039 """
2040 kw["lazy"] = "dynamic"
2041 return relationship(argument, **kw)
2044def backref(name: str, **kwargs: Any) -> ORMBackrefArgument:
2045 """When using the :paramref:`_orm.relationship.backref` parameter,
2046 provides specific parameters to be used when the new
2047 :func:`_orm.relationship` is generated.
2049 E.g.::
2051 'items':relationship(
2052 SomeItem, backref=backref('parent', lazy='subquery'))
2054 The :paramref:`_orm.relationship.backref` parameter is generally
2055 considered to be legacy; for modern applications, using
2056 explicit :func:`_orm.relationship` constructs linked together using
2057 the :paramref:`_orm.relationship.back_populates` parameter should be
2058 preferred.
2060 .. seealso::
2062 :ref:`relationships_backref` - background on backrefs
2064 """
2066 return (name, kwargs)
2069def deferred(
2070 column: _ORMColumnExprArgument[_T],
2071 *additional_columns: _ORMColumnExprArgument[Any],
2072 group: Optional[str] = None,
2073 raiseload: bool = False,
2074 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
2075 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
2076 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2077 default: Optional[Any] = _NoArg.NO_ARG,
2078 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
2079 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
2080 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
2081 active_history: bool = False,
2082 expire_on_flush: bool = True,
2083 info: Optional[_InfoType] = None,
2084 doc: Optional[str] = None,
2085) -> MappedSQLExpression[_T]:
2086 r"""Indicate a column-based mapped attribute that by default will
2087 not load unless accessed.
2089 When using :func:`_orm.mapped_column`, the same functionality as
2090 that of :func:`_orm.deferred` construct is provided by using the
2091 :paramref:`_orm.mapped_column.deferred` parameter.
2093 :param \*columns: columns to be mapped. This is typically a single
2094 :class:`_schema.Column` object,
2095 however a collection is supported in order
2096 to support multiple columns mapped under the same attribute.
2098 :param raiseload: boolean, if True, indicates an exception should be raised
2099 if the load operation is to take place.
2101 .. versionadded:: 1.4
2104 Additional arguments are the same as that of :func:`_orm.column_property`.
2106 .. seealso::
2108 :ref:`orm_queryguide_deferred_imperative`
2110 """
2111 return MappedSQLExpression(
2112 column,
2113 *additional_columns,
2114 attribute_options=_AttributeOptions(
2115 init, repr, default, default_factory, compare, kw_only
2116 ),
2117 group=group,
2118 deferred=True,
2119 raiseload=raiseload,
2120 comparator_factory=comparator_factory,
2121 active_history=active_history,
2122 expire_on_flush=expire_on_flush,
2123 info=info,
2124 doc=doc,
2125 )
2128def query_expression(
2129 default_expr: _ORMColumnExprArgument[_T] = sql.null(),
2130 *,
2131 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2132 compare: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2133 expire_on_flush: bool = True,
2134 info: Optional[_InfoType] = None,
2135 doc: Optional[str] = None,
2136) -> MappedSQLExpression[_T]:
2137 """Indicate an attribute that populates from a query-time SQL expression.
2139 :param default_expr: Optional SQL expression object that will be used in
2140 all cases if not assigned later with :func:`_orm.with_expression`.
2142 .. versionadded:: 1.2
2144 .. seealso::
2146 :ref:`orm_queryguide_with_expression` - background and usage examples
2148 """
2149 prop = MappedSQLExpression(
2150 default_expr,
2151 attribute_options=_AttributeOptions(
2152 False,
2153 repr,
2154 _NoArg.NO_ARG,
2155 _NoArg.NO_ARG,
2156 compare,
2157 _NoArg.NO_ARG,
2158 ),
2159 expire_on_flush=expire_on_flush,
2160 info=info,
2161 doc=doc,
2162 _assume_readonly_dc_attributes=True,
2163 )
2165 prop.strategy_key = (("query_expression", True),)
2166 return prop
2169def clear_mappers() -> None:
2170 """Remove all mappers from all classes.
2172 .. versionchanged:: 1.4 This function now locates all
2173 :class:`_orm.registry` objects and calls upon the
2174 :meth:`_orm.registry.dispose` method of each.
2176 This function removes all instrumentation from classes and disposes
2177 of their associated mappers. Once called, the classes are unmapped
2178 and can be later re-mapped with new mappers.
2180 :func:`.clear_mappers` is *not* for normal use, as there is literally no
2181 valid usage for it outside of very specific testing scenarios. Normally,
2182 mappers are permanent structural components of user-defined classes, and
2183 are never discarded independently of their class. If a mapped class
2184 itself is garbage collected, its mapper is automatically disposed of as
2185 well. As such, :func:`.clear_mappers` is only for usage in test suites
2186 that re-use the same classes with different mappings, which is itself an
2187 extremely rare use case - the only such use case is in fact SQLAlchemy's
2188 own test suite, and possibly the test suites of other ORM extension
2189 libraries which intend to test various combinations of mapper construction
2190 upon a fixed set of classes.
2192 """
2194 mapperlib._dispose_registries(mapperlib._all_registries(), False)
2197# I would really like a way to get the Type[] here that shows up
2198# in a different way in typing tools, however there is no current method
2199# that is accepted by mypy (subclass of Type[_O] works in pylance, rejected
2200# by mypy).
2201AliasedType = Annotated[Type[_O], "aliased"]
2204@overload
2205def aliased(
2206 element: Type[_O],
2207 alias: Optional[FromClause] = None,
2208 name: Optional[str] = None,
2209 flat: bool = False,
2210 adapt_on_names: bool = False,
2211) -> AliasedType[_O]: ...
2214@overload
2215def aliased(
2216 element: Union[AliasedClass[_O], Mapper[_O], AliasedInsp[_O]],
2217 alias: Optional[FromClause] = None,
2218 name: Optional[str] = None,
2219 flat: bool = False,
2220 adapt_on_names: bool = False,
2221) -> AliasedClass[_O]: ...
2224@overload
2225def aliased(
2226 element: FromClause,
2227 alias: None = None,
2228 name: Optional[str] = None,
2229 flat: bool = False,
2230 adapt_on_names: bool = False,
2231) -> FromClause: ...
2234def aliased(
2235 element: Union[_EntityType[_O], FromClause],
2236 alias: Optional[FromClause] = None,
2237 name: Optional[str] = None,
2238 flat: bool = False,
2239 adapt_on_names: bool = False,
2240) -> Union[AliasedClass[_O], FromClause, AliasedType[_O]]:
2241 """Produce an alias of the given element, usually an :class:`.AliasedClass`
2242 instance.
2244 E.g.::
2246 my_alias = aliased(MyClass)
2248 stmt = select(MyClass, my_alias).filter(MyClass.id > my_alias.id)
2249 result = session.execute(stmt)
2251 The :func:`.aliased` function is used to create an ad-hoc mapping of a
2252 mapped class to a new selectable. By default, a selectable is generated
2253 from the normally mapped selectable (typically a :class:`_schema.Table`
2254 ) using the
2255 :meth:`_expression.FromClause.alias` method. However, :func:`.aliased`
2256 can also be
2257 used to link the class to a new :func:`_expression.select` statement.
2258 Also, the :func:`.with_polymorphic` function is a variant of
2259 :func:`.aliased` that is intended to specify a so-called "polymorphic
2260 selectable", that corresponds to the union of several joined-inheritance
2261 subclasses at once.
2263 For convenience, the :func:`.aliased` function also accepts plain
2264 :class:`_expression.FromClause` constructs, such as a
2265 :class:`_schema.Table` or
2266 :func:`_expression.select` construct. In those cases, the
2267 :meth:`_expression.FromClause.alias`
2268 method is called on the object and the new
2269 :class:`_expression.Alias` object returned. The returned
2270 :class:`_expression.Alias` is not
2271 ORM-mapped in this case.
2273 .. seealso::
2275 :ref:`tutorial_orm_entity_aliases` - in the :ref:`unified_tutorial`
2277 :ref:`orm_queryguide_orm_aliases` - in the :ref:`queryguide_toplevel`
2279 :param element: element to be aliased. Is normally a mapped class,
2280 but for convenience can also be a :class:`_expression.FromClause`
2281 element.
2283 :param alias: Optional selectable unit to map the element to. This is
2284 usually used to link the object to a subquery, and should be an aliased
2285 select construct as one would produce from the
2286 :meth:`_query.Query.subquery` method or
2287 the :meth:`_expression.Select.subquery` or
2288 :meth:`_expression.Select.alias` methods of the :func:`_expression.select`
2289 construct.
2291 :param name: optional string name to use for the alias, if not specified
2292 by the ``alias`` parameter. The name, among other things, forms the
2293 attribute name that will be accessible via tuples returned by a
2294 :class:`_query.Query` object. Not supported when creating aliases
2295 of :class:`_sql.Join` objects.
2297 :param flat: Boolean, will be passed through to the
2298 :meth:`_expression.FromClause.alias` call so that aliases of
2299 :class:`_expression.Join` objects will alias the individual tables
2300 inside the join, rather than creating a subquery. This is generally
2301 supported by all modern databases with regards to right-nested joins
2302 and generally produces more efficient queries.
2304 When :paramref:`_orm.aliased.flat` is combined with
2305 :paramref:`_orm.aliased.name`, the resulting joins will alias individual
2306 tables using a naming scheme similar to ``<prefix>_<tablename>``. This
2307 naming scheme is for visibility / debugging purposes only and the
2308 specific scheme is subject to change without notice.
2310 .. versionadded:: 2.0.32 added support for combining
2311 :paramref:`_orm.aliased.name` with :paramref:`_orm.aliased.flat`.
2312 Previously, this would raise ``NotImplementedError``.
2314 :param adapt_on_names: if True, more liberal "matching" will be used when
2315 mapping the mapped columns of the ORM entity to those of the
2316 given selectable - a name-based match will be performed if the
2317 given selectable doesn't otherwise have a column that corresponds
2318 to one on the entity. The use case for this is when associating
2319 an entity with some derived selectable such as one that uses
2320 aggregate functions::
2322 class UnitPrice(Base):
2323 __tablename__ = 'unit_price'
2324 ...
2325 unit_id = Column(Integer)
2326 price = Column(Numeric)
2328 aggregated_unit_price = Session.query(
2329 func.sum(UnitPrice.price).label('price')
2330 ).group_by(UnitPrice.unit_id).subquery()
2332 aggregated_unit_price = aliased(UnitPrice,
2333 alias=aggregated_unit_price, adapt_on_names=True)
2335 Above, functions on ``aggregated_unit_price`` which refer to
2336 ``.price`` will return the
2337 ``func.sum(UnitPrice.price).label('price')`` column, as it is
2338 matched on the name "price". Ordinarily, the "price" function
2339 wouldn't have any "column correspondence" to the actual
2340 ``UnitPrice.price`` column as it is not a proxy of the original.
2342 """
2343 return AliasedInsp._alias_factory(
2344 element,
2345 alias=alias,
2346 name=name,
2347 flat=flat,
2348 adapt_on_names=adapt_on_names,
2349 )
2352def with_polymorphic(
2353 base: Union[Type[_O], Mapper[_O]],
2354 classes: Union[Literal["*"], Iterable[Type[Any]]],
2355 selectable: Union[Literal[False, None], FromClause] = False,
2356 flat: bool = False,
2357 polymorphic_on: Optional[ColumnElement[Any]] = None,
2358 aliased: bool = False,
2359 innerjoin: bool = False,
2360 adapt_on_names: bool = False,
2361 name: Optional[str] = None,
2362 _use_mapper_path: bool = False,
2363) -> AliasedClass[_O]:
2364 """Produce an :class:`.AliasedClass` construct which specifies
2365 columns for descendant mappers of the given base.
2367 Using this method will ensure that each descendant mapper's
2368 tables are included in the FROM clause, and will allow filter()
2369 criterion to be used against those tables. The resulting
2370 instances will also have those columns already loaded so that
2371 no "post fetch" of those columns will be required.
2373 .. seealso::
2375 :ref:`with_polymorphic` - full discussion of
2376 :func:`_orm.with_polymorphic`.
2378 :param base: Base class to be aliased.
2380 :param classes: a single class or mapper, or list of
2381 class/mappers, which inherit from the base class.
2382 Alternatively, it may also be the string ``'*'``, in which case
2383 all descending mapped classes will be added to the FROM clause.
2385 :param aliased: when True, the selectable will be aliased. For a
2386 JOIN, this means the JOIN will be SELECTed from inside of a subquery
2387 unless the :paramref:`_orm.with_polymorphic.flat` flag is set to
2388 True, which is recommended for simpler use cases.
2390 :param flat: Boolean, will be passed through to the
2391 :meth:`_expression.FromClause.alias` call so that aliases of
2392 :class:`_expression.Join` objects will alias the individual tables
2393 inside the join, rather than creating a subquery. This is generally
2394 supported by all modern databases with regards to right-nested joins
2395 and generally produces more efficient queries. Setting this flag is
2396 recommended as long as the resulting SQL is functional.
2398 :param selectable: a table or subquery that will
2399 be used in place of the generated FROM clause. This argument is
2400 required if any of the desired classes use concrete table
2401 inheritance, since SQLAlchemy currently cannot generate UNIONs
2402 among tables automatically. If used, the ``selectable`` argument
2403 must represent the full set of tables and columns mapped by every
2404 mapped class. Otherwise, the unaccounted mapped columns will
2405 result in their table being appended directly to the FROM clause
2406 which will usually lead to incorrect results.
2408 When left at its default value of ``False``, the polymorphic
2409 selectable assigned to the base mapper is used for selecting rows.
2410 However, it may also be passed as ``None``, which will bypass the
2411 configured polymorphic selectable and instead construct an ad-hoc
2412 selectable for the target classes given; for joined table inheritance
2413 this will be a join that includes all target mappers and their
2414 subclasses.
2416 :param polymorphic_on: a column to be used as the "discriminator"
2417 column for the given selectable. If not given, the polymorphic_on
2418 attribute of the base classes' mapper will be used, if any. This
2419 is useful for mappings that don't have polymorphic loading
2420 behavior by default.
2422 :param innerjoin: if True, an INNER JOIN will be used. This should
2423 only be specified if querying for one specific subtype only
2425 :param adapt_on_names: Passes through the
2426 :paramref:`_orm.aliased.adapt_on_names`
2427 parameter to the aliased object. This may be useful in situations where
2428 the given selectable is not directly related to the existing mapped
2429 selectable.
2431 .. versionadded:: 1.4.33
2433 :param name: Name given to the generated :class:`.AliasedClass`.
2435 .. versionadded:: 2.0.31
2437 """
2438 return AliasedInsp._with_polymorphic_factory(
2439 base,
2440 classes,
2441 selectable=selectable,
2442 flat=flat,
2443 polymorphic_on=polymorphic_on,
2444 adapt_on_names=adapt_on_names,
2445 aliased=aliased,
2446 innerjoin=innerjoin,
2447 name=name,
2448 _use_mapper_path=_use_mapper_path,
2449 )
2452def join(
2453 left: _FromClauseArgument,
2454 right: _FromClauseArgument,
2455 onclause: Optional[_OnClauseArgument] = None,
2456 isouter: bool = False,
2457 full: bool = False,
2458) -> _ORMJoin:
2459 r"""Produce an inner join between left and right clauses.
2461 :func:`_orm.join` is an extension to the core join interface
2462 provided by :func:`_expression.join()`, where the
2463 left and right selectable may be not only core selectable
2464 objects such as :class:`_schema.Table`, but also mapped classes or
2465 :class:`.AliasedClass` instances. The "on" clause can
2466 be a SQL expression or an ORM mapped attribute
2467 referencing a configured :func:`_orm.relationship`.
2469 :func:`_orm.join` is not commonly needed in modern usage,
2470 as its functionality is encapsulated within that of the
2471 :meth:`_sql.Select.join` and :meth:`_query.Query.join`
2472 methods. which feature a
2473 significant amount of automation beyond :func:`_orm.join`
2474 by itself. Explicit use of :func:`_orm.join`
2475 with ORM-enabled SELECT statements involves use of the
2476 :meth:`_sql.Select.select_from` method, as in::
2478 from sqlalchemy.orm import join
2479 stmt = select(User).\
2480 select_from(join(User, Address, User.addresses)).\
2481 filter(Address.email_address=='foo@bar.com')
2483 In modern SQLAlchemy the above join can be written more
2484 succinctly as::
2486 stmt = select(User).\
2487 join(User.addresses).\
2488 filter(Address.email_address=='foo@bar.com')
2490 .. warning:: using :func:`_orm.join` directly may not work properly
2491 with modern ORM options such as :func:`_orm.with_loader_criteria`.
2492 It is strongly recommended to use the idiomatic join patterns
2493 provided by methods such as :meth:`.Select.join` and
2494 :meth:`.Select.join_from` when creating ORM joins.
2496 .. seealso::
2498 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel` for
2499 background on idiomatic ORM join patterns
2501 """
2502 return _ORMJoin(left, right, onclause, isouter, full)
2505def outerjoin(
2506 left: _FromClauseArgument,
2507 right: _FromClauseArgument,
2508 onclause: Optional[_OnClauseArgument] = None,
2509 full: bool = False,
2510) -> _ORMJoin:
2511 """Produce a left outer join between left and right clauses.
2513 This is the "outer join" version of the :func:`_orm.join` function,
2514 featuring the same behavior except that an OUTER JOIN is generated.
2515 See that function's documentation for other usage details.
2517 """
2518 return _ORMJoin(left, right, onclause, True, full)