ORM Internals¶
Key ORM constructs, not otherwise covered in other sections, are listed here.
Object Name | Description |
---|---|
A token propagated throughout the course of a chain of attribute events. |
|
Provide an inspection interface corresponding to a particular attribute on a particular mapped object. |
|
Keeps track of the options sent to
|
|
Tracks state information at the class level. |
|
Describes an object attribute that corresponds to a table column or other column expression. |
|
Declarative-compatible front-end for the |
|
Defines a “composite” mapped attribute, representing a collection of columns as one attribute. |
|
A base class applied to all ORM objects and attributes that are
related to things that can be returned by the |
|
Symbols indicating the type of extension that a
|
|
Adds the |
|
tracks state information at the instance level. |
|
Base class for descriptor objects that intercept
attribute events on behalf of a |
|
An enumeration. |
|
Represent an ORM mapped attribute on a mapped class. |
|
Maps a single |
|
Declarative front-end for the |
|
Represent a particular class attribute mapped by |
|
merge_frozen_result(session, statement, frozen_result[, load]) |
Merge a |
merge_result(query, iterator[, load]) |
Merge a result into the given |
An enumeration. |
|
Defines SQL operations for ORM mapped attributes. |
|
Base class for descriptor objects that intercept
attribute events on behalf of a |
|
Describes an object property that holds a single item or list of items that correspond to a related database table. |
|
enumeration which indicates the ‘direction’ of a
|
|
Describes an object property that holds a single item or list of items that correspond to a related database table. |
|
A type that may be used to indicate any ORM-level attribute or object that acts in place of one, in the context of SQL expression construction. |
|
Declarative front-end for the |
|
Denote an attribute name as a synonym to a mapped property, in that the attribute will mirror the value and expression behavior of another attribute. |
|
- class sqlalchemy.orm.AttributeState¶
Provide an inspection interface corresponding to a particular attribute on a particular mapped object.
The
AttributeState
object is accessed via theInstanceState.attrs
collection of a particularInstanceState
:Members
from sqlalchemy import inspect insp = inspect(some_mapped_object) attr_state = insp.attrs.some_attribute
-
attribute
sqlalchemy.orm.AttributeState.
history¶ Return the current pre-flush change history for this attribute, via the
History
interface.This method will not emit loader callables if the value of the attribute is unloaded.
Note
The attribute history system tracks changes on a per flush basis. Each time the
Session
is flushed, the history of each attribute is reset to empty. TheSession
by default autoflushes each time aQuery
is invoked. For options on how to control this, see Flushing.See also
AttributeState.load_history()
- retrieve history using loader callables if the value is not locally present.get_history()
- underlying function
-
method
sqlalchemy.orm.AttributeState.
load_history() History ¶ Return the current pre-flush change history for this attribute, via the
History
interface.This method will emit loader callables if the value of the attribute is unloaded.
-
attribute
sqlalchemy.orm.AttributeState.
loaded_value¶ The current value of this attribute as loaded from the database.
If the value has not been loaded, or is otherwise not present in the object’s dictionary, returns NO_VALUE.
-
attribute
sqlalchemy.orm.AttributeState.
value¶ Return the value of this attribute.
This operation is equivalent to accessing the object’s attribute directly or via
getattr()
, and will fire off any pending loader callables if needed.
-
attribute
- class sqlalchemy.orm.CascadeOptions¶
Keeps track of the options sent to
relationship.cascade
Class signature
class
sqlalchemy.orm.CascadeOptions
(builtins.frozenset
,typing.Generic
)
- class sqlalchemy.orm.ClassManager¶
Tracks state information at the class level.
Members
deferred_scalar_loader, expired_attribute_loader, has_parent(), manage(), state_getter(), unregister()
Class signature
class
sqlalchemy.orm.ClassManager
(sqlalchemy.util.langhelpers.HasMemoized
,builtins.dict
,typing.Generic
,sqlalchemy.event.registry.EventTarget
)-
attribute
sqlalchemy.orm.ClassManager.
deferred_scalar_loader¶ Deprecated since version 1.4: The ClassManager.deferred_scalar_loader attribute is now named expired_attribute_loader
-
attribute
sqlalchemy.orm.ClassManager.
expired_attribute_loader: _ExpiredAttributeLoaderProto¶ previously known as deferred_scalar_loader
-
method
sqlalchemy.orm.ClassManager.
has_parent(state: InstanceState[_O], key: str, optimistic: bool = False) bool ¶ TODO
-
method
sqlalchemy.orm.ClassManager.
manage()¶ Mark this instance as the manager for its class.
-
method
sqlalchemy.orm.ClassManager.
state_getter()¶ Return a (instance) -> InstanceState callable.
“state getter” callables should raise either KeyError or AttributeError if no InstanceState could be found for the instance.
-
method
sqlalchemy.orm.ClassManager.
unregister() None ¶ remove all instrumentation established by this ClassManager.
-
attribute
- class sqlalchemy.orm.ColumnProperty¶
Describes an object attribute that corresponds to a table column or other column expression.
Public constructor is the
column_property()
function.Members
expressions, operate(), reverse_operate(), columns_to_assign, declarative_scan(), do_init(), expression, instrument_class(), mapper_property_to_assign, merge()
Class signature
class
sqlalchemy.orm.ColumnProperty
(sqlalchemy.orm._MapsColumns
,sqlalchemy.orm.StrategizedProperty
,sqlalchemy.orm._IntrospectsAnnotations
,sqlalchemy.log.Identified
)- class Comparator¶
Produce boolean, comparison, and other operators for
ColumnProperty
attributes.See the documentation for
PropComparator
for a brief overview.Class signature
class
sqlalchemy.orm.ColumnProperty.Comparator
(sqlalchemy.util.langhelpers.MemoizedSlots
,sqlalchemy.orm.PropComparator
)-
attribute
sqlalchemy.orm.ColumnProperty.Comparator.
expressions: Sequence[NamedColumn[Any]]¶ - The full sequence of columns referenced by this
attribute, adjusted for any aliasing in progress.
New in version 1.3.17.
See also
Mapping a Class against Multiple Tables - usage example
-
method
sqlalchemy.orm.ColumnProperty.Comparator.
operate(op: OperatorType, *other: Any, **kwargs: Any) ColumnElement[Any] ¶ Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding
ColumnOperators
to applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs)
- Parameters:
op – Operator callable.
*other – the ‘other’ side of the operation. Will be a single scalar for most operations.
**kwargs – modifiers. These may be passed by special operators such as
ColumnOperators.contains()
.
-
method
sqlalchemy.orm.ColumnProperty.Comparator.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) ColumnElement[Any] ¶ Reverse operate on an argument.
Usage is the same as
operate()
.
-
attribute
-
attribute
sqlalchemy.orm.ColumnProperty.
columns_to_assign¶
-
method
sqlalchemy.orm.ColumnProperty.
declarative_scan(decl_scan: _ClassScanMapperConfig, registry: _RegistryType, cls: Type[Any], originating_module: Optional[str], key: str, mapped_container: Optional[Type[Mapped[Any]]], annotation: Optional[_AnnotationScanType], extracted_mapped_annotation: Optional[_AnnotationScanType], is_dataclass_field: bool) None ¶ Perform class-specific initializaton at early declarative scanning time.
New in version 2.0.
-
method
sqlalchemy.orm.ColumnProperty.
do_init() None ¶ Perform subclass-specific initialization post-mapper-creation steps.
This is a template method called by the
MapperProperty
object’s init() method.
-
attribute
sqlalchemy.orm.ColumnProperty.
expression¶ Return the primary column or expression for this ColumnProperty.
E.g.:
class File(Base): # ... name = Column(String(64)) extension = Column(String(8)) filename = column_property(name + '.' + extension) path = column_property('C:/' + filename.expression)
-
method
sqlalchemy.orm.ColumnProperty.
instrument_class(mapper: Mapper[Any]) None ¶ Hook called by the Mapper to the property to initiate instrumentation of the class attribute managed by this MapperProperty.
The MapperProperty here will typically call out to the attributes module to set up an InstrumentedAttribute.
This step is the first of two steps to set up an InstrumentedAttribute, and is called early in the mapper setup process.
The second step is typically the init_class_attribute step, called from StrategizedProperty via the post_instrument_class() hook. This step assigns additional state to the InstrumentedAttribute (specifically the “impl”) which has been determined after the MapperProperty has determined what kind of persistence management it needs to do (e.g. scalar, object, collection, etc).
-
attribute
sqlalchemy.orm.ColumnProperty.
mapper_property_to_assign¶
-
method
sqlalchemy.orm.ColumnProperty.
merge(session: Session, source_state: InstanceState[Any], source_dict: _InstanceDict, dest_state: InstanceState[Any], dest_dict: _InstanceDict, load: bool, _recursive: Dict[Any, object], _resolve_conflict_map: Dict[_IdentityKeyType[Any], object]) None ¶ Merge the attribute represented by this
MapperProperty
from source to destination object.
- class sqlalchemy.orm.Composite¶
Declarative-compatible front-end for the
CompositeProperty
class.Public constructor is the
composite()
function.Changed in version 2.0: Added
Composite
as a Declarative compatible subclass ofCompositeProperty
.See also
Class signature
class
sqlalchemy.orm.Composite
(sqlalchemy.orm.descriptor_props.CompositeProperty
,sqlalchemy.orm.base._DeclarativeMapped
)
- class sqlalchemy.orm.CompositeProperty¶
Defines a “composite” mapped attribute, representing a collection of columns as one attribute.
CompositeProperty
is constructed using thecomposite()
function.See also
Members
create_row_processor(), columns_to_assign, declarative_scan(), do_init(), get_history(), instrument_class(), mapper_property_to_assign
Class signature
class
sqlalchemy.orm.CompositeProperty
(sqlalchemy.orm._MapsColumns
,sqlalchemy.orm._IntrospectsAnnotations
,sqlalchemy.orm.descriptor_props.DescriptorProperty
)- class Comparator¶
Produce boolean, comparison, and other operators for
Composite
attributes.See the example in Redefining Comparison Operations for Composites for an overview of usage , as well as the documentation for
PropComparator
.Class signature
class
sqlalchemy.orm.CompositeProperty.Comparator
(sqlalchemy.orm.PropComparator
)
- class CompositeBundle¶
Class signature
class
sqlalchemy.orm.CompositeProperty.CompositeBundle
(sqlalchemy.orm.Bundle
)-
method
sqlalchemy.orm.CompositeProperty.CompositeBundle.
create_row_processor(query: Select[Any], procs: Sequence[Callable[[Row[Any]], Any]], labels: Sequence[str]) Callable[[Row[Any]], Any] ¶ Produce the “row processing” function for this
Bundle
.May be overridden by subclasses to provide custom behaviors when results are fetched. The method is passed the statement object and a set of “row processor” functions at query execution time; these processor functions when given a result row will return the individual attribute value, which can then be adapted into any kind of return data structure.
The example below illustrates replacing the usual
Row
return structure with a straight Python dictionary:from sqlalchemy.orm import Bundle class DictBundle(Bundle): def create_row_processor(self, query, procs, labels): 'Override create_row_processor to return values as dictionaries' def proc(row): return dict( zip(labels, (proc(row) for proc in procs)) ) return proc
A result from the above
Bundle
will return dictionary values:bn = DictBundle('mybundle', MyClass.data1, MyClass.data2) for row in session.execute(select(bn)).where(bn.c.data1 == 'd1'): print(row.mybundle['data1'], row.mybundle['data2'])
-
method
-
attribute
sqlalchemy.orm.CompositeProperty.
columns_to_assign¶
-
method
sqlalchemy.orm.CompositeProperty.
declarative_scan(decl_scan: _ClassScanMapperConfig, registry: _RegistryType, cls: Type[Any], originating_module: Optional[str], key: str, mapped_container: Optional[Type[Mapped[Any]]], annotation: Optional[_AnnotationScanType], extracted_mapped_annotation: Optional[_AnnotationScanType], is_dataclass_field: bool) None ¶ Perform class-specific initializaton at early declarative scanning time.
New in version 2.0.
-
method
sqlalchemy.orm.CompositeProperty.
do_init() None ¶ Initialization which occurs after the
Composite
has been associated with its parent mapper.
-
method
sqlalchemy.orm.CompositeProperty.
get_history(state: InstanceState[Any], dict_: _InstanceDict, passive: PassiveFlag = symbol('PASSIVE_OFF')) History ¶ Provided for userland code that uses attributes.get_history().
-
method
sqlalchemy.orm.CompositeProperty.
instrument_class(mapper: Mapper[Any]) None ¶ Hook called by the Mapper to the property to initiate instrumentation of the class attribute managed by this MapperProperty.
The MapperProperty here will typically call out to the attributes module to set up an InstrumentedAttribute.
This step is the first of two steps to set up an InstrumentedAttribute, and is called early in the mapper setup process.
The second step is typically the init_class_attribute step, called from StrategizedProperty via the post_instrument_class() hook. This step assigns additional state to the InstrumentedAttribute (specifically the “impl”) which has been determined after the MapperProperty has determined what kind of persistence management it needs to do (e.g. scalar, object, collection, etc).
-
attribute
sqlalchemy.orm.CompositeProperty.
mapper_property_to_assign¶
- class sqlalchemy.orm.AttributeEventToken¶
A token propagated throughout the course of a chain of attribute events.
Serves as an indicator of the source of the event and also provides a means of controlling propagation across a chain of attribute operations.
The
Event
object is sent as theinitiator
argument when dealing with events such asAttributeEvents.append()
,AttributeEvents.set()
, andAttributeEvents.remove()
.The
Event
object is currently interpreted by the backref event handlers, and is used to control the propagation of operations across two mutually-dependent attributes.Changed in version 2.0: Changed the name from
AttributeEvent
toAttributeEventToken
.- Attribute impl:
The
AttributeImpl
which is the current event initiator.- Attribute op:
The symbol
OP_APPEND
,OP_REMOVE
,OP_REPLACE
, orOP_BULK_REPLACE
, indicating the source operation.
- class sqlalchemy.orm.IdentityMap¶
Members
-
method
sqlalchemy.orm.IdentityMap.
check_modified() bool ¶ return True if any InstanceStates present have been marked as ‘modified’.
-
method
- class sqlalchemy.orm.InspectionAttr¶
A base class applied to all ORM objects and attributes that are related to things that can be returned by the
inspect()
function.The attributes defined here allow the usage of simple boolean checks to test basic facts about the object returned.
Members
extension_type, is_aliased_class, is_attribute, is_bundle, is_clause_element, is_instance, is_mapper, is_property, is_selectable
While the boolean checks here are basically the same as using the Python isinstance() function, the flags here can be used without the need to import all of these classes, and also such that the SQLAlchemy class system can change while leaving the flags here intact for forwards-compatibility.
-
attribute
sqlalchemy.orm.InspectionAttr.
extension_type: InspectionAttrExtensionType = 'not_extension'¶ The extension type, if any. Defaults to
NotExtension.NOT_EXTENSION
-
attribute
sqlalchemy.orm.InspectionAttr.
is_aliased_class = False¶ True if this object is an instance of
AliasedClass
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_attribute = False¶ True if this object is a Python descriptor.
This can refer to one of many types. Usually a
QueryableAttribute
which handles attributes events on behalf of aMapperProperty
. But can also be an extension type such asAssociationProxy
orhybrid_property
. TheInspectionAttr.extension_type
will refer to a constant identifying the specific subtype.See also
-
attribute
sqlalchemy.orm.InspectionAttr.
is_bundle = False¶ True if this object is an instance of
Bundle
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_clause_element = False¶ True if this object is an instance of
ClauseElement
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_instance = False¶ True if this object is an instance of
InstanceState
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_mapper = False¶ True if this object is an instance of
Mapper
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_property = False¶ True if this object is an instance of
MapperProperty
.
-
attribute
sqlalchemy.orm.InspectionAttr.
is_selectable = False¶ Return True if this object is an instance of
Selectable
.
-
attribute
- class sqlalchemy.orm.InspectionAttrInfo¶
Adds the
.info
attribute toInspectionAttr
.The rationale for
InspectionAttr
vs.InspectionAttrInfo
is that the former is compatible as a mixin for classes that specify__slots__
; this is essentially an implementation artifact.Members
Class signature
class
sqlalchemy.orm.InspectionAttrInfo
(sqlalchemy.orm.base.InspectionAttr
)-
attribute
sqlalchemy.orm.InspectionAttrInfo.
info¶ Info dictionary associated with the object, allowing user-defined data to be associated with this
InspectionAttr
.The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the
column_property()
,relationship()
, orcomposite()
functions.
-
attribute
- class sqlalchemy.orm.InstanceState¶
tracks state information at the instance level.
The
InstanceState
is a key object used by the SQLAlchemy ORM in order to track the state of an object; it is created the moment an object is instantiated, typically as a result of instrumentation which SQLAlchemy applies to the__init__()
method of the class.InstanceState
is also a semi-public object, available for runtime inspection as to the state of a mapped instance, including information such as its current status within a particularSession
and details about data on individual attributes. The public API in order to acquire aInstanceState
object is to use theinspect()
system:>>> from sqlalchemy import inspect >>> insp = inspect(some_mapped_object) >>> insp.attrs.nickname.history History(added=['new nickname'], unchanged=(), deleted=['nickname'])
See also
Members
async_session, attrs, callables, deleted, detached, dict, expired_attributes, has_identity, identity, identity_key, is_instance, mapper, object, pending, persistent, session, transient, unloaded, unloaded_expirable, unmodified, unmodified_intersection(), was_deleted
Class signature
class
sqlalchemy.orm.InstanceState
(sqlalchemy.orm.base.InspectionAttrInfo
,typing.Generic
)-
attribute
sqlalchemy.orm.InstanceState.
async_session¶ Return the owning
AsyncSession
for this instance, orNone
if none available.This attribute is only non-None when the
sqlalchemy.ext.asyncio
API is in use for this ORM object. The returnedAsyncSession
object will be a proxy for theSession
object that would be returned from theInstanceState.session
attribute for thisInstanceState
.New in version 1.4.18.
See also
-
attribute
sqlalchemy.orm.InstanceState.
attrs¶ Return a namespace representing each attribute on the mapped object, including its current value and history.
The returned object is an instance of
AttributeState
. This object allows inspection of the current data within an attribute as well as attribute history since the last flush.
-
attribute
sqlalchemy.orm.InstanceState.
callables: Dict[str, Callable[[InstanceState[_O], PassiveFlag], Any]] = {}¶ A namespace where a per-state loader callable can be associated.
In SQLAlchemy 1.0, this is only used for lazy loaders / deferred loaders that were set up via query option.
Previously, callables was used also to indicate expired attributes by storing a link to the InstanceState itself in this dictionary. This role is now handled by the expired_attributes set.
-
attribute
sqlalchemy.orm.InstanceState.
deleted¶ Return
True
if the object is deleted.An object that is in the deleted state is guaranteed to not be within the
Session.identity_map
of its parentSession
; however if the session’s transaction is rolled back, the object will be restored to the persistent state and the identity map.Note
The
InstanceState.deleted
attribute refers to a specific state of the object that occurs between the “persistent” and “detached” states; once the object is detached, theInstanceState.deleted
attribute no longer returns True; in order to detect that a state was deleted, regardless of whether or not the object is associated with aSession
, use theInstanceState.was_deleted
accessor.See also
-
attribute
sqlalchemy.orm.InstanceState.
detached¶ Return
True
if the object is detached.See also
-
attribute
sqlalchemy.orm.InstanceState.
dict¶ Return the instance dict used by the object.
Under normal circumstances, this is always synonymous with the
__dict__
attribute of the mapped object, unless an alternative instrumentation system has been configured.In the case that the actual object has been garbage collected, this accessor returns a blank dictionary.
-
attribute
sqlalchemy.orm.InstanceState.
expired_attributes: Set[str]¶ The set of keys which are ‘expired’ to be loaded by the manager’s deferred scalar loader, assuming no pending changes.
see also the
unmodified
collection which is intersected against this set when a refresh operation occurs.
-
attribute
sqlalchemy.orm.InstanceState.
has_identity¶ Return
True
if this object has an identity key.This should always have the same value as the expression
state.persistent
orstate.detached
.
-
attribute
sqlalchemy.orm.InstanceState.
identity¶ Return the mapped identity of the mapped object. This is the primary key identity as persisted by the ORM which can always be passed directly to
Query.get()
.Returns
None
if the object has no primary key identity.
-
attribute
sqlalchemy.orm.InstanceState.
identity_key¶ Return the identity key for the mapped object.
This is the key used to locate the object within the
Session.identity_map
mapping. It contains the identity as returned byidentity
within it.
-
attribute
sqlalchemy.orm.InstanceState.
is_instance: bool = True¶ True if this object is an instance of
InstanceState
.
-
attribute
sqlalchemy.orm.InstanceState.
mapper¶ Return the
Mapper
used for this mapped object.
-
attribute
sqlalchemy.orm.InstanceState.
object¶ Return the mapped object represented by this
InstanceState
.Returns None if the object has been garbage collected
-
attribute
sqlalchemy.orm.InstanceState.
pending¶ Return
True
if the object is pending.See also
-
attribute
sqlalchemy.orm.InstanceState.
persistent¶ Return
True
if the object is persistent.An object that is in the persistent state is guaranteed to be within the
Session.identity_map
of its parentSession
.See also
-
attribute
sqlalchemy.orm.InstanceState.
session¶ Return the owning
Session
for this instance, orNone
if none available.Note that the result here can in some cases be different from that of
obj in session
; an object that’s been deleted will report as notin session
, however if the transaction is still in progress, this attribute will still refer to that session. Only when the transaction is completed does the object become fully detached under normal circumstances.See also
-
attribute
sqlalchemy.orm.InstanceState.
transient¶ Return
True
if the object is transient.See also
-
attribute
sqlalchemy.orm.InstanceState.
unloaded¶ Return the set of keys which do not have a loaded value.
This includes expired attributes and any other attribute that was never populated or modified.
-
attribute
sqlalchemy.orm.InstanceState.
unloaded_expirable¶ Synonymous with
InstanceState.unloaded
.Deprecated since version 2.0: The
InstanceState.unloaded_expirable
attribute is deprecated. Please useInstanceState.unloaded
.This attribute was added as an implementation-specific detail at some point and should be considered to be private.
-
attribute
sqlalchemy.orm.InstanceState.
unmodified¶ Return the set of keys which have no uncommitted changes
-
method
sqlalchemy.orm.InstanceState.
unmodified_intersection(keys: Iterable[str]) Set[str] ¶ Return self.unmodified.intersection(keys).
-
attribute
sqlalchemy.orm.InstanceState.
was_deleted¶ Return True if this object is or was previously in the “deleted” state and has not been reverted to persistent.
This flag returns True once the object was deleted in flush. When the object is expunged from the session either explicitly or via transaction commit and enters the “detached” state, this flag will continue to report True.
-
attribute
- class sqlalchemy.orm.InstrumentedAttribute¶
Base class for descriptor objects that intercept attribute events on behalf of a
MapperProperty
object. The actualMapperProperty
is accessible via theQueryableAttribute.property
attribute.Class signature
class
sqlalchemy.orm.InstrumentedAttribute
(sqlalchemy.orm.QueryableAttribute
)
- class sqlalchemy.orm.LoaderCallableStatus¶
An enumeration.
Class signature
class
sqlalchemy.orm.LoaderCallableStatus
(enum.Enum
)-
attribute
sqlalchemy.orm.LoaderCallableStatus.
ATTR_EMPTY = 3¶ Symbol used internally to indicate an attribute had no callable.
-
attribute
sqlalchemy.orm.LoaderCallableStatus.
ATTR_WAS_SET = 2¶ Symbol returned by a loader callable to indicate the retrieved value, or values, were assigned to their attributes on the target object.
-
attribute
sqlalchemy.orm.LoaderCallableStatus.
NEVER_SET = 4¶ Synonymous with NO_VALUE
Changed in version 1.4: NEVER_SET was merged with NO_VALUE
-
attribute
sqlalchemy.orm.LoaderCallableStatus.
NO_VALUE = 4¶ Symbol which may be placed as the ‘previous’ value of an attribute, indicating no value was loaded for an attribute when it was modified, and flags indicated we were not to load it.
-
attribute
sqlalchemy.orm.LoaderCallableStatus.
PASSIVE_CLASS_MISMATCH = 1¶ Symbol indicating that an object is locally present for a given primary key identity but it is not of the requested class. The return value is therefore None and no SQL should be emitted.
-
attribute
sqlalchemy.orm.LoaderCallableStatus.
PASSIVE_NO_RESULT = 0¶ Symbol returned by a loader callable or other attribute/history retrieval operation when a value could not be determined, based on loader callable flags.
-
attribute
- class sqlalchemy.orm.Mapped¶
Represent an ORM mapped attribute on a mapped class.
This class represents the complete descriptor interface for any class attribute that will have been instrumented by the ORM
Mapper
class. Provides appropriate information to type checkers such as pylance and mypy so that ORM-mapped attributes are correctly typed.The most prominent use of
Mapped
is in the Declarative Mapping form ofMapper
configuration, where used explicitly it drives the configuration of ORM attributes such asmapped_class()
andrelationship()
.Tip
The
Mapped
class represents attributes that are handled directly by theMapper
class. It does not include other Python descriptor classes that are provided as extensions, including Hybrid Attributes and the Association Proxy. While these systems still make use of ORM-specific superclasses and structures, they are not instrumented by theMapper
and instead provide their own functionality when they are accessed on a class.New in version 1.4.
Class signature
class
sqlalchemy.orm.Mapped
(sqlalchemy.orm.base.SQLORMExpression
,sqlalchemy.orm.base.ORMDescriptor
,sqlalchemy.orm.base._MappedAnnotationBase
,sqlalchemy.sql.roles.DDLConstraintColumnRole
)
- class sqlalchemy.orm.MappedColumn¶
Maps a single
Column
on a class.MappedColumn
is a specialization of theColumnProperty
class and is oriented towards declarative configuration.To construct
MappedColumn
objects, use themapped_column()
constructor function.New in version 2.0.
Class signature
class
sqlalchemy.orm.MappedColumn
(sqlalchemy.orm._IntrospectsAnnotations
,sqlalchemy.orm._MapsColumns
,sqlalchemy.orm.base._DeclarativeMapped
)
- class sqlalchemy.orm.MapperProperty¶
Represent a particular class attribute mapped by
Mapper
.The most common occurrences of
MapperProperty
are the mappedColumn
, which is represented in a mapping as an instance ofColumnProperty
, and a reference to another class produced byrelationship()
, represented in the mapping as an instance ofRelationship
.Members
cascade_iterator(), class_attribute, comparator, create_row_processor(), do_init(), doc, info, init(), instrument_class(), is_property, key, merge(), parent, post_instrument_class(), set_parent(), setup()
Class signature
class
sqlalchemy.orm.MapperProperty
(sqlalchemy.sql.cache_key.HasCacheKey
,sqlalchemy.orm._DCAttributeOptions
,sqlalchemy.orm.base._MappedAttribute
,sqlalchemy.orm.base.InspectionAttrInfo
,sqlalchemy.util.langhelpers.MemoizedSlots
)-
method
sqlalchemy.orm.MapperProperty.
cascade_iterator(type_: str, state: InstanceState[Any], dict_: _InstanceDict, visited_states: Set[InstanceState[Any]], halt_on: Optional[Callable[[InstanceState[Any]], bool]] = None) Iterator[Tuple[object, Mapper[Any], InstanceState[Any], _InstanceDict]] ¶ Iterate through instances related to the given instance for a particular ‘cascade’, starting with this MapperProperty.
Return an iterator3-tuples (instance, mapper, state).
Note that the ‘cascade’ collection on this MapperProperty is checked first for the given type before cascade_iterator is called.
This method typically only applies to Relationship.
-
attribute
sqlalchemy.orm.MapperProperty.
class_attribute¶ Return the class-bound descriptor corresponding to this
MapperProperty
.This is basically a
getattr()
call:return getattr(self.parent.class_, self.key)
I.e. if this
MapperProperty
were namedaddresses
, and the class to which it is mapped isUser
, this sequence is possible:>>> from sqlalchemy import inspect >>> mapper = inspect(User) >>> addresses_property = mapper.attrs.addresses >>> addresses_property.class_attribute is User.addresses True >>> User.addresses.property is addresses_property True
-
attribute
sqlalchemy.orm.MapperProperty.
comparator: PropComparator[_T]¶ The
PropComparator
instance that implements SQL expression construction on behalf of this mapped attribute.
-
method
sqlalchemy.orm.MapperProperty.
create_row_processor(context: ORMCompileState, query_entity: _MapperEntity, path: AbstractEntityRegistry, mapper: Mapper[Any], result: Result[Any], adapter: Optional[ORMAdapter], populators: _PopulatorDict) None ¶ Produce row processing functions and append to the given set of populators lists.
-
method
sqlalchemy.orm.MapperProperty.
do_init() None ¶ Perform subclass-specific initialization post-mapper-creation steps.
This is a template method called by the
MapperProperty
object’s init() method.
-
attribute
sqlalchemy.orm.MapperProperty.
doc: Optional[str]¶ optional documentation string
-
attribute
sqlalchemy.orm.MapperProperty.
info: _InfoType¶ Info dictionary associated with the object, allowing user-defined data to be associated with this
InspectionAttr
.The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the
column_property()
,relationship()
, orcomposite()
functions.
-
method
sqlalchemy.orm.MapperProperty.
init() None ¶ Called after all mappers are created to assemble relationships between mappers and perform other post-mapper-creation initialization steps.
-
method
sqlalchemy.orm.MapperProperty.
instrument_class(mapper: Mapper[Any]) None ¶ Hook called by the Mapper to the property to initiate instrumentation of the class attribute managed by this MapperProperty.
The MapperProperty here will typically call out to the attributes module to set up an InstrumentedAttribute.
This step is the first of two steps to set up an InstrumentedAttribute, and is called early in the mapper setup process.
The second step is typically the init_class_attribute step, called from StrategizedProperty via the post_instrument_class() hook. This step assigns additional state to the InstrumentedAttribute (specifically the “impl”) which has been determined after the MapperProperty has determined what kind of persistence management it needs to do (e.g. scalar, object, collection, etc).
-
attribute
sqlalchemy.orm.MapperProperty.
is_property = True¶ Part of the InspectionAttr interface; states this object is a mapper property.
-
attribute
sqlalchemy.orm.MapperProperty.
key: str¶ name of class attribute
-
method
sqlalchemy.orm.MapperProperty.
merge(session: Session, source_state: InstanceState[Any], source_dict: _InstanceDict, dest_state: InstanceState[Any], dest_dict: _InstanceDict, load: bool, _recursive: Dict[Any, object], _resolve_conflict_map: Dict[_IdentityKeyType[Any], object]) None ¶ Merge the attribute represented by this
MapperProperty
from source to destination object.
-
attribute
sqlalchemy.orm.MapperProperty.
parent: Mapper[Any]¶ the
Mapper
managing this property.
-
method
sqlalchemy.orm.MapperProperty.
post_instrument_class(mapper: Mapper[Any]) None ¶ Perform instrumentation adjustments that need to occur after init() has completed.
The given Mapper is the Mapper invoking the operation, which may not be the same Mapper as self.parent in an inheritance scenario; however, Mapper will always at least be a sub-mapper of self.parent.
This method is typically used by StrategizedProperty, which delegates it to LoaderStrategy.init_class_attribute() to perform final setup on the class-bound InstrumentedAttribute.
-
method
sqlalchemy.orm.MapperProperty.
set_parent(parent: Mapper[Any], init: bool) None ¶ Set the parent mapper that references this MapperProperty.
This method is overridden by some subclasses to perform extra setup when the mapper is first known.
-
method
sqlalchemy.orm.MapperProperty.
setup(context: ORMCompileState, query_entity: _MapperEntity, path: AbstractEntityRegistry, adapter: Optional[ORMAdapter], **kwargs: Any) None ¶ Called by Query for the purposes of constructing a SQL statement.
Each MapperProperty associated with the target mapper processes the statement referenced by the query context, adding columns and/or criterion as appropriate.
-
method
- class sqlalchemy.orm.MappedSQLExpression¶
Declarative front-end for the
ColumnProperty
class.Public constructor is the
column_property()
function.Changed in version 2.0: Added
MappedSQLExpression
as a Declarative compatible subclass forColumnProperty
.See also
Class signature
class
sqlalchemy.orm.MappedSQLExpression
(sqlalchemy.orm.properties.ColumnProperty
,sqlalchemy.orm.base._DeclarativeMapped
)
- class sqlalchemy.orm.InspectionAttrExtensionType¶
Symbols indicating the type of extension that a
InspectionAttr
is part of.Class signature
class
sqlalchemy.orm.InspectionAttrExtensionType
(enum.Enum
)
- class sqlalchemy.orm.NotExtension¶
An enumeration.
Members
Class signature
class
sqlalchemy.orm.NotExtension
(sqlalchemy.orm.base.InspectionAttrExtensionType
)-
attribute
sqlalchemy.orm.NotExtension.
NOT_EXTENSION = 'not_extension'¶ Symbol indicating an
InspectionAttr
that’s not part of sqlalchemy.ext.Is assigned to the
InspectionAttr.extension_type
attribute.
-
attribute
- function sqlalchemy.orm.merge_result(query: Query[Any], iterator: Union[FrozenResult, Iterable[Sequence[Any]], Iterable[object]], load: bool = True) Union[FrozenResult, Iterable[Any]] ¶
Merge a result into the given
Query
object’s Session.Deprecated since version 2.0: The
merge_result()
function is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The function as well as the method onQuery
is superseded by themerge_frozen_result()
function. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 - Major Migration Guide)See
Query.merge_result()
for top-level documentation on this function.
- function sqlalchemy.orm.merge_frozen_result(session, statement, frozen_result, load=True)¶
Merge a
FrozenResult
back into aSession
, returning a newResult
object with persistent objects.See the section Re-Executing Statements for an example.
- class sqlalchemy.orm.PropComparator¶
Defines SQL operations for ORM mapped attributes.
SQLAlchemy allows for operators to be redefined at both the Core and ORM level.
PropComparator
is the base class of operator redefinition for ORM-level operations, including those ofColumnProperty
,Relationship
, andComposite
.User-defined subclasses of
PropComparator
may be created. The built-in Python comparison and math operator methods, such asColumnOperators.__eq__()
,ColumnOperators.__lt__()
, andColumnOperators.__add__()
, can be overridden to provide new operator behavior. The customPropComparator
is passed to theMapperProperty
instance via thecomparator_factory
argument. In each case, the appropriate subclass ofPropComparator
should be used:# definition of custom PropComparator subclasses from sqlalchemy.orm.properties import \ ColumnProperty,\ Composite,\ Relationship class MyColumnComparator(ColumnProperty.Comparator): def __eq__(self, other): return self.__clause_element__() == other class MyRelationshipComparator(Relationship.Comparator): def any(self, expression): "define the 'any' operation" # ... class MyCompositeComparator(Composite.Comparator): def __gt__(self, other): "redefine the 'greater than' operation" return sql.and_(*[a>b for a, b in zip(self.__clause_element__().clauses, other.__composite_values__())]) # application of custom PropComparator subclasses from sqlalchemy.orm import column_property, relationship, composite from sqlalchemy import Column, String class SomeMappedClass(Base): some_column = column_property(Column("some_column", String), comparator_factory=MyColumnComparator) some_relationship = relationship(SomeOtherClass, comparator_factory=MyRelationshipComparator) some_composite = composite( Column("a", String), Column("b", String), comparator_factory=MyCompositeComparator )
Note that for column-level operator redefinition, it’s usually simpler to define the operators at the Core level, using the
TypeEngine.comparator_factory
attribute. See Redefining and Creating New Operators for more detail.Members
__eq__(), __le__(), __lt__(), __ne__(), adapt_to_entity(), adapter, all_(), and_(), any(), any_(), asc(), between(), bitwise_and(), bitwise_lshift(), bitwise_not(), bitwise_or(), bitwise_rshift(), bitwise_xor(), bool_op(), collate(), concat(), contains(), desc(), distinct(), endswith(), has(), icontains(), iendswith(), ilike(), in_(), is_(), is_distinct_from(), is_not(), is_not_distinct_from(), isnot(), isnot_distinct_from(), istartswith(), like(), match(), not_ilike(), not_in(), not_like(), notilike(), notin_(), notlike(), nulls_first(), nulls_last(), nullsfirst(), nullslast(), of_type(), op(), operate(), property, regexp_match(), regexp_replace(), reverse_operate(), startswith(), timetuple
Class signature
class
sqlalchemy.orm.PropComparator
(sqlalchemy.orm.base.SQLORMOperations
,typing.Generic
,sqlalchemy.sql.expression.ColumnOperators
)-
method
sqlalchemy.orm.PropComparator.
__eq__(other: Any) ColumnOperators ¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__eq__
method ofColumnOperators
Implement the
==
operator.In a column context, produces the clause
a = b
. If the target isNone
, producesa IS NULL
.
-
method
sqlalchemy.orm.PropComparator.
__le__(other: Any) ColumnOperators ¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__le__
method ofColumnOperators
Implement the
<=
operator.In a column context, produces the clause
a <= b
.
-
method
sqlalchemy.orm.PropComparator.
__lt__(other: Any) ColumnOperators ¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__lt__
method ofColumnOperators
Implement the
<
operator.In a column context, produces the clause
a < b
.
-
method
sqlalchemy.orm.PropComparator.
__ne__(other: Any) ColumnOperators ¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__ne__
method ofColumnOperators
Implement the
!=
operator.In a column context, produces the clause
a != b
. If the target isNone
, producesa IS NOT NULL
.
-
method
sqlalchemy.orm.PropComparator.
adapt_to_entity(adapt_to_entity: AliasedInsp[Any]) PropComparator[_T] ¶ Return a copy of this PropComparator which will use the given
AliasedInsp
to produce corresponding expressions.
-
attribute
sqlalchemy.orm.PropComparator.
adapter¶ Produce a callable that adapts column expressions to suit an aliased version of this comparator.
-
method
sqlalchemy.orm.PropComparator.
all_() ColumnOperators ¶ inherited from the
ColumnOperators.all_()
method ofColumnOperators
Produce an
all_()
clause against the parent object.See the documentation for
all_()
for examples.Note
be sure to not confuse the newer
ColumnOperators.all_()
method with its olderARRAY
-specific counterpart, theComparator.all()
method, which a different calling syntax and usage pattern.
-
method
sqlalchemy.orm.PropComparator.
and_(*criteria: _ColumnExpressionArgument[bool]) PropComparator[bool] ¶ Add additional criteria to the ON clause that’s represented by this relationship attribute.
E.g.:
stmt = select(User).join( User.addresses.and_(Address.email_address != 'foo') ) stmt = select(User).options( joinedload(User.addresses.and_(Address.email_address != 'foo')) )
New in version 1.4.
-
method
sqlalchemy.orm.PropComparator.
any(criterion: Optional[_ColumnExpressionArgument[bool]] = None, **kwargs: Any) ColumnElement[bool] ¶ Return a SQL expression representing true if this element references a member which meets the given criterion.
The usual implementation of
any()
isComparator.any()
.- Parameters:
criterion – an optional ClauseElement formulated against the member class’ table or attributes.
**kwargs – key/value pairs corresponding to member class attribute names which will be compared via equality to the corresponding values.
-
method
sqlalchemy.orm.PropComparator.
any_() ColumnOperators ¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
Produce an
any_()
clause against the parent object.See the documentation for
any_()
for examples.Note
be sure to not confuse the newer
ColumnOperators.any_()
method with its olderARRAY
-specific counterpart, theComparator.any()
method, which a different calling syntax and usage pattern.
-
method
sqlalchemy.orm.PropComparator.
asc() ColumnOperators ¶ inherited from the
ColumnOperators.asc()
method ofColumnOperators
Produce a
asc()
clause against the parent object.
-
method
sqlalchemy.orm.PropComparator.
between(cleft: Any, cright: Any, symmetric: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
Produce a
between()
clause against the parent object, given the lower and upper range.
-
method
sqlalchemy.orm.PropComparator.
bitwise_and(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_and()
method ofColumnOperators
Produce a bitwise AND operation, typically via the
&
operator.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bitwise_lshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_lshift()
method ofColumnOperators
Produce a bitwise LSHIFT operation, typically via the
<<
operator.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bitwise_not() ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_not()
method ofColumnOperators
Produce a bitwise NOT operation, typically via the
~
operator.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bitwise_or(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_or()
method ofColumnOperators
Produce a bitwise OR operation, typically via the
|
operator.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bitwise_rshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_rshift()
method ofColumnOperators
Produce a bitwise RSHIFT operation, typically via the
>>
operator.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bitwise_xor(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_xor()
method ofColumnOperators
Produce a bitwise XOR operation, typically via the
^
operator, or#
for PostgreSQL.New in version 2.0.2.
See also
-
method
sqlalchemy.orm.PropComparator.
bool_op(opstring: str, precedence: int = 0, python_impl: Optional[Callable[[...], Any]] = None) Callable[[Any], Operators] ¶ inherited from the
Operators.bool_op()
method ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True. A key advantage to usingOperators.bool_op()
is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.See also
-
method
sqlalchemy.orm.PropComparator.
collate(collation: str) ColumnOperators ¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.See also
-
method
sqlalchemy.orm.PropComparator.
concat(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.concat()
method ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
operator on MySQL.
-
method
sqlalchemy.orm.PropComparator.
contains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.contains()
method ofColumnOperators
Implement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle of a string value:
column LIKE '%' || <other> || '%'
E.g.:
stmt = select(sometable).\ where(sometable.c.column.contains("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.contains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.contains.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.contains.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.contains("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.contains("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.contains.autoescape
:somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
-
method
sqlalchemy.orm.PropComparator.
desc() ColumnOperators ¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
method
sqlalchemy.orm.PropComparator.
distinct() ColumnOperators ¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
Produce a
distinct()
clause against the parent object.
-
method
sqlalchemy.orm.PropComparator.
endswith(other: Any, escape: Optional[str] = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.endswith()
method ofColumnOperators
Implement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end of a string value:
column LIKE '%' || <other>
E.g.:
stmt = select(sometable).\ where(sometable.c.column.endswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.endswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.endswith.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.endswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.endswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param ESCAPE '^'
The parameter may also be combined with
ColumnOperators.endswith.autoescape
:somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
-
method
sqlalchemy.orm.PropComparator.
has(criterion: Optional[_ColumnExpressionArgument[bool]] = None, **kwargs: Any) ColumnElement[bool] ¶ Return a SQL expression representing true if this element references a member which meets the given criterion.
The usual implementation of
has()
isComparator.has()
.- Parameters:
criterion – an optional ClauseElement formulated against the member class’ table or attributes.
**kwargs – key/value pairs corresponding to member class attribute names which will be compared via equality to the corresponding values.
-
method
sqlalchemy.orm.PropComparator.
icontains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.icontains()
method ofColumnOperators
Implement the
icontains
operator, e.g. case insensitive version ofColumnOperators.contains()
.Produces a LIKE expression that tests against an insensitive match for the middle of a string value:
lower(column) LIKE '%' || lower(<other>) || '%'
E.g.:
stmt = select(sometable).\ where(sometable.c.column.icontains("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.icontains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.icontains.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.icontains.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.icontains("foo%bar", autoescape=True)
Will render as:
lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.icontains("foo/%bar", escape="^")
Will render as:
lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.contains.autoescape
:somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
See also
-
method
sqlalchemy.orm.PropComparator.
iendswith(other: Any, escape: Optional[str] = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.iendswith()
method ofColumnOperators
Implement the
iendswith
operator, e.g. case insensitive version ofColumnOperators.endswith()
.Produces a LIKE expression that tests against an insensitive match for the end of a string value:
lower(column) LIKE '%' || lower(<other>)
E.g.:
stmt = select(sometable).\ where(sometable.c.column.iendswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.iendswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.iendswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.iendswith.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.iendswith("foo%bar", autoescape=True)
Will render as:
lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.iendswith("foo/%bar", escape="^")
Will render as:
lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'
The parameter may also be combined with
ColumnOperators.iendswith.autoescape
:somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
See also
-
method
sqlalchemy.orm.PropComparator.
ilike(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.ilike()
method ofColumnOperators
Implement the
ilike
operator, e.g. case insensitive LIKE.In a column context, produces an expression either of the form:
lower(a) LIKE lower(other)
Or on backends that support the ILIKE operator:
a ILIKE other
E.g.:
stmt = select(sometable).\ where(sometable.c.column.ilike("%foobar%"))
- Parameters:
other – expression to be compared
escape –
optional escape character, renders the
ESCAPE
keyword, e.g.:somecolumn.ilike("foo/%bar", escape="/")
See also
-
method
sqlalchemy.orm.PropComparator.
in_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
Implement the
in
operator.In a column context, produces the clause
column IN <other>
.The given parameter
other
may be:A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))
In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)
A list of tuples may be provided if the comparison is against a
tuple_()
containing multiple expressions:from sqlalchemy import tuple_ stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
An empty list, e.g.:
stmt.where(column.in_([]))
In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:
WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.
A bound parameter, e.g.
bindparam()
, may be used if it includes thebindparam.expanding
flag:stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})
The database would be passed a bound parameter for each value:
WHERE COL IN (?, ?, ?)
New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support empty lists
a
select()
construct, which is usually a correlated scalar select:stmt.where( column.in_( select(othertable.c.y). where(table.c.x == othertable.c.x) ) )
In this calling form,
ColumnOperators.in_()
renders as given:WHERE COL IN (SELECT othertable.y FROM othertable WHERE othertable.x = table.x)
- Parameters:
other – a list of literals, a
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
method
sqlalchemy.orm.PropComparator.
is_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.See also
-
method
sqlalchemy.orm.PropComparator.
is_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
Implement the
IS DISTINCT FROM
operator.Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.
-
method
sqlalchemy.orm.PropComparator.
is_not(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_not()
method ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.Changed in version 1.4: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
is_not_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_not_distinct_from()
method ofColumnOperators
Implement the
IS NOT DISTINCT FROM
operator.Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.
Changed in version 1.4: The
is_not_distinct_from()
operator is renamed fromisnot_distinct_from()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
isnot(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.isnot()
method ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.Changed in version 1.4: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
isnot_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
Implement the
IS NOT DISTINCT FROM
operator.Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.
Changed in version 1.4: The
is_not_distinct_from()
operator is renamed fromisnot_distinct_from()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
istartswith(other: Any, escape: Optional[str] = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.istartswith()
method ofColumnOperators
Implement the
istartswith
operator, e.g. case insensitive version ofColumnOperators.startswith()
.Produces a LIKE expression that tests against an insensitive match for the start of a string value:
lower(column) LIKE lower(<other>) || '%'
E.g.:
stmt = select(sometable).\ where(sometable.c.column.istartswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.istartswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.istartswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.istartswith.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.istartswith("foo%bar", autoescape=True)
Will render as:
lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.istartswith("foo/%bar", escape="^")
Will render as:
lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.istartswith.autoescape
:somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
See also
-
method
sqlalchemy.orm.PropComparator.
like(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
Implement the
like
operator.In a column context, produces the expression:
a LIKE other
E.g.:
stmt = select(sometable).\ where(sometable.c.column.like("%foobar%"))
- Parameters:
other – expression to be compared
escape –
optional escape character, renders the
ESCAPE
keyword, e.g.:somecolumn.like("foo/%bar", escape="/")
See also
-
method
sqlalchemy.orm.PropComparator.
match(other: Any, **kwargs: Any) ColumnOperators ¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
Implements a database-specific ‘match’ operator.
ColumnOperators.match()
attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:PostgreSQL - renders
x @@ plainto_tsquery(y)
Changed in version 2.0:
plainto_tsquery()
is used instead ofto_tsquery()
for PostgreSQL now; for compatibility with other forms, see Full Text Search.MySQL - renders
MATCH (x) AGAINST (y IN BOOLEAN MODE)
See also
match
- MySQL specific construct with additional features.Oracle - renders
CONTAINS(x, y)
other backends may provide special implementations.
Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.
-
method
sqlalchemy.orm.PropComparator.
not_ilike(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.not_ilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.Changed in version 1.4: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
not_in(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.not_in()
method ofColumnOperators
implement the
NOT IN
operator.This is equivalent to using negation with
ColumnOperators.in_()
, i.e.~x.in_(y)
.In the case that
other
is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.Changed in version 1.4: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.Changed in version 1.2: The
ColumnOperators.in_()
andColumnOperators.not_in()
operators now produce a “static” expression for an empty IN sequence by default.See also
-
method
sqlalchemy.orm.PropComparator.
not_like(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.not_like()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.Changed in version 1.4: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
notilike(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.notilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.Changed in version 1.4: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
notin_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
implement the
NOT IN
operator.This is equivalent to using negation with
ColumnOperators.in_()
, i.e.~x.in_(y)
.In the case that
other
is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.Changed in version 1.4: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.Changed in version 1.2: The
ColumnOperators.in_()
andColumnOperators.not_in()
operators now produce a “static” expression for an empty IN sequence by default.See also
-
method
sqlalchemy.orm.PropComparator.
notlike(other: Any, escape: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.Changed in version 1.4: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.orm.PropComparator.
nulls_first() ColumnOperators ¶ inherited from the
ColumnOperators.nulls_first()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.Changed in version 1.4: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
nulls_last() ColumnOperators ¶ inherited from the
ColumnOperators.nulls_last()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.Changed in version 1.4: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
nullsfirst() ColumnOperators ¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.Changed in version 1.4: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
nullslast() ColumnOperators ¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.Changed in version 1.4: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.orm.PropComparator.
of_type(class_: _EntityType[Any]) PropComparator[_T] ¶ Redefine this object in terms of a polymorphic subclass,
with_polymorphic()
construct, oraliased()
construct.Returns a new PropComparator from which further criterion can be evaluated.
e.g.:
query.join(Company.employees.of_type(Engineer)).\ filter(Engineer.name=='foo')
- Parameters:
class_ – a class or mapper indicating that criterion will be against this specific subclass.
-
method
sqlalchemy.orm.PropComparator.
op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Optional[Union[Type[TypeEngine[Any]], TypeEngine[Any]]] = None, python_impl: Optional[Callable[..., Any]] = None) Callable[[Any], Operators] ¶ inherited from the
Operators.op()
method ofOperators
Produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in
somecolumn
.- Parameters:
opstring – a string which will be output as the infix operator between this element and the expression passed to the generated function.
precedence –
precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of
0
is lower than all operators except for the comma (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.See also
I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis
is_comparison –
legacy; if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like
==
,>
, etc. This flag is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.Using the
is_comparison
parameter is superseded by using theOperators.bool_op()
method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e.BinaryExpression[bool]
.return_type – a
TypeEngine
class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specifyOperators.op.is_comparison
will resolve toBoolean
, and those that do not will be of the same type as the left-hand operand.python_impl –
an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.
e.g.:
>>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')
The operator for the above expression will also work for non-SQL left and right objects:
>>> expr.operator(5, 10) 15
New in version 2.0.
-
method
sqlalchemy.orm.PropComparator.
operate(op: OperatorType, *other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.operate()
method ofOperators
Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding
ColumnOperators
to applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs)
- Parameters:
op – Operator callable.
*other – the ‘other’ side of the operation. Will be a single scalar for most operations.
**kwargs – modifiers. These may be passed by special operators such as
ColumnOperators.contains()
.
-
attribute
sqlalchemy.orm.PropComparator.
property¶ Return the
MapperProperty
associated with thisPropComparator
.Return values here will commonly be instances of
ColumnProperty
orRelationship
.
-
method
sqlalchemy.orm.PropComparator.
regexp_match(pattern: Any, flags: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_match()
method ofColumnOperators
Implements a database-specific ‘regexp match’ operator.
E.g.:
stmt = select(table.c.some_column).where( table.c.some_column.regexp_match('^(b|c)') )
ColumnOperators.regexp_match()
attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are not backend agnostic.Examples include:
PostgreSQL - renders
x ~ y
orx !~ y
when negated.Oracle - renders
REGEXP_LIKE(x, y)
SQLite - uses SQLite’s
REGEXP
placeholder operator and calls into the Pythonre.match()
builtin.other backends may provide special implementations.
Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.
Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.
- Parameters:
pattern – The regular expression pattern string or column clause.
flags – Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag ‘i’ in PostgreSQL, the ignore case regexp match operator
~*
or!~*
will be used.
New in version 1.4.
See also
-
method
sqlalchemy.orm.PropComparator.
regexp_replace(pattern: Any, replacement: Any, flags: Optional[str] = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_replace()
method ofColumnOperators
Implements a database-specific ‘regexp replace’ operator.
E.g.:
stmt = select( table.c.some_column.regexp_replace( 'b(..)', 'XY', flags='g' ) )
ColumnOperators.regexp_replace()
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the functionREGEXP_REPLACE()
. However, the specific regular expression syntax and flags available are not backend agnostic.Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.
- Parameters:
pattern – The regular expression pattern string or column clause.
pattern – The replacement string or column clause.
flags – Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.
New in version 1.4.
See also
-
method
sqlalchemy.orm.PropComparator.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.reverse_operate()
method ofOperators
Reverse operate on an argument.
Usage is the same as
operate()
.
-
method
sqlalchemy.orm.PropComparator.
startswith(other: Any, escape: Optional[str] = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.startswith()
method ofColumnOperators
Implement the
startswith
operator.Produces a LIKE expression that tests against a match for the start of a string value:
column LIKE <other> || '%'
E.g.:
stmt = select(sometable).\ where(sometable.c.column.startswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.startswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.startswith.autoescape
flag is set to True.autoescape –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.startswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE :param || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.escape –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape character. This character can then be placed preceding occurrences of%
and_
to allow them to act as themselves and not wildcard characters.An expression such as:
somecolumn.startswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.startswith.autoescape
:somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
-
attribute
sqlalchemy.orm.PropComparator.
timetuple: Literal[None] = None¶ inherited from the
ColumnOperators.timetuple
attribute ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
method
- class sqlalchemy.orm.Relationship¶
Describes an object property that holds a single item or list of items that correspond to a related database table.
Public constructor is the
relationship()
function.See also
Changed in version 2.0: Added
Relationship
as a Declarative compatible subclass forRelationshipProperty
.Class signature
class
sqlalchemy.orm.Relationship
(sqlalchemy.orm.RelationshipProperty
,sqlalchemy.orm.base._DeclarativeMapped
,sqlalchemy.orm.base.WriteOnlyMapped
,sqlalchemy.orm.base.DynamicMapped
)
- class sqlalchemy.orm.RelationshipDirection¶
enumeration which indicates the ‘direction’ of a
RelationshipProperty
.RelationshipDirection
is accessible from theRelationship.direction
attribute ofRelationshipProperty
.Members
Class signature
class
sqlalchemy.orm.RelationshipDirection
(enum.Enum
)-
attribute
sqlalchemy.orm.RelationshipDirection.
MANYTOMANY = 3¶ Indicates the many-to-many direction for a
relationship()
.This symbol is typically used by the internals but may be exposed within certain API features.
-
attribute
sqlalchemy.orm.RelationshipDirection.
MANYTOONE = 2¶ Indicates the many-to-one direction for a
relationship()
.This symbol is typically used by the internals but may be exposed within certain API features.
-
attribute
sqlalchemy.orm.RelationshipDirection.
ONETOMANY = 1¶ Indicates the one-to-many direction for a
relationship()
.This symbol is typically used by the internals but may be exposed within certain API features.
-
attribute
- class sqlalchemy.orm.RelationshipProperty¶
Describes an object property that holds a single item or list of items that correspond to a related database table.
Public constructor is the
relationship()
function.See also
Members
__eq__(), __init__(), __ne__(), adapt_to_entity(), and_(), any(), contains(), entity, has(), in_(), mapper, of_type(), cascade, cascade_iterator(), declarative_scan(), do_init(), entity, instrument_class(), mapper, merge()
Class signature
class
sqlalchemy.orm.RelationshipProperty
(sqlalchemy.orm._IntrospectsAnnotations
,sqlalchemy.orm.StrategizedProperty
,sqlalchemy.log.Identified
)- class Comparator¶
Produce boolean, comparison, and other operators for
RelationshipProperty
attributes.See the documentation for
PropComparator
for a brief overview of ORM level operator definition.Class signature
class
sqlalchemy.orm.RelationshipProperty.Comparator
(sqlalchemy.util.langhelpers.MemoizedSlots
,sqlalchemy.orm.PropComparator
)-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
__eq__(other: Any) ColumnElement[bool] ¶ Implement the
==
operator.In a many-to-one context, such as:
MyClass.some_prop == <some object>
this will typically produce a clause such as:
mytable.related_id == <some id>
Where
<some id>
is the primary key of the given object.The
==
operator provides partial functionality for non- many-to-one comparisons:Comparisons against collections are not supported. Use
Comparator.contains()
.Compared to a scalar one-to-many, will produce a clause that compares the target columns in the parent to the given target.
Compared to a scalar many-to-many, an alias of the association table will be rendered as well, forming a natural join that is part of the main body of the query. This will not work for queries that go beyond simple AND conjunctions of comparisons, such as those which use OR. Use explicit joins, outerjoins, or
Comparator.has()
for more comprehensive non-many-to-one scalar membership tests.Comparisons against
None
given in a one-to-many or many-to-many context produce a NOT EXISTS clause.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
__init__(prop: RelationshipProperty[_PT], parentmapper: _InternalEntityType[Any], adapt_to_entity: Optional[AliasedInsp[Any]] = None, of_type: Optional[_EntityType[_PT]] = None, extra_criteria: Tuple[ColumnElement[bool], ...] = ())¶ Construction of
Comparator
is internal to the ORM’s attribute mechanics.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
__ne__(other: Any) ColumnElement[bool] ¶ Implement the
!=
operator.In a many-to-one context, such as:
MyClass.some_prop != <some object>
This will typically produce a clause such as:
mytable.related_id != <some id>
Where
<some id>
is the primary key of the given object.The
!=
operator provides partial functionality for non- many-to-one comparisons:Comparisons against collections are not supported. Use
Comparator.contains()
in conjunction withnot_()
.Compared to a scalar one-to-many, will produce a clause that compares the target columns in the parent to the given target.
Compared to a scalar many-to-many, an alias of the association table will be rendered as well, forming a natural join that is part of the main body of the query. This will not work for queries that go beyond simple AND conjunctions of comparisons, such as those which use OR. Use explicit joins, outerjoins, or
Comparator.has()
in conjunction withnot_()
for more comprehensive non-many-to-one scalar membership tests.Comparisons against
None
given in a one-to-many or many-to-many context produce an EXISTS clause.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
adapt_to_entity(adapt_to_entity: AliasedInsp[Any]) RelationshipProperty.Comparator[Any] ¶ Return a copy of this PropComparator which will use the given
AliasedInsp
to produce corresponding expressions.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
and_(*criteria: _ColumnExpressionArgument[bool]) PropComparator[Any] ¶ Add AND criteria.
See
PropComparator.and_()
for an example.New in version 1.4.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
any(criterion: Optional[_ColumnExpressionArgument[bool]] = None, **kwargs: Any) ColumnElement[bool] ¶ Produce an expression that tests a collection against particular criterion, using EXISTS.
An expression like:
session.query(MyClass).filter( MyClass.somereference.any(SomeRelated.x==2) )
Will produce a query like:
SELECT * FROM my_table WHERE EXISTS (SELECT 1 FROM related WHERE related.my_id=my_table.id AND related.x=2)
Because
Comparator.any()
uses a correlated subquery, its performance is not nearly as good when compared against large target tables as that of using a join.Comparator.any()
is particularly useful for testing for empty collections:session.query(MyClass).filter( ~MyClass.somereference.any() )
will produce:
SELECT * FROM my_table WHERE NOT (EXISTS (SELECT 1 FROM related WHERE related.my_id=my_table.id))
Comparator.any()
is only valid for collections, i.e. arelationship()
that hasuselist=True
. For scalar references, useComparator.has()
.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
contains(other: _ColumnExpressionArgument[Any], **kwargs: Any) ColumnElement[bool] ¶ Return a simple expression that tests a collection for containment of a particular item.
Comparator.contains()
is only valid for a collection, i.e. arelationship()
that implements one-to-many or many-to-many withuselist=True
.When used in a simple one-to-many context, an expression like:
MyClass.contains(other)
Produces a clause like:
mytable.id == <some id>
Where
<some id>
is the value of the foreign key attribute onother
which refers to the primary key of its parent object. From this it follows thatComparator.contains()
is very useful when used with simple one-to-many operations.For many-to-many operations, the behavior of
Comparator.contains()
has more caveats. The association table will be rendered in the statement, producing an “implicit” join, that is, includes multiple tables in the FROM clause which are equated in the WHERE clause:query(MyClass).filter(MyClass.contains(other))
Produces a query like:
SELECT * FROM my_table, my_association_table AS my_association_table_1 WHERE my_table.id = my_association_table_1.parent_id AND my_association_table_1.child_id = <some id>
Where
<some id>
would be the primary key ofother
. From the above, it is clear thatComparator.contains()
will not work with many-to-many collections when used in queries that move beyond simple AND conjunctions, such as multipleComparator.contains()
expressions joined by OR. In such cases subqueries or explicit “outer joins” will need to be used instead. SeeComparator.any()
for a less-performant alternative using EXISTS, or refer toQuery.outerjoin()
as well as Joins for more details on constructing outer joins.kwargs may be ignored by this operator but are required for API conformance.
-
attribute
sqlalchemy.orm.RelationshipProperty.Comparator.
entity: _InternalEntityType[_PT]¶ The target entity referred to by this
Comparator
.This is either a
Mapper
orAliasedInsp
object.This is the “target” or “remote” side of the
relationship()
.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
has(criterion: Optional[_ColumnExpressionArgument[bool]] = None, **kwargs: Any) ColumnElement[bool] ¶ Produce an expression that tests a scalar reference against particular criterion, using EXISTS.
An expression like:
session.query(MyClass).filter( MyClass.somereference.has(SomeRelated.x==2) )
Will produce a query like:
SELECT * FROM my_table WHERE EXISTS (SELECT 1 FROM related WHERE related.id==my_table.related_id AND related.x=2)
Because
Comparator.has()
uses a correlated subquery, its performance is not nearly as good when compared against large target tables as that of using a join.Comparator.has()
is only valid for scalar references, i.e. arelationship()
that hasuselist=False
. For collection references, useComparator.any()
.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
in_(other: Any) NoReturn ¶ Produce an IN clause - this is not implemented for
relationship()
-based attributes at this time.
-
attribute
sqlalchemy.orm.RelationshipProperty.Comparator.
mapper: Mapper[_PT]¶ The target
Mapper
referred to by thisComparator
.This is the “target” or “remote” side of the
relationship()
.
-
method
sqlalchemy.orm.RelationshipProperty.Comparator.
of_type(class_: _EntityType[Any]) PropComparator[_PT] ¶ Redefine this object in terms of a polymorphic subclass.
See
PropComparator.of_type()
for an example.
-
method
-
attribute
sqlalchemy.orm.RelationshipProperty.
cascade¶ Return the current cascade setting for this
RelationshipProperty
.
-
method
sqlalchemy.orm.RelationshipProperty.
cascade_iterator(type_: str, state: InstanceState[Any], dict_: _InstanceDict, visited_states: Set[InstanceState[Any]], halt_on: Optional[Callable[[InstanceState[Any]], bool]] = None) Iterator[Tuple[Any, Mapper[Any], InstanceState[Any], _InstanceDict]] ¶ Iterate through instances related to the given instance for a particular ‘cascade’, starting with this MapperProperty.
Return an iterator3-tuples (instance, mapper, state).
Note that the ‘cascade’ collection on this MapperProperty is checked first for the given type before cascade_iterator is called.
This method typically only applies to Relationship.
-
method
sqlalchemy.orm.RelationshipProperty.
declarative_scan(decl_scan: _ClassScanMapperConfig, registry: _RegistryType, cls: Type[Any], originating_module: Optional[str], key: str, mapped_container: Optional[Type[Mapped[Any]]], annotation: Optional[_AnnotationScanType], extracted_mapped_annotation: Optional[_AnnotationScanType], is_dataclass_field: bool) None ¶ Perform class-specific initializaton at early declarative scanning time.
New in version 2.0.
-
method
sqlalchemy.orm.RelationshipProperty.
do_init() None ¶ Perform subclass-specific initialization post-mapper-creation steps.
This is a template method called by the
MapperProperty
object’s init() method.
-
attribute
sqlalchemy.orm.RelationshipProperty.
entity¶ Return the target mapped entity, which is an inspect() of the class or aliased class that is referred towards.
-
method
sqlalchemy.orm.RelationshipProperty.
instrument_class(mapper: Mapper[Any]) None ¶ Hook called by the Mapper to the property to initiate instrumentation of the class attribute managed by this MapperProperty.
The MapperProperty here will typically call out to the attributes module to set up an InstrumentedAttribute.
This step is the first of two steps to set up an InstrumentedAttribute, and is called early in the mapper setup process.
The second step is typically the init_class_attribute step, called from StrategizedProperty via the post_instrument_class() hook. This step assigns additional state to the InstrumentedAttribute (specifically the “impl”) which has been determined after the MapperProperty has determined what kind of persistence management it needs to do (e.g. scalar, object, collection, etc).
-
attribute
sqlalchemy.orm.RelationshipProperty.
mapper¶ Return the targeted
Mapper
for thisRelationshipProperty
.
-
method
sqlalchemy.orm.RelationshipProperty.
merge(session: Session, source_state: InstanceState[Any], source_dict: _InstanceDict, dest_state: InstanceState[Any], dest_dict: _InstanceDict, load: bool, _recursive: Dict[Any, object], _resolve_conflict_map: Dict[_IdentityKeyType[Any], object]) None ¶ Merge the attribute represented by this
MapperProperty
from source to destination object.
- class sqlalchemy.orm.SQLORMExpression¶
A type that may be used to indicate any ORM-level attribute or object that acts in place of one, in the context of SQL expression construction.
SQLORMExpression
extends from the CoreSQLColumnExpression
to add additional SQL methods that are ORM specific, such asPropComparator.of_type()
, and is part of the bases forInstrumentedAttribute
. It may be used in PEP 484 typing to indicate arguments or return values that should behave as ORM-level attribute expressions.New in version 2.0.0b4.
Class signature
class
sqlalchemy.orm.SQLORMExpression
(sqlalchemy.orm.base.SQLORMOperations
,sqlalchemy.sql.expression.SQLColumnExpression
,sqlalchemy.util.langhelpers.TypingOnly
)
- class sqlalchemy.orm.Synonym¶
Declarative front-end for the
SynonymProperty
class.Public constructor is the
synonym()
function.Changed in version 2.0: Added
Synonym
as a Declarative compatible subclass forSynonymProperty
See also
Synonyms - Overview of synonyms
Class signature
class
sqlalchemy.orm.Synonym
(sqlalchemy.orm.descriptor_props.SynonymProperty
,sqlalchemy.orm.base._DeclarativeMapped
)
- class sqlalchemy.orm.SynonymProperty¶
Denote an attribute name as a synonym to a mapped property, in that the attribute will mirror the value and expression behavior of another attribute.
Synonym
is constructed using thesynonym()
function.See also
Synonyms - Overview of synonyms
Members
doc, info, key, parent, set_parent(), uses_objects
Class signature
class
sqlalchemy.orm.SynonymProperty
(sqlalchemy.orm.descriptor_props.DescriptorProperty
)-
attribute
sqlalchemy.orm.SynonymProperty.
doc: Optional[str]¶ inherited from the
DescriptorProperty.doc
attribute ofDescriptorProperty
optional documentation string
-
attribute
sqlalchemy.orm.SynonymProperty.
info: _InfoType¶ inherited from the
MapperProperty.info
attribute ofMapperProperty
Info dictionary associated with the object, allowing user-defined data to be associated with this
InspectionAttr
.The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the
column_property()
,relationship()
, orcomposite()
functions.
-
attribute
sqlalchemy.orm.SynonymProperty.
key: str¶ inherited from the
MapperProperty.key
attribute ofMapperProperty
name of class attribute
-
attribute
sqlalchemy.orm.SynonymProperty.
parent: Mapper[Any]¶ inherited from the
MapperProperty.parent
attribute ofMapperProperty
the
Mapper
managing this property.
-
method
sqlalchemy.orm.SynonymProperty.
set_parent(parent: Mapper[Any], init: bool) None ¶ Set the parent mapper that references this MapperProperty.
This method is overridden by some subclasses to perform extra setup when the mapper is first known.
-
attribute
sqlalchemy.orm.SynonymProperty.
uses_objects¶
-
attribute
- class sqlalchemy.orm.QueryContext¶
- class default_load_options¶
Class signature
class
sqlalchemy.orm.QueryContext.default_load_options
(sqlalchemy.sql.expression.Options
)
- class sqlalchemy.orm.QueryableAttribute¶
Base class for descriptor objects that intercept attribute events on behalf of a
MapperProperty
object. The actualMapperProperty
is accessible via theQueryableAttribute.property
attribute.Members
adapt_to_entity(), and_(), expression, info, is_attribute, of_type(), operate(), parent, reverse_operate()
Class signature
class
sqlalchemy.orm.QueryableAttribute
(sqlalchemy.orm.base._DeclarativeMapped
,sqlalchemy.orm.base.SQLORMExpression
,sqlalchemy.orm.base.InspectionAttr
,sqlalchemy.orm.PropComparator
,sqlalchemy.sql.roles.JoinTargetRole
,sqlalchemy.sql.roles.OnClauseRole
,sqlalchemy.sql.expression.Immutable
,sqlalchemy.sql.cache_key.SlotsMemoizedHasCacheKey
,sqlalchemy.util.langhelpers.MemoizedSlots
,sqlalchemy.event.registry.EventTarget
)-
method
sqlalchemy.orm.QueryableAttribute.
adapt_to_entity(adapt_to_entity: AliasedInsp[Any]) Self ¶ Return a copy of this PropComparator which will use the given
AliasedInsp
to produce corresponding expressions.
-
method
sqlalchemy.orm.QueryableAttribute.
and_(*clauses: _ColumnExpressionArgument[bool]) QueryableAttribute[bool] ¶ Add additional criteria to the ON clause that’s represented by this relationship attribute.
E.g.:
stmt = select(User).join( User.addresses.and_(Address.email_address != 'foo') ) stmt = select(User).options( joinedload(User.addresses.and_(Address.email_address != 'foo')) )
New in version 1.4.
-
attribute
sqlalchemy.orm.QueryableAttribute.
expression: ColumnElement[_T]¶ The SQL expression object represented by this
QueryableAttribute
.This will typically be an instance of a
ColumnElement
subclass representing a column expression.
-
attribute
sqlalchemy.orm.QueryableAttribute.
info¶ Return the ‘info’ dictionary for the underlying SQL element.
The behavior here is as follows:
If the attribute is a column-mapped property, i.e.
ColumnProperty
, which is mapped directly to a schema-levelColumn
object, this attribute will return theSchemaItem.info
dictionary associated with the core-levelColumn
object.If the attribute is a
ColumnProperty
but is mapped to any other kind of SQL expression other than aColumn
, the attribute will refer to theMapperProperty.info
dictionary associated directly with theColumnProperty
, assuming the SQL expression itself does not have its own.info
attribute (which should be the case, unless a user-defined SQL construct has defined one).If the attribute refers to any other kind of
MapperProperty
, includingRelationship
, the attribute will refer to theMapperProperty.info
dictionary associated with thatMapperProperty
.To access the
MapperProperty.info
dictionary of theMapperProperty
unconditionally, including for aColumnProperty
that’s associated directly with aColumn
, the attribute can be referred to usingQueryableAttribute.property
attribute, asMyClass.someattribute.property.info
.
-
attribute
sqlalchemy.orm.QueryableAttribute.
is_attribute = True¶ True if this object is a Python descriptor.
This can refer to one of many types. Usually a
QueryableAttribute
which handles attributes events on behalf of aMapperProperty
. But can also be an extension type such asAssociationProxy
orhybrid_property
. TheInspectionAttr.extension_type
will refer to a constant identifying the specific subtype.See also
-
method
sqlalchemy.orm.QueryableAttribute.
of_type(entity: _EntityType[Any]) QueryableAttribute[_T] ¶ Redefine this object in terms of a polymorphic subclass,
with_polymorphic()
construct, oraliased()
construct.Returns a new PropComparator from which further criterion can be evaluated.
e.g.:
query.join(Company.employees.of_type(Engineer)).\ filter(Engineer.name=='foo')
- Parameters:
class_ – a class or mapper indicating that criterion will be against this specific subclass.
-
method
sqlalchemy.orm.QueryableAttribute.
operate(op: OperatorType, *other: Any, **kwargs: Any) ColumnElement[Any] ¶ Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding
ColumnOperators
to applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs)
- Parameters:
op – Operator callable.
*other – the ‘other’ side of the operation. Will be a single scalar for most operations.
**kwargs – modifiers. These may be passed by special operators such as
ColumnOperators.contains()
.
-
attribute
sqlalchemy.orm.QueryableAttribute.
parent: _InternalEntityType[Any]¶ Return an inspection instance representing the parent.
This will be either an instance of
Mapper
orAliasedInsp
, depending upon the nature of the parent entity which this attribute is associated with.
-
method
sqlalchemy.orm.QueryableAttribute.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) ColumnElement[Any] ¶ Reverse operate on an argument.
Usage is the same as
operate()
.
-
method
- class sqlalchemy.orm.UOWTransaction¶
-
method
sqlalchemy.orm.UOWTransaction.
filter_states_for_dep(dep, states)¶ Filter the given list of InstanceStates to those relevant to the given DependencyProcessor.
-
method
sqlalchemy.orm.UOWTransaction.
finalize_flush_changes() None ¶ Mark processed objects as clean / deleted after a successful flush().
This method is called within the flush() method after the execute() method has succeeded and the transaction has been committed.
-
method
sqlalchemy.orm.UOWTransaction.
get_attribute_history(state, key, passive=symbol('PASSIVE_NO_INITIALIZE'))¶ Facade to attributes.get_state_history(), including caching of results.
-
method
sqlalchemy.orm.UOWTransaction.
is_deleted(state)¶ Return
True
if the given state is marked as deleted within this uowtransaction.
-
method
sqlalchemy.orm.UOWTransaction.
remove_state_actions(state)¶ Remove pending actions for a state from the uowtransaction.
Members
filter_states_for_dep(), finalize_flush_changes(), get_attribute_history(), is_deleted(), remove_state_actions(), was_already_deleted()
-
method
sqlalchemy.orm.UOWTransaction.
was_already_deleted(state)¶ Return
True
if the given state is expired and was deleted previously.
-
method