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-2026 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 Annotated
12from typing import Any
13from typing import Callable
14from typing import Collection
15from typing import Iterable
16from typing import Literal
17from typing import Mapping
18from typing import NoReturn
19from typing import Optional
20from typing import overload
21from typing import Type
22from typing import TYPE_CHECKING
23from typing import Union
25from . import mapperlib as mapperlib
26from ._typing import _O
27from .descriptor_props import Composite
28from .descriptor_props import Synonym
29from .interfaces import _AttributeOptions
30from .properties import MappedColumn
31from .properties import MappedSQLExpression
32from .properties import UnmappedColumn
33from .query import AliasOption
34from .relationships import _RelationshipArgumentType
35from .relationships import _RelationshipBackPopulatesArgument
36from .relationships import _RelationshipDeclared
37from .relationships import _RelationshipSecondaryArgument
38from .relationships import RelationshipProperty
39from .session import Session
40from .util import _ORMJoin
41from .util import AliasedClass
42from .util import AliasedInsp
43from .util import LoaderCriteriaOption
44from .. import sql
45from .. import util
46from ..exc import InvalidRequestError
47from ..sql._typing import _no_kw
48from ..sql.base import _NoArg
49from ..sql.base import SchemaEventTarget
50from ..sql.schema import _InsertSentinelColumnDefault
51from ..sql.schema import SchemaConst
52from ..sql.selectable import FromClause
54if TYPE_CHECKING:
55 from ._typing import _EntityType
56 from ._typing import _ORMColumnExprArgument
57 from .descriptor_props import _CC
58 from .descriptor_props import _CompositeAttrType
59 from .interfaces import PropComparator
60 from .mapper import Mapper
61 from .query import Query
62 from .relationships import _LazyLoadArgumentType
63 from .relationships import _ORMColCollectionArgument
64 from .relationships import _ORMOrderByArgument
65 from .relationships import _RelationshipJoinConditionArgument
66 from .relationships import ORMBackrefArgument
67 from .session import _SessionBind
68 from ..sql._typing import _AutoIncrementType
69 from ..sql._typing import _ColumnExpressionArgument
70 from ..sql._typing import _FromClauseArgument
71 from ..sql._typing import _InfoType
72 from ..sql._typing import _OnClauseArgument
73 from ..sql._typing import _TypeEngineArgument
74 from ..sql.elements import ColumnElement
75 from ..sql.schema import _ServerDefaultArgument
76 from ..sql.schema import _ServerOnUpdateArgument
77 from ..sql.selectable import Alias
78 from ..sql.selectable import Subquery
81_T = typing.TypeVar("_T")
84@util.deprecated(
85 "1.4",
86 "The :class:`.AliasOption` object is not necessary "
87 "for entities to be matched up to a query that is established "
88 "via :meth:`.Query.from_statement` and now does nothing.",
89 enable_warnings=False, # AliasOption itself warns
90)
91def contains_alias(alias: Union[Alias, Subquery]) -> AliasOption:
92 r"""Return a :class:`.MapperOption` that will indicate to the
93 :class:`_query.Query`
94 that the main table has been aliased.
96 """
97 return AliasOption(alias)
100def mapped_column(
101 __name_pos: Optional[
102 Union[str, _TypeEngineArgument[Any], SchemaEventTarget]
103 ] = None,
104 __type_pos: Optional[
105 Union[_TypeEngineArgument[Any], SchemaEventTarget]
106 ] = None,
107 /,
108 *args: SchemaEventTarget,
109 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
110 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
111 default: Optional[Any] = _NoArg.NO_ARG,
112 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
113 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
114 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
115 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
116 nullable: Optional[
117 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
118 ] = SchemaConst.NULL_UNSPECIFIED,
119 primary_key: Optional[bool] = False,
120 deferred: Union[_NoArg, bool] = _NoArg.NO_ARG,
121 deferred_group: Optional[str] = None,
122 deferred_raiseload: Optional[bool] = None,
123 use_existing_column: bool = False,
124 name: Optional[str] = None,
125 type_: Optional[_TypeEngineArgument[Any]] = None,
126 autoincrement: _AutoIncrementType = "auto",
127 doc: Optional[str] = None,
128 key: Optional[str] = None,
129 index: Optional[bool] = None,
130 unique: Optional[bool] = None,
131 info: Optional[_InfoType] = None,
132 onupdate: Optional[Any] = None,
133 insert_default: Optional[Any] = _NoArg.NO_ARG,
134 server_default: Optional[_ServerDefaultArgument] = None,
135 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
136 active_history: bool = False,
137 quote: Optional[bool] = None,
138 system: bool = False,
139 comment: Optional[str] = None,
140 sort_order: Union[_NoArg, int] = _NoArg.NO_ARG,
141 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
142 **kw: Any,
143) -> MappedColumn[Any]:
144 r"""declare a new ORM-mapped :class:`_schema.Column` construct
145 for use within :ref:`Declarative Table <orm_declarative_table>`
146 configuration.
148 The :func:`_orm.mapped_column` function provides an ORM-aware and
149 Python-typing-compatible construct which is used with
150 :ref:`declarative <orm_declarative_mapping>` mappings to indicate an
151 attribute that's mapped to a Core :class:`_schema.Column` object. It
152 provides the equivalent feature as mapping an attribute to a
153 :class:`_schema.Column` object directly when using Declarative,
154 specifically when using :ref:`Declarative Table <orm_declarative_table>`
155 configuration.
157 .. versionadded:: 2.0
159 :func:`_orm.mapped_column` is normally used with explicit typing along with
160 the :class:`_orm.Mapped` annotation type, where it can derive the SQL
161 type and nullability for the column based on what's present within the
162 :class:`_orm.Mapped` annotation. It also may be used without annotations
163 as a drop-in replacement for how :class:`_schema.Column` is used in
164 Declarative mappings in SQLAlchemy 1.x style.
166 For usage examples of :func:`_orm.mapped_column`, see the documentation
167 at :ref:`orm_declarative_table`.
169 .. seealso::
171 :ref:`orm_declarative_table` - complete documentation
173 :ref:`whatsnew_20_orm_declarative_typing` - migration notes for
174 Declarative mappings using 1.x style mappings
176 :param __name: String name to give to the :class:`_schema.Column`. This
177 is an optional, positional only argument that if present must be the
178 first positional argument passed. If omitted, the attribute name to
179 which the :func:`_orm.mapped_column` is mapped will be used as the SQL
180 column name.
181 :param __type: :class:`_types.TypeEngine` type or instance which will
182 indicate the datatype to be associated with the :class:`_schema.Column`.
183 This is an optional, positional-only argument that if present must
184 immediately follow the ``__name`` parameter if present also, or otherwise
185 be the first positional parameter. If omitted, the ultimate type for
186 the column may be derived either from the annotated type, or if a
187 :class:`_schema.ForeignKey` is present, from the datatype of the
188 referenced column.
189 :param \*args: Additional positional arguments include constructs such
190 as :class:`_schema.ForeignKey`, :class:`_schema.CheckConstraint`,
191 and :class:`_schema.Identity`, which are passed through to the constructed
192 :class:`_schema.Column`.
193 :param nullable: Optional bool, whether the column should be "NULL" or
194 "NOT NULL". If omitted, the nullability is derived from the type
195 annotation based on whether or not ``typing.Optional`` (or its equivalent)
196 is present. ``nullable`` defaults to ``True`` otherwise for non-primary
197 key columns, and ``False`` for primary key columns.
198 :param primary_key: optional bool, indicates the :class:`_schema.Column`
199 would be part of the table's primary key or not.
200 :param deferred: Optional bool - this keyword argument is consumed by the
201 ORM declarative process, and is not part of the :class:`_schema.Column`
202 itself; instead, it indicates that this column should be "deferred" for
203 loading as though mapped by :func:`_orm.deferred`.
205 .. seealso::
207 :ref:`orm_queryguide_deferred_declarative`
209 :param deferred_group: Implies :paramref:`_orm.mapped_column.deferred`
210 to ``True``, and set the :paramref:`_orm.deferred.group` parameter.
212 .. seealso::
214 :ref:`orm_queryguide_deferred_group`
216 :param deferred_raiseload: Implies :paramref:`_orm.mapped_column.deferred`
217 to ``True``, and set the :paramref:`_orm.deferred.raiseload` parameter.
219 .. seealso::
221 :ref:`orm_queryguide_deferred_raiseload`
223 :param use_existing_column: if True, will attempt to locate the given
224 column name on an inherited superclass (typically single inheriting
225 superclass), and if present, will not produce a new column, mapping
226 to the superclass column as though it were omitted from this class.
227 This is used for mixins that add new columns to an inherited superclass.
229 .. seealso::
231 :ref:`orm_inheritance_column_conflicts`
233 .. versionadded:: 2.0.0b4
235 :param default: Passed directly to the
236 :paramref:`_schema.Column.default` parameter if the
237 :paramref:`_orm.mapped_column.insert_default` parameter is not present.
238 Additionally, when used with :ref:`orm_declarative_native_dataclasses`,
239 indicates a default Python value that should be applied to the keyword
240 constructor within the generated ``__init__()`` method.
242 Note that in the case of dataclass generation when
243 :paramref:`_orm.mapped_column.insert_default` is not present, this means
244 the :paramref:`_orm.mapped_column.default` value is used in **two**
245 places, both the ``__init__()`` method as well as the
246 :paramref:`_schema.Column.default` parameter. While this behavior may
247 change in a future release, for the moment this tends to "work out"; a
248 default of ``None`` will mean that the :class:`_schema.Column` gets no
249 default generator, whereas a default that refers to a non-``None`` Python
250 or SQL expression value will be assigned up front on the object when
251 ``__init__()`` is called, which is the same value that the Core
252 :class:`_sql.Insert` construct would use in any case, leading to the same
253 end result.
255 .. note:: When using Core level column defaults that are callables to
256 be interpreted by the underlying :class:`_schema.Column` in conjunction
257 with :ref:`ORM-mapped dataclasses
258 <orm_declarative_native_dataclasses>`, especially those that are
259 :ref:`context-aware default functions <context_default_functions>`,
260 **the** :paramref:`_orm.mapped_column.insert_default` **parameter must
261 be used instead**. This is necessary to disambiguate the callable from
262 being interpreted as a dataclass level default.
264 .. seealso::
266 :ref:`defaults_default_factory_insert_default`
268 :paramref:`_orm.mapped_column.insert_default`
270 :paramref:`_orm.mapped_column.default_factory`
272 :param insert_default: Passed directly to the
273 :paramref:`_schema.Column.default` parameter; will supersede the value
274 of :paramref:`_orm.mapped_column.default` when present, however
275 :paramref:`_orm.mapped_column.default` will always apply to the
276 constructor default for a dataclasses mapping.
278 .. seealso::
280 :ref:`defaults_default_factory_insert_default`
282 :paramref:`_orm.mapped_column.default`
284 :paramref:`_orm.mapped_column.default_factory`
286 :param sort_order: An integer that indicates how this mapped column
287 should be sorted compared to the others when the ORM is creating a
288 :class:`_schema.Table`. Among mapped columns that have the same
289 value the default ordering is used, placing first the mapped columns
290 defined in the main class, then the ones in the super classes.
291 Defaults to 0. The sort is ascending.
293 .. versionadded:: 2.0.4
295 :param active_history=False:
297 When ``True``, indicates that the "previous" value for a
298 scalar attribute should be loaded when replaced, if not
299 already loaded. Normally, history tracking logic for
300 simple non-primary-key scalar values only needs to be
301 aware of the "new" value in order to perform a flush. This
302 flag is available for applications that make use of
303 :func:`.attributes.get_history` or :meth:`.Session.is_modified`
304 which also need to know the "previous" value of the attribute.
306 .. versionadded:: 2.0.10
309 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
310 specifies if the mapped attribute should be part of the ``__init__()``
311 method as generated by the dataclass process.
312 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
313 specifies if the mapped attribute should be part of the ``__repr__()``
314 method as generated by the dataclass process.
315 :param default_factory: Specific to
316 :ref:`orm_declarative_native_dataclasses`,
317 specifies a default-value generation function that will take place
318 as part of the ``__init__()``
319 method as generated by the dataclass process.
321 .. seealso::
323 :ref:`defaults_default_factory_insert_default`
325 :paramref:`_orm.mapped_column.default`
327 :paramref:`_orm.mapped_column.insert_default`
329 :param compare: Specific to
330 :ref:`orm_declarative_native_dataclasses`, indicates if this field
331 should be included in comparison operations when generating the
332 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
334 .. versionadded:: 2.0.0b4
336 :param kw_only: Specific to
337 :ref:`orm_declarative_native_dataclasses`, indicates if this field
338 should be marked as keyword-only when generating the ``__init__()``.
340 :param hash: Specific to
341 :ref:`orm_declarative_native_dataclasses`, controls if this field
342 is included when generating the ``__hash__()`` method for the mapped
343 class.
345 .. versionadded:: 2.0.36
347 :param dataclass_metadata: Specific to
348 :ref:`orm_declarative_native_dataclasses`, supplies metadata
349 to be attached to the generated dataclass field.
351 .. versionadded:: 2.0.42
353 :param \**kw: All remaining keyword arguments are passed through to the
354 constructor for the :class:`_schema.Column`.
356 """
358 return MappedColumn(
359 __name_pos,
360 __type_pos,
361 *args,
362 name=name,
363 type_=type_,
364 autoincrement=autoincrement,
365 insert_default=insert_default,
366 attribute_options=_AttributeOptions(
367 init,
368 repr,
369 default,
370 default_factory,
371 compare,
372 kw_only,
373 hash,
374 dataclass_metadata,
375 ),
376 doc=doc,
377 key=key,
378 index=index,
379 unique=unique,
380 info=info,
381 active_history=active_history,
382 nullable=nullable,
383 onupdate=onupdate,
384 primary_key=primary_key,
385 server_default=server_default,
386 server_onupdate=server_onupdate,
387 use_existing_column=use_existing_column,
388 quote=quote,
389 comment=comment,
390 system=system,
391 deferred=deferred,
392 deferred_group=deferred_group,
393 deferred_raiseload=deferred_raiseload,
394 sort_order=sort_order,
395 **kw,
396 )
399def unmapped_column(
400 __name_pos: Optional[
401 Union[str, _TypeEngineArgument[Any], SchemaEventTarget]
402 ] = None,
403 __type_pos: Optional[
404 Union[_TypeEngineArgument[Any], SchemaEventTarget]
405 ] = None,
406 /,
407 *args: SchemaEventTarget,
408 init: Literal[False] = False,
409 nullable: Optional[
410 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
411 ] = SchemaConst.NULL_UNSPECIFIED,
412 primary_key: Optional[bool] = False,
413 autoincrement: _AutoIncrementType = "auto",
414 use_existing_column: bool = False,
415 name: Optional[str] = None,
416 type_: Optional[_TypeEngineArgument[Any]] = None,
417 index: Optional[bool] = None,
418 unique: Optional[bool] = None,
419 info: Optional[_InfoType] = None,
420 default: Optional[Any] = _NoArg.NO_ARG,
421 insert_default: Optional[Any] = _NoArg.NO_ARG,
422 onupdate: Optional[Any] = None,
423 server_default: Optional[_ServerDefaultArgument] = None,
424 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
425 quote: Optional[bool] = None,
426 system: bool = False,
427 comment: Optional[str] = None,
428 sort_order: Union[_NoArg, int] = _NoArg.NO_ARG,
429 **kw: Any,
430) -> UnmappedColumn[Any]:
431 r"""declare a :class:`_schema.Column` that is part of the
432 :class:`_schema.Table` for a :ref:`Declarative Table
433 <orm_declarative_table>` mapping, but which is not mapped by the ORM.
435 :func:`_orm.unmapped_column` is used with the :class:`_orm.Unmapped`
436 annotation::
438 class User(Base):
439 __tablename__ = "user"
441 id: Mapped[int] = mapped_column(primary_key=True)
442 name: Mapped[str]
444 legacy_name: Unmapped[str | None] = unmapped_column()
446 The resulting :class:`_schema.Column` is added to the
447 :class:`_schema.Table` in the same way as one produced by
448 :func:`_orm.mapped_column`, deriving its type and nullability from the
449 annotation, so that the column is present in DDL as well as for schema
450 comparison tools such as Alembic autogenerate. However, the column is
451 excluded from the mapping, the attribute is removed from the class, and
452 when using :ref:`orm_declarative_native_dataclasses` it is not part of
453 the dataclass.
455 A bare ``Unmapped[]`` annotation with no right-hand side is equivalent
456 to ``unmapped_column()`` with no arguments; however, see the note at
457 :class:`_orm.Unmapped` regarding typing tools and dataclasses.
459 As the attribute is not part of the Python model, ORM and dataclass
460 oriented arguments of :func:`_orm.mapped_column`, such as
461 ``default_factory`` and ``deferred``, are not part of
462 :func:`_orm.unmapped_column`.
464 .. versionadded:: 2.1.5
466 .. seealso::
468 :ref:`orm_declarative_unmapped_column`
470 :param __name: String name to give to the :class:`_schema.Column`;
471 defaults to the attribute name.
472 :param __type: :class:`_types.TypeEngine` type or instance for the
473 :class:`_schema.Column`; if omitted, it is derived from the
474 annotation, or from a :class:`_schema.ForeignKey` if present.
475 :param \*args: Additional positional arguments such as
476 :class:`_schema.ForeignKey` and :class:`_schema.CheckConstraint`,
477 passed through to the :class:`_schema.Column`.
478 :param init: Always ``False``; this parameter is present so that
479 :pep:`681` typing tools omit the attribute from the ``__init__()``
480 method of :ref:`ORM mapped dataclasses
481 <orm_declarative_native_dataclasses>`.
482 :param nullable: whether the column is "NULL" or "NOT NULL"; if omitted,
483 derived from the annotation as with :func:`_orm.mapped_column`.
484 :param primary_key: indicates the :class:`_schema.Column` is part of the
485 table's primary key. As the mapper requires that its primary key
486 columns be mapped, the columns that the mapper should use for the
487 identity of the class must then be indicated using the
488 :paramref:`_orm.Mapper.primary_key` parameter; see
489 :ref:`orm_declarative_unmapped_column` for an example.
490 :param autoincrement: Passed directly to the
491 :paramref:`_schema.Column.autoincrement` parameter.
492 :param use_existing_column: see
493 :paramref:`_orm.mapped_column.use_existing_column`.
494 :param default: Passed directly to the :paramref:`_schema.Column.default`
495 parameter. As the attribute is not part of a dataclass, this is
496 strictly a Core column default.
497 :param insert_default: synonym for
498 :paramref:`_orm.unmapped_column.default`.
499 :param sort_order: see :paramref:`_orm.mapped_column.sort_order`.
500 :param \**kw: All remaining keyword arguments are passed through to the
501 constructor for the :class:`_schema.Column`, including dialect-specific
502 arguments.
504 """
506 return UnmappedColumn(
507 __name_pos,
508 __type_pos,
509 *args,
510 name=name,
511 type_=type_,
512 default=default,
513 insert_default=insert_default,
514 index=index,
515 unique=unique,
516 info=info,
517 nullable=nullable,
518 primary_key=primary_key,
519 autoincrement=autoincrement,
520 onupdate=onupdate,
521 server_default=server_default,
522 server_onupdate=server_onupdate,
523 use_existing_column=use_existing_column,
524 quote=quote,
525 comment=comment,
526 system=system,
527 sort_order=sort_order,
528 **kw,
529 )
532def orm_insert_sentinel(
533 name: Optional[str] = None,
534 type_: Optional[_TypeEngineArgument[Any]] = None,
535 *,
536 default: Optional[Any] = None,
537 omit_from_statements: bool = True,
538) -> MappedColumn[Any]:
539 """Provides a surrogate :func:`_orm.mapped_column` that generates
540 a so-called :term:`sentinel` column, allowing efficient bulk
541 inserts with deterministic RETURNING sorting for tables that don't
542 otherwise have qualifying primary key configurations.
544 Use of :func:`_orm.orm_insert_sentinel` is analogous to the use of the
545 :func:`_schema.insert_sentinel` construct within a Core
546 :class:`_schema.Table` construct.
548 Guidelines for adding this construct to a Declarative mapped class
549 are the same as that of the :func:`_schema.insert_sentinel` construct;
550 the database table itself also needs to have a column with this name
551 present.
553 For background on how this object is used, see the section
554 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
555 section :ref:`engine_insertmanyvalues`.
557 .. seealso::
559 :func:`_schema.insert_sentinel`
561 :ref:`engine_insertmanyvalues`
563 :ref:`engine_insertmanyvalues_sentinel_columns`
566 .. versionadded:: 2.0.10
568 """
570 return mapped_column(
571 name=name,
572 default=(
573 default if default is not None else _InsertSentinelColumnDefault()
574 ),
575 _omit_from_statements=omit_from_statements,
576 insert_sentinel=True,
577 use_existing_column=True,
578 nullable=True,
579 )
582@util.deprecated_params(
583 **{
584 arg: (
585 "2.0",
586 f"The :paramref:`_orm.column_property.{arg}` parameter is "
587 "deprecated for :func:`_orm.column_property`. This parameter "
588 "applies to a writeable-attribute in a Declarative Dataclasses "
589 "configuration only, and :func:`_orm.column_property` is treated "
590 "as a read-only attribute in this context.",
591 )
592 for arg in ("init", "kw_only", "default", "default_factory")
593 }
594)
595def column_property(
596 column: _ORMColumnExprArgument[_T],
597 *additional_columns: _ORMColumnExprArgument[Any],
598 group: Optional[str] = None,
599 deferred: bool = False,
600 raiseload: bool = False,
601 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
602 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
603 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
604 default: Optional[Any] = _NoArg.NO_ARG,
605 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
606 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
607 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
608 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
609 active_history: bool = False,
610 expire_on_flush: bool = True,
611 info: Optional[_InfoType] = None,
612 doc: Optional[str] = None,
613 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
614) -> MappedSQLExpression[_T]:
615 r"""Provide a column-level property for use with a mapping.
617 With Declarative mappings, :func:`_orm.column_property` is used to
618 map read-only SQL expressions to a mapped class.
620 When using Imperative mappings, :func:`_orm.column_property` also
621 takes on the role of mapping table columns with additional features.
622 When using fully Declarative mappings, the :func:`_orm.mapped_column`
623 construct should be used for this purpose.
625 With Declarative Dataclass mappings, :func:`_orm.column_property`
626 is considered to be **read only**, and will not be included in the
627 Dataclass ``__init__()`` constructor.
629 The :func:`_orm.column_property` function returns an instance of
630 :class:`.ColumnProperty`.
632 .. seealso::
634 :ref:`mapper_column_property_sql_expressions` - general use of
635 :func:`_orm.column_property` to map SQL expressions
637 :ref:`orm_imperative_table_column_options` - usage of
638 :func:`_orm.column_property` with Imperative Table mappings to apply
639 additional options to a plain :class:`_schema.Column` object
641 :param \*cols:
642 list of Column objects to be mapped.
644 :param active_history=False:
646 Used only for Imperative Table mappings, or legacy-style Declarative
647 mappings (i.e. which have not been upgraded to
648 :func:`_orm.mapped_column`), for column-based attributes that are
649 expected to be writeable; use :func:`_orm.mapped_column` with
650 :paramref:`_orm.mapped_column.active_history` for Declarative mappings.
651 See that parameter for functional details.
653 :param comparator_factory: a class which extends
654 :class:`.ColumnProperty.Comparator` which provides custom SQL
655 clause generation for comparison operations.
657 :param group:
658 a group name for this property when marked as deferred.
660 :param deferred:
661 when True, the column property is "deferred", meaning that
662 it does not load immediately, and is instead loaded when the
663 attribute is first accessed on an instance. See also
664 :func:`~sqlalchemy.orm.deferred`.
666 :param doc:
667 optional string that will be applied as the doc on the
668 class-bound descriptor.
670 :param expire_on_flush=True:
671 Disable expiry on flush. A column_property() which refers
672 to a SQL expression (and not a single table-bound column)
673 is considered to be a "read only" property; populating it
674 has no effect on the state of data, and it can only return
675 database state. For this reason a column_property()'s value
676 is expired whenever the parent object is involved in a
677 flush, that is, has any kind of "dirty" state within a flush.
678 Setting this parameter to ``False`` will have the effect of
679 leaving any existing value present after the flush proceeds.
680 Note that the :class:`.Session` with default expiration
681 settings still expires
682 all attributes after a :meth:`.Session.commit` call, however.
684 :param info: Optional data dictionary which will be populated into the
685 :attr:`.MapperProperty.info` attribute of this object.
687 :param raiseload: if True, indicates the column should raise an error
688 when undeferred, rather than loading the value. This can be
689 altered at query time by using the :func:`.deferred` option with
690 raiseload=False.
692 .. versionadded:: 1.4
694 .. seealso::
696 :ref:`orm_queryguide_deferred_raiseload`
698 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
699 specifies if the mapped attribute should be part of the ``__init__()``
700 method as generated by the dataclass process.
701 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
702 specifies if the mapped attribute should be part of the ``__repr__()``
703 method as generated by the dataclass process.
704 :param default_factory: Specific to
705 :ref:`orm_declarative_native_dataclasses`,
706 specifies a default-value generation function that will take place
707 as part of the ``__init__()``
708 method as generated by the dataclass process.
710 .. seealso::
712 :ref:`defaults_default_factory_insert_default`
714 :paramref:`_orm.mapped_column.default`
716 :paramref:`_orm.mapped_column.insert_default`
718 :param compare: Specific to
719 :ref:`orm_declarative_native_dataclasses`, indicates if this field
720 should be included in comparison operations when generating the
721 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
723 .. versionadded:: 2.0.0b4
725 :param kw_only: Specific to
726 :ref:`orm_declarative_native_dataclasses`, indicates if this field
727 should be marked as keyword-only when generating the ``__init__()``.
729 :param hash: Specific to
730 :ref:`orm_declarative_native_dataclasses`, controls if this field
731 is included when generating the ``__hash__()`` method for the mapped
732 class.
734 .. versionadded:: 2.0.36
736 :param dataclass_metadata: Specific to
737 :ref:`orm_declarative_native_dataclasses`, supplies metadata
738 to be attached to the generated dataclass field.
740 .. versionadded:: 2.0.42
742 """
743 return MappedSQLExpression(
744 column,
745 *additional_columns,
746 attribute_options=_AttributeOptions(
747 False if init is _NoArg.NO_ARG else init,
748 repr,
749 default,
750 default_factory,
751 compare,
752 kw_only,
753 hash,
754 dataclass_metadata,
755 ),
756 group=group,
757 deferred=deferred,
758 raiseload=raiseload,
759 comparator_factory=comparator_factory,
760 active_history=active_history,
761 expire_on_flush=expire_on_flush,
762 info=info,
763 doc=doc,
764 _assume_readonly_dc_attributes=True,
765 )
768@overload
769def composite(
770 _class_or_attr: _CompositeAttrType[Any],
771 /,
772 *attrs: _CompositeAttrType[Any],
773 group: Optional[str] = None,
774 deferred: bool = False,
775 raiseload: bool = False,
776 return_none_on: Union[_NoArg, None, Callable[..., bool]] = _NoArg.NO_ARG,
777 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
778 active_history: bool = False,
779 column_template: Optional[str] = None,
780 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
781 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
782 default: Optional[Any] = _NoArg.NO_ARG,
783 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
784 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
785 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
786 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
787 info: Optional[_InfoType] = None,
788 doc: Optional[str] = None,
789 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
790 **__kw: Any,
791) -> Composite[Any]: ...
794@overload
795def composite(
796 _class_or_attr: Type[_CC],
797 /,
798 *attrs: _CompositeAttrType[Any],
799 group: Optional[str] = None,
800 deferred: bool = False,
801 raiseload: bool = False,
802 return_none_on: Union[_NoArg, None, Callable[..., bool]] = _NoArg.NO_ARG,
803 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
804 active_history: bool = False,
805 column_template: Optional[str] = None,
806 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
807 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
808 default: Optional[Any] = _NoArg.NO_ARG,
809 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
810 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
811 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
812 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
813 info: Optional[_InfoType] = None,
814 doc: Optional[str] = None,
815 **__kw: Any,
816) -> Composite[_CC]: ...
819@overload
820def composite(
821 _class_or_attr: Callable[..., _CC],
822 /,
823 *attrs: _CompositeAttrType[Any],
824 group: Optional[str] = None,
825 deferred: bool = False,
826 raiseload: bool = False,
827 return_none_on: Union[_NoArg, None, Callable[..., bool]] = _NoArg.NO_ARG,
828 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
829 active_history: bool = False,
830 column_template: Optional[str] = None,
831 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
832 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
833 default: Optional[Any] = _NoArg.NO_ARG,
834 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
835 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
836 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
837 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
838 info: Optional[_InfoType] = None,
839 doc: Optional[str] = None,
840 **__kw: Any,
841) -> Composite[_CC]: ...
844def composite(
845 _class_or_attr: Union[
846 None, Type[_CC], Callable[..., _CC], _CompositeAttrType[Any]
847 ] = None,
848 /,
849 *attrs: _CompositeAttrType[Any],
850 group: Optional[str] = None,
851 deferred: bool = False,
852 raiseload: bool = False,
853 return_none_on: Union[_NoArg, None, Callable[..., bool]] = _NoArg.NO_ARG,
854 comparator_factory: Optional[Type[Composite.Comparator[_T]]] = None,
855 active_history: bool = False,
856 column_template: Optional[str] = None,
857 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
858 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
859 default: Optional[Any] = _NoArg.NO_ARG,
860 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
861 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
862 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
863 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
864 info: Optional[_InfoType] = None,
865 doc: Optional[str] = None,
866 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
867 **__kw: Any,
868) -> Composite[Any]:
869 r"""Return a composite column-based property for use with a Mapper.
871 See the mapping documentation section :ref:`mapper_composite` for a
872 full usage example.
874 The :class:`.MapperProperty` returned by :func:`.composite`
875 is the :class:`.Composite`.
877 :param class\_:
878 The "composite type" class, or any classmethod or callable which
879 will produce a new instance of the composite object given the
880 column values in order.
882 :param \*attrs:
883 List of elements to be mapped, which may include:
885 * :class:`_schema.Column` objects
886 * :func:`_orm.mapped_column` constructs
887 * string names of other attributes on the mapped class, which may be
888 any other SQL or object-mapped attribute. This can for
889 example allow a composite that refers to a many-to-one relationship
891 :param active_history=False:
892 When ``True``, indicates that the "previous" value for a
893 scalar attribute should be loaded when replaced, if not
894 already loaded. See the same flag on :func:`.column_property`.
896 :param return_none_on=None: A callable that will be evaluated when the
897 composite object is to be constructed, which upon returning the boolean
898 value ``True`` will instead bypass the construction and cause the
899 resulting value to be None. This typically may be assigned a lambda
900 that will evaluate to True when all the columns within the composite
901 are themselves None, e.g.::
903 composite(
904 MyComposite, return_none_on=lambda *cols: all(x is None for x in cols)
905 )
907 The above lambda for :paramref:`.composite.return_none_on` is used
908 automatically when using ORM Annotated Declarative along with an optional
909 value within the :class:`.Mapped` annotation.
911 .. versionadded:: 2.1
913 :param group:
914 A group name for this property when marked as deferred.
916 :param deferred:
917 When True, the column property is "deferred", meaning that it does
918 not load immediately, and is instead loaded when the attribute is
919 first accessed on an instance. See also
920 :func:`~sqlalchemy.orm.deferred`.
922 :param comparator_factory: a class which extends
923 :class:`.Composite.Comparator` which provides custom SQL
924 clause generation for comparison operations.
926 :param column_template: A string template such as ``"person_%s"``,
927 containing exactly one ``%s`` placeholder, that's used to generate
928 column names for fields of a dataclass :paramref:`.composite.class_`
929 that don't otherwise have an explicit name. Only supported for
930 dataclass-based composite classes.
932 .. seealso::
934 :ref:`composite_column_template`
936 .. versionadded:: 2.1
938 :param doc:
939 optional string that will be applied as the doc on the
940 class-bound descriptor.
942 :param info: Optional data dictionary which will be populated into the
943 :attr:`.MapperProperty.info` attribute of this object.
945 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
946 specifies if the mapped attribute should be part of the ``__init__()``
947 method as generated by the dataclass process.
948 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
949 specifies if the mapped attribute should be part of the ``__repr__()``
950 method as generated by the dataclass process.
951 :param default_factory: Specific to
952 :ref:`orm_declarative_native_dataclasses`,
953 specifies a default-value generation function that will take place
954 as part of the ``__init__()``
955 method as generated by the dataclass process.
957 :param compare: Specific to
958 :ref:`orm_declarative_native_dataclasses`, indicates if this field
959 should be included in comparison operations when generating the
960 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
962 .. versionadded:: 2.0.0b4
964 :param kw_only: Specific to
965 :ref:`orm_declarative_native_dataclasses`, indicates if this field
966 should be marked as keyword-only when generating the ``__init__()``.
968 :param hash: Specific to
969 :ref:`orm_declarative_native_dataclasses`, controls if this field
970 is included when generating the ``__hash__()`` method for the mapped
971 class.
973 .. versionadded:: 2.0.36
975 :param dataclass_metadata: Specific to
976 :ref:`orm_declarative_native_dataclasses`, supplies metadata
977 to be attached to the generated dataclass field.
979 .. versionadded:: 2.0.42
981 """ # noqa: E501
983 if __kw:
984 raise _no_kw()
986 return Composite(
987 _class_or_attr,
988 *attrs,
989 return_none_on=return_none_on,
990 attribute_options=_AttributeOptions(
991 init,
992 repr,
993 default,
994 default_factory,
995 compare,
996 kw_only,
997 hash,
998 dataclass_metadata,
999 ),
1000 group=group,
1001 deferred=deferred,
1002 raiseload=raiseload,
1003 comparator_factory=comparator_factory,
1004 active_history=active_history,
1005 column_template=column_template,
1006 info=info,
1007 doc=doc,
1008 )
1011def with_loader_criteria(
1012 entity_or_base: _EntityType[Any],
1013 where_criteria: Union[
1014 _ColumnExpressionArgument[bool],
1015 Callable[[Any], _ColumnExpressionArgument[bool]],
1016 ],
1017 loader_only: bool = False,
1018 include_aliases: bool = False,
1019 propagate_to_loaders: bool = True,
1020 track_closure_variables: bool = True,
1021) -> LoaderCriteriaOption:
1022 """Add additional WHERE criteria to the load for all occurrences of
1023 a particular entity.
1025 .. versionadded:: 1.4
1027 The :func:`_orm.with_loader_criteria` option is intended to add
1028 limiting criteria to a particular kind of entity in a query,
1029 **globally**, meaning it will apply to the entity as it appears
1030 in the SELECT query as well as within any subqueries, join
1031 conditions, and relationship loads, including both eager and lazy
1032 loaders, without the need for it to be specified in any particular
1033 part of the query. The rendering logic uses the same system used by
1034 single table inheritance to ensure a certain discriminator is applied
1035 to a table.
1037 E.g., using :term:`2.0-style` queries, we can limit the way the
1038 ``User.addresses`` collection is loaded, regardless of the kind
1039 of loading used::
1041 from sqlalchemy.orm import with_loader_criteria
1043 stmt = select(User).options(
1044 selectinload(User.addresses),
1045 with_loader_criteria(Address, Address.email_address != "foo"),
1046 )
1048 Above, the "selectinload" for ``User.addresses`` will apply the
1049 given filtering criteria to the WHERE clause.
1051 Another example, where the filtering will be applied to the
1052 ON clause of the join, in this example using :term:`1.x style`
1053 queries::
1055 q = (
1056 session.query(User)
1057 .outerjoin(User.addresses)
1058 .options(with_loader_criteria(Address, Address.email_address != "foo"))
1059 )
1061 The primary purpose of :func:`_orm.with_loader_criteria` is to use
1062 it in the :meth:`_orm.SessionEvents.do_orm_execute` event handler
1063 to ensure that all occurrences of a particular entity are filtered
1064 in a certain way, such as filtering for access control roles. It
1065 also can be used to apply criteria to relationship loads. In the
1066 example below, we can apply a certain set of rules to all queries
1067 emitted by a particular :class:`_orm.Session`::
1069 session = Session(bind=engine)
1072 @event.listens_for("do_orm_execute", session)
1073 def _add_filtering_criteria(execute_state):
1075 if (
1076 execute_state.is_select
1077 and not execute_state.is_column_load
1078 and not execute_state.is_relationship_load
1079 ):
1080 execute_state.statement = execute_state.statement.options(
1081 with_loader_criteria(
1082 SecurityRole,
1083 lambda cls: cls.role.in_(["some_role"]),
1084 include_aliases=True,
1085 )
1086 )
1088 In the above example, the :meth:`_orm.SessionEvents.do_orm_execute`
1089 event will intercept all queries emitted using the
1090 :class:`_orm.Session`. For those queries which are SELECT statements
1091 and are not attribute or relationship loads a custom
1092 :func:`_orm.with_loader_criteria` option is added to the query. The
1093 :func:`_orm.with_loader_criteria` option will be used in the given
1094 statement and will also be automatically propagated to all relationship
1095 loads that descend from this query.
1097 The criteria argument given is a ``lambda`` that accepts a ``cls``
1098 argument. The given class will expand to include all mapped subclass
1099 and need not itself be a mapped class.
1101 .. tip::
1103 When using :func:`_orm.with_loader_criteria` option in
1104 conjunction with the :func:`_orm.contains_eager` loader option,
1105 it's important to note that :func:`_orm.with_loader_criteria` only
1106 affects the part of the query that determines what SQL is rendered
1107 in terms of the WHERE and FROM clauses. The
1108 :func:`_orm.contains_eager` option does not affect the rendering of
1109 the SELECT statement outside of the columns clause, so does not have
1110 any interaction with the :func:`_orm.with_loader_criteria` option.
1111 However, the way things "work" is that :func:`_orm.contains_eager`
1112 is meant to be used with a query that is already selecting from the
1113 additional entities in some way, where
1114 :func:`_orm.with_loader_criteria` can apply it's additional
1115 criteria.
1117 In the example below, assuming a mapping relationship as
1118 ``A -> A.bs -> B``, the given :func:`_orm.with_loader_criteria`
1119 option will affect the way in which the JOIN is rendered::
1121 stmt = (
1122 select(A)
1123 .join(A.bs)
1124 .options(contains_eager(A.bs), with_loader_criteria(B, B.flag == 1))
1125 )
1127 Above, the given :func:`_orm.with_loader_criteria` option will
1128 affect the ON clause of the JOIN that is specified by
1129 ``.join(A.bs)``, so is applied as expected. The
1130 :func:`_orm.contains_eager` option has the effect that columns from
1131 ``B`` are added to the columns clause:
1133 .. sourcecode:: sql
1135 SELECT
1136 b.id, b.a_id, b.data, b.flag,
1137 a.id AS id_1,
1138 a.data AS data_1
1139 FROM a JOIN b ON a.id = b.a_id AND b.flag = :flag_1
1142 The use of the :func:`_orm.contains_eager` option within the above
1143 statement has no effect on the behavior of the
1144 :func:`_orm.with_loader_criteria` option. If the
1145 :func:`_orm.contains_eager` option were omitted, the SQL would be
1146 the same as regards the FROM and WHERE clauses, where
1147 :func:`_orm.with_loader_criteria` continues to add its criteria to
1148 the ON clause of the JOIN. The addition of
1149 :func:`_orm.contains_eager` only affects the columns clause, in that
1150 additional columns against ``b`` are added which are then consumed
1151 by the ORM to produce ``B`` instances.
1153 .. warning:: The use of a lambda inside of the call to
1154 :func:`_orm.with_loader_criteria` is only invoked **once per unique
1155 class**. Custom functions should not be invoked within this lambda.
1156 See :ref:`engine_lambda_caching` for an overview of the "lambda SQL"
1157 feature, which is for advanced use only.
1159 :param entity_or_base: a mapped class, or a class that is a super
1160 class of a particular set of mapped classes, to which the rule
1161 will apply.
1163 :param where_criteria: a Core SQL expression that applies limiting
1164 criteria. This may also be a "lambda:" or Python function that
1165 accepts a target class as an argument, when the given class is
1166 a base with many different mapped subclasses.
1168 .. note:: To support pickling, use a module-level Python function to
1169 produce the SQL expression instead of a lambda or a fixed SQL
1170 expression, which tend to not be picklable.
1172 :param include_aliases: if True, apply the rule to :func:`_orm.aliased`
1173 constructs as well.
1175 :param propagate_to_loaders: defaults to True, apply to relationship
1176 loaders such as lazy loaders. This indicates that the
1177 option object itself including SQL expression is carried along with
1178 each loaded instance. Set to ``False`` to prevent the object from
1179 being assigned to individual instances.
1182 .. seealso::
1184 :ref:`examples_session_orm_events` - includes examples of using
1185 :func:`_orm.with_loader_criteria`.
1187 :ref:`do_orm_execute_global_criteria` - basic example on how to
1188 combine :func:`_orm.with_loader_criteria` with the
1189 :meth:`_orm.SessionEvents.do_orm_execute` event.
1191 :param track_closure_variables: when False, closure variables inside
1192 of a lambda expression will not be used as part of
1193 any cache key. This allows more complex expressions to be used
1194 inside of a lambda expression but requires that the lambda ensures
1195 it returns the identical SQL every time given a particular class.
1197 .. versionadded:: 1.4.0b2
1199 """ # noqa: E501
1200 return LoaderCriteriaOption(
1201 entity_or_base,
1202 where_criteria,
1203 loader_only,
1204 include_aliases,
1205 propagate_to_loaders,
1206 track_closure_variables,
1207 )
1210def relationship(
1211 argument: Optional[_RelationshipArgumentType[Any]] = None,
1212 secondary: Optional[_RelationshipSecondaryArgument] = None,
1213 *,
1214 uselist: Optional[bool] = None,
1215 collection_class: Optional[
1216 Union[Type[Collection[Any]], Callable[[], Collection[Any]]]
1217 ] = None,
1218 primaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
1219 secondaryjoin: Optional[_RelationshipJoinConditionArgument] = None,
1220 back_populates: Optional[_RelationshipBackPopulatesArgument] = None,
1221 order_by: _ORMOrderByArgument = False,
1222 backref: Optional[ORMBackrefArgument] = None,
1223 overlaps: Optional[str] = None,
1224 post_update: bool = False,
1225 cascade: str = "save-update, merge",
1226 viewonly: bool = False,
1227 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
1228 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
1229 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
1230 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
1231 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
1232 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
1233 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
1234 lazy: _LazyLoadArgumentType = "select",
1235 passive_deletes: Union[Literal["all"], bool] = False,
1236 passive_updates: bool = True,
1237 active_history: bool = False,
1238 enable_typechecks: bool = True,
1239 foreign_keys: Optional[_ORMColCollectionArgument] = None,
1240 remote_side: Optional[_ORMColCollectionArgument] = None,
1241 join_depth: Optional[int] = None,
1242 comparator_factory: Optional[
1243 Type[RelationshipProperty.Comparator[Any]]
1244 ] = None,
1245 single_parent: bool = False,
1246 innerjoin: bool = False,
1247 distinct_target_key: Optional[bool] = None,
1248 load_on_pending: bool = False,
1249 query_class: Optional[Type[Query[Any]]] = None,
1250 info: Optional[_InfoType] = None,
1251 omit_join: Literal[None, False] = None,
1252 sync_backref: Optional[bool] = None,
1253 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
1254 **kw: Any,
1255) -> _RelationshipDeclared[Any]:
1256 """Provide a relationship between two mapped classes.
1258 This corresponds to a parent-child or associative table relationship.
1259 The constructed class is an instance of :class:`.Relationship`.
1261 .. seealso::
1263 :ref:`tutorial_orm_related_objects` - tutorial introduction
1264 to :func:`_orm.relationship` in the :ref:`unified_tutorial`
1266 :ref:`relationship_config_toplevel` - narrative documentation
1268 :param argument:
1269 This parameter refers to the class that is to be related. It
1270 accepts several forms, including a direct reference to the target
1271 class itself, the :class:`_orm.Mapper` instance for the target class,
1272 a Python callable / lambda that will return a reference to the
1273 class or :class:`_orm.Mapper` when called, and finally a string
1274 name for the class, which will be resolved from the
1275 :class:`_orm.registry` in use in order to locate the class, e.g.::
1277 class SomeClass(Base):
1278 # ...
1280 related = relationship("RelatedClass")
1282 The :paramref:`_orm.relationship.argument` may also be omitted from the
1283 :func:`_orm.relationship` construct entirely, and instead placed inside
1284 a :class:`_orm.Mapped` annotation on the left side, which should
1285 include a Python collection type if the relationship is expected
1286 to be a collection, such as::
1288 class SomeClass(Base):
1289 # ...
1291 related_items: Mapped[List["RelatedItem"]] = relationship()
1293 Or for a many-to-one or one-to-one relationship::
1295 class SomeClass(Base):
1296 # ...
1298 related_item: Mapped["RelatedItem"] = relationship()
1300 .. seealso::
1302 :ref:`orm_declarative_properties` - further detail
1303 on relationship configuration when using Declarative.
1305 :param secondary:
1306 For a many-to-many relationship, specifies the intermediary
1307 table, and is typically an instance of :class:`_schema.Table`.
1308 In less common circumstances, the argument may also be specified
1309 as an :class:`_expression.Alias` construct, or even a
1310 :class:`_expression.Join` construct.
1312 :paramref:`_orm.relationship.secondary` may
1313 also be passed as a callable function which is evaluated at
1314 mapper initialization time. When using Declarative, it may also
1315 be a string argument noting the name of a :class:`_schema.Table`
1316 that is
1317 present in the :class:`_schema.MetaData`
1318 collection associated with the
1319 parent-mapped :class:`_schema.Table`.
1321 .. versionchanged:: 2.1 When passed as a string, the argument is
1322 interpreted as a string name that should exist directly in the
1323 registry of tables. The Python ``eval()`` function is no longer
1324 used for the :paramref:`_orm.relationship.secondary` argument when
1325 passed as a string.
1327 The :paramref:`_orm.relationship.secondary` keyword argument is
1328 typically applied in the case where the intermediary
1329 :class:`_schema.Table`
1330 is not otherwise expressed in any direct class mapping. If the
1331 "secondary" table is also explicitly mapped elsewhere (e.g. as in
1332 :ref:`association_pattern`), one should consider applying the
1333 :paramref:`_orm.relationship.viewonly` flag so that this
1334 :func:`_orm.relationship`
1335 is not used for persistence operations which
1336 may conflict with those of the association object pattern.
1338 .. seealso::
1340 :ref:`relationships_many_to_many` - Reference example of "many
1341 to many".
1343 :ref:`self_referential_many_to_many` - Specifics on using
1344 many-to-many in a self-referential case.
1346 :ref:`declarative_many_to_many` - Additional options when using
1347 Declarative.
1349 :ref:`association_pattern` - an alternative to
1350 :paramref:`_orm.relationship.secondary`
1351 when composing association
1352 table relationships, allowing additional attributes to be
1353 specified on the association table.
1355 :ref:`composite_secondary_join` - a lesser-used pattern which
1356 in some cases can enable complex :func:`_orm.relationship` SQL
1357 conditions to be used.
1359 :param active_history=False:
1360 When ``True``, indicates that the "previous" value for a
1361 many-to-one reference should be loaded when replaced, if
1362 not already loaded. Normally, history tracking logic for
1363 simple many-to-ones only needs to be aware of the "new"
1364 value in order to perform a flush. This flag is available
1365 for applications that make use of
1366 :func:`.attributes.get_history` which also need to know
1367 the "previous" value of the attribute.
1369 :param backref:
1370 A reference to a string relationship name, or a :func:`_orm.backref`
1371 construct, which will be used to automatically generate a new
1372 :func:`_orm.relationship` on the related class, which then refers to this
1373 one using a bi-directional :paramref:`_orm.relationship.back_populates`
1374 configuration.
1376 In modern Python, explicit use of :func:`_orm.relationship`
1377 with :paramref:`_orm.relationship.back_populates` should be preferred,
1378 as it is more robust in terms of mapper configuration as well as
1379 more conceptually straightforward. It also integrates with
1380 new :pep:`484` typing features introduced in SQLAlchemy 2.0 which
1381 is not possible with dynamically generated attributes.
1383 .. seealso::
1385 :ref:`relationships_backref` - notes on using
1386 :paramref:`_orm.relationship.backref`
1388 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1389 presents an overview of bi-directional relationship configuration
1390 and behaviors using :paramref:`_orm.relationship.back_populates`
1392 :func:`.backref` - allows control over :func:`_orm.relationship`
1393 configuration when using :paramref:`_orm.relationship.backref`.
1396 :param back_populates:
1397 Indicates the name of a :func:`_orm.relationship` on the related
1398 class that will be synchronized with this one. It is usually
1399 expected that the :func:`_orm.relationship` on the related class
1400 also refer to this one. This allows objects on both sides of
1401 each :func:`_orm.relationship` to synchronize in-Python state
1402 changes and also provides directives to the :term:`unit of work`
1403 flush process how changes along these relationships should
1404 be persisted.
1406 .. seealso::
1408 :ref:`tutorial_orm_related_objects` - in the :ref:`unified_tutorial`,
1409 presents an overview of bi-directional relationship configuration
1410 and behaviors.
1412 :ref:`relationship_patterns` - includes many examples of
1413 :paramref:`_orm.relationship.back_populates`.
1415 :paramref:`_orm.relationship.backref` - legacy form which allows
1416 more succinct configuration, but does not support explicit typing
1418 :param overlaps:
1419 A string name or comma-delimited set of names of other relationships
1420 on either this mapper, a descendant mapper, or a target mapper with
1421 which this relationship may write to the same foreign keys upon
1422 persistence. The only effect this has is to eliminate the
1423 warning that this relationship will conflict with another upon
1424 persistence. This is used for such relationships that are truly
1425 capable of conflicting with each other on write, but the application
1426 will ensure that no such conflicts occur.
1428 .. versionadded:: 1.4
1430 .. seealso::
1432 :ref:`error_qzyx` - usage example
1434 :param cascade:
1435 A comma-separated list of cascade rules which determines how
1436 Session operations should be "cascaded" from parent to child.
1437 This defaults to ``False``, which means the default cascade
1438 should be used - this default cascade is ``"save-update, merge"``.
1440 The available cascades are ``save-update``, ``merge``,
1441 ``expunge``, ``delete``, ``delete-orphan``, and ``refresh-expire``.
1442 An additional option, ``all`` indicates shorthand for
1443 ``"save-update, merge, refresh-expire,
1444 expunge, delete"``, and is often used as in ``"all, delete-orphan"``
1445 to indicate that related objects should follow along with the
1446 parent object in all cases, and be deleted when de-associated.
1448 .. seealso::
1450 :ref:`unitofwork_cascades` - Full detail on each of the available
1451 cascade options.
1453 :param cascade_backrefs=False:
1454 Legacy; this flag is always False.
1456 .. versionchanged:: 2.0 "cascade_backrefs" functionality has been
1457 removed.
1459 :param collection_class:
1460 A class or callable that returns a new list-holding object. will
1461 be used in place of a plain list for storing elements.
1463 .. seealso::
1465 :ref:`custom_collections` - Introductory documentation and
1466 examples.
1468 :param comparator_factory:
1469 A class which extends :class:`.Relationship.Comparator`
1470 which provides custom SQL clause generation for comparison
1471 operations.
1473 .. seealso::
1475 :class:`.PropComparator` - some detail on redefining comparators
1476 at this level.
1478 :ref:`custom_comparators` - Brief intro to this feature.
1481 :param distinct_target_key=None:
1482 Indicate if a "subquery" eager load should apply the DISTINCT
1483 keyword to the innermost SELECT statement. When left as ``None``,
1484 the DISTINCT keyword will be applied in those cases when the target
1485 columns do not comprise the full primary key of the target table.
1486 When set to ``True``, the DISTINCT keyword is applied to the
1487 innermost SELECT unconditionally.
1489 It may be desirable to set this flag to False when the DISTINCT is
1490 reducing performance of the innermost subquery beyond that of what
1491 duplicate innermost rows may be causing.
1493 .. seealso::
1495 :ref:`loading_toplevel` - includes an introduction to subquery
1496 eager loading.
1498 :param doc:
1499 Docstring which will be applied to the resulting descriptor.
1501 :param foreign_keys:
1503 A list of columns which are to be used as "foreign key"
1504 columns, or columns which refer to the value in a remote
1505 column, within the context of this :func:`_orm.relationship`
1506 object's :paramref:`_orm.relationship.primaryjoin` condition.
1507 That is, if the :paramref:`_orm.relationship.primaryjoin`
1508 condition of this :func:`_orm.relationship` is ``a.id ==
1509 b.a_id``, and the values in ``b.a_id`` are required to be
1510 present in ``a.id``, then the "foreign key" column of this
1511 :func:`_orm.relationship` is ``b.a_id``.
1513 In normal cases, the :paramref:`_orm.relationship.foreign_keys`
1514 parameter is **not required.** :func:`_orm.relationship` will
1515 automatically determine which columns in the
1516 :paramref:`_orm.relationship.primaryjoin` condition are to be
1517 considered "foreign key" columns based on those
1518 :class:`_schema.Column` objects that specify
1519 :class:`_schema.ForeignKey`,
1520 or are otherwise listed as referencing columns in a
1521 :class:`_schema.ForeignKeyConstraint` construct.
1522 :paramref:`_orm.relationship.foreign_keys` is only needed when:
1524 1. There is more than one way to construct a join from the local
1525 table to the remote table, as there are multiple foreign key
1526 references present. Setting ``foreign_keys`` will limit the
1527 :func:`_orm.relationship`
1528 to consider just those columns specified
1529 here as "foreign".
1531 2. The :class:`_schema.Table` being mapped does not actually have
1532 :class:`_schema.ForeignKey` or
1533 :class:`_schema.ForeignKeyConstraint`
1534 constructs present, often because the table
1535 was reflected from a database that does not support foreign key
1536 reflection (MySQL MyISAM).
1538 3. The :paramref:`_orm.relationship.primaryjoin`
1539 argument is used to
1540 construct a non-standard join condition, which makes use of
1541 columns or expressions that do not normally refer to their
1542 "parent" column, such as a join condition expressed by a
1543 complex comparison using a SQL function.
1545 The :func:`_orm.relationship` construct will raise informative
1546 error messages that suggest the use of the
1547 :paramref:`_orm.relationship.foreign_keys` parameter when
1548 presented with an ambiguous condition. In typical cases,
1549 if :func:`_orm.relationship` doesn't raise any exceptions, the
1550 :paramref:`_orm.relationship.foreign_keys` parameter is usually
1551 not needed.
1553 :paramref:`_orm.relationship.foreign_keys` may also be passed as a
1554 callable function which is evaluated at mapper initialization time,
1555 and may be passed as a Python-evaluable string when using
1556 Declarative.
1558 .. warning:: When passed as a Python-evaluable string, the
1559 argument is interpreted using Python's ``eval()`` function.
1560 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1561 See :ref:`declarative_relationship_eval` for details on
1562 declarative evaluation of :func:`_orm.relationship` arguments.
1564 .. seealso::
1566 :ref:`relationship_foreign_keys`
1568 :ref:`relationship_custom_foreign`
1570 :func:`.foreign` - allows direct annotation of the "foreign"
1571 columns within a :paramref:`_orm.relationship.primaryjoin`
1572 condition.
1574 :param info: Optional data dictionary which will be populated into the
1575 :attr:`.MapperProperty.info` attribute of this object.
1577 :param innerjoin=False:
1578 When ``True``, joined eager loads will use an inner join to join
1579 against related tables instead of an outer join. The purpose
1580 of this option is generally one of performance, as inner joins
1581 generally perform better than outer joins.
1583 This flag can be set to ``True`` when the relationship references an
1584 object via many-to-one using local foreign keys that are not
1585 nullable, or when the reference is one-to-one or a collection that
1586 is guaranteed to have one or at least one entry.
1588 The option supports the same "nested" and "unnested" options as
1589 that of :paramref:`_orm.joinedload.innerjoin`. See that flag
1590 for details on nested / unnested behaviors.
1592 .. seealso::
1594 :paramref:`_orm.joinedload.innerjoin` - the option as specified by
1595 loader option, including detail on nesting behavior.
1597 :ref:`what_kind_of_loading` - Discussion of some details of
1598 various loader options.
1601 :param join_depth:
1602 When non-``None``, an integer value indicating how many levels
1603 deep "eager" loaders should join on a self-referring or cyclical
1604 relationship. The number counts how many times the same Mapper
1605 shall be present in the loading condition along a particular join
1606 branch. When left at its default of ``None``, eager loaders
1607 will stop chaining when they encounter a the same target mapper
1608 which is already higher up in the chain. This option applies
1609 both to joined- and subquery- eager loaders.
1611 .. seealso::
1613 :ref:`self_referential_eager_loading` - Introductory documentation
1614 and examples.
1616 :param lazy='select': specifies
1617 How the related items should be loaded. Default value is
1618 ``select``. Values include:
1620 * ``select`` - items should be loaded lazily when the property is
1621 first accessed, using a separate SELECT statement, or identity map
1622 fetch for simple many-to-one references.
1624 * ``immediate`` - items should be loaded as the parents are loaded,
1625 using a separate SELECT statement, or identity map fetch for
1626 simple many-to-one references.
1628 * ``joined`` - items should be loaded "eagerly" in the same query as
1629 that of the parent, using a JOIN or LEFT OUTER JOIN. Whether
1630 the join is "outer" or not is determined by the
1631 :paramref:`_orm.relationship.innerjoin` parameter.
1633 * ``subquery`` - items should be loaded "eagerly" as the parents are
1634 loaded, using one additional SQL statement, which issues a JOIN to
1635 a subquery of the original statement, for each collection
1636 requested.
1638 * ``selectin`` - items should be loaded "eagerly" as the parents
1639 are loaded, using one or more additional SQL statements, which
1640 issues a JOIN to the immediate parent object, specifying primary
1641 key identifiers using an IN clause.
1643 * ``raise`` - lazy loading is disallowed; accessing
1644 the attribute, if its value were not already loaded via eager
1645 loading, will raise an :exc:`~sqlalchemy.exc.InvalidRequestError`.
1646 This strategy can be used when objects are to be detached from
1647 their attached :class:`.Session` after they are loaded.
1649 * ``raise_on_sql`` - lazy loading that emits SQL is disallowed;
1650 accessing the attribute, if its value were not already loaded via
1651 eager loading, will raise an
1652 :exc:`~sqlalchemy.exc.InvalidRequestError`, **if the lazy load
1653 needs to emit SQL**. If the lazy load can pull the related value
1654 from the identity map or determine that it should be None, the
1655 value is loaded. This strategy can be used when objects will
1656 remain associated with the attached :class:`.Session`, however
1657 additional SELECT statements should be blocked.
1659 * ``write_only`` - the attribute will be configured with a special
1660 "virtual collection" that may receive
1661 :meth:`_orm.WriteOnlyCollection.add` and
1662 :meth:`_orm.WriteOnlyCollection.remove` commands to add or remove
1663 individual objects, but will not under any circumstances load or
1664 iterate the full set of objects from the database directly. Instead,
1665 methods such as :meth:`_orm.WriteOnlyCollection.select`,
1666 :meth:`_orm.WriteOnlyCollection.insert`,
1667 :meth:`_orm.WriteOnlyCollection.update` and
1668 :meth:`_orm.WriteOnlyCollection.delete` are provided which generate SQL
1669 constructs that may be used to load and modify rows in bulk. Used for
1670 large collections that are never appropriate to load at once into
1671 memory.
1673 The ``write_only`` loader style is configured automatically when
1674 the :class:`_orm.WriteOnlyMapped` annotation is provided on the
1675 left hand side within a Declarative mapping. See the section
1676 :ref:`write_only_relationship` for examples.
1678 .. versionadded:: 2.0
1680 .. seealso::
1682 :ref:`write_only_relationship` - in the :ref:`queryguide_toplevel`
1684 * ``dynamic`` - the attribute will return a pre-configured
1685 :class:`_query.Query` object for all read
1686 operations, onto which further filtering operations can be
1687 applied before iterating the results.
1689 The ``dynamic`` loader style is configured automatically when
1690 the :class:`_orm.DynamicMapped` annotation is provided on the
1691 left hand side within a Declarative mapping. See the section
1692 :ref:`dynamic_relationship` for examples.
1694 .. legacy:: The "dynamic" lazy loader strategy is the legacy form of
1695 what is now the "write_only" strategy described in the section
1696 :ref:`write_only_relationship`.
1698 .. seealso::
1700 :ref:`dynamic_relationship` - in the :ref:`queryguide_toplevel`
1702 :ref:`write_only_relationship` - more generally useful approach
1703 for large collections that should not fully load into memory
1705 * ``noload`` - no loading should occur at any time. The related
1706 collection will remain empty.
1708 .. deprecated:: 2.1 The ``noload`` loader strategy is deprecated and
1709 will be removed in a future release. This option produces incorrect
1710 results by returning ``None`` for related items.
1712 * True - a synonym for 'select'
1714 * False - a synonym for 'joined'
1716 * None - a synonym for 'noload'
1718 .. seealso::
1720 :ref:`orm_queryguide_relationship_loaders` - Full documentation on
1721 relationship loader configuration in the :ref:`queryguide_toplevel`.
1724 :param load_on_pending=False:
1725 Indicates loading behavior for transient or pending parent objects.
1727 When set to ``True``, causes the lazy-loader to
1728 issue a query for a parent object that is not persistent, meaning it
1729 has never been flushed. This may take effect for a pending object
1730 when autoflush is disabled, or for a transient object that has been
1731 "attached" to a :class:`.Session` but is not part of its pending
1732 collection.
1734 The :paramref:`_orm.relationship.load_on_pending`
1735 flag does not improve
1736 behavior when the ORM is used normally - object references should be
1737 constructed at the object level, not at the foreign key level, so
1738 that they are present in an ordinary way before a flush proceeds.
1739 This flag is not not intended for general use.
1741 .. seealso::
1743 :meth:`.Session.enable_relationship_loading` - this method
1744 establishes "load on pending" behavior for the whole object, and
1745 also allows loading on objects that remain transient or
1746 detached.
1748 :param order_by:
1749 Indicates the ordering that should be applied when loading these
1750 items. :paramref:`_orm.relationship.order_by`
1751 is expected to refer to
1752 one of the :class:`_schema.Column`
1753 objects to which the target class is
1754 mapped, or the attribute itself bound to the target class which
1755 refers to the column.
1757 :paramref:`_orm.relationship.order_by`
1758 may also be passed as a callable
1759 function which is evaluated at mapper initialization time, and may
1760 be passed as a Python-evaluable string when using Declarative.
1762 .. warning:: When passed as a Python-evaluable string, the
1763 argument is interpreted using Python's ``eval()`` function.
1764 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1765 See :ref:`declarative_relationship_eval` for details on
1766 declarative evaluation of :func:`_orm.relationship` arguments.
1768 :param passive_deletes=False:
1769 Indicates loading behavior during delete operations.
1771 A value of True indicates that unloaded child items should not
1772 be loaded during a delete operation on the parent. Normally,
1773 when a parent item is deleted, all child items are loaded so
1774 that they can either be marked as deleted, or have their
1775 foreign key to the parent set to NULL. Marking this flag as
1776 True usually implies an ON DELETE <CASCADE|SET NULL> rule is in
1777 place which will handle updating/deleting child rows on the
1778 database side.
1780 Additionally, setting the flag to the string value 'all' will
1781 disable the "nulling out" of the child foreign keys, when the parent
1782 object is deleted and there is no delete or delete-orphan cascade
1783 enabled. This is typically used when a triggering or error raise
1784 scenario is in place on the database side. Note that the foreign
1785 key attributes on in-session child objects will not be changed after
1786 a flush occurs so this is a very special use-case setting.
1787 Additionally, the "nulling out" will still occur if the child
1788 object is de-associated with the parent.
1790 .. seealso::
1792 :ref:`passive_deletes` - Introductory documentation
1793 and examples.
1795 :param passive_updates=True:
1796 Indicates the persistence behavior to take when a referenced
1797 primary key value changes in place, indicating that the referencing
1798 foreign key columns will also need their value changed.
1800 When True, it is assumed that ``ON UPDATE CASCADE`` is configured on
1801 the foreign key in the database, and that the database will
1802 handle propagation of an UPDATE from a source column to
1803 dependent rows. When False, the SQLAlchemy
1804 :func:`_orm.relationship`
1805 construct will attempt to emit its own UPDATE statements to
1806 modify related targets. However note that SQLAlchemy **cannot**
1807 emit an UPDATE for more than one level of cascade. Also,
1808 setting this flag to False is not compatible in the case where
1809 the database is in fact enforcing referential integrity, unless
1810 those constraints are explicitly "deferred", if the target backend
1811 supports it.
1813 It is highly advised that an application which is employing
1814 mutable primary keys keeps ``passive_updates`` set to True,
1815 and instead uses the referential integrity features of the database
1816 itself in order to handle the change efficiently and fully.
1818 .. seealso::
1820 :ref:`passive_updates` - Introductory documentation and
1821 examples.
1823 :paramref:`.mapper.passive_updates` - a similar flag which
1824 takes effect for joined-table inheritance mappings.
1826 :param post_update:
1827 This indicates that the relationship should be handled by a
1828 second UPDATE statement after an INSERT or before a
1829 DELETE. This flag is used to handle saving bi-directional
1830 dependencies between two individual rows (i.e. each row
1831 references the other), where it would otherwise be impossible to
1832 INSERT or DELETE both rows fully since one row exists before the
1833 other. Use this flag when a particular mapping arrangement will
1834 incur two rows that are dependent on each other, such as a table
1835 that has a one-to-many relationship to a set of child rows, and
1836 also has a column that references a single child row within that
1837 list (i.e. both tables contain a foreign key to each other). If
1838 a flush operation returns an error that a "cyclical
1839 dependency" was detected, this is a cue that you might want to
1840 use :paramref:`_orm.relationship.post_update` to "break" the cycle.
1842 .. seealso::
1844 :ref:`post_update` - Introductory documentation and examples.
1846 :param primaryjoin:
1847 A SQL expression that will be used as the primary
1848 join of the child object against the parent object, or in a
1849 many-to-many relationship the join of the parent object to the
1850 association table. By default, this value is computed based on the
1851 foreign key relationships of the parent and child tables (or
1852 association table).
1854 :paramref:`_orm.relationship.primaryjoin` may also be passed as a
1855 callable function which is evaluated at mapper initialization time,
1856 and may be passed as a Python-evaluable string when using
1857 Declarative.
1859 .. warning:: When passed as a Python-evaluable string, the
1860 argument is interpreted using Python's ``eval()`` function.
1861 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1862 See :ref:`declarative_relationship_eval` for details on
1863 declarative evaluation of :func:`_orm.relationship` arguments.
1865 .. seealso::
1867 :ref:`relationship_primaryjoin`
1869 :param remote_side:
1870 Used for self-referential relationships, indicates the column or
1871 list of columns that form the "remote side" of the relationship.
1873 :paramref:`_orm.relationship.remote_side` may also be passed as a
1874 callable function which is evaluated at mapper initialization time,
1875 and may be passed as a Python-evaluable string when using
1876 Declarative.
1878 .. warning:: When passed as a Python-evaluable string, the
1879 argument is interpreted using Python's ``eval()`` function.
1880 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1881 See :ref:`declarative_relationship_eval` for details on
1882 declarative evaluation of :func:`_orm.relationship` arguments.
1884 .. seealso::
1886 :ref:`self_referential` - in-depth explanation of how
1887 :paramref:`_orm.relationship.remote_side`
1888 is used to configure self-referential relationships.
1890 :func:`.remote` - an annotation function that accomplishes the
1891 same purpose as :paramref:`_orm.relationship.remote_side`,
1892 typically
1893 when a custom :paramref:`_orm.relationship.primaryjoin` condition
1894 is used.
1896 :param query_class:
1897 A :class:`_query.Query`
1898 subclass that will be used internally by the
1899 ``AppenderQuery`` returned by a "dynamic" relationship, that
1900 is, a relationship that specifies ``lazy="dynamic"`` or was
1901 otherwise constructed using the :func:`_orm.dynamic_loader`
1902 function.
1904 .. seealso::
1906 :ref:`dynamic_relationship` - Introduction to "dynamic"
1907 relationship loaders.
1909 :param secondaryjoin:
1910 A SQL expression that will be used as the join of
1911 an association table to the child object. By default, this value is
1912 computed based on the foreign key relationships of the association
1913 and child tables.
1915 :paramref:`_orm.relationship.secondaryjoin` may also be passed as a
1916 callable function which is evaluated at mapper initialization time,
1917 and may be passed as a Python-evaluable string when using
1918 Declarative.
1920 .. warning:: When passed as a Python-evaluable string, the
1921 argument is interpreted using Python's ``eval()`` function.
1922 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1923 See :ref:`declarative_relationship_eval` for details on
1924 declarative evaluation of :func:`_orm.relationship` arguments.
1926 .. seealso::
1928 :ref:`relationship_primaryjoin`
1930 :param single_parent:
1931 When True, installs a validator which will prevent objects
1932 from being associated with more than one parent at a time.
1933 This is used for many-to-one or many-to-many relationships that
1934 should be treated either as one-to-one or one-to-many. Its usage
1935 is optional, except for :func:`_orm.relationship` constructs which
1936 are many-to-one or many-to-many and also
1937 specify the ``delete-orphan`` cascade option. The
1938 :func:`_orm.relationship` construct itself will raise an error
1939 instructing when this option is required.
1941 .. seealso::
1943 :ref:`unitofwork_cascades` - includes detail on when the
1944 :paramref:`_orm.relationship.single_parent`
1945 flag may be appropriate.
1947 :param uselist:
1948 A boolean that indicates if this property should be loaded as a
1949 list or a scalar. In most cases, this value is determined
1950 automatically by :func:`_orm.relationship` at mapper configuration
1951 time. When using explicit :class:`_orm.Mapped` annotations,
1952 :paramref:`_orm.relationship.uselist` may be derived from the
1953 whether or not the annotation within :class:`_orm.Mapped` contains
1954 a collection class.
1955 Otherwise, :paramref:`_orm.relationship.uselist` may be derived from
1956 the type and direction
1957 of the relationship - one to many forms a list, many to one
1958 forms a scalar, many to many is a list. If a scalar is desired
1959 where normally a list would be present, such as a bi-directional
1960 one-to-one relationship, use an appropriate :class:`_orm.Mapped`
1961 annotation or set :paramref:`_orm.relationship.uselist` to False.
1963 The :paramref:`_orm.relationship.uselist`
1964 flag is also available on an
1965 existing :func:`_orm.relationship`
1966 construct as a read-only attribute,
1967 which can be used to determine if this :func:`_orm.relationship`
1968 deals
1969 with collections or scalar attributes::
1971 >>> User.addresses.property.uselist
1972 True
1974 .. seealso::
1976 :ref:`relationships_one_to_one` - Introduction to the "one to
1977 one" relationship pattern, which is typically when an alternate
1978 setting for :paramref:`_orm.relationship.uselist` is involved.
1980 :param viewonly=False:
1981 When set to ``True``, the relationship is used only for loading
1982 objects, and not for any persistence operation. A
1983 :func:`_orm.relationship` which specifies
1984 :paramref:`_orm.relationship.viewonly` can work
1985 with a wider range of SQL operations within the
1986 :paramref:`_orm.relationship.primaryjoin` condition, including
1987 operations that feature the use of a variety of comparison operators
1988 as well as SQL functions such as :func:`_expression.cast`. The
1989 :paramref:`_orm.relationship.viewonly`
1990 flag is also of general use when defining any kind of
1991 :func:`_orm.relationship` that doesn't represent
1992 the full set of related objects, to prevent modifications of the
1993 collection from resulting in persistence operations.
1995 .. seealso::
1997 :ref:`relationship_viewonly_notes` - more details on best practices
1998 when using :paramref:`_orm.relationship.viewonly`.
2000 :param sync_backref:
2001 A boolean that enables the events used to synchronize the in-Python
2002 attributes when this relationship is target of either
2003 :paramref:`_orm.relationship.backref` or
2004 :paramref:`_orm.relationship.back_populates`.
2006 Defaults to ``None``, which indicates that an automatic value should
2007 be selected based on the value of the
2008 :paramref:`_orm.relationship.viewonly` flag. When left at its
2009 default, changes in state will be back-populated only if neither
2010 sides of a relationship is viewonly.
2012 .. versionchanged:: 1.4 - A relationship that specifies
2013 :paramref:`_orm.relationship.viewonly` automatically implies
2014 that :paramref:`_orm.relationship.sync_backref` is ``False``.
2016 .. seealso::
2018 :paramref:`_orm.relationship.viewonly`
2020 :param omit_join:
2021 Allows manual control over the "selectin" automatic join
2022 optimization. Set to ``False`` to disable the "omit join" feature
2023 added in SQLAlchemy 1.3; or leave as ``None`` to leave automatic
2024 optimization in place.
2026 .. note:: This flag may only be set to ``False``. It is not
2027 necessary to set it to ``True`` as the "omit_join" optimization is
2028 automatically detected; if it is not detected, then the
2029 optimization is not supported.
2031 :param default: Specific to :ref:`orm_declarative_native_dataclasses`,
2032 specifies an immutable scalar default value for the relationship that
2033 will behave as though it is the default value for the parameter in the
2034 ``__init__()`` method. This is only supported for a ``uselist=False``
2035 relationship, that is many-to-one or one-to-one, and only supports the
2036 scalar value ``None``, since no other immutable value is valid for such a
2037 relationship.
2039 .. versionchanged:: 2.1 the :paramref:`_orm.relationship.default`
2040 parameter only supports a value of ``None``.
2042 :param init: Specific to :ref:`orm_declarative_native_dataclasses`,
2043 specifies if the mapped attribute should be part of the ``__init__()``
2044 method as generated by the dataclass process.
2045 :param repr: Specific to :ref:`orm_declarative_native_dataclasses`,
2046 specifies if the mapped attribute should be part of the ``__repr__()``
2047 method as generated by the dataclass process.
2048 :param default_factory: Specific to
2049 :ref:`orm_declarative_native_dataclasses`,
2050 specifies a default-value generation function that will take place
2051 as part of the ``__init__()``
2052 method as generated by the dataclass process.
2053 :param compare: Specific to
2054 :ref:`orm_declarative_native_dataclasses`, indicates if this field
2055 should be included in comparison operations when generating the
2056 ``__eq__()`` and ``__ne__()`` methods for the mapped class.
2058 .. versionadded:: 2.0.0b4
2060 :param kw_only: Specific to
2061 :ref:`orm_declarative_native_dataclasses`, indicates if this field
2062 should be marked as keyword-only when generating the ``__init__()``.
2064 :param hash: Specific to
2065 :ref:`orm_declarative_native_dataclasses`, controls if this field
2066 is included when generating the ``__hash__()`` method for the mapped
2067 class.
2069 .. versionadded:: 2.0.36
2071 :param dataclass_metadata: Specific to
2072 :ref:`orm_declarative_native_dataclasses`, supplies metadata
2073 to be attached to the generated dataclass field.
2075 .. versionadded:: 2.0.42
2077 """
2079 return _RelationshipDeclared(
2080 argument,
2081 secondary=secondary,
2082 uselist=uselist,
2083 collection_class=collection_class,
2084 primaryjoin=primaryjoin,
2085 secondaryjoin=secondaryjoin,
2086 back_populates=back_populates,
2087 order_by=order_by,
2088 backref=backref,
2089 overlaps=overlaps,
2090 post_update=post_update,
2091 cascade=cascade,
2092 viewonly=viewonly,
2093 attribute_options=_AttributeOptions(
2094 init,
2095 repr,
2096 default,
2097 default_factory,
2098 compare,
2099 kw_only,
2100 hash,
2101 dataclass_metadata,
2102 ),
2103 lazy=lazy,
2104 passive_deletes=passive_deletes,
2105 passive_updates=passive_updates,
2106 active_history=active_history,
2107 enable_typechecks=enable_typechecks,
2108 foreign_keys=foreign_keys,
2109 remote_side=remote_side,
2110 join_depth=join_depth,
2111 comparator_factory=comparator_factory,
2112 single_parent=single_parent,
2113 innerjoin=innerjoin,
2114 distinct_target_key=distinct_target_key,
2115 load_on_pending=load_on_pending,
2116 query_class=query_class,
2117 info=info,
2118 omit_join=omit_join,
2119 sync_backref=sync_backref,
2120 **kw,
2121 )
2124def synonym(
2125 name: str,
2126 *,
2127 map_column: Optional[bool] = None,
2128 descriptor: Optional[Any] = None,
2129 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
2130 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
2131 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2132 default: Union[_NoArg, _T] = _NoArg.NO_ARG,
2133 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
2134 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
2135 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
2136 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
2137 info: Optional[_InfoType] = None,
2138 doc: Optional[str] = None,
2139 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
2140) -> Synonym[Any]:
2141 """Denote an attribute name as a synonym to a mapped property,
2142 in that the attribute will mirror the value and expression behavior
2143 of another attribute.
2145 e.g.::
2147 class MyClass(Base):
2148 __tablename__ = "my_table"
2150 id = Column(Integer, primary_key=True)
2151 job_status = Column(String(50))
2153 status = synonym("job_status")
2155 :param name: the name of the existing mapped property. This
2156 can refer to the string name ORM-mapped attribute
2157 configured on the class, including column-bound attributes
2158 and relationships.
2160 :param descriptor: a Python :term:`descriptor` that will be used
2161 as a getter (and potentially a setter) when this attribute is
2162 accessed at the instance level.
2164 :param map_column: **For classical mappings and mappings against
2165 an existing Table object only**. if ``True``, the :func:`.synonym`
2166 construct will locate the :class:`_schema.Column`
2167 object upon the mapped
2168 table that would normally be associated with the attribute name of
2169 this synonym, and produce a new :class:`.ColumnProperty` that instead
2170 maps this :class:`_schema.Column`
2171 to the alternate name given as the "name"
2172 argument of the synonym; in this way, the usual step of redefining
2173 the mapping of the :class:`_schema.Column`
2174 to be under a different name is
2175 unnecessary. This is usually intended to be used when a
2176 :class:`_schema.Column`
2177 is to be replaced with an attribute that also uses a
2178 descriptor, that is, in conjunction with the
2179 :paramref:`.synonym.descriptor` parameter::
2181 my_table = Table(
2182 "my_table",
2183 metadata,
2184 Column("id", Integer, primary_key=True),
2185 Column("job_status", String(50)),
2186 )
2189 class MyClass:
2190 @property
2191 def _job_status_descriptor(self):
2192 return "Status: %s" % self._job_status
2195 mapper(
2196 MyClass,
2197 my_table,
2198 properties={
2199 "job_status": synonym(
2200 "_job_status",
2201 map_column=True,
2202 descriptor=MyClass._job_status_descriptor,
2203 )
2204 },
2205 )
2207 Above, the attribute named ``_job_status`` is automatically
2208 mapped to the ``job_status`` column::
2210 >>> j1 = MyClass()
2211 >>> j1._job_status = "employed"
2212 >>> j1.job_status
2213 Status: employed
2215 When using Declarative, in order to provide a descriptor in
2216 conjunction with a synonym, use the
2217 :func:`sqlalchemy.ext.declarative.synonym_for` helper. However,
2218 note that the :ref:`hybrid properties <mapper_hybrids>` feature
2219 should usually be preferred, particularly when redefining attribute
2220 behavior.
2222 :param info: Optional data dictionary which will be populated into the
2223 :attr:`.InspectionAttr.info` attribute of this object.
2225 :param comparator_factory: A subclass of :class:`.PropComparator`
2226 that will provide custom comparison behavior at the SQL expression
2227 level.
2229 .. note::
2231 For the use case of providing an attribute which redefines both
2232 Python-level and SQL-expression level behavior of an attribute,
2233 please refer to the Hybrid attribute introduced at
2234 :ref:`mapper_hybrids` for a more effective technique.
2236 .. seealso::
2238 :ref:`synonyms` - Overview of synonyms
2240 :func:`.synonym_for` - a helper oriented towards Declarative
2242 :ref:`mapper_hybrids` - The Hybrid Attribute extension provides an
2243 updated approach to augmenting attribute behavior more flexibly
2244 than can be achieved with synonyms.
2246 """
2247 return Synonym(
2248 name,
2249 map_column=map_column,
2250 descriptor=descriptor,
2251 comparator_factory=comparator_factory,
2252 attribute_options=_AttributeOptions(
2253 init,
2254 repr,
2255 default,
2256 default_factory,
2257 compare,
2258 kw_only,
2259 hash,
2260 dataclass_metadata,
2261 ),
2262 doc=doc,
2263 info=info,
2264 )
2267def create_session(
2268 bind: Optional[_SessionBind] = None, **kwargs: Any
2269) -> Session:
2270 r"""Create a new :class:`.Session`
2271 with no automation enabled by default.
2273 This function is used primarily for testing. The usual
2274 route to :class:`.Session` creation is via its constructor
2275 or the :func:`.sessionmaker` function.
2277 :param bind: optional, a single Connectable to use for all
2278 database access in the created
2279 :class:`~sqlalchemy.orm.session.Session`.
2281 :param \*\*kwargs: optional, passed through to the
2282 :class:`.Session` constructor.
2284 :returns: an :class:`~sqlalchemy.orm.session.Session` instance
2286 The defaults of create_session() are the opposite of that of
2287 :func:`sessionmaker`; ``autoflush`` and ``expire_on_commit`` are
2288 False.
2290 Usage::
2292 >>> from sqlalchemy.orm import create_session
2293 >>> session = create_session()
2295 It is recommended to use :func:`sessionmaker` instead of
2296 create_session().
2298 """
2300 kwargs.setdefault("autoflush", False)
2301 kwargs.setdefault("expire_on_commit", False)
2302 return Session(bind=bind, **kwargs)
2305def _mapper_fn(*arg: Any, **kw: Any) -> NoReturn:
2306 """Placeholder for the now-removed ``mapper()`` function.
2308 Classical mappings should be performed using the
2309 :meth:`_orm.registry.map_imperatively` method.
2311 This symbol remains in SQLAlchemy 2.0 to suit the deprecated use case
2312 of using the ``mapper()`` function as a target for ORM event listeners,
2313 which failed to be marked as deprecated in the 1.4 series.
2315 Global ORM mapper listeners should instead use the :class:`_orm.Mapper`
2316 class as the target.
2318 .. versionchanged:: 2.0 The ``mapper()`` function was removed; the
2319 symbol remains temporarily as a placeholder for the event listening
2320 use case.
2322 """
2323 raise InvalidRequestError(
2324 "The 'sqlalchemy.orm.mapper()' function is removed as of "
2325 "SQLAlchemy 2.0. Use the "
2326 "'sqlalchemy.orm.registry.map_imperatively()` "
2327 "method of the ``sqlalchemy.orm.registry`` class to perform "
2328 "classical mapping."
2329 )
2332def dynamic_loader(
2333 argument: Optional[_RelationshipArgumentType[Any]] = None, **kw: Any
2334) -> RelationshipProperty[Any]:
2335 """Construct a dynamically-loading mapper property.
2337 This is essentially the same as
2338 using the ``lazy='dynamic'`` argument with :func:`relationship`::
2340 dynamic_loader(SomeClass)
2342 # is the same as
2344 relationship(SomeClass, lazy="dynamic")
2346 See the section :ref:`dynamic_relationship` for more details
2347 on dynamic loading.
2349 """
2350 kw["lazy"] = "dynamic"
2351 return relationship(argument, **kw)
2354def backref(name: str, **kwargs: Any) -> ORMBackrefArgument:
2355 """When using the :paramref:`_orm.relationship.backref` parameter,
2356 provides specific parameters to be used when the new
2357 :func:`_orm.relationship` is generated.
2359 E.g.::
2361 "items": relationship(SomeItem, backref=backref("parent", lazy="subquery"))
2363 The :paramref:`_orm.relationship.backref` parameter is generally
2364 considered to be legacy; for modern applications, using
2365 explicit :func:`_orm.relationship` constructs linked together using
2366 the :paramref:`_orm.relationship.back_populates` parameter should be
2367 preferred.
2369 .. seealso::
2371 :ref:`relationships_backref` - background on backrefs
2373 """ # noqa: E501
2375 return (name, kwargs)
2378def deferred(
2379 column: _ORMColumnExprArgument[_T],
2380 *additional_columns: _ORMColumnExprArgument[Any],
2381 group: Optional[str] = None,
2382 raiseload: bool = False,
2383 comparator_factory: Optional[Type[PropComparator[_T]]] = None,
2384 init: Union[_NoArg, bool] = _NoArg.NO_ARG,
2385 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2386 default: Optional[Any] = _NoArg.NO_ARG,
2387 default_factory: Union[_NoArg, Callable[[], _T]] = _NoArg.NO_ARG,
2388 compare: Union[_NoArg, bool] = _NoArg.NO_ARG,
2389 kw_only: Union[_NoArg, bool] = _NoArg.NO_ARG,
2390 hash: Union[_NoArg, bool, None] = _NoArg.NO_ARG, # noqa: A002
2391 active_history: bool = False,
2392 expire_on_flush: bool = True,
2393 info: Optional[_InfoType] = None,
2394 doc: Optional[str] = None,
2395 dataclass_metadata: Union[_NoArg, Mapping[Any, Any], None] = _NoArg.NO_ARG,
2396) -> MappedSQLExpression[_T]:
2397 r"""Indicate a column-based mapped attribute that by default will
2398 not load unless accessed.
2400 When using :func:`_orm.mapped_column`, the same functionality as
2401 that of :func:`_orm.deferred` construct is provided by using the
2402 :paramref:`_orm.mapped_column.deferred` parameter.
2404 :param \*columns: columns to be mapped. This is typically a single
2405 :class:`_schema.Column` object,
2406 however a collection is supported in order
2407 to support multiple columns mapped under the same attribute.
2409 :param raiseload: boolean, if True, indicates an exception should be raised
2410 if the load operation is to take place.
2412 .. versionadded:: 1.4
2415 Additional arguments are the same as that of :func:`_orm.column_property`.
2417 .. seealso::
2419 :ref:`orm_queryguide_deferred_imperative`
2421 """
2422 return MappedSQLExpression(
2423 column,
2424 *additional_columns,
2425 attribute_options=_AttributeOptions(
2426 init,
2427 repr,
2428 default,
2429 default_factory,
2430 compare,
2431 kw_only,
2432 hash,
2433 dataclass_metadata,
2434 ),
2435 group=group,
2436 deferred=True,
2437 raiseload=raiseload,
2438 comparator_factory=comparator_factory,
2439 active_history=active_history,
2440 expire_on_flush=expire_on_flush,
2441 info=info,
2442 doc=doc,
2443 )
2446def query_expression(
2447 default_expr: _ORMColumnExprArgument[_T] = sql.null(),
2448 *,
2449 repr: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2450 compare: Union[_NoArg, bool] = _NoArg.NO_ARG, # noqa: A002
2451 expire_on_flush: bool = True,
2452 info: Optional[_InfoType] = None,
2453 doc: Optional[str] = None,
2454) -> MappedSQLExpression[_T]:
2455 """Indicate an attribute that populates from a query-time SQL expression.
2457 :param default_expr: Optional SQL expression object that will be used in
2458 all cases if not assigned later with :func:`_orm.with_expression`.
2460 .. seealso::
2462 :ref:`orm_queryguide_with_expression` - background and usage examples
2464 """
2465 prop = MappedSQLExpression(
2466 default_expr,
2467 attribute_options=_AttributeOptions(
2468 False,
2469 repr,
2470 _NoArg.NO_ARG,
2471 _NoArg.NO_ARG,
2472 compare,
2473 _NoArg.NO_ARG,
2474 _NoArg.NO_ARG,
2475 _NoArg.NO_ARG,
2476 ),
2477 expire_on_flush=expire_on_flush,
2478 info=info,
2479 doc=doc,
2480 _assume_readonly_dc_attributes=True,
2481 )
2483 prop.strategy_key = (("query_expression", True),)
2484 return prop
2487def clear_mappers() -> None:
2488 """Remove all mappers from all classes.
2490 .. versionchanged:: 1.4 This function now locates all
2491 :class:`_orm.registry` objects and calls upon the
2492 :meth:`_orm.registry.dispose` method of each.
2494 This function removes all instrumentation from classes and disposes
2495 of their associated mappers. Once called, the classes are unmapped
2496 and can be later re-mapped with new mappers.
2498 :func:`.clear_mappers` is *not* for normal use, as there is literally no
2499 valid usage for it outside of very specific testing scenarios. Normally,
2500 mappers are permanent structural components of user-defined classes, and
2501 are never discarded independently of their class. If a mapped class
2502 itself is garbage collected, its mapper is automatically disposed of as
2503 well. As such, :func:`.clear_mappers` is only for usage in test suites
2504 that reuse the same classes with different mappings, which is itself an
2505 extremely rare use case - the only such use case is in fact SQLAlchemy's
2506 own test suite, and possibly the test suites of other ORM extension
2507 libraries which intend to test various combinations of mapper construction
2508 upon a fixed set of classes.
2510 """
2512 mapperlib._dispose_registries(mapperlib._all_registries(), False)
2515# I would really like a way to get the Type[] here that shows up
2516# in a different way in typing tools, however there is no current method
2517# that is accepted by mypy (subclass of Type[_O] works in pylance, rejected
2518# by mypy).
2519AliasedType = Annotated[Type[_O], "aliased"]
2522@overload
2523def aliased(
2524 element: Type[_O],
2525 alias: Optional[FromClause] = None,
2526 name: Optional[str] = None,
2527 flat: bool = False,
2528 adapt_on_names: bool = False,
2529) -> AliasedType[_O]: ...
2532@overload
2533def aliased(
2534 element: Union[AliasedClass[_O], Mapper[_O], AliasedInsp[_O]],
2535 alias: Optional[FromClause] = None,
2536 name: Optional[str] = None,
2537 flat: bool = False,
2538 adapt_on_names: bool = False,
2539) -> AliasedClass[_O]: ...
2542@overload
2543def aliased(
2544 element: FromClause,
2545 alias: None = None,
2546 name: Optional[str] = None,
2547 flat: bool = False,
2548 adapt_on_names: bool = False,
2549) -> FromClause: ...
2552def aliased(
2553 element: Union[_EntityType[_O], FromClause],
2554 alias: Optional[FromClause] = None,
2555 name: Optional[str] = None,
2556 flat: bool = False,
2557 adapt_on_names: bool = False,
2558) -> Union[AliasedClass[_O], FromClause, AliasedType[_O]]:
2559 """Produce an alias of the given element, usually an :class:`.AliasedClass`
2560 instance.
2562 E.g.::
2564 my_alias = aliased(MyClass)
2566 stmt = select(MyClass, my_alias).filter(MyClass.id > my_alias.id)
2567 result = session.execute(stmt)
2569 The :func:`.aliased` function is used to create an ad-hoc mapping of a
2570 mapped class to a new selectable. By default, a selectable is generated
2571 from the normally mapped selectable (typically a :class:`_schema.Table`
2572 ) using the
2573 :meth:`_expression.FromClause.alias` method. However, :func:`.aliased`
2574 can also be
2575 used to link the class to a new :func:`_expression.select` statement.
2576 Also, the :func:`.with_polymorphic` function is a variant of
2577 :func:`.aliased` that is intended to specify a so-called "polymorphic
2578 selectable", that corresponds to the union of several joined-inheritance
2579 subclasses at once.
2581 For convenience, the :func:`.aliased` function also accepts plain
2582 :class:`_expression.FromClause` constructs, such as a
2583 :class:`_schema.Table` or
2584 :func:`_expression.select` construct. In those cases, the
2585 :meth:`_expression.FromClause.alias`
2586 method is called on the object and the new
2587 :class:`_expression.Alias` object returned. The returned
2588 :class:`_expression.Alias` is not
2589 ORM-mapped in this case.
2591 .. seealso::
2593 :ref:`tutorial_orm_entity_aliases` - in the :ref:`unified_tutorial`
2595 :ref:`orm_queryguide_orm_aliases` - in the :ref:`queryguide_toplevel`
2597 :param element: element to be aliased. Is normally a mapped class,
2598 but for convenience can also be a :class:`_expression.FromClause`
2599 element.
2601 :param alias: Optional selectable unit to map the element to. This is
2602 usually used to link the object to a subquery, and should be an aliased
2603 select construct as one would produce from the
2604 :meth:`_query.Query.subquery` method or
2605 the :meth:`_expression.Select.subquery` or
2606 :meth:`_expression.Select.alias` methods of the :func:`_expression.select`
2607 construct.
2609 :param name: optional string name to use for the alias, if not specified
2610 by the ``alias`` parameter. The name, among other things, forms the
2611 attribute name that will be accessible via tuples returned by a
2612 :class:`_query.Query` object. Not supported when creating aliases
2613 of :class:`_sql.Join` objects.
2615 :param flat: Boolean, will be passed through to the
2616 :meth:`_expression.FromClause.alias` call so that aliases of
2617 :class:`_expression.Join` objects will alias the individual tables
2618 inside the join, rather than creating a subquery. This is generally
2619 supported by all modern databases with regards to right-nested joins
2620 and generally produces more efficient queries.
2622 When :paramref:`_orm.aliased.flat` is combined with
2623 :paramref:`_orm.aliased.name`, the resulting joins will alias individual
2624 tables using a naming scheme similar to ``<prefix>_<tablename>``. This
2625 naming scheme is for visibility / debugging purposes only and the
2626 specific scheme is subject to change without notice.
2628 .. versionadded:: 2.0.32 added support for combining
2629 :paramref:`_orm.aliased.name` with :paramref:`_orm.aliased.flat`.
2630 Previously, this would raise ``NotImplementedError``.
2632 :param adapt_on_names: if True, more liberal "matching" will be used when
2633 mapping the mapped columns of the ORM entity to those of the
2634 given selectable - a name-based match will be performed if the
2635 given selectable doesn't otherwise have a column that corresponds
2636 to one on the entity. The use case for this is when associating
2637 an entity with some derived selectable such as one that uses
2638 aggregate functions::
2640 class UnitPrice(Base):
2641 __tablename__ = "unit_price"
2642 ...
2643 unit_id = Column(Integer)
2644 price = Column(Numeric)
2647 aggregated_unit_price = (
2648 Session.query(func.sum(UnitPrice.price).label("price"))
2649 .group_by(UnitPrice.unit_id)
2650 .subquery()
2651 )
2653 aggregated_unit_price = aliased(
2654 UnitPrice, alias=aggregated_unit_price, adapt_on_names=True
2655 )
2657 Above, functions on ``aggregated_unit_price`` which refer to
2658 ``.price`` will return the
2659 ``func.sum(UnitPrice.price).label('price')`` column, as it is
2660 matched on the name "price". Ordinarily, the "price" function
2661 wouldn't have any "column correspondence" to the actual
2662 ``UnitPrice.price`` column as it is not a proxy of the original.
2664 """
2665 return AliasedInsp._alias_factory(
2666 element,
2667 alias=alias,
2668 name=name,
2669 flat=flat,
2670 adapt_on_names=adapt_on_names,
2671 )
2674def with_polymorphic(
2675 base: Union[Type[_O], Mapper[_O]],
2676 classes: Union[Literal["*"], Iterable[Type[Any]]],
2677 selectable: Union[Literal[False, None], FromClause] = False,
2678 flat: bool = False,
2679 polymorphic_on: Optional[ColumnElement[Any]] = None,
2680 aliased: bool = False,
2681 innerjoin: bool = False,
2682 adapt_on_names: bool = False,
2683 name: Optional[str] = None,
2684 _use_mapper_path: bool = False,
2685) -> AliasedClass[_O]:
2686 """Produce an :class:`.AliasedClass` construct which specifies
2687 columns for descendant mappers of the given base.
2689 Using this method will ensure that each descendant mapper's
2690 tables are included in the FROM clause, and will allow filter()
2691 criterion to be used against those tables. The resulting
2692 instances will also have those columns already loaded so that
2693 no "post fetch" of those columns will be required.
2695 .. seealso::
2697 :ref:`with_polymorphic` - full discussion of
2698 :func:`_orm.with_polymorphic`.
2700 :param base: Base class to be aliased.
2702 :param classes: a single class or mapper, or list of
2703 class/mappers, which inherit from the base class.
2704 Alternatively, it may also be the string ``'*'``, in which case
2705 all descending mapped classes will be added to the FROM clause.
2707 :param aliased: when True, the selectable will be aliased. For a
2708 JOIN, this means the JOIN will be SELECTed from inside of a subquery
2709 unless the :paramref:`_orm.with_polymorphic.flat` flag is set to
2710 True, which is recommended for simpler use cases.
2712 :param flat: Boolean, will be passed through to the
2713 :meth:`_expression.FromClause.alias` call so that aliases of
2714 :class:`_expression.Join` objects will alias the individual tables
2715 inside the join, rather than creating a subquery. This is generally
2716 supported by all modern databases with regards to right-nested joins
2717 and generally produces more efficient queries. Setting this flag is
2718 recommended as long as the resulting SQL is functional.
2720 :param selectable: a table or subquery that will
2721 be used in place of the generated FROM clause. This argument is
2722 required if any of the desired classes use concrete table
2723 inheritance, since SQLAlchemy currently cannot generate UNIONs
2724 among tables automatically. If used, the ``selectable`` argument
2725 must represent the full set of tables and columns mapped by every
2726 mapped class. Otherwise, the unaccounted mapped columns will
2727 result in their table being appended directly to the FROM clause
2728 which will usually lead to incorrect results.
2730 When left at its default value of ``False``, the polymorphic
2731 selectable assigned to the base mapper is used for selecting rows.
2732 However, it may also be passed as ``None``, which will bypass the
2733 configured polymorphic selectable and instead construct an ad-hoc
2734 selectable for the target classes given; for joined table inheritance
2735 this will be a join that includes all target mappers and their
2736 subclasses.
2738 :param polymorphic_on: a column to be used as the "discriminator"
2739 column for the given selectable. If not given, the polymorphic_on
2740 attribute of the base classes' mapper will be used, if any. This
2741 is useful for mappings that don't have polymorphic loading
2742 behavior by default.
2744 :param innerjoin: if True, an INNER JOIN will be used. This should
2745 only be specified if querying for one specific subtype only
2747 :param adapt_on_names: Passes through the
2748 :paramref:`_orm.aliased.adapt_on_names`
2749 parameter to the aliased object. This may be useful in situations where
2750 the given selectable is not directly related to the existing mapped
2751 selectable.
2753 .. versionadded:: 1.4.33
2755 :param name: Name given to the generated :class:`.AliasedClass`.
2757 .. versionadded:: 2.0.31
2759 """
2760 return AliasedInsp._with_polymorphic_factory(
2761 base,
2762 classes,
2763 selectable=selectable,
2764 flat=flat,
2765 polymorphic_on=polymorphic_on,
2766 adapt_on_names=adapt_on_names,
2767 aliased=aliased,
2768 innerjoin=innerjoin,
2769 name=name,
2770 _use_mapper_path=_use_mapper_path,
2771 )
2774def join(
2775 left: _FromClauseArgument,
2776 right: _FromClauseArgument,
2777 onclause: Optional[_OnClauseArgument] = None,
2778 isouter: bool = False,
2779 full: bool = False,
2780) -> _ORMJoin:
2781 r"""Produce an inner join between left and right clauses.
2783 :func:`_orm.join` is an extension to the core join interface
2784 provided by :func:`_expression.join()`, where the
2785 left and right selectable may be not only core selectable
2786 objects such as :class:`_schema.Table`, but also mapped classes or
2787 :class:`.AliasedClass` instances. The "on" clause can
2788 be a SQL expression or an ORM mapped attribute
2789 referencing a configured :func:`_orm.relationship`.
2791 :func:`_orm.join` is not commonly needed in modern usage,
2792 as its functionality is encapsulated within that of the
2793 :meth:`_sql.Select.join` and :meth:`_query.Query.join`
2794 methods. which feature a
2795 significant amount of automation beyond :func:`_orm.join`
2796 by itself. Explicit use of :func:`_orm.join`
2797 with ORM-enabled SELECT statements involves use of the
2798 :meth:`_sql.Select.select_from` method, as in::
2800 from sqlalchemy.orm import join
2802 stmt = (
2803 select(User)
2804 .select_from(join(User, Address, User.addresses))
2805 .filter(Address.email_address == "foo@bar.com")
2806 )
2808 In modern SQLAlchemy the above join can be written more
2809 succinctly as::
2811 stmt = (
2812 select(User)
2813 .join(User.addresses)
2814 .filter(Address.email_address == "foo@bar.com")
2815 )
2817 .. warning:: using :func:`_orm.join` directly may not work properly
2818 with modern ORM options such as :func:`_orm.with_loader_criteria`.
2819 It is strongly recommended to use the idiomatic join patterns
2820 provided by methods such as :meth:`.Select.join` and
2821 :meth:`.Select.join_from` when creating ORM joins.
2823 .. seealso::
2825 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel` for
2826 background on idiomatic ORM join patterns
2828 """
2829 return _ORMJoin(left, right, onclause, isouter, full)
2832def outerjoin(
2833 left: _FromClauseArgument,
2834 right: _FromClauseArgument,
2835 onclause: Optional[_OnClauseArgument] = None,
2836 full: bool = False,
2837) -> _ORMJoin:
2838 """Produce a left outer join between left and right clauses.
2840 This is the "outer join" version of the :func:`_orm.join` function,
2841 featuring the same behavior except that an OUTER JOIN is generated.
2842 See that function's documentation for other usage details.
2844 """
2845 return _ORMJoin(left, right, onclause, True, full)