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-2025 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 _RelationshipDeclared
32from .relationships import _RelationshipSecondaryArgument
33from .relationships import RelationshipProperty
34from .session import Session
35from .util import _ORMJoin
36from .util import AliasedClass
37from .util import AliasedInsp
38from .util import LoaderCriteriaOption
39from .. import sql
40from .. import util
41from ..exc import InvalidRequestError
42from ..sql._typing import _no_kw
43from ..sql.base import _NoArg
44from ..sql.base import SchemaEventTarget
45from ..sql.schema import _InsertSentinelColumnDefault
46from ..sql.schema import SchemaConst
47from ..sql.selectable import FromClause
48from ..util.typing import Annotated
49from ..util.typing import Literal
51if TYPE_CHECKING:
52 from ._typing import _EntityType
53 from ._typing import _ORMColumnExprArgument
54 from .descriptor_props import _CC
55 from .descriptor_props import _CompositeAttrType
56 from .interfaces import PropComparator
57 from .mapper import Mapper
58 from .query import Query
59 from .relationships import _LazyLoadArgumentType
60 from .relationships import _ORMColCollectionArgument
61 from .relationships import _ORMOrderByArgument
62 from .relationships import _RelationshipJoinConditionArgument
63 from .relationships import ORMBackrefArgument
64 from .session import _SessionBind
65 from ..sql._typing import _AutoIncrementType
66 from ..sql._typing import _ColumnExpressionArgument
67 from ..sql._typing import _FromClauseArgument
68 from ..sql._typing import _InfoType
69 from ..sql._typing import _OnClauseArgument
70 from ..sql._typing import _TypeEngineArgument
71 from ..sql.elements import ColumnElement
72 from ..sql.schema import _ServerDefaultArgument
73 from ..sql.schema import _ServerOnUpdateArgument
74 from ..sql.selectable import Alias
75 from ..sql.selectable import Subquery
78_T = typing.TypeVar("_T")
81@util.deprecated(
82 "1.4",
83 "The :class:`.AliasOption` object is not necessary "
84 "for entities to be matched up to a query that is established "
85 "via :meth:`.Query.from_statement` and now does nothing.",
86 enable_warnings=False, # AliasOption itself warns
87)
88def contains_alias(alias: Union[Alias, Subquery]) -> AliasOption:
89 r"""Return a :class:`.MapperOption` that will indicate to the
90 :class:`_query.Query`
91 that the main table has been aliased.
93 """
94 return AliasOption(alias)
97def mapped_column(
98 __name_pos: Optional[
99 Union[str, _TypeEngineArgument[Any], SchemaEventTarget]
100 ] = None,
101 __type_pos: Optional[
102 Union[_TypeEngineArgument[Any], SchemaEventTarget]
103 ] = None,
104 *args: SchemaEventTarget,
105 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
106 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
107 default: Optional[Any] = _NoArg.NO_ARG,
108 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
109 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
110 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
111 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
112 nullable: Optional[
113 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
114 ] = SchemaConst.NULL_UNSPECIFIED,
115 primary_key: Optional[bool] = False,
116 deferred: Union[_NoArg, bool] = _NoArg.NO_ARG,
117 deferred_group: Optional[str] = None,
118 deferred_raiseload: Optional[bool] = None,
119 use_existing_column: bool = False,
120 name: Optional[str] = None,
121 type_: Optional[_TypeEngineArgument[Any]] = None,
122 autoincrement: _AutoIncrementType = "auto",
123 doc: Optional[str] = None,
124 key: Optional[str] = None,
125 index: Optional[bool] = None,
126 unique: Optional[bool] = None,
127 info: Optional[_InfoType] = None,
128 onupdate: Optional[Any] = None,
129 insert_default: Optional[Any] = _NoArg.NO_ARG,
130 server_default: Optional[_ServerDefaultArgument] = None,
131 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
132 active_history: bool = False,
133 quote: Optional[bool] = None,
134 system: bool = False,
135 comment: Optional[str] = None,
136 sort_order: Union[_NoArg, int] = _NoArg.NO_ARG,
137 **kw: Any,
138) -> MappedColumn[Any]:
139 r"""declare a new ORM-mapped :class:`_schema.Column` construct
140 for use within :ref:`Declarative Table <orm_declarative_table>`
141 configuration.
143 The :func:`_orm.mapped_column` function provides an ORM-aware and
144 Python-typing-compatible construct which is used with
145 :ref:`declarative <orm_declarative_mapping>` mappings to indicate an
146 attribute that's mapped to a Core :class:`_schema.Column` object. It
147 provides the equivalent feature as mapping an attribute to a
148 :class:`_schema.Column` object directly when using Declarative,
149 specifically when using :ref:`Declarative Table <orm_declarative_table>`
150 configuration.
152 .. versionadded:: 2.0
154 :func:`_orm.mapped_column` is normally used with explicit typing along with
155 the :class:`_orm.Mapped` annotation type, where it can derive the SQL
156 type and nullability for the column based on what's present within the
157 :class:`_orm.Mapped` annotation. It also may be used without annotations
158 as a drop-in replacement for how :class:`_schema.Column` is used in
159 Declarative mappings in SQLAlchemy 1.x style.
161 For usage examples of :func:`_orm.mapped_column`, see the documentation
162 at :ref:`orm_declarative_table`.
164 .. seealso::
166 :ref:`orm_declarative_table` - complete documentation
168 :ref:`whatsnew_20_orm_declarative_typing` - migration notes for
169 Declarative mappings using 1.x style mappings
171 :param __name: String name to give to the :class:`_schema.Column`. This
172 is an optional, positional only argument that if present must be the
173 first positional argument passed. If omitted, the attribute name to
174 which the :func:`_orm.mapped_column` is mapped will be used as the SQL
175 column name.
176 :param __type: :class:`_types.TypeEngine` type or instance which will
177 indicate the datatype to be associated with the :class:`_schema.Column`.
178 This is an optional, positional-only argument that if present must
179 immediately follow the ``__name`` parameter if present also, or otherwise
180 be the first positional parameter. If omitted, the ultimate type for
181 the column may be derived either from the annotated type, or if a
182 :class:`_schema.ForeignKey` is present, from the datatype of the
183 referenced column.
184 :param \*args: Additional positional arguments include constructs such
185 as :class:`_schema.ForeignKey`, :class:`_schema.CheckConstraint`,
186 and :class:`_schema.Identity`, which are passed through to the constructed
187 :class:`_schema.Column`.
188 :param nullable: Optional bool, whether the column should be "NULL" or
189 "NOT NULL". If omitted, the nullability is derived from the type
190 annotation based on whether or not ``typing.Optional`` is present.
191 ``nullable`` defaults to ``True`` otherwise for non-primary key columns,
192 and ``False`` for primary key columns.
193 :param primary_key: optional bool, indicates the :class:`_schema.Column`
194 would be part of the table's primary key or not.
195 :param deferred: Optional bool - this keyword argument is consumed by the
196 ORM declarative process, and is not part of the :class:`_schema.Column`
197 itself; instead, it indicates that this column should be "deferred" for
198 loading as though mapped by :func:`_orm.deferred`.
200 .. seealso::
202 :ref:`orm_queryguide_deferred_declarative`
204 :param deferred_group: Implies :paramref:`_orm.mapped_column.deferred`
205 to ``True``, and set the :paramref:`_orm.deferred.group` parameter.
207 .. seealso::
209 :ref:`orm_queryguide_deferred_group`
211 :param deferred_raiseload: Implies :paramref:`_orm.mapped_column.deferred`
212 to ``True``, and set the :paramref:`_orm.deferred.raiseload` parameter.
214 .. seealso::
216 :ref:`orm_queryguide_deferred_raiseload`
218 :param use_existing_column: if True, will attempt to locate the given
219 column name on an inherited superclass (typically single inheriting
220 superclass), and if present, will not produce a new column, mapping
221 to the superclass column as though it were omitted from this class.
222 This is used for mixins that add new columns to an inherited superclass.
224 .. seealso::
226 :ref:`orm_inheritance_column_conflicts`
228 .. versionadded:: 2.0.0b4
230 :param default: Passed directly to the
231 :paramref:`_schema.Column.default` parameter if the
232 :paramref:`_orm.mapped_column.insert_default` parameter is not present.
233 Additionally, when used with :ref:`orm_declarative_native_dataclasses`,
234 indicates a default Python value that should be applied to the keyword
235 constructor within the generated ``__init__()`` method.
237 Note that in the case of dataclass generation when
238 :paramref:`_orm.mapped_column.insert_default` is not present, this means
239 the :paramref:`_orm.mapped_column.default` value is used in **two**
240 places, both the ``__init__()`` method as well as the
241 :paramref:`_schema.Column.default` parameter. While this behavior may
242 change in a future release, for the moment this tends to "work out"; a
243 default of ``None`` will mean that the :class:`_schema.Column` gets no
244 default generator, whereas a default that refers to a non-``None`` Python
245 or SQL expression value will be assigned up front on the object when
246 ``__init__()`` is called, which is the same value that the Core
247 :class:`_sql.Insert` construct would use in any case, leading to the same
248 end result.
250 .. note:: When using Core level column defaults that are callables to
251 be interpreted by the underlying :class:`_schema.Column` in conjunction
252 with :ref:`ORM-mapped dataclasses
253 <orm_declarative_native_dataclasses>`, especially those that are
254 :ref:`context-aware default functions <context_default_functions>`,
255 **the** :paramref:`_orm.mapped_column.insert_default` **parameter must
256 be used instead**. This is necessary to disambiguate the callable from
257 being interpreted as a dataclass level default.
259 .. seealso::
261 :ref:`defaults_default_factory_insert_default`
263 :paramref:`_orm.mapped_column.insert_default`
265 :paramref:`_orm.mapped_column.default_factory`
267 :param insert_default: Passed directly to the
268 :paramref:`_schema.Column.default` parameter; will supersede the value
269 of :paramref:`_orm.mapped_column.default` when present, however
270 :paramref:`_orm.mapped_column.default` will always apply to the
271 constructor default for a dataclasses mapping.
273 .. seealso::
275 :ref:`defaults_default_factory_insert_default`
277 :paramref:`_orm.mapped_column.default`
279 :paramref:`_orm.mapped_column.default_factory`
281 :param sort_order: An integer that indicates how this mapped column
282 should be sorted compared to the others when the ORM is creating a
283 :class:`_schema.Table`. Among mapped columns that have the same
284 value the default ordering is used, placing first the mapped columns
285 defined in the main class, then the ones in the super classes.
286 Defaults to 0. The sort is ascending.
288 .. versionadded:: 2.0.4
290 :param active_history=False:
292 When ``True``, indicates that the "previous" value for a
293 scalar attribute should be loaded when replaced, if not
294 already loaded. Normally, history tracking logic for
295 simple non-primary-key scalar values only needs to be
296 aware of the "new" value in order to perform a flush. This
297 flag is available for applications that make use of
298 :func:`.attributes.get_history` or :meth:`.Session.is_modified`
299 which also need to know the "previous" value of the attribute.
301 .. versionadded:: 2.0.10
304 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
305 specifies if the mapped attribute should be part of the ``__init__()``
306 method as generated by the dataclass process.
307 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
308 specifies if the mapped attribute should be part of the ``__repr__()``
309 method as generated by the dataclass process.
310 :param default_factory: Specific to
311 :ref:`orm_declarative_native_dataclasses`,
312 specifies a default-value generation function that will take place
313 as part of the ``__init__()``
314 method as generated by the dataclass process.
316 .. seealso::
318 :ref:`defaults_default_factory_insert_default`
320 :paramref:`_orm.mapped_column.default`
322 :paramref:`_orm.mapped_column.insert_default`
324 :param compare: Specific to
325 :ref:`orm_declarative_native_dataclasses`, indicates if this field
326 should be included in comparison operations when generating the
327 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
329 .. versionadded:: 2.0.0b4
331 :param kw_only: Specific to
332 :ref:`orm_declarative_native_dataclasses`, indicates if this field
333 should be marked as keyword-only when generating the ``__init__()``.
335 :param hash: Specific to
336 :ref:`orm_declarative_native_dataclasses`, controls if this field
337 is included when generating the ``__hash__()`` method for the mapped
338 class.
340 .. versionadded:: 2.0.36
342 :param \**kw: All remaining keyword arguments are passed through to the
343 constructor for the :class:`_schema.Column`.
345 """
347 return MappedColumn(
348 __name_pos,
349 __type_pos,
350 *args,
351 name=name,
352 type_=type_,
353 autoincrement=autoincrement,
354 insert_default=insert_default,
355 attribute_options=_AttributeOptions(
356 init, repr, default, default_factory, compare, kw_only, hash
357 ),
358 doc=doc,
359 key=key,
360 index=index,
361 unique=unique,
362 info=info,
363 active_history=active_history,
364 nullable=nullable,
365 onupdate=onupdate,
366 primary_key=primary_key,
367 server_default=server_default,
368 server_onupdate=server_onupdate,
369 use_existing_column=use_existing_column,
370 quote=quote,
371 comment=comment,
372 system=system,
373 deferred=deferred,
374 deferred_group=deferred_group,
375 deferred_raiseload=deferred_raiseload,
376 sort_order=sort_order,
377 **kw,
378 )
381def orm_insert_sentinel(
382 name: Optional[str] = None,
383 type_: Optional[_TypeEngineArgument[Any]] = None,
384 *,
385 default: Optional[Any] = None,
386 omit_from_statements: bool = True,
387) -> MappedColumn[Any]:
388 """Provides a surrogate :func:`_orm.mapped_column` that generates
389 a so-called :term:`sentinel` column, allowing efficient bulk
390 inserts with deterministic RETURNING sorting for tables that don't
391 otherwise have qualifying primary key configurations.
393 Use of :func:`_orm.orm_insert_sentinel` is analogous to the use of the
394 :func:`_schema.insert_sentinel` construct within a Core
395 :class:`_schema.Table` construct.
397 Guidelines for adding this construct to a Declarative mapped class
398 are the same as that of the :func:`_schema.insert_sentinel` construct;
399 the database table itself also needs to have a column with this name
400 present.
402 For background on how this object is used, see the section
403 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
404 section :ref:`engine_insertmanyvalues`.
406 .. seealso::
408 :func:`_schema.insert_sentinel`
410 :ref:`engine_insertmanyvalues`
412 :ref:`engine_insertmanyvalues_sentinel_columns`
415 .. versionadded:: 2.0.10
417 """
419 return mapped_column(
420 name=name,
421 default=(
422 default if default is not None else _InsertSentinelColumnDefault()
423 ),
424 _omit_from_statements=omit_from_statements,
425 insert_sentinel=True,
426 use_existing_column=True,
427 nullable=True,
428 )
431@util.deprecated_params(
432 **{
433 arg: (
434 "2.0",
435 f"The :paramref:`_orm.column_property.{arg}` parameter is "
436 "deprecated for :func:`_orm.column_property`. This parameter "
437 "applies to a writeable-attribute in a Declarative Dataclasses "
438 "configuration only, and :func:`_orm.column_property` is treated "
439 "as a read-only attribute in this context.",
440 )
441 for arg in ("init", "kw_only", "default", "default_factory")
442 }
443)
444def column_property(
445 column: _ORMColumnExprArgument[_T],
446 *additional_columns: _ORMColumnExprArgument[Any],
447 group: Optional[str] = None,
448 deferred: bool = False,
449 raiseload: bool = False,
450 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
451 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
452 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
453 default: Optional[Any] = _NoArg.NO_ARG,
454 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
455 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
456 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
457 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
458 active_history: bool = False,
459 expire_on_flush: bool = True,
460 info: Optional[_InfoType] = None,
461 doc: Optional[str] = None,
462) -> MappedSQLExpression[_T]:
463 r"""Provide a column-level property for use with a mapping.
465 With Declarative mappings, :func:`_orm.column_property` is used to
466 map read-only SQL expressions to a mapped class.
468 When using Imperative mappings, :func:`_orm.column_property` also
469 takes on the role of mapping table columns with additional features.
470 When using fully Declarative mappings, the :func:`_orm.mapped_column`
471 construct should be used for this purpose.
473 With Declarative Dataclass mappings, :func:`_orm.column_property`
474 is considered to be **read only**, and will not be included in the
475 Dataclass ``__init__()`` constructor.
477 The :func:`_orm.column_property` function returns an instance of
478 :class:`.ColumnProperty`.
480 .. seealso::
482 :ref:`mapper_column_property_sql_expressions` - general use of
483 :func:`_orm.column_property` to map SQL expressions
485 :ref:`orm_imperative_table_column_options` - usage of
486 :func:`_orm.column_property` with Imperative Table mappings to apply
487 additional options to a plain :class:`_schema.Column` object
489 :param \*cols:
490 list of Column objects to be mapped.
492 :param active_history=False:
494 Used only for Imperative Table mappings, or legacy-style Declarative
495 mappings (i.e. which have not been upgraded to
496 :func:`_orm.mapped_column`), for column-based attributes that are
497 expected to be writeable; use :func:`_orm.mapped_column` with
498 :paramref:`_orm.mapped_column.active_history` for Declarative mappings.
499 See that parameter for functional details.
501 :param comparator_factory: a class which extends
502 :class:`.ColumnProperty.Comparator` which provides custom SQL
503 clause generation for comparison operations.
505 :param group:
506 a group name for this property when marked as deferred.
508 :param deferred:
509 when True, the column property is "deferred", meaning that
510 it does not load immediately, and is instead loaded when the
511 attribute is first accessed on an instance. See also
512 :func:`~sqlalchemy.orm.deferred`.
514 :param doc:
515 optional string that will be applied as the doc on the
516 class-bound descriptor.
518 :param expire_on_flush=True:
519 Disable expiry on flush. A column_property() which refers
520 to a SQL expression (and not a single table-bound column)
521 is considered to be a "read only" property; populating it
522 has no effect on the state of data, and it can only return
523 database state. For this reason a column_property()'s value
524 is expired whenever the parent object is involved in a
525 flush, that is, has any kind of "dirty" state within a flush.
526 Setting this parameter to ``False`` will have the effect of
527 leaving any existing value present after the flush proceeds.
528 Note that the :class:`.Session` with default expiration
529 settings still expires
530 all attributes after a :meth:`.Session.commit` call, however.
532 :param info: Optional data dictionary which will be populated into the
533 :attr:`.MapperProperty.info` attribute of this object.
535 :param raiseload: if True, indicates the column should raise an error
536 when undeferred, rather than loading the value. This can be
537 altered at query time by using the :func:`.deferred` option with
538 raiseload=False.
540 .. versionadded:: 1.4
542 .. seealso::
544 :ref:`orm_queryguide_deferred_raiseload`
546 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
547 specifies if the mapped attribute should be part of the ``__init__()``
548 method as generated by the dataclass process.
549 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
550 specifies if the mapped attribute should be part of the ``__repr__()``
551 method as generated by the dataclass process.
552 :param default_factory: Specific to
553 :ref:`orm_declarative_native_dataclasses`,
554 specifies a default-value generation function that will take place
555 as part of the ``__init__()``
556 method as generated by the dataclass process.
558 .. seealso::
560 :ref:`defaults_default_factory_insert_default`
562 :paramref:`_orm.mapped_column.default`
564 :paramref:`_orm.mapped_column.insert_default`
566 :param compare: Specific to
567 :ref:`orm_declarative_native_dataclasses`, indicates if this field
568 should be included in comparison operations when generating the
569 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
571 .. versionadded:: 2.0.0b4
573 :param kw_only: Specific to
574 :ref:`orm_declarative_native_dataclasses`, indicates if this field
575 should be marked as keyword-only when generating the ``__init__()``.
577 :param hash: Specific to
578 :ref:`orm_declarative_native_dataclasses`, controls if this field
579 is included when generating the ``__hash__()`` method for the mapped
580 class.
582 .. versionadded:: 2.0.36
584 """
585 return MappedSQLExpression(
586 column,
587 *additional_columns,
588 attribute_options=_AttributeOptions(
589 False if init is _NoArg.NO_ARG else init,
590 repr,
591 default,
592 default_factory,
593 compare,
594 kw_only,
595 hash,
596 ),
597 group=group,
598 deferred=deferred,
599 raiseload=raiseload,
600 comparator_factory=comparator_factory,
601 active_history=active_history,
602 expire_on_flush=expire_on_flush,
603 info=info,
604 doc=doc,
605 _assume_readonly_dc_attributes=True,
606 )
609@overload
610def composite(
611 _class_or_attr: _CompositeAttrType[Any],
612 *attrs: _CompositeAttrType[Any],
613 group: Optional[str] = None,
614 deferred: bool = False,
615 raiseload: bool = False,
616 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
617 active_history: bool = False,
618 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
619 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
620 default: Optional[Any] = _NoArg.NO_ARG,
621 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
622 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
623 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
624 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
625 info: Optional[_InfoType] = None,
626 doc: Optional[str] = None,
627 **__kw: Any,
628) -> Composite[Any]: ...
631@overload
632def composite(
633 _class_or_attr: Type[_CC],
634 *attrs: _CompositeAttrType[Any],
635 group: Optional[str] = None,
636 deferred: bool = False,
637 raiseload: bool = False,
638 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
639 active_history: bool = False,
640 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
641 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
642 default: Optional[Any] = _NoArg.NO_ARG,
643 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
644 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
645 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
646 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
647 info: Optional[_InfoType] = None,
648 doc: Optional[str] = None,
649 **__kw: Any,
650) -> Composite[_CC]: ...
653@overload
654def composite(
655 _class_or_attr: Callable[..., _CC],
656 *attrs: _CompositeAttrType[Any],
657 group: Optional[str] = None,
658 deferred: bool = False,
659 raiseload: bool = False,
660 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
661 active_history: bool = False,
662 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
663 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
664 default: Optional[Any] = _NoArg.NO_ARG,
665 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
666 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
667 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
668 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
669 info: Optional[_InfoType] = None,
670 doc: Optional[str] = None,
671 **__kw: Any,
672) -> Composite[_CC]: ...
675def composite(
676 _class_or_attr: Union[
677 None, Type[_CC], Callable[..., _CC], _CompositeAttrType[Any]
678 ] = None,
679 *attrs: _CompositeAttrType[Any],
680 group: Optional[str] = None,
681 deferred: bool = False,
682 raiseload: bool = False,
683 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
684 active_history: bool = False,
685 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
686 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
687 default: Optional[Any] = _NoArg.NO_ARG,
688 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
689 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
690 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
691 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
692 info: Optional[_InfoType] = None,
693 doc: Optional[str] = None,
694 **__kw: Any,
695) -> Composite[Any]:
696 r"""Return a composite column-based property for use with a Mapper.
698 See the mapping documentation section :ref:`mapper_composite` for a
699 full usage example.
701 The :class:`.MapperProperty` returned by :func:`.composite`
702 is the :class:`.Composite`.
704 :param class\_:
705 The "composite type" class, or any classmethod or callable which
706 will produce a new instance of the composite object given the
707 column values in order.
709 :param \*attrs:
710 List of elements to be mapped, which may include:
712 * :class:`_schema.Column` objects
713 * :func:`_orm.mapped_column` constructs
714 * string names of other attributes on the mapped class, which may be
715 any other SQL or object-mapped attribute. This can for
716 example allow a composite that refers to a many-to-one relationship
718 :param active_history=False:
719 When ``True``, indicates that the "previous" value for a
720 scalar attribute should be loaded when replaced, if not
721 already loaded. See the same flag on :func:`.column_property`.
723 :param group:
724 A group name for this property when marked as deferred.
726 :param deferred:
727 When True, the column property is "deferred", meaning that it does
728 not load immediately, and is instead loaded when the attribute is
729 first accessed on an instance. See also
730 :func:`~sqlalchemy.orm.deferred`.
732 :param comparator_factory: a class which extends
733 :class:`.Composite.Comparator` which provides custom SQL
734 clause generation for comparison operations.
736 :param doc:
737 optional string that will be applied as the doc on the
738 class-bound descriptor.
740 :param info: Optional data dictionary which will be populated into the
741 :attr:`.MapperProperty.info` attribute of this object.
743 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
744 specifies if the mapped attribute should be part of the ``__init__()``
745 method as generated by the dataclass process.
746 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
747 specifies if the mapped attribute should be part of the ``__repr__()``
748 method as generated by the dataclass process.
749 :param default_factory: Specific to
750 :ref:`orm_declarative_native_dataclasses`,
751 specifies a default-value generation function that will take place
752 as part of the ``__init__()``
753 method as generated by the dataclass process.
755 :param compare: Specific to
756 :ref:`orm_declarative_native_dataclasses`, indicates if this field
757 should be included in comparison operations when generating the
758 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
760 .. versionadded:: 2.0.0b4
762 :param kw_only: Specific to
763 :ref:`orm_declarative_native_dataclasses`, indicates if this field
764 should be marked as keyword-only when generating the ``__init__()``.
766 :param hash: Specific to
767 :ref:`orm_declarative_native_dataclasses`, controls if this field
768 is included when generating the ``__hash__()`` method for the mapped
769 class.
771 .. versionadded:: 2.0.36
772 """
773 if __kw:
774 raise _no_kw()
776 return Composite(
777 _class_or_attr,
778 *attrs,
779 attribute_options=_AttributeOptions(
780 init, repr, default, default_factory, compare, kw_only, hash
781 ),
782 group=group,
783 deferred=deferred,
784 raiseload=raiseload,
785 comparator_factory=comparator_factory,
786 active_history=active_history,
787 info=info,
788 doc=doc,
789 )
792def with_loader_criteria(
793 entity_or_base: _EntityType[Any],
794 where_criteria: Union[
795 _ColumnExpressionArgument[bool],
796 Callable[[Any], _ColumnExpressionArgument[bool]],
797 ],
798 loader_only: bool = False,
799 include_aliases: bool = False,
800 propagate_to_loaders: bool = True,
801 track_closure_variables: bool = True,
802) -> LoaderCriteriaOption:
803 """Add additional WHERE criteria to the load for all occurrences of
804 a particular entity.
806 .. versionadded:: 1.4
808 The :func:`_orm.with_loader_criteria` option is intended to add
809 limiting criteria to a particular kind of entity in a query,
810 **globally**, meaning it will apply to the entity as it appears
811 in the SELECT query as well as within any subqueries, join
812 conditions, and relationship loads, including both eager and lazy
813 loaders, without the need for it to be specified in any particular
814 part of the query. The rendering logic uses the same system used by
815 single table inheritance to ensure a certain discriminator is applied
816 to a table.
818 E.g., using :term:`2.0-style` queries, we can limit the way the
819 ``User.addresses`` collection is loaded, regardless of the kind
820 of loading used::
822 from sqlalchemy.orm import with_loader_criteria
824 stmt = select(User).options(
825 selectinload(User.addresses),
826 with_loader_criteria(Address, Address.email_address != "foo"),
827 )
829 Above, the "selectinload" for ``User.addresses`` will apply the
830 given filtering criteria to the WHERE clause.
832 Another example, where the filtering will be applied to the
833 ON clause of the join, in this example using :term:`1.x style`
834 queries::
836 q = (
837 session.query(User)
838 .outerjoin(User.addresses)
839 .options(with_loader_criteria(Address, Address.email_address != "foo"))
840 )
842 The primary purpose of :func:`_orm.with_loader_criteria` is to use
843 it in the :meth:`_orm.SessionEvents.do_orm_execute` event handler
844 to ensure that all occurrences of a particular entity are filtered
845 in a certain way, such as filtering for access control roles. It
846 also can be used to apply criteria to relationship loads. In the
847 example below, we can apply a certain set of rules to all queries
848 emitted by a particular :class:`_orm.Session`::
850 session = Session(bind=engine)
853 @event.listens_for("do_orm_execute", session)
854 def _add_filtering_criteria(execute_state):
856 if (
857 execute_state.is_select
858 and not execute_state.is_column_load
859 and not execute_state.is_relationship_load
860 ):
861 execute_state.statement = execute_state.statement.options(
862 with_loader_criteria(
863 SecurityRole,
864 lambda cls: cls.role.in_(["some_role"]),
865 include_aliases=True,
866 )
867 )
869 In the above example, the :meth:`_orm.SessionEvents.do_orm_execute`
870 event will intercept all queries emitted using the
871 :class:`_orm.Session`. For those queries which are SELECT statements
872 and are not attribute or relationship loads a custom
873 :func:`_orm.with_loader_criteria` option is added to the query. The
874 :func:`_orm.with_loader_criteria` option will be used in the given
875 statement and will also be automatically propagated to all relationship
876 loads that descend from this query.
878 The criteria argument given is a ``lambda`` that accepts a ``cls``
879 argument. The given class will expand to include all mapped subclass
880 and need not itself be a mapped class.
882 .. tip::
884 When using :func:`_orm.with_loader_criteria` option in
885 conjunction with the :func:`_orm.contains_eager` loader option,
886 it's important to note that :func:`_orm.with_loader_criteria` only
887 affects the part of the query that determines what SQL is rendered
888 in terms of the WHERE and FROM clauses. The
889 :func:`_orm.contains_eager` option does not affect the rendering of
890 the SELECT statement outside of the columns clause, so does not have
891 any interaction with the :func:`_orm.with_loader_criteria` option.
892 However, the way things "work" is that :func:`_orm.contains_eager`
893 is meant to be used with a query that is already selecting from the
894 additional entities in some way, where
895 :func:`_orm.with_loader_criteria` can apply it's additional
896 criteria.
898 In the example below, assuming a mapping relationship as
899 ``A -> A.bs -> B``, the given :func:`_orm.with_loader_criteria`
900 option will affect the way in which the JOIN is rendered::
902 stmt = (
903 select(A)
904 .join(A.bs)
905 .options(contains_eager(A.bs), with_loader_criteria(B, B.flag == 1))
906 )
908 Above, the given :func:`_orm.with_loader_criteria` option will
909 affect the ON clause of the JOIN that is specified by
910 ``.join(A.bs)``, so is applied as expected. The
911 :func:`_orm.contains_eager` option has the effect that columns from
912 ``B`` are added to the columns clause:
914 .. sourcecode:: sql
916 SELECT
917 b.id, b.a_id, b.data, b.flag,
918 a.id AS id_1,
919 a.data AS data_1
920 FROM a JOIN b ON a.id = b.a_id AND b.flag = :flag_1
923 The use of the :func:`_orm.contains_eager` option within the above
924 statement has no effect on the behavior of the
925 :func:`_orm.with_loader_criteria` option. If the
926 :func:`_orm.contains_eager` option were omitted, the SQL would be
927 the same as regards the FROM and WHERE clauses, where
928 :func:`_orm.with_loader_criteria` continues to add its criteria to
929 the ON clause of the JOIN. The addition of
930 :func:`_orm.contains_eager` only affects the columns clause, in that
931 additional columns against ``b`` are added which are then consumed
932 by the ORM to produce ``B`` instances.
934 .. warning:: The use of a lambda inside of the call to
935 :func:`_orm.with_loader_criteria` is only invoked **once per unique
936 class**. Custom functions should not be invoked within this lambda.
937 See :ref:`engine_lambda_caching` for an overview of the "lambda SQL"
938 feature, which is for advanced use only.
940 :param entity_or_base: a mapped class, or a class that is a super
941 class of a particular set of mapped classes, to which the rule
942 will apply.
944 :param where_criteria: a Core SQL expression that applies limiting
945 criteria. This may also be a "lambda:" or Python function that
946 accepts a target class as an argument, when the given class is
947 a base with many different mapped subclasses.
949 .. note:: To support pickling, use a module-level Python function to
950 produce the SQL expression instead of a lambda or a fixed SQL
951 expression, which tend to not be picklable.
953 :param include_aliases: if True, apply the rule to :func:`_orm.aliased`
954 constructs as well.
956 :param propagate_to_loaders: defaults to True, apply to relationship
957 loaders such as lazy loaders. This indicates that the
958 option object itself including SQL expression is carried along with
959 each loaded instance. Set to ``False`` to prevent the object from
960 being assigned to individual instances.
963 .. seealso::
965 :ref:`examples_session_orm_events` - includes examples of using
966 :func:`_orm.with_loader_criteria`.
968 :ref:`do_orm_execute_global_criteria` - basic example on how to
969 combine :func:`_orm.with_loader_criteria` with the
970 :meth:`_orm.SessionEvents.do_orm_execute` event.
972 :param track_closure_variables: when False, closure variables inside
973 of a lambda expression will not be used as part of
974 any cache key. This allows more complex expressions to be used
975 inside of a lambda expression but requires that the lambda ensures
976 it returns the identical SQL every time given a particular class.
978 .. versionadded:: 1.4.0b2
980 """ # noqa: E501
981 return LoaderCriteriaOption(
982 entity_or_base,
983 where_criteria,
984 loader_only,
985 include_aliases,
986 propagate_to_loaders,
987 track_closure_variables,
988 )
991def relationship(
992 argument: Optional[_RelationshipArgumentType[Any]] = None,
993 secondary: Optional[_RelationshipSecondaryArgument] = None,
994 *,
995 uselist: Optional[bool] = None,
996 collection_class: Optional[
997 Union[Type[Collection[Any]], Callable[[], Collection[Any]]]
998 ] = None,
999 primaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
1000 secondaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
1001 back_populates: Optional[str] = None,
1002 order_by: _ORMOrderByArgument = False,
1003 backref: Optional[ORMBackrefArgument] = None,
1004 overlaps: Optional[str] = None,
1005 post_update: bool = False,
1006 cascade: str = "save-update, merge",
1007 viewonly: bool = False,
1008 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
1009 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
1010 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
1011 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
1012 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
1013 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
1014 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
1015 lazy: _LazyLoadArgumentType = "select",
1016 passive_deletes: Union[Literal["all"], bool] = False,
1017 passive_updates: bool = True,
1018 active_history: bool = False,
1019 enable_typechecks: bool = True,
1020 foreign_keys: Optional[_ORMColCollectionArgument] = None,
1021 remote_side: Optional[_ORMColCollectionArgument] = None,
1022 join_depth: Optional[int] = None,
1023 comparator_factory: Optional[
1024 Type[RelationshipProperty.Comparator[Any]]
1025 ] = None,
1026 single_parent: bool = False,
1027 innerjoin: bool = False,
1028 distinct_target_key: Optional[bool] = None,
1029 load_on_pending: bool = False,
1030 query_class: Optional[Type[Query[Any]]] = None,
1031 info: Optional[_InfoType] = None,
1032 omit_join: Literal[None, False] = None,
1033 sync_backref: Optional[bool] = None,
1034 **kw: Any,
1035) -> _RelationshipDeclared[Any]:
1036 """Provide a relationship between two mapped classes.
1038 This corresponds to a parent-child or associative table relationship.
1039 The constructed class is an instance of :class:`.Relationship`.
1041 .. seealso::
1043 :ref:`tutorial_orm_related_objects` - tutorial introduction
1044 to :func:`_orm.relationship` in the :ref:`unified_tutorial`
1046 :ref:`relationship_config_toplevel` - narrative documentation
1048 :param argument:
1049 This parameter refers to the class that is to be related. It
1050 accepts several forms, including a direct reference to the target
1051 class itself, the :class:`_orm.Mapper` instance for the target class,
1052 a Python callable / lambda that will return a reference to the
1053 class or :class:`_orm.Mapper` when called, and finally a string
1054 name for the class, which will be resolved from the
1055 :class:`_orm.registry` in use in order to locate the class, e.g.::
1057 class SomeClass(Base):
1058 # ...
1060 related = relationship("RelatedClass")
1062 The :paramref:`_orm.relationship.argument` may also be omitted from the
1063 :func:`_orm.relationship` construct entirely, and instead placed inside
1064 a :class:`_orm.Mapped` annotation on the left side, which should
1065 include a Python collection type if the relationship is expected
1066 to be a collection, such as::
1068 class SomeClass(Base):
1069 # ...
1071 related_items: Mapped[List["RelatedItem"]] = relationship()
1073 Or for a many-to-one or one-to-one relationship::
1075 class SomeClass(Base):
1076 # ...
1078 related_item: Mapped["RelatedItem"] = relationship()
1080 .. seealso::
1082 :ref:`orm_declarative_properties` - further detail
1083 on relationship configuration when using Declarative.
1085 :param secondary:
1086 For a many-to-many relationship, specifies the intermediary
1087 table, and is typically an instance of :class:`_schema.Table`.
1088 In less common circumstances, the argument may also be specified
1089 as an :class:`_expression.Alias` construct, or even a
1090 :class:`_expression.Join` construct.
1092 :paramref:`_orm.relationship.secondary` may
1093 also be passed as a callable function which is evaluated at
1094 mapper initialization time. When using Declarative, it may also
1095 be a string argument noting the name of a :class:`_schema.Table`
1096 that is
1097 present in the :class:`_schema.MetaData`
1098 collection associated with the
1099 parent-mapped :class:`_schema.Table`.
1101 .. warning:: When passed as a Python-evaluable string, the
1102 argument is interpreted using Python's ``eval()`` function.
1103 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1104 See :ref:`declarative_relationship_eval` for details on
1105 declarative evaluation of :func:`_orm.relationship` arguments.
1107 The :paramref:`_orm.relationship.secondary` keyword argument is
1108 typically applied in the case where the intermediary
1109 :class:`_schema.Table`
1110 is not otherwise expressed in any direct class mapping. If the
1111 "secondary" table is also explicitly mapped elsewhere (e.g. as in
1112 :ref:`association_pattern`), one should consider applying the
1113 :paramref:`_orm.relationship.viewonly` flag so that this
1114 :func:`_orm.relationship`
1115 is not used for persistence operations which
1116 may conflict with those of the association object pattern.
1118 .. seealso::
1120 :ref:`relationships_many_to_many` - Reference example of "many
1121 to many".
1123 :ref:`self_referential_many_to_many` - Specifics on using
1124 many-to-many in a self-referential case.
1126 :ref:`declarative_many_to_many` - Additional options when using
1127 Declarative.
1129 :ref:`association_pattern` - an alternative to
1130 :paramref:`_orm.relationship.secondary`
1131 when composing association
1132 table relationships, allowing additional attributes to be
1133 specified on the association table.
1135 :ref:`composite_secondary_join` - a lesser-used pattern which
1136 in some cases can enable complex :func:`_orm.relationship` SQL
1137 conditions to be used.
1139 :param active_history=False:
1140 When ``True``, indicates that the "previous" value for a
1141 many-to-one reference should be loaded when replaced, if
1142 not already loaded. Normally, history tracking logic for
1143 simple many-to-ones only needs to be aware of the "new"
1144 value in order to perform a flush. This flag is available
1145 for applications that make use of
1146 :func:`.attributes.get_history` which also need to know
1147 the "previous" value of the attribute.
1149 :param backref:
1150 A reference to a string relationship name, or a :func:`_orm.backref`
1151 construct, which will be used to automatically generate a new
1152 :func:`_orm.relationship` on the related class, which then refers to this
1153 one using a bi-directional :paramref:`_orm.relationship.back_populates`
1154 configuration.
1156 In modern Python, explicit use of :func:`_orm.relationship`
1157 with :paramref:`_orm.relationship.back_populates` should be preferred,
1158 as it is more robust in terms of mapper configuration as well as
1159 more conceptually straightforward. It also integrates with
1160 new :pep:`484` typing features introduced in SQLAlchemy 2.0 which
1161 is not possible with dynamically generated attributes.
1163 .. seealso::
1165 :ref:`relationships_backref` - notes on using
1166 :paramref:`_orm.relationship.backref`
1168 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1169 presents an overview of bi-directional relationship configuration
1170 and behaviors using :paramref:`_orm.relationship.back_populates`
1172 :func:`.backref` - allows control over :func:`_orm.relationship`
1173 configuration when using :paramref:`_orm.relationship.backref`.
1176 :param back_populates:
1177 Indicates the name of a :func:`_orm.relationship` on the related
1178 class that will be synchronized with this one. It is usually
1179 expected that the :func:`_orm.relationship` on the related class
1180 also refer to this one. This allows objects on both sides of
1181 each :func:`_orm.relationship` to synchronize in-Python state
1182 changes and also provides directives to the :term:`unit of work`
1183 flush process how changes along these relationships should
1184 be persisted.
1186 .. seealso::
1188 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1189 presents an overview of bi-directional relationship configuration
1190 and behaviors.
1192 :ref:`relationship_patterns` - includes many examples of
1193 :paramref:`_orm.relationship.back_populates`.
1195 :paramref:`_orm.relationship.backref` - legacy form which allows
1196 more succinct configuration, but does not support explicit typing
1198 :param overlaps:
1199 A string name or comma-delimited set of names of other relationships
1200 on either this mapper, a descendant mapper, or a target mapper with
1201 which this relationship may write to the same foreign keys upon
1202 persistence. The only effect this has is to eliminate the
1203 warning that this relationship will conflict with another upon
1204 persistence. This is used for such relationships that are truly
1205 capable of conflicting with each other on write, but the application
1206 will ensure that no such conflicts occur.
1208 .. versionadded:: 1.4
1210 .. seealso::
1212 :ref:`error_qzyx` - usage example
1214 :param cascade:
1215 A comma-separated list of cascade rules which determines how
1216 Session operations should be "cascaded" from parent to child.
1217 This defaults to ``False``, which means the default cascade
1218 should be used - this default cascade is ``"save-update, merge"``.
1220 The available cascades are ``save-update``, ``merge``,
1221 ``expunge``, ``delete``, ``delete-orphan``, and ``refresh-expire``.
1222 An additional option, ``all`` indicates shorthand for
1223 ``"save-update, merge, refresh-expire,
1224 expunge, delete"``, and is often used as in ``"all, delete-orphan"``
1225 to indicate that related objects should follow along with the
1226 parent object in all cases, and be deleted when de-associated.
1228 .. seealso::
1230 :ref:`unitofwork_cascades` - Full detail on each of the available
1231 cascade options.
1233 :param cascade_backrefs=False:
1234 Legacy; this flag is always False.
1236 .. versionchanged:: 2.0 "cascade_backrefs" functionality has been
1237 removed.
1239 :param collection_class:
1240 A class or callable that returns a new list-holding object. will
1241 be used in place of a plain list for storing elements.
1243 .. seealso::
1245 :ref:`custom_collections` - Introductory documentation and
1246 examples.
1248 :param comparator_factory:
1249 A class which extends :class:`.Relationship.Comparator`
1250 which provides custom SQL clause generation for comparison
1251 operations.
1253 .. seealso::
1255 :class:`.PropComparator` - some detail on redefining comparators
1256 at this level.
1258 :ref:`custom_comparators` - Brief intro to this feature.
1261 :param distinct_target_key=None:
1262 Indicate if a "subquery" eager load should apply the DISTINCT
1263 keyword to the innermost SELECT statement. When left as ``None``,
1264 the DISTINCT keyword will be applied in those cases when the target
1265 columns do not comprise the full primary key of the target table.
1266 When set to ``True``, the DISTINCT keyword is applied to the
1267 innermost SELECT unconditionally.
1269 It may be desirable to set this flag to False when the DISTINCT is
1270 reducing performance of the innermost subquery beyond that of what
1271 duplicate innermost rows may be causing.
1273 .. seealso::
1275 :ref:`loading_toplevel` - includes an introduction to subquery
1276 eager loading.
1278 :param doc:
1279 Docstring which will be applied to the resulting descriptor.
1281 :param foreign_keys:
1283 A list of columns which are to be used as "foreign key"
1284 columns, or columns which refer to the value in a remote
1285 column, within the context of this :func:`_orm.relationship`
1286 object's :paramref:`_orm.relationship.primaryjoin` condition.
1287 That is, if the :paramref:`_orm.relationship.primaryjoin`
1288 condition of this :func:`_orm.relationship` is ``a.id ==
1289 b.a_id``, and the values in ``b.a_id`` are required to be
1290 present in ``a.id``, then the "foreign key" column of this
1291 :func:`_orm.relationship` is ``b.a_id``.
1293 In normal cases, the :paramref:`_orm.relationship.foreign_keys`
1294 parameter is **not required.** :func:`_orm.relationship` will
1295 automatically determine which columns in the
1296 :paramref:`_orm.relationship.primaryjoin` condition are to be
1297 considered "foreign key" columns based on those
1298 :class:`_schema.Column` objects that specify
1299 :class:`_schema.ForeignKey`,
1300 or are otherwise listed as referencing columns in a
1301 :class:`_schema.ForeignKeyConstraint` construct.
1302 :paramref:`_orm.relationship.foreign_keys` is only needed when:
1304 1. There is more than one way to construct a join from the local
1305 table to the remote table, as there are multiple foreign key
1306 references present. Setting ``foreign_keys`` will limit the
1307 :func:`_orm.relationship`
1308 to consider just those columns specified
1309 here as "foreign".
1311 2. The :class:`_schema.Table` being mapped does not actually have
1312 :class:`_schema.ForeignKey` or
1313 :class:`_schema.ForeignKeyConstraint`
1314 constructs present, often because the table
1315 was reflected from a database that does not support foreign key
1316 reflection (MySQL MyISAM).
1318 3. The :paramref:`_orm.relationship.primaryjoin`
1319 argument is used to
1320 construct a non-standard join condition, which makes use of
1321 columns or expressions that do not normally refer to their
1322 "parent" column, such as a join condition expressed by a
1323 complex comparison using a SQL function.
1325 The :func:`_orm.relationship` construct will raise informative
1326 error messages that suggest the use of the
1327 :paramref:`_orm.relationship.foreign_keys` parameter when
1328 presented with an ambiguous condition. In typical cases,
1329 if :func:`_orm.relationship` doesn't raise any exceptions, the
1330 :paramref:`_orm.relationship.foreign_keys` parameter is usually
1331 not needed.
1333 :paramref:`_orm.relationship.foreign_keys` may also be passed as a
1334 callable function which is evaluated at mapper initialization time,
1335 and may be passed as a Python-evaluable string when using
1336 Declarative.
1338 .. warning:: When passed as a Python-evaluable string, the
1339 argument is interpreted using Python's ``eval()`` function.
1340 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1341 See :ref:`declarative_relationship_eval` for details on
1342 declarative evaluation of :func:`_orm.relationship` arguments.
1344 .. seealso::
1346 :ref:`relationship_foreign_keys`
1348 :ref:`relationship_custom_foreign`
1350 :func:`.foreign` - allows direct annotation of the "foreign"
1351 columns within a :paramref:`_orm.relationship.primaryjoin`
1352 condition.
1354 :param info: Optional data dictionary which will be populated into the
1355 :attr:`.MapperProperty.info` attribute of this object.
1357 :param innerjoin=False:
1358 When ``True``, joined eager loads will use an inner join to join
1359 against related tables instead of an outer join. The purpose
1360 of this option is generally one of performance, as inner joins
1361 generally perform better than outer joins.
1363 This flag can be set to ``True`` when the relationship references an
1364 object via many-to-one using local foreign keys that are not
1365 nullable, or when the reference is one-to-one or a collection that
1366 is guaranteed to have one or at least one entry.
1368 The option supports the same "nested" and "unnested" options as
1369 that of :paramref:`_orm.joinedload.innerjoin`. See that flag
1370 for details on nested / unnested behaviors.
1372 .. seealso::
1374 :paramref:`_orm.joinedload.innerjoin` - the option as specified by
1375 loader option, including detail on nesting behavior.
1377 :ref:`what_kind_of_loading` - Discussion of some details of
1378 various loader options.
1381 :param join_depth:
1382 When non-``None``, an integer value indicating how many levels
1383 deep "eager" loaders should join on a self-referring or cyclical
1384 relationship. The number counts how many times the same Mapper
1385 shall be present in the loading condition along a particular join
1386 branch. When left at its default of ``None``, eager loaders
1387 will stop chaining when they encounter a the same target mapper
1388 which is already higher up in the chain. This option applies
1389 both to joined- and subquery- eager loaders.
1391 .. seealso::
1393 :ref:`self_referential_eager_loading` - Introductory documentation
1394 and examples.
1396 :param lazy='select': specifies
1397 How the related items should be loaded. Default value is
1398 ``select``. Values include:
1400 * ``select`` - items should be loaded lazily when the property is
1401 first accessed, using a separate SELECT statement, or identity map
1402 fetch for simple many-to-one references.
1404 * ``immediate`` - items should be loaded as the parents are loaded,
1405 using a separate SELECT statement, or identity map fetch for
1406 simple many-to-one references.
1408 * ``joined`` - items should be loaded "eagerly" in the same query as
1409 that of the parent, using a JOIN or LEFT OUTER JOIN. Whether
1410 the join is "outer" or not is determined by the
1411 :paramref:`_orm.relationship.innerjoin` parameter.
1413 * ``subquery`` - items should be loaded "eagerly" as the parents are
1414 loaded, using one additional SQL statement, which issues a JOIN to
1415 a subquery of the original statement, for each collection
1416 requested.
1418 * ``selectin`` - items should be loaded "eagerly" as the parents
1419 are loaded, using one or more additional SQL statements, which
1420 issues a JOIN to the immediate parent object, specifying primary
1421 key identifiers using an IN clause.
1423 * ``noload`` - no loading should occur at any time. The related
1424 collection will remain empty. The ``noload`` strategy is not
1425 recommended for general use. For a general use "never load"
1426 approach, see :ref:`write_only_relationship`
1428 * ``raise`` - lazy loading is disallowed; accessing
1429 the attribute, if its value were not already loaded via eager
1430 loading, will raise an :exc:`~sqlalchemy.exc.InvalidRequestError`.
1431 This strategy can be used when objects are to be detached from
1432 their attached :class:`.Session` after they are loaded.
1434 * ``raise_on_sql`` - lazy loading that emits SQL is disallowed;
1435 accessing the attribute, if its value were not already loaded via
1436 eager loading, will raise an
1437 :exc:`~sqlalchemy.exc.InvalidRequestError`, **if the lazy load
1438 needs to emit SQL**. If the lazy load can pull the related value
1439 from the identity map or determine that it should be None, the
1440 value is loaded. This strategy can be used when objects will
1441 remain associated with the attached :class:`.Session`, however
1442 additional SELECT statements should be blocked.
1444 * ``write_only`` - the attribute will be configured with a special
1445 "virtual collection" that may receive
1446 :meth:`_orm.WriteOnlyCollection.add` and
1447 :meth:`_orm.WriteOnlyCollection.remove` commands to add or remove
1448 individual objects, but will not under any circumstances load or
1449 iterate the full set of objects from the database directly. Instead,
1450 methods such as :meth:`_orm.WriteOnlyCollection.select`,
1451 :meth:`_orm.WriteOnlyCollection.insert`,
1452 :meth:`_orm.WriteOnlyCollection.update` and
1453 :meth:`_orm.WriteOnlyCollection.delete` are provided which generate SQL
1454 constructs that may be used to load and modify rows in bulk. Used for
1455 large collections that are never appropriate to load at once into
1456 memory.
1458 The ``write_only`` loader style is configured automatically when
1459 the :class:`_orm.WriteOnlyMapped` annotation is provided on the
1460 left hand side within a Declarative mapping. See the section
1461 :ref:`write_only_relationship` for examples.
1463 .. versionadded:: 2.0
1465 .. seealso::
1467 :ref:`write_only_relationship` - in the :ref:`queryguide_toplevel`
1469 * ``dynamic`` - the attribute will return a pre-configured
1470 :class:`_query.Query` object for all read
1471 operations, onto which further filtering operations can be
1472 applied before iterating the results.
1474 The ``dynamic`` loader style is configured automatically when
1475 the :class:`_orm.DynamicMapped` annotation is provided on the
1476 left hand side within a Declarative mapping. See the section
1477 :ref:`dynamic_relationship` for examples.
1479 .. legacy:: The "dynamic" lazy loader strategy is the legacy form of
1480 what is now the "write_only" strategy described in the section
1481 :ref:`write_only_relationship`.
1483 .. seealso::
1485 :ref:`dynamic_relationship` - in the :ref:`queryguide_toplevel`
1487 :ref:`write_only_relationship` - more generally useful approach
1488 for large collections that should not fully load into memory
1490 * True - a synonym for 'select'
1492 * False - a synonym for 'joined'
1494 * None - a synonym for 'noload'
1496 .. seealso::
1498 :ref:`orm_queryguide_relationship_loaders` - Full documentation on
1499 relationship loader configuration in the :ref:`queryguide_toplevel`.
1502 :param load_on_pending=False:
1503 Indicates loading behavior for transient or pending parent objects.
1505 When set to ``True``, causes the lazy-loader to
1506 issue a query for a parent object that is not persistent, meaning it
1507 has never been flushed. This may take effect for a pending object
1508 when autoflush is disabled, or for a transient object that has been
1509 "attached" to a :class:`.Session` but is not part of its pending
1510 collection.
1512 The :paramref:`_orm.relationship.load_on_pending`
1513 flag does not improve
1514 behavior when the ORM is used normally - object references should be
1515 constructed at the object level, not at the foreign key level, so
1516 that they are present in an ordinary way before a flush proceeds.
1517 This flag is not not intended for general use.
1519 .. seealso::
1521 :meth:`.Session.enable_relationship_loading` - this method
1522 establishes "load on pending" behavior for the whole object, and
1523 also allows loading on objects that remain transient or
1524 detached.
1526 :param order_by:
1527 Indicates the ordering that should be applied when loading these
1528 items. :paramref:`_orm.relationship.order_by`
1529 is expected to refer to
1530 one of the :class:`_schema.Column`
1531 objects to which the target class is
1532 mapped, or the attribute itself bound to the target class which
1533 refers to the column.
1535 :paramref:`_orm.relationship.order_by`
1536 may also be passed as a callable
1537 function which is evaluated at mapper initialization time, and may
1538 be passed as a Python-evaluable string when using Declarative.
1540 .. warning:: When passed as a Python-evaluable string, the
1541 argument is interpreted using Python's ``eval()`` function.
1542 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1543 See :ref:`declarative_relationship_eval` for details on
1544 declarative evaluation of :func:`_orm.relationship` arguments.
1546 :param passive_deletes=False:
1547 Indicates loading behavior during delete operations.
1549 A value of True indicates that unloaded child items should not
1550 be loaded during a delete operation on the parent. Normally,
1551 when a parent item is deleted, all child items are loaded so
1552 that they can either be marked as deleted, or have their
1553 foreign key to the parent set to NULL. Marking this flag as
1554 True usually implies an ON DELETE <CASCADE|SET NULL> rule is in
1555 place which will handle updating/deleting child rows on the
1556 database side.
1558 Additionally, setting the flag to the string value 'all' will
1559 disable the "nulling out" of the child foreign keys, when the parent
1560 object is deleted and there is no delete or delete-orphan cascade
1561 enabled. This is typically used when a triggering or error raise
1562 scenario is in place on the database side. Note that the foreign
1563 key attributes on in-session child objects will not be changed after
1564 a flush occurs so this is a very special use-case setting.
1565 Additionally, the "nulling out" will still occur if the child
1566 object is de-associated with the parent.
1568 .. seealso::
1570 :ref:`passive_deletes` - Introductory documentation
1571 and examples.
1573 :param passive_updates=True:
1574 Indicates the persistence behavior to take when a referenced
1575 primary key value changes in place, indicating that the referencing
1576 foreign key columns will also need their value changed.
1578 When True, it is assumed that ``ON UPDATE CASCADE`` is configured on
1579 the foreign key in the database, and that the database will
1580 handle propagation of an UPDATE from a source column to
1581 dependent rows. When False, the SQLAlchemy
1582 :func:`_orm.relationship`
1583 construct will attempt to emit its own UPDATE statements to
1584 modify related targets. However note that SQLAlchemy **cannot**
1585 emit an UPDATE for more than one level of cascade. Also,
1586 setting this flag to False is not compatible in the case where
1587 the database is in fact enforcing referential integrity, unless
1588 those constraints are explicitly "deferred", if the target backend
1589 supports it.
1591 It is highly advised that an application which is employing
1592 mutable primary keys keeps ``passive_updates`` set to True,
1593 and instead uses the referential integrity features of the database
1594 itself in order to handle the change efficiently and fully.
1596 .. seealso::
1598 :ref:`passive_updates` - Introductory documentation and
1599 examples.
1601 :paramref:`.mapper.passive_updates` - a similar flag which
1602 takes effect for joined-table inheritance mappings.
1604 :param post_update:
1605 This indicates that the relationship should be handled by a
1606 second UPDATE statement after an INSERT or before a
1607 DELETE. This flag is used to handle saving bi-directional
1608 dependencies between two individual rows (i.e. each row
1609 references the other), where it would otherwise be impossible to
1610 INSERT or DELETE both rows fully since one row exists before the
1611 other. Use this flag when a particular mapping arrangement will
1612 incur two rows that are dependent on each other, such as a table
1613 that has a one-to-many relationship to a set of child rows, and
1614 also has a column that references a single child row within that
1615 list (i.e. both tables contain a foreign key to each other). If
1616 a flush operation returns an error that a "cyclical
1617 dependency" was detected, this is a cue that you might want to
1618 use :paramref:`_orm.relationship.post_update` to "break" the cycle.
1620 .. seealso::
1622 :ref:`post_update` - Introductory documentation and examples.
1624 :param primaryjoin:
1625 A SQL expression that will be used as the primary
1626 join of the child object against the parent object, or in a
1627 many-to-many relationship the join of the parent object to the
1628 association table. By default, this value is computed based on the
1629 foreign key relationships of the parent and child tables (or
1630 association table).
1632 :paramref:`_orm.relationship.primaryjoin` may also be passed as a
1633 callable function which is evaluated at mapper initialization time,
1634 and may be passed as a Python-evaluable string when using
1635 Declarative.
1637 .. warning:: When passed as a Python-evaluable string, the
1638 argument is interpreted using Python's ``eval()`` function.
1639 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1640 See :ref:`declarative_relationship_eval` for details on
1641 declarative evaluation of :func:`_orm.relationship` arguments.
1643 .. seealso::
1645 :ref:`relationship_primaryjoin`
1647 :param remote_side:
1648 Used for self-referential relationships, indicates the column or
1649 list of columns that form the "remote side" of the relationship.
1651 :paramref:`_orm.relationship.remote_side` may also be passed as a
1652 callable function which is evaluated at mapper initialization time,
1653 and may be passed as a Python-evaluable string when using
1654 Declarative.
1656 .. warning:: When passed as a Python-evaluable string, the
1657 argument is interpreted using Python's ``eval()`` function.
1658 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1659 See :ref:`declarative_relationship_eval` for details on
1660 declarative evaluation of :func:`_orm.relationship` arguments.
1662 .. seealso::
1664 :ref:`self_referential` - in-depth explanation of how
1665 :paramref:`_orm.relationship.remote_side`
1666 is used to configure self-referential relationships.
1668 :func:`.remote` - an annotation function that accomplishes the
1669 same purpose as :paramref:`_orm.relationship.remote_side`,
1670 typically
1671 when a custom :paramref:`_orm.relationship.primaryjoin` condition
1672 is used.
1674 :param query_class:
1675 A :class:`_query.Query`
1676 subclass that will be used internally by the
1677 ``AppenderQuery`` returned by a "dynamic" relationship, that
1678 is, a relationship that specifies ``lazy="dynamic"`` or was
1679 otherwise constructed using the :func:`_orm.dynamic_loader`
1680 function.
1682 .. seealso::
1684 :ref:`dynamic_relationship` - Introduction to "dynamic"
1685 relationship loaders.
1687 :param secondaryjoin:
1688 A SQL expression that will be used as the join of
1689 an association table to the child object. By default, this value is
1690 computed based on the foreign key relationships of the association
1691 and child tables.
1693 :paramref:`_orm.relationship.secondaryjoin` may also be passed as a
1694 callable function which is evaluated at mapper initialization time,
1695 and may be passed as a Python-evaluable string when using
1696 Declarative.
1698 .. warning:: When passed as a Python-evaluable string, the
1699 argument is interpreted using Python's ``eval()`` function.
1700 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1701 See :ref:`declarative_relationship_eval` for details on
1702 declarative evaluation of :func:`_orm.relationship` arguments.
1704 .. seealso::
1706 :ref:`relationship_primaryjoin`
1708 :param single_parent:
1709 When True, installs a validator which will prevent objects
1710 from being associated with more than one parent at a time.
1711 This is used for many-to-one or many-to-many relationships that
1712 should be treated either as one-to-one or one-to-many. Its usage
1713 is optional, except for :func:`_orm.relationship` constructs which
1714 are many-to-one or many-to-many and also
1715 specify the ``delete-orphan`` cascade option. The
1716 :func:`_orm.relationship` construct itself will raise an error
1717 instructing when this option is required.
1719 .. seealso::
1721 :ref:`unitofwork_cascades` - includes detail on when the
1722 :paramref:`_orm.relationship.single_parent`
1723 flag may be appropriate.
1725 :param uselist:
1726 A boolean that indicates if this property should be loaded as a
1727 list or a scalar. In most cases, this value is determined
1728 automatically by :func:`_orm.relationship` at mapper configuration
1729 time. When using explicit :class:`_orm.Mapped` annotations,
1730 :paramref:`_orm.relationship.uselist` may be derived from the
1731 whether or not the annotation within :class:`_orm.Mapped` contains
1732 a collection class.
1733 Otherwise, :paramref:`_orm.relationship.uselist` may be derived from
1734 the type and direction
1735 of the relationship - one to many forms a list, many to one
1736 forms a scalar, many to many is a list. If a scalar is desired
1737 where normally a list would be present, such as a bi-directional
1738 one-to-one relationship, use an appropriate :class:`_orm.Mapped`
1739 annotation or set :paramref:`_orm.relationship.uselist` to False.
1741 The :paramref:`_orm.relationship.uselist`
1742 flag is also available on an
1743 existing :func:`_orm.relationship`
1744 construct as a read-only attribute,
1745 which can be used to determine if this :func:`_orm.relationship`
1746 deals
1747 with collections or scalar attributes::
1749 >>> User.addresses.property.uselist
1750 True
1752 .. seealso::
1754 :ref:`relationships_one_to_one` - Introduction to the "one to
1755 one" relationship pattern, which is typically when an alternate
1756 setting for :paramref:`_orm.relationship.uselist` is involved.
1758 :param viewonly=False:
1759 When set to ``True``, the relationship is used only for loading
1760 objects, and not for any persistence operation. A
1761 :func:`_orm.relationship` which specifies
1762 :paramref:`_orm.relationship.viewonly` can work
1763 with a wider range of SQL operations within the
1764 :paramref:`_orm.relationship.primaryjoin` condition, including
1765 operations that feature the use of a variety of comparison operators
1766 as well as SQL functions such as :func:`_expression.cast`. The
1767 :paramref:`_orm.relationship.viewonly`
1768 flag is also of general use when defining any kind of
1769 :func:`_orm.relationship` that doesn't represent
1770 the full set of related objects, to prevent modifications of the
1771 collection from resulting in persistence operations.
1773 .. seealso::
1775 :ref:`relationship_viewonly_notes` - more details on best practices
1776 when using :paramref:`_orm.relationship.viewonly`.
1778 :param sync_backref:
1779 A boolean that enables the events used to synchronize the in-Python
1780 attributes when this relationship is target of either
1781 :paramref:`_orm.relationship.backref` or
1782 :paramref:`_orm.relationship.back_populates`.
1784 Defaults to ``None``, which indicates that an automatic value should
1785 be selected based on the value of the
1786 :paramref:`_orm.relationship.viewonly` flag. When left at its
1787 default, changes in state will be back-populated only if neither
1788 sides of a relationship is viewonly.
1790 .. versionadded:: 1.3.17
1792 .. versionchanged:: 1.4 - A relationship that specifies
1793 :paramref:`_orm.relationship.viewonly` automatically implies
1794 that :paramref:`_orm.relationship.sync_backref` is ``False``.
1796 .. seealso::
1798 :paramref:`_orm.relationship.viewonly`
1800 :param omit_join:
1801 Allows manual control over the "selectin" automatic join
1802 optimization. Set to ``False`` to disable the "omit join" feature
1803 added in SQLAlchemy 1.3; or leave as ``None`` to leave automatic
1804 optimization in place.
1806 .. note:: This flag may only be set to ``False``. It is not
1807 necessary to set it to ``True`` as the "omit_join" optimization is
1808 automatically detected; if it is not detected, then the
1809 optimization is not supported.
1811 .. versionchanged:: 1.3.11 setting ``omit_join`` to True will now
1812 emit a warning as this was not the intended use of this flag.
1814 .. versionadded:: 1.3
1816 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
1817 specifies if the mapped attribute should be part of the ``__init__()``
1818 method as generated by the dataclass process.
1819 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
1820 specifies if the mapped attribute should be part of the ``__repr__()``
1821 method as generated by the dataclass process.
1822 :param default_factory: Specific to
1823 :ref:`orm_declarative_native_dataclasses`,
1824 specifies a default-value generation function that will take place
1825 as part of the ``__init__()``
1826 method as generated by the dataclass process.
1827 :param compare: Specific to
1828 :ref:`orm_declarative_native_dataclasses`, indicates if this field
1829 should be included in comparison operations when generating the
1830 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
1832 .. versionadded:: 2.0.0b4
1834 :param kw_only: Specific to
1835 :ref:`orm_declarative_native_dataclasses`, indicates if this field
1836 should be marked as keyword-only when generating the ``__init__()``.
1838 :param hash: Specific to
1839 :ref:`orm_declarative_native_dataclasses`, controls if this field
1840 is included when generating the ``__hash__()`` method for the mapped
1841 class.
1843 .. versionadded:: 2.0.36
1844 """
1846 return _RelationshipDeclared(
1847 argument,
1848 secondary=secondary,
1849 uselist=uselist,
1850 collection_class=collection_class,
1851 primaryjoin=primaryjoin,
1852 secondaryjoin=secondaryjoin,
1853 back_populates=back_populates,
1854 order_by=order_by,
1855 backref=backref,
1856 overlaps=overlaps,
1857 post_update=post_update,
1858 cascade=cascade,
1859 viewonly=viewonly,
1860 attribute_options=_AttributeOptions(
1861 init, repr, default, default_factory, compare, kw_only, hash
1862 ),
1863 lazy=lazy,
1864 passive_deletes=passive_deletes,
1865 passive_updates=passive_updates,
1866 active_history=active_history,
1867 enable_typechecks=enable_typechecks,
1868 foreign_keys=foreign_keys,
1869 remote_side=remote_side,
1870 join_depth=join_depth,
1871 comparator_factory=comparator_factory,
1872 single_parent=single_parent,
1873 innerjoin=innerjoin,
1874 distinct_target_key=distinct_target_key,
1875 load_on_pending=load_on_pending,
1876 query_class=query_class,
1877 info=info,
1878 omit_join=omit_join,
1879 sync_backref=sync_backref,
1880 **kw,
1881 )
1884def synonym(
1885 name: str,
1886 *,
1887 map_column: Optional[bool] = None,
1888 descriptor: Optional[Any] = None,
1889 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
1890 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
1891 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
1892 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
1893 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
1894 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
1895 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
1896 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
1897 info: Optional[_InfoType] = None,
1898 doc: Optional[str] = None,
1899) -> Synonym[Any]:
1900 """Denote an attribute name as a synonym to a mapped property,
1901 in that the attribute will mirror the value and expression behavior
1902 of another attribute.
1904 e.g.::
1906 class MyClass(Base):
1907 __tablename__ = "my_table"
1909 id = Column(Integer, primary_key=True)
1910 job_status = Column(String(50))
1912 status = synonym("job_status")
1914 :param name: the name of the existing mapped property. This
1915 can refer to the string name ORM-mapped attribute
1916 configured on the class, including column-bound attributes
1917 and relationships.
1919 :param descriptor: a Python :term:`descriptor` that will be used
1920 as a getter (and potentially a setter) when this attribute is
1921 accessed at the instance level.
1923 :param map_column: **For classical mappings and mappings against
1924 an existing Table object only**. if ``True``, the :func:`.synonym`
1925 construct will locate the :class:`_schema.Column`
1926 object upon the mapped
1927 table that would normally be associated with the attribute name of
1928 this synonym, and produce a new :class:`.ColumnProperty` that instead
1929 maps this :class:`_schema.Column`
1930 to the alternate name given as the "name"
1931 argument of the synonym; in this way, the usual step of redefining
1932 the mapping of the :class:`_schema.Column`
1933 to be under a different name is
1934 unnecessary. This is usually intended to be used when a
1935 :class:`_schema.Column`
1936 is to be replaced with an attribute that also uses a
1937 descriptor, that is, in conjunction with the
1938 :paramref:`.synonym.descriptor` parameter::
1940 my_table = Table(
1941 "my_table",
1942 metadata,
1943 Column("id", Integer, primary_key=True),
1944 Column("job_status", String(50)),
1945 )
1948 class MyClass:
1949 @property
1950 def _job_status_descriptor(self):
1951 return "Status: %s" % self._job_status
1954 mapper(
1955 MyClass,
1956 my_table,
1957 properties={
1958 "job_status": synonym(
1959 "_job_status",
1960 map_column=True,
1961 descriptor=MyClass._job_status_descriptor,
1962 )
1963 },
1964 )
1966 Above, the attribute named ``_job_status`` is automatically
1967 mapped to the ``job_status`` column::
1969 >>> j1 = MyClass()
1970 >>> j1._job_status = "employed"
1971 >>> j1.job_status
1972 Status: employed
1974 When using Declarative, in order to provide a descriptor in
1975 conjunction with a synonym, use the
1976 :func:`sqlalchemy.ext.declarative.synonym_for` helper. However,
1977 note that the :ref:`hybrid properties <mapper_hybrids>` feature
1978 should usually be preferred, particularly when redefining attribute
1979 behavior.
1981 :param info: Optional data dictionary which will be populated into the
1982 :attr:`.InspectionAttr.info` attribute of this object.
1984 :param comparator_factory: A subclass of :class:`.PropComparator`
1985 that will provide custom comparison behavior at the SQL expression
1986 level.
1988 .. note::
1990 For the use case of providing an attribute which redefines both
1991 Python-level and SQL-expression level behavior of an attribute,
1992 please refer to the Hybrid attribute introduced at
1993 :ref:`mapper_hybrids` for a more effective technique.
1995 .. seealso::
1997 :ref:`synonyms` - Overview of synonyms
1999 :func:`.synonym_for` - a helper oriented towards Declarative
2001 :ref:`mapper_hybrids` - The Hybrid Attribute extension provides an
2002 updated approach to augmenting attribute behavior more flexibly
2003 than can be achieved with synonyms.
2005 """
2006 return Synonym(
2007 name,
2008 map_column=map_column,
2009 descriptor=descriptor,
2010 comparator_factory=comparator_factory,
2011 attribute_options=_AttributeOptions(
2012 init, repr, default, default_factory, compare, kw_only, hash
2013 ),
2014 doc=doc,
2015 info=info,
2016 )
2019def create_session(
2020 bind: Optional[_SessionBind] = None, **kwargs: Any
2021) -> Session:
2022 r"""Create a new :class:`.Session`
2023 with no automation enabled by default.
2025 This function is used primarily for testing. The usual
2026 route to :class:`.Session` creation is via its constructor
2027 or the :func:`.sessionmaker` function.
2029 :param bind: optional, a single Connectable to use for all
2030 database access in the created
2031 :class:`~sqlalchemy.orm.session.Session`.
2033 :param \*\*kwargs: optional, passed through to the
2034 :class:`.Session` constructor.
2036 :returns: an :class:`~sqlalchemy.orm.session.Session` instance
2038 The defaults of create_session() are the opposite of that of
2039 :func:`sessionmaker`; ``autoflush`` and ``expire_on_commit`` are
2040 False.
2042 Usage::
2044 >>> from sqlalchemy.orm import create_session
2045 >>> session = create_session()
2047 It is recommended to use :func:`sessionmaker` instead of
2048 create_session().
2050 """
2052 kwargs.setdefault("autoflush", False)
2053 kwargs.setdefault("expire_on_commit", False)
2054 return Session(bind=bind, **kwargs)
2057def _mapper_fn(*arg: Any, **kw: Any) -> NoReturn:
2058 """Placeholder for the now-removed ``mapper()`` function.
2060 Classical mappings should be performed using the
2061 :meth:`_orm.registry.map_imperatively` method.
2063 This symbol remains in SQLAlchemy 2.0 to suit the deprecated use case
2064 of using the ``mapper()`` function as a target for ORM event listeners,
2065 which failed to be marked as deprecated in the 1.4 series.
2067 Global ORM mapper listeners should instead use the :class:`_orm.Mapper`
2068 class as the target.
2070 .. versionchanged:: 2.0 The ``mapper()`` function was removed; the
2071 symbol remains temporarily as a placeholder for the event listening
2072 use case.
2074 """
2075 raise InvalidRequestError(
2076 "The 'sqlalchemy.orm.mapper()' function is removed as of "
2077 "SQLAlchemy 2.0. Use the "
2078 "'sqlalchemy.orm.registry.map_imperatively()` "
2079 "method of the ``sqlalchemy.orm.registry`` class to perform "
2080 "classical mapping."
2081 )
2084def dynamic_loader(
2085 argument: Optional[_RelationshipArgumentType[Any]] = None, **kw: Any
2086) -> RelationshipProperty[Any]:
2087 """Construct a dynamically-loading mapper property.
2089 This is essentially the same as
2090 using the ``lazy='dynamic'`` argument with :func:`relationship`::
2092 dynamic_loader(SomeClass)
2094 # is the same as
2096 relationship(SomeClass, lazy="dynamic")
2098 See the section :ref:`dynamic_relationship` for more details
2099 on dynamic loading.
2101 """
2102 kw["lazy"] = "dynamic"
2103 return relationship(argument, **kw)
2106def backref(name: str, **kwargs: Any) -> ORMBackrefArgument:
2107 """When using the :paramref:`_orm.relationship.backref` parameter,
2108 provides specific parameters to be used when the new
2109 :func:`_orm.relationship` is generated.
2111 E.g.::
2113 "items": relationship(SomeItem, backref=backref("parent", lazy="subquery"))
2115 The :paramref:`_orm.relationship.backref` parameter is generally
2116 considered to be legacy; for modern applications, using
2117 explicit :func:`_orm.relationship` constructs linked together using
2118 the :paramref:`_orm.relationship.back_populates` parameter should be
2119 preferred.
2121 .. seealso::
2123 :ref:`relationships_backref` - background on backrefs
2125 """ # noqa: E501
2127 return (name, kwargs)
2130def deferred(
2131 column: _ORMColumnExprArgument[_T],
2132 *additional_columns: _ORMColumnExprArgument[Any],
2133 group: Optional[str] = None,
2134 raiseload: bool = False,
2135 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
2136 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
2137 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2138 default: Optional[Any] = _NoArg.NO_ARG,
2139 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
2140 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
2141 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
2142 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
2143 active_history: bool = False,
2144 expire_on_flush: bool = True,
2145 info: Optional[_InfoType] = None,
2146 doc: Optional[str] = None,
2147) -> MappedSQLExpression[_T]:
2148 r"""Indicate a column-based mapped attribute that by default will
2149 not load unless accessed.
2151 When using :func:`_orm.mapped_column`, the same functionality as
2152 that of :func:`_orm.deferred` construct is provided by using the
2153 :paramref:`_orm.mapped_column.deferred` parameter.
2155 :param \*columns: columns to be mapped. This is typically a single
2156 :class:`_schema.Column` object,
2157 however a collection is supported in order
2158 to support multiple columns mapped under the same attribute.
2160 :param raiseload: boolean, if True, indicates an exception should be raised
2161 if the load operation is to take place.
2163 .. versionadded:: 1.4
2166 Additional arguments are the same as that of :func:`_orm.column_property`.
2168 .. seealso::
2170 :ref:`orm_queryguide_deferred_imperative`
2172 """
2173 return MappedSQLExpression(
2174 column,
2175 *additional_columns,
2176 attribute_options=_AttributeOptions(
2177 init, repr, default, default_factory, compare, kw_only, hash
2178 ),
2179 group=group,
2180 deferred=True,
2181 raiseload=raiseload,
2182 comparator_factory=comparator_factory,
2183 active_history=active_history,
2184 expire_on_flush=expire_on_flush,
2185 info=info,
2186 doc=doc,
2187 )
2190def query_expression(
2191 default_expr: _ORMColumnExprArgument[_T] = sql.null(),
2192 *,
2193 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2194 compare: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2195 expire_on_flush: bool = True,
2196 info: Optional[_InfoType] = None,
2197 doc: Optional[str] = None,
2198) -> MappedSQLExpression[_T]:
2199 """Indicate an attribute that populates from a query-time SQL expression.
2201 :param default_expr: Optional SQL expression object that will be used in
2202 all cases if not assigned later with :func:`_orm.with_expression`.
2204 .. versionadded:: 1.2
2206 .. seealso::
2208 :ref:`orm_queryguide_with_expression` - background and usage examples
2210 """
2211 prop = MappedSQLExpression(
2212 default_expr,
2213 attribute_options=_AttributeOptions(
2214 False,
2215 repr,
2216 _NoArg.NO_ARG,
2217 _NoArg.NO_ARG,
2218 compare,
2219 _NoArg.NO_ARG,
2220 _NoArg.NO_ARG,
2221 ),
2222 expire_on_flush=expire_on_flush,
2223 info=info,
2224 doc=doc,
2225 _assume_readonly_dc_attributes=True,
2226 )
2228 prop.strategy_key = (("query_expression", True),)
2229 return prop
2232def clear_mappers() -> None:
2233 """Remove all mappers from all classes.
2235 .. versionchanged:: 1.4 This function now locates all
2236 :class:`_orm.registry` objects and calls upon the
2237 :meth:`_orm.registry.dispose` method of each.
2239 This function removes all instrumentation from classes and disposes
2240 of their associated mappers. Once called, the classes are unmapped
2241 and can be later re-mapped with new mappers.
2243 :func:`.clear_mappers` is *not* for normal use, as there is literally no
2244 valid usage for it outside of very specific testing scenarios. Normally,
2245 mappers are permanent structural components of user-defined classes, and
2246 are never discarded independently of their class. If a mapped class
2247 itself is garbage collected, its mapper is automatically disposed of as
2248 well. As such, :func:`.clear_mappers` is only for usage in test suites
2249 that re-use the same classes with different mappings, which is itself an
2250 extremely rare use case - the only such use case is in fact SQLAlchemy's
2251 own test suite, and possibly the test suites of other ORM extension
2252 libraries which intend to test various combinations of mapper construction
2253 upon a fixed set of classes.
2255 """
2257 mapperlib._dispose_registries(mapperlib._all_registries(), False)
2260# I would really like a way to get the Type[] here that shows up
2261# in a different way in typing tools, however there is no current method
2262# that is accepted by mypy (subclass of Type[_O] works in pylance, rejected
2263# by mypy).
2264AliasedType = Annotated[Type[_O], "aliased"]
2267@overload
2268def aliased(
2269 element: Type[_O],
2270 alias: Optional[FromClause] = None,
2271 name: Optional[str] = None,
2272 flat: bool = False,
2273 adapt_on_names: bool = False,
2274) -> AliasedType[_O]: ...
2277@overload
2278def aliased(
2279 element: Union[AliasedClass[_O], Mapper[_O], AliasedInsp[_O]],
2280 alias: Optional[FromClause] = None,
2281 name: Optional[str] = None,
2282 flat: bool = False,
2283 adapt_on_names: bool = False,
2284) -> AliasedClass[_O]: ...
2287@overload
2288def aliased(
2289 element: FromClause,
2290 alias: None = None,
2291 name: Optional[str] = None,
2292 flat: bool = False,
2293 adapt_on_names: bool = False,
2294) -> FromClause: ...
2297def aliased(
2298 element: Union[_EntityType[_O], FromClause],
2299 alias: Optional[FromClause] = None,
2300 name: Optional[str] = None,
2301 flat: bool = False,
2302 adapt_on_names: bool = False,
2303) -> Union[AliasedClass[_O], FromClause, AliasedType[_O]]:
2304 """Produce an alias of the given element, usually an :class:`.AliasedClass`
2305 instance.
2307 E.g.::
2309 my_alias = aliased(MyClass)
2311 stmt = select(MyClass, my_alias).filter(MyClass.id > my_alias.id)
2312 result = session.execute(stmt)
2314 The :func:`.aliased` function is used to create an ad-hoc mapping of a
2315 mapped class to a new selectable. By default, a selectable is generated
2316 from the normally mapped selectable (typically a :class:`_schema.Table`
2317 ) using the
2318 :meth:`_expression.FromClause.alias` method. However, :func:`.aliased`
2319 can also be
2320 used to link the class to a new :func:`_expression.select` statement.
2321 Also, the :func:`.with_polymorphic` function is a variant of
2322 :func:`.aliased` that is intended to specify a so-called "polymorphic
2323 selectable", that corresponds to the union of several joined-inheritance
2324 subclasses at once.
2326 For convenience, the :func:`.aliased` function also accepts plain
2327 :class:`_expression.FromClause` constructs, such as a
2328 :class:`_schema.Table` or
2329 :func:`_expression.select` construct. In those cases, the
2330 :meth:`_expression.FromClause.alias`
2331 method is called on the object and the new
2332 :class:`_expression.Alias` object returned. The returned
2333 :class:`_expression.Alias` is not
2334 ORM-mapped in this case.
2336 .. seealso::
2338 :ref:`tutorial_orm_entity_aliases` - in the :ref:`unified_tutorial`
2340 :ref:`orm_queryguide_orm_aliases` - in the :ref:`queryguide_toplevel`
2342 :param element: element to be aliased. Is normally a mapped class,
2343 but for convenience can also be a :class:`_expression.FromClause`
2344 element.
2346 :param alias: Optional selectable unit to map the element to. This is
2347 usually used to link the object to a subquery, and should be an aliased
2348 select construct as one would produce from the
2349 :meth:`_query.Query.subquery` method or
2350 the :meth:`_expression.Select.subquery` or
2351 :meth:`_expression.Select.alias` methods of the :func:`_expression.select`
2352 construct.
2354 :param name: optional string name to use for the alias, if not specified
2355 by the ``alias`` parameter. The name, among other things, forms the
2356 attribute name that will be accessible via tuples returned by a
2357 :class:`_query.Query` object. Not supported when creating aliases
2358 of :class:`_sql.Join` objects.
2360 :param flat: Boolean, will be passed through to the
2361 :meth:`_expression.FromClause.alias` call so that aliases of
2362 :class:`_expression.Join` objects will alias the individual tables
2363 inside the join, rather than creating a subquery. This is generally
2364 supported by all modern databases with regards to right-nested joins
2365 and generally produces more efficient queries.
2367 When :paramref:`_orm.aliased.flat` is combined with
2368 :paramref:`_orm.aliased.name`, the resulting joins will alias individual
2369 tables using a naming scheme similar to ``<prefix>_<tablename>``. This
2370 naming scheme is for visibility / debugging purposes only and the
2371 specific scheme is subject to change without notice.
2373 .. versionadded:: 2.0.32 added support for combining
2374 :paramref:`_orm.aliased.name` with :paramref:`_orm.aliased.flat`.
2375 Previously, this would raise ``NotImplementedError``.
2377 :param adapt_on_names: if True, more liberal "matching" will be used when
2378 mapping the mapped columns of the ORM entity to those of the
2379 given selectable - a name-based match will be performed if the
2380 given selectable doesn't otherwise have a column that corresponds
2381 to one on the entity. The use case for this is when associating
2382 an entity with some derived selectable such as one that uses
2383 aggregate functions::
2385 class UnitPrice(Base):
2386 __tablename__ = "unit_price"
2387 ...
2388 unit_id = Column(Integer)
2389 price = Column(Numeric)
2392 aggregated_unit_price = (
2393 Session.query(func.sum(UnitPrice.price).label("price"))
2394 .group_by(UnitPrice.unit_id)
2395 .subquery()
2396 )
2398 aggregated_unit_price = aliased(
2399 UnitPrice, alias=aggregated_unit_price, adapt_on_names=True
2400 )
2402 Above, functions on ``aggregated_unit_price`` which refer to
2403 ``.price`` will return the
2404 ``func.sum(UnitPrice.price).label('price')`` column, as it is
2405 matched on the name "price". Ordinarily, the "price" function
2406 wouldn't have any "column correspondence" to the actual
2407 ``UnitPrice.price`` column as it is not a proxy of the original.
2409 """
2410 return AliasedInsp._alias_factory(
2411 element,
2412 alias=alias,
2413 name=name,
2414 flat=flat,
2415 adapt_on_names=adapt_on_names,
2416 )
2419def with_polymorphic(
2420 base: Union[Type[_O], Mapper[_O]],
2421 classes: Union[Literal["*"], Iterable[Type[Any]]],
2422 selectable: Union[Literal[False, None], FromClause] = False,
2423 flat: bool = False,
2424 polymorphic_on: Optional[ColumnElement[Any]] = None,
2425 aliased: bool = False,
2426 innerjoin: bool = False,
2427 adapt_on_names: bool = False,
2428 name: Optional[str] = None,
2429 _use_mapper_path: bool = False,
2430) -> AliasedClass[_O]:
2431 """Produce an :class:`.AliasedClass` construct which specifies
2432 columns for descendant mappers of the given base.
2434 Using this method will ensure that each descendant mapper's
2435 tables are included in the FROM clause, and will allow filter()
2436 criterion to be used against those tables. The resulting
2437 instances will also have those columns already loaded so that
2438 no "post fetch" of those columns will be required.
2440 .. seealso::
2442 :ref:`with_polymorphic` - full discussion of
2443 :func:`_orm.with_polymorphic`.
2445 :param base: Base class to be aliased.
2447 :param classes: a single class or mapper, or list of
2448 class/mappers, which inherit from the base class.
2449 Alternatively, it may also be the string ``'*'``, in which case
2450 all descending mapped classes will be added to the FROM clause.
2452 :param aliased: when True, the selectable will be aliased. For a
2453 JOIN, this means the JOIN will be SELECTed from inside of a subquery
2454 unless the :paramref:`_orm.with_polymorphic.flat` flag is set to
2455 True, which is recommended for simpler use cases.
2457 :param flat: Boolean, will be passed through to the
2458 :meth:`_expression.FromClause.alias` call so that aliases of
2459 :class:`_expression.Join` objects will alias the individual tables
2460 inside the join, rather than creating a subquery. This is generally
2461 supported by all modern databases with regards to right-nested joins
2462 and generally produces more efficient queries. Setting this flag is
2463 recommended as long as the resulting SQL is functional.
2465 :param selectable: a table or subquery that will
2466 be used in place of the generated FROM clause. This argument is
2467 required if any of the desired classes use concrete table
2468 inheritance, since SQLAlchemy currently cannot generate UNIONs
2469 among tables automatically. If used, the ``selectable`` argument
2470 must represent the full set of tables and columns mapped by every
2471 mapped class. Otherwise, the unaccounted mapped columns will
2472 result in their table being appended directly to the FROM clause
2473 which will usually lead to incorrect results.
2475 When left at its default value of ``False``, the polymorphic
2476 selectable assigned to the base mapper is used for selecting rows.
2477 However, it may also be passed as ``None``, which will bypass the
2478 configured polymorphic selectable and instead construct an ad-hoc
2479 selectable for the target classes given; for joined table inheritance
2480 this will be a join that includes all target mappers and their
2481 subclasses.
2483 :param polymorphic_on: a column to be used as the "discriminator"
2484 column for the given selectable. If not given, the polymorphic_on
2485 attribute of the base classes' mapper will be used, if any. This
2486 is useful for mappings that don't have polymorphic loading
2487 behavior by default.
2489 :param innerjoin: if True, an INNER JOIN will be used. This should
2490 only be specified if querying for one specific subtype only
2492 :param adapt_on_names: Passes through the
2493 :paramref:`_orm.aliased.adapt_on_names`
2494 parameter to the aliased object. This may be useful in situations where
2495 the given selectable is not directly related to the existing mapped
2496 selectable.
2498 .. versionadded:: 1.4.33
2500 :param name: Name given to the generated :class:`.AliasedClass`.
2502 .. versionadded:: 2.0.31
2504 """
2505 return AliasedInsp._with_polymorphic_factory(
2506 base,
2507 classes,
2508 selectable=selectable,
2509 flat=flat,
2510 polymorphic_on=polymorphic_on,
2511 adapt_on_names=adapt_on_names,
2512 aliased=aliased,
2513 innerjoin=innerjoin,
2514 name=name,
2515 _use_mapper_path=_use_mapper_path,
2516 )
2519def join(
2520 left: _FromClauseArgument,
2521 right: _FromClauseArgument,
2522 onclause: Optional[_OnClauseArgument] = None,
2523 isouter: bool = False,
2524 full: bool = False,
2525) -> _ORMJoin:
2526 r"""Produce an inner join between left and right clauses.
2528 :func:`_orm.join` is an extension to the core join interface
2529 provided by :func:`_expression.join()`, where the
2530 left and right selectable may be not only core selectable
2531 objects such as :class:`_schema.Table`, but also mapped classes or
2532 :class:`.AliasedClass` instances. The "on" clause can
2533 be a SQL expression or an ORM mapped attribute
2534 referencing a configured :func:`_orm.relationship`.
2536 :func:`_orm.join` is not commonly needed in modern usage,
2537 as its functionality is encapsulated within that of the
2538 :meth:`_sql.Select.join` and :meth:`_query.Query.join`
2539 methods. which feature a
2540 significant amount of automation beyond :func:`_orm.join`
2541 by itself. Explicit use of :func:`_orm.join`
2542 with ORM-enabled SELECT statements involves use of the
2543 :meth:`_sql.Select.select_from` method, as in::
2545 from sqlalchemy.orm import join
2547 stmt = (
2548 select(User)
2549 .select_from(join(User, Address, User.addresses))
2550 .filter(Address.email_address == "foo@bar.com")
2551 )
2553 In modern SQLAlchemy the above join can be written more
2554 succinctly as::
2556 stmt = (
2557 select(User)
2558 .join(User.addresses)
2559 .filter(Address.email_address == "foo@bar.com")
2560 )
2562 .. warning:: using :func:`_orm.join` directly may not work properly
2563 with modern ORM options such as :func:`_orm.with_loader_criteria`.
2564 It is strongly recommended to use the idiomatic join patterns
2565 provided by methods such as :meth:`.Select.join` and
2566 :meth:`.Select.join_from` when creating ORM joins.
2568 .. seealso::
2570 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel` for
2571 background on idiomatic ORM join patterns
2573 """
2574 return _ORMJoin(left, right, onclause, isouter, full)
2577def outerjoin(
2578 left: _FromClauseArgument,
2579 right: _FromClauseArgument,
2580 onclause: Optional[_OnClauseArgument] = None,
2581 full: bool = False,
2582) -> _ORMJoin:
2583 """Produce a left outer join between left and right clauses.
2585 This is the "outer join" version of the :func:`_orm.join` function,
2586 featuring the same behavior except that an OUTER JOIN is generated.
2587 See that function's documentation for other usage details.
2589 """
2590 return _ORMJoin(left, right, onclause, True, full)