思维导图备注

- <code>__eq__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.__eq__)
-

inherited from theeq()method ofColumnOperators

Implement the == operator.

In a column context, produces the clause a = b.If the target is None, produces a IS NULL.

- <code>__le__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.__le__)
-

inherited from thele()method ofColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

- <code>__lt__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.__lt__)
-

inherited from thelt()method ofColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

- <code>__ne__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.__ne__)
-

inherited from thene()method ofColumnOperators

Implement the != operator.

In a column context, produces the clause a != b.If the target is None, produces a IS NOT NULL.

- <code>adapt_to_entity</code>(_adapt_to_entity_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.adapt_to_entity)
-

inherited from theadapt_to_entity()method ofPropComparator

Return a copy of this PropComparator which will use the givenAliasedInsp to produce corresponding expressions.

- _property _<code>adapter</code>[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.adapter)
-

Produce a callable that adapts column expressionsto suit an aliased version of this comparator.

- <code>all_</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.all_)
-

inherited from theall_()method ofColumnOperators

Produce a all_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
 
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

- <code>any</code>(_criterion=None_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.any)
-

inherited from theany()method ofPropComparator

Return true if this collection contains any member that meets thegiven criterion.

The usual implementation of any() isRelationshipProperty.Comparator.any().

  - Parameters
  - 
    -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

- <code>any_</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.any_)
-

inherited from theany_()method ofColumnOperators

Produce a any_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
 
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

- <code>asc</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.asc)
-

inherited from theasc()method ofColumnOperators

Produce a asc() clause against theparent object.

- <code>between</code>(_cleft_, _cright_, _symmetric=False_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.between)
-

inherited from thebetween()method ofColumnOperators

Produce a between() clause againstthe parent object, given the lower and upper range.

- <code>bool_op</code>(_opstring_, _precedence=0_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.bool_op)
-

inherited from thebool_op()method ofOperators

Return a custom boolean operator.

This method is shorthand for callingOperators.op() and passing theOperators.op.is_comparisonflag with True.

New in version 1.2.0b3.

See also

- <code>collate</code>(_collation_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.collate)
-

inherited from thecollate()method ofColumnOperators

Produce a collate() clause againstthe parent object, given the collation string.

See also

- <code>concat</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.concat)
-

inherited from theconcat()method ofColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b,or uses the concat() operator on MySQL.

- <code>contains</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.contains)
-

inherited from thecontains()method ofColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middleof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.contains.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.contains.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  - Parameters
  - 
    -

other – expression to be compared. This is usually a plainstring value, but can also be an arbitrary SQL expression. LIKEwildcard characters % and _ are not escaped by default unlessthe ColumnOperators.contains.autoescape flag isset to True.

autoescape –

boolean; when True, establishes an escape characterwithin the LIKE expression, then applies it to all occurrences of"%", "_" and the escape character itself within thecomparison value, which is assumed to be a literal string and not aSQL 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".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.contains.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.contains.escape parameter.

escape –

a character which when given will render with theESCAPE keyword to establish that character as the escapecharacter. This character can then be placed preceding occurrencesof % and _ to allow them to act as themselves and notwildcard characters.

An expression such as:

somecolumn.contains("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

- <code>desc</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.desc)
-

inherited from thedesc()method ofColumnOperators

Produce a desc() clause against theparent object.

- <code>distinct</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.distinct)
-

inherited from thedistinct()method ofColumnOperators

Produce a distinct() clause against theparent object.

- <code>endswith</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.endswith)
-

inherited from theendswith()method ofColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the endof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.endswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.endswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  - Parameters
  - 
    -

autoescape –

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".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.endswith.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.endswith.escape parameter.

escape –

An expression such as:

somecolumn.endswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

- <code>has</code>(_criterion=None_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.has)
-

inherited from thehas()method ofPropComparator

Return true if this element references a member which meets thegiven criterion.

The usual implementation of has() isRelationshipProperty.Comparator.has().

  - Parameters
  - 
    -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

- <code>ilike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.ilike)
-

inherited from theilike()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 ESCAPEkeyword, e.g.:

somecolumn.ilike("foo/%bar", escape="/")

See also

- <code>in_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.in_)
-

inherited from thein_()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 ofbound parameters the same length as the list given:

WHERE COL IN (?, ?, ?)

A list of tuples may be provided if the comparison is against atuple_() 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 a “false” expression,e.g.:

WHERE 1 != 1

This “false” expression has historically had different behaviorsin older SQLAlchemy versions, seecreate_engine.empty_in_strategy for behavioral options.

Changed in version 1.2: simplified the behavior of “empty in”expressions

A bound parameter, e.g. bindparam(), may be used if itincludes the bindparam.expanding flag:

stmt.where(column.in_(bindparam('value', expanding=True)))

In this calling form, the expression renders a special non-SQLplaceholder expression that looks like:

WHERE COL IN ([EXPANDING_value])

This placeholder expression is intercepted at statement executiontime to be converted into the variable number of bound parameterform 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. OnSQLite this would be:

WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

New in version 1.3: “expanding” bound parameters now supportempty lists

a select() construct, which is usually a correlatedscalar 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 a bindparam() construct that includes thebindparam.expanding flag set to True.

- <code>is_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.is_)
-

inherited from theis_()method ofColumnOperators

Implement the IS operator.

Normally, IS is generated automatically when comparing to avalue of None, which resolves to NULL. However, explicitusage of IS may be desirable if comparing to boolean valueson certain platforms.

See also

- <code>is_distinct_from</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.is_distinct_from)
-

inherited from theis_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”.

New in version 1.1.

- <code>isnot</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.isnot)
-

inherited from theisnot()method ofColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to avalue of None, which resolves to NULL. However, explicitusage of IS NOT may be desirable if comparing to boolean valueson certain platforms.

See also

- <code>isnot_distinct_from</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.isnot_distinct_from)
-

inherited from theisnot_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”.

New in version 1.1.

- <code>like</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.like)
-

inherited from thelike()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 ESCAPEkeyword, e.g.:

somecolumn.like("foo/%bar", escape="/")

See also

- <code>match</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.match)
-

inherited from thematch()method ofColumnOperators

Implements a database-specific ‘match’ operator.

match() attempts to resolve toa MATCH-like function or operator provided by the backend.Examples include:

PostgreSQL - renders x @@ to_tsquery(y)

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

Oracle - renders CONTAINS(x, y)

other backends may provide special implementations.

Backends without any special implementation will emitthe operator as “MATCH”. This is compatible with SQLite, forexample.

- <code>notilike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.notilike)
-

inherited from thenotilike()method ofColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation withColumnOperators.ilike(), i.e. ~x.ilike(y).

See also

- <code>notin_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.notin_)
-

inherited from thenotin_()method ofColumnOperators

implement the NOT IN operator.

This is equivalent to using negation withColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compilerproduces an “empty not in” expression. This defaults to theexpression “1 = 1” to produce true in all cases. Thecreate_engine.empty_in_strategy may be used toalter this behavior.

Changed in version 1.2: The ColumnOperators.in_() andColumnOperators.notin_() operatorsnow produce a “static” expression for an empty IN sequenceby default.

See also

- <code>notlike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.notlike)
-

inherited from thenotlike()method ofColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation withColumnOperators.like(), i.e. ~x.like(y).

See also

- <code>nullsfirst</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.nullsfirst)
-

inherited from thenullsfirst()method ofColumnOperators

Produce a nullsfirst() clause against theparent object.

- <code>nullslast</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.nullslast)
-

inherited from thenullslast()method ofColumnOperators

Produce a nullslast() clause against theparent object.

- <code>of_type</code>(_class__)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.of_type)
-

inherited from theof_type()method ofPropComparator

Redefine this object in terms of a polymorphic subclass.

Returns a new PropComparator from which further criterion can beevaluated.

e.g.:

query.join(Company.employees.of_type(Engineer)).\
   filter(Engineer.name=='foo')

  - Parameters
  -

class_ – a class or mapper indicating that criterion will beagainst this specific subclass.

- <code>op</code>(_opstring_, _precedence=0_, _is_comparison=False_, _return_type=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.op)
-

inherited from theop()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. Forexample:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

  - Parameters
  - 
    -

operator – a string which will be output as the infix operatorbetween this element and the expression passed to thegenerated function.

precedence – precedence to apply to the operator, whenparenthesizing expressions. A lower number will cause the expressionto be parenthesized when applied against another operator withhigher precedence. The default value of 0 is lower than alloperators except for the comma (,) and AS operators.A value of 100 will be higher or equal to all operators, and -100will be lower than or equal to all operators.

is_comparison –

if True, the operator will be considered as a“comparison” operator, that is which evaluates to a booleantrue/false value, like ==, >, etc. This flag should be setso that ORM relationships can establish that the operator is acomparison operator when used in a custom join condition.

New in version 0.9.2: - added theOperators.op.is_comparison flag.

return_type –

a TypeEngine class or object that willforce the return type of an expression produced by this operatorto be of that type. By default, operators that specifyOperators.op.is_comparison will resolve toBoolean, and those that do not will be of the sametype as the left-hand operand.

New in version 1.2.0b3: - added theOperators.op.return_type argument.

See also

- <code>operate</code>(_op_, _*other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.operate)
-

Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))

  - Parameters
  - 
    -

op – Operator callable.

*other – the ‘other’ side of the operation. Willbe a single scalar for most operations.

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

- <code>reverse_operate</code>(_op_, _other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.reverse_operate)
-

Reverse operate on an argument.

Usage is the same as operate().

- <code>startswith</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.ColumnProperty.Comparator.startswith)
-

inherited from thestartswith()method ofColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the startof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.startswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.startswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  - Parameters
  - 
    -

autoescape –

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".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.startswith.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.startswith.escape parameter.

escape –

An expression such as:

somecolumn.startswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

eq()

inherited from the eq() method of object

Return self==value.

init(*columns, **kwargs)
Construct a new ColumnProperty object.

This constructor is mirrored as a public API function; see column_property() for a full usage and argument description.

le()

inherited from the le() method of object

Return self<=value.

lt()

inherited from the lt() method of object

Return self

class sqlalchemy.orm.properties.ComparableProperty(comparator_factory, descriptor=None, doc=None, info=None)
Bases: sqlalchemy.orm.descriptor_props.DescriptorProperty

Deprecated since version 0.7: comparable_property() is deprecated and will be removed in a future release. Please refer to the hybrid extension.

Instruments a Python property for use in query expressions.

init(comparator_factory, descriptor=None, doc=None, info=None)
Construct a new ComparableProperty object.

This constructor is mirrored as a public API function; see comparable_property() for a full usage and argument description.

class sqlalchemy.orm.descriptorprops.CompositeProperty(class_, attrs, *kwargs_)
Bases: sqlalchemy.orm.descriptor_props.DescriptorProperty

Defines a “composite” mapped attribute, representing a collectionof columns as one attribute.

CompositeProperty is constructed using the composite()function.

See also

Composite Column Types

class Comparator(prop, parentmapper, adapt_to_entity=None)
Bases: sqlalchemy.orm.interfaces.PropComparator

Produce boolean, comparison, and other operators forCompositeProperty attributes.

See the example in Redefining Comparison Operations for Composites for an overviewof usage , as well as the documentation for PropComparator.

See also

PropComparator

Mapper.all_orm_descriptors

class CompositeBundle(property__, _expr)
Bases: sqlalchemy.orm.query.Bundle
- createrow_processor(_query, procs, labels)
- Produce the “row processing” function for this Bundle.

May be overridden by subclasses.

See also

Column Bundles - includes an example of subclassing.

init(class__, attrs, *kwargs)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.descriptor_props.CompositeProperty.__init_)
Construct a new CompositeProperty object.

This constructor is mirrored as a public API function; see composite() for a full usage and argument description.

do_init()
Initialization which occurs after the CompositePropertyhas been associated with its parent mapper.
gethistory(_state, dict__, _passive=symbol('PASSIVE_OFF'))
Provided for userland code that uses attributes.get_history().
instrumentclass(_mapper)
Hook called by the Mapper to the property to initiateinstrumentation of the class attribute managed by thisMapperProperty.

The MapperProperty here will typically call out to theattributes 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 theMapperProperty has determined what kind of persistencemanagement it needs to do (e.g. scalar, object, collection, etc).

class sqlalchemy.orm.attributes.Event(attribute_impl, op)
A token propagated throughout the course of a chain of attributeevents.

Serves as an indicator of the source of the event and also providesa means of controlling propagation across a chain of attributeoperations.

The Event object is sent as the initiator argumentwhen dealing with events such as AttributeEvents.append(),AttributeEvents.set(),and AttributeEvents.remove().

The Event object is currently interpreted by the backrefevent handlers, and is used to control the propagation of operationsacross two mutually-dependent attributes.

New in version 0.9.0.

Variables
- impl – The AttributeImpl which is the current eventinitiator.
- op – The symbol OP_APPEND, OP_REMOVE,OP_REPLACE, or OP_BULK_REPLACE, indicating thesource operation.

class sqlalchemy.orm.identity.IdentityMap
- check_modified()
- return True if any InstanceStates present have been markedas ‘modified’.
class sqlalchemy.orm.base.InspectionAttr
A base class applied to all ORM objects that can be returnedby the inspect() function.

The attributes defined here allow the usage of simple booleanchecks to test basic facts about the object returned.

While the boolean checks here are basically the same as usingthe Python isinstance() function, the flags here can be used withoutthe need to import all of these classes, and also such thatthe SQLAlchemy class system can change while leaving the flagshere intact for forwards-compatibility.

extensiontype = symbol('NOTEXTENSION')
The extension type, if any.Defaults to interfaces.NOT_EXTENSION

See also

HYBRID_METHOD

HYBRID_PROPERTY

ASSOCIATION_PROXY

isaliased_class = False_
True if this object is an instance of AliasedClass.
isattribute = False_
True if this object is a Python descriptor.

This can refer to one of many types. Usually aQueryableAttribute which handles attributes events on behalfof a MapperProperty. But can also be an extension typesuch as AssociationProxy or hybrid_property.The InspectionAttr.extension_type will refer to a constantidentifying the specific subtype.

See also

isclause_element = False_
True if this object is an instance of ClauseElement.
isinstance = False_
True if this object is an instance of InstanceState.
ismapper = False_
True if this object is an instance of Mapper.
isproperty = False_
True if this object is an instance of MapperProperty.
isselectable = False_
Return True if this object is an instance of Selectable.

class sqlalchemy.orm.base.InspectionAttrInfo
Bases: sqlalchemy.orm.base.InspectionAttr

Adds the .info attribute to InspectionAttr.

The rationale for InspectionAttr vs. InspectionAttrInfois that the former is compatible as a mixin for classes that specifyslots; this is essentially an implementation artifact.

info
Info dictionary associated with the object, allowing user-defineddata to be associated with this InspectionAttr.

The dictionary is generated when first accessed. Alternatively,it can be specified as a constructor argument to thecolumn_property(), relationship(), or composite()functions.

Changed in version 1.0.0: MapperProperty.info is alsoavailable on extension types via theInspectionAttrInfo.info attribute, so that it can applyto a wider variety of ORM and extension constructs.

See also

QueryableAttribute.info

SchemaItem.info

class sqlalchemy.orm.state.InstanceState(obj, manager)
Bases: sqlalchemy.orm.base.InspectionAttrInfo

tracks state information at the instance level.

The InstanceState is a key object used by theSQLAlchemy ORM in order to track the state of an object;it is created the moment an object is instantiated, typicallyas a result of instrumentation which SQLAlchemy appliesto the init() method of the class.

InstanceState is also a semi-public object,available for runtime inspection as to the state of amapped instance, including information such as its currentstatus within a particular Session and detailsabout data on individual attributes. The public APIin order to acquire a InstanceState objectis to use the inspect() system:

>>> from sqlalchemy import inspect
>>> insp = inspect(some_mapped_object)

See also

Runtime Inspection API

attrs
Return a namespace representing each attribute onthe mapped object, including its current valueand history.

The returned object is an instance of AttributeState.This object allows inspection of the current datawithin an attribute as well as attribute historysince the last flush.

callables = ()
A namespace where a per-state loader callable can be associated.

In SQLAlchemy 1.0, this is only used for lazy loaders / deferredloaders that were set up via query option.

Previously, callables was used also to indicate expired attributesby storing a link to the InstanceState itself in this dictionary.This role is now handled by the expired_attributes set.

property deleted
Return true if the object is deleted.

An object that is in the deleted state is guaranteed tonot be within the Session.identity_map of its parentSession; however if the session’s transaction is rolledback, the object will be restored to the persistent state andthe identity map.

Note

The InstanceState.deleted attribute refers to a specificstate of the object that occurs between the “persistent” and“detached” states; once the object is detached, theInstanceState.deleted attribute no longer returnsTrue; in order to detect that a state was deleted, regardlessof whether or not the object is associated with aSession, use the InstanceState.was_deletedaccessor.

See also

property detached
Return true if the object is detached.

See also

property dict
Return the instance dict used by the object.

Under normal circumstances, this is always synonymouswith the dict attribute of the mapped object,unless an alternative instrumentation system has beenconfigured.

In the case that the actual object has been garbagecollected, this accessor returns a blank dictionary.

expiredattributes = None_
The set of keys which are ‘expired’ to be loaded bythe manager’s deferred scalar loader, assuming no pendingchanges.

see also the unmodified collection which is intersectedagainst this set when a refresh operation occurs.

property has_identity
Return True if this object has an identity key.

This should always have the same value as theexpression state.persistent or state.detached.

property identity
Return the mapped identity of the mapped object.This is the primary key identity as persisted by the ORMwhich can always be passed directly toQuery.get().

Returns None if the object has no primary key identity.

Note

An object which is transient or pendingdoes not have a mapped identity until it is flushed,even if its attributes include primary key values.

property identity_key
Return the identity key for the mapped object.

This is the key used to locate the object withinthe Session.identity_map mapping. It containsthe identity as returned by identity within it.

mapper
Return the Mapper used for this mapped object.
property object
Return the mapped object represented by thisInstanceState.
property pending
Return true if the object is pending.

See also

property persistent
Return true if the object is persistent.

An object that is in the persistent state is guaranteed tobe within the Session.identity_map of its parentSession.

Changed in version 1.1: The InstanceState.persistentaccessor no longer returns True for an object that was“deleted” within a flush; use the InstanceState.deletedaccessor to detect this state. This allows the “persistent”state to guarantee membership in the identity map.

See also

property session
Return the owning Session for this instance,or None 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 deletedwill report as not in session, however if the transaction isstill in progress, this attribute will still refer to that session.Only when the transaction is completed does the object becomefully detached under normal circumstances.

property transient
Return true if the object is transient.

See also

property unloaded
Return the set of keys which do not have a loaded value.

This includes expired attributes and any other attribute thatwas never populated or modified.

property unloaded_expirable
Return the set of keys which do not have a loaded value.

This includes expired attributes and any other attribute thatwas never populated or modified.

property unmodified
Return the set of keys which have no uncommitted changes
unmodifiedintersection(_keys)
Return self.unmodified.intersection(keys).
property 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 explicitlyor via transaction commit and enters the “detached” state,this flag will continue to report True.

New in version 1.1: - added a local method form oform.util.was_deleted().

See also

InstanceState.deleted - refers to the “deleted” state

orm.util.was_deleted() - standalone function

QueryableAttribute.info

class sqlalchemy.orm.attributes.InstrumentedAttribute(class__, _key, impl=None, comparator=None, parententity=None, of_type=None)
Bases: sqlalchemy.orm.attributes.QueryableAttribute

Class bound instrumented attribute which adds basicdescriptor methods.

See QueryableAttribute for a description of most features.

delete(instance)
get(instance, owner)
set(instance, value)
- sqlalchemy.orm.interfaces.MANYTOONE = symbol('MANYTOONE')
- Indicates the many-to-one direction for a relationship().

This symbol is typically used by the internals but may be exposed withincertain API features.

sqlalchemy.orm.interfaces.MANYTOMANY = symbol('MANYTOMANY')
Indicates the many-to-many direction for a relationship().

This symbol is typically used by the internals but may be exposed withincertain API features.

class sqlalchemy.orm.interfaces.MapperProperty
Bases: sqlalchemy.orm.base._MappedAttribute, sqlalchemy.orm.base.InspectionAttr, sqlalchemy.util.langhelpers.MemoizedSlots

Represent a particular class attribute mapped by Mapper.

The most common occurrences of MapperProperty are themapped Column, which is represented in a mapping asan instance of ColumnProperty,and a reference to another class produced by relationship(),represented in the mapping as an instance ofRelationshipProperty.

info
Info dictionary associated with the object, allowing user-defineddata to be associated with this InspectionAttr.

The dictionary is generated when first accessed. Alternatively,it can be specified as a constructor argument to thecolumn_property(), relationship(), or composite()functions.

Changed in version 1.0.0: InspectionAttr.info movedfrom MapperProperty so that it can apply to a widervariety of ORM and extension constructs.

See also

SchemaItem.info

cascade = frozenset({})
The set of ‘cascade’ attribute names.

This collection is checked before the ‘cascade_iterator’ method is called.

The collection typically only applies to a RelationshipProperty.

cascadeiterator(type_, _state, visited_instances=None, halt_on=None)
Iterate through instances related to the given instance fora particular ‘cascade’, starting with this MapperProperty.

Return an iterator3-tuples (instance, mapper, state).

Note that the ‘cascade’ collection on this MapperProperty ischecked first for the given type before cascade_iterator is called.

This method typically only applies to RelationshipProperty.

property class_attribute
Return the class-bound descriptor corresponding to thisMapperProperty.

This is basically a getattr() call:

return getattr(self.parent.class_, self.key)

I.e. if this MapperProperty were named addresses,and the class to which it is mapped is User, this sequenceis 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

createrow_processor(_context, path, mapper, result, adapter, populators)
Produce row processing functions and append to the givenset of populators lists.
do_init()
Perform subclass-specific initialization post-mapper-creationsteps.

This is a template method called by the MapperPropertyobject’s init() method.

init()
Called after all mappers are created to assemblerelationships between mappers and perform other post-mapper-creationinitialization steps.
instrumentclass(_mapper)
Hook called by the Mapper to the property to initiateinstrumentation of the class attribute managed by thisMapperProperty.

The MapperProperty here will typically call out to theattributes 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.

isproperty = True_
Part of the InspectionAttr interface; states this object is amapper property.
merge(session, source_state, source_dict, dest_state, dest_dict, load, recursive_, resolveconflict_map)
Merge the attribute represented by this MapperPropertyfrom source to destination object.
postinstrument_class(_mapper)
Perform instrumentation adjustments that need to occurafter init() has completed.

The given Mapper is the Mapper invoking the operation, whichmay not be the same Mapper as self.parent in an inheritancescenario; however, Mapper will always at least be a sub-mapper ofself.parent.

This method is typically used by StrategizedProperty, which delegatesit to LoaderStrategy.init_class_attribute() to perform final setupon the class-bound InstrumentedAttribute.

setparent(_parent, init)
Set the parent mapper that references this MapperProperty.

This method is overridden by some subclasses to perform extrasetup when the mapper is first known.

setup(context, query_entity, path, adapter, **kwargs)
Called by Query for the purposes of constructing a SQL statement.

Each MapperProperty associated with the target mapper processes thestatement referenced by the query context, adding columns and/orcriterion as appropriate.

sqlalchemy.orm.interfaces.NOTEXTENSION = symbol('NOTEXTENSION')
Symbol indicating an InspectionAttr that’snot part of sqlalchemy.ext.

Is assigned to the InspectionAttr.extension_typeattribute.

sqlalchemy.orm.interfaces.ONETOMANY = symbol('ONETOMANY')
Indicates the one-to-many direction for a relationship().

This symbol is typically used by the internals but may be exposed withincertain API features.

class sqlalchemy.orm.interfaces.PropComparator(prop, parentmapper, adapt_to_entity=None)
Bases: sqlalchemy.sql.operators.ColumnOperators

Defines SQL operators for MapperProperty objects.

SQLAlchemy allows for operators tobe redefined at both the Core and ORM level. PropComparatoris the base class of operator redefinition for ORM-level operations,including those of ColumnProperty,RelationshipProperty, and CompositeProperty.

Note

With the advent of Hybrid properties introduced in SQLAlchemy0.7, as well as Core-level operator redefinition inSQLAlchemy 0.8, the use case for user-defined PropComparatorinstances is extremely rare. See Hybrid Attributes as wellas Redefining and Creating New Operators.

User-defined subclasses of PropComparator may be created. Thebuilt-in Python comparison and math operator methods, such asoperators.ColumnOperators.eq(),operators.ColumnOperators.lt(), andoperators.ColumnOperators.add(), can be overridden to providenew operator behavior. The custom PropComparator is passed tothe MapperProperty instance via the comparator_factoryargument. In each case,the appropriate subclass of PropComparator should be used:

# definition of custom PropComparator subclasses
 
from sqlalchemy.orm.properties import \
                        ColumnProperty,\
                        CompositeProperty,\
                        RelationshipProperty
 
class MyColumnComparator(ColumnProperty.Comparator):
    def __eq__(self, other):
        return self.__clause_element__() == other
 
class MyRelationshipComparator(RelationshipProperty.Comparator):
    def any(self, expression):
        "define the 'any' operation"
        # ...
 
class MyCompositeComparator(CompositeProperty.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 usuallysimpler to define the operators at the Core level, using theTypeEngine.comparator_factory attribute. SeeRedefining and Creating New Operators for more detail.

See also

ColumnProperty.Comparator

RelationshipProperty.Comparator

CompositeProperty.Comparator

eq(other)

inherited from theeq()method ofColumnOperators

Implement the == operator.

In a column context, produces the clause a = b.If the target is None, produces a IS NULL.

le(other)

inherited from thele()method ofColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

lt(other)

inherited from thelt()method ofColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

ne(other)

inherited from thene()method ofColumnOperators

Implement the != operator.

In a column context, produces the clause a != b.If the target is None, produces a IS NOT NULL.

adaptto_entity(_adapt_to_entity)
Return a copy of this PropComparator which will use the givenAliasedInsp to produce corresponding expressions.
property adapter
Produce a callable that adapts column expressionsto suit an aliased version of this comparator.
all()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.interfaces.PropComparator.all)

inherited from theall_()method ofColumnOperators

Produce a all_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
 
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

any(criterion=None, **kwargs)
Return true if this collection contains any member that meets thegiven criterion.

The usual implementation of any() isRelationshipProperty.Comparator.any().

- Parameters
- 
  -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

any()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.interfaces.PropComparator.any)

inherited from theany_()method ofColumnOperators

Produce a any_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
 
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

asc()

inherited from theasc()method ofColumnOperators

Produce a asc() clause against theparent object.

between(cleft, cright, symmetric=False)

inherited from thebetween()method ofColumnOperators

Produce a between() clause againstthe parent object, given the lower and upper range.

boolop(_opstring, precedence=0)

inherited from thebool_op()method ofOperators

Return a custom boolean operator.

This method is shorthand for callingOperators.op() and passing theOperators.op.is_comparisonflag with True.

New in version 1.2.0b3.

See also

collate(collation)

inherited from thecollate()method ofColumnOperators

Produce a collate() clause againstthe parent object, given the collation string.

See also

concat(other)

inherited from theconcat()method ofColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b,or uses the concat() operator on MySQL.

contains(other, **kwargs)

inherited from thecontains()method ofColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middleof a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.contains("foobar"))

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.contains("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

desc()

inherited from thedesc()method ofColumnOperators

Produce a desc() clause against theparent object.

distinct()

inherited from thedistinct()method ofColumnOperators

Produce a distinct() clause against theparent object.

endswith(other, **kwargs)

inherited from theendswith()method ofColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the endof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.endswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.endswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.endswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

has(criterion=None, **kwargs)
Return true if this element references a member which meets thegiven criterion.

The usual implementation of has() isRelationshipProperty.Comparator.has().

- Parameters
- 
  -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

ilike(other, escape=None)

inherited from theilike()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 ESCAPEkeyword, e.g.:

somecolumn.ilike("foo/%bar", escape="/")

See also

in(_other)

inherited from thein_()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 ofbound parameters the same length as the list given:

WHERE COL IN (?, ?, ?)

A list of tuples may be provided if the comparison is against atuple_() 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 a “false” expression,e.g.:

WHERE 1 != 1

This “false” expression has historically had different behaviorsin older SQLAlchemy versions, seecreate_engine.empty_in_strategy for behavioral options.

Changed in version 1.2: simplified the behavior of “empty in”expressions

A bound parameter, e.g. bindparam(), may be used if itincludes the bindparam.expanding flag:

stmt.where(column.in_(bindparam('value', expanding=True)))

In this calling form, the expression renders a special non-SQLplaceholder expression that looks like:

WHERE COL IN ([EXPANDING_value])

This placeholder expression is intercepted at statement executiontime to be converted into the variable number of bound parameterform 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. OnSQLite this would be:

WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

New in version 1.3: “expanding” bound parameters now supportempty lists

a select() construct, which is usually a correlatedscalar 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 a bindparam() construct that includes thebindparam.expanding flag set to True.

is(_other)

inherited from theis_()method ofColumnOperators

Implement the IS operator.

See also

isdistinct_from(_other)

inherited from theis_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”.

New in version 1.1.

isnot(other)

inherited from theisnot()method ofColumnOperators

Implement the IS NOT operator.

See also

isnotdistinct_from(_other)

inherited from theisnot_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”.

New in version 1.1.

like(other, escape=None)

inherited from thelike()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 ESCAPEkeyword, e.g.:

somecolumn.like("foo/%bar", escape="/")

See also

match(other, **kwargs)

inherited from thematch()method ofColumnOperators

Implements a database-specific ‘match’ operator.

match() attempts to resolve toa MATCH-like function or operator provided by the backend.Examples include:

PostgreSQL - renders x @@ to_tsquery(y)

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

Oracle - renders CONTAINS(x, y)

other backends may provide special implementations.

Backends without any special implementation will emitthe operator as “MATCH”. This is compatible with SQLite, forexample.

notilike(other, escape=None)

inherited from thenotilike()method ofColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation withColumnOperators.ilike(), i.e. ~x.ilike(y).

See also

notin(_other)

inherited from thenotin_()method ofColumnOperators

implement the NOT IN operator.

This is equivalent to using negation withColumnOperators.in_(), i.e. ~x.in_(y).

Changed in version 1.2: The ColumnOperators.in_() andColumnOperators.notin_() operatorsnow produce a “static” expression for an empty IN sequenceby default.

See also

notlike(other, escape=None)

inherited from thenotlike()method ofColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation withColumnOperators.like(), i.e. ~x.like(y).

See also

nullsfirst()

inherited from thenullsfirst()method ofColumnOperators

Produce a nullsfirst() clause against theparent object.

nullslast()

inherited from thenullslast()method ofColumnOperators

Produce a nullslast() clause against theparent object.

oftype(class)
Redefine this object in terms of a polymorphic subclass.

Returns a new PropComparator from which further criterion can beevaluated.

e.g.:

query.join(Company.employees.of_type(Engineer)).\
   filter(Engineer.name=='foo')

- Parameters
-

class_ – a class or mapper indicating that criterion will beagainst this specific subclass.

op(opstring, precedence=0, is_comparison=False, return_type=None)

inherited from theop()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. Forexample:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

- Parameters
- 
  -

operator – a string which will be output as the infix operatorbetween this element and the expression passed to thegenerated function.

is_comparison –

New in version 0.9.2: - added theOperators.op.is_comparison flag.

return_type –

New in version 1.2.0b3: - added theOperators.op.return_type argument.

See also

operate(op, *other, **kwargs)

inherited from theoperate()method ofOperators

Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))

- Parameters
- 
  -

op – Operator callable.

*other – the ‘other’ side of the operation. Willbe a single scalar for most operations.

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

reverseoperate(_op, other, **kwargs)

inherited from thereverse_operate()method ofOperators

Reverse operate on an argument.

Usage is the same as operate().

startswith(other, **kwargs)

inherited from thestartswith()method ofColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the startof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.startswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.startswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.startswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

Relationship Configuration

class sqlalchemy.orm.properties.RelationshipProperty(argument, secondary=None, primaryjoin=None, secondaryjoin=None, foreign_keys=None, uselist=None, order_by=False, backref=None, back_populates=None, post_update=False, cascade=False, extension=None, viewonly=False, lazy='select', collection_class=None, passive_deletes=False, passive_updates=True, remote_side=None, enable_typechecks=True, join_depth=None, comparator_factory=None, single_parent=False, innerjoin=False, distinct_target_key=None, doc=None, active_history=False, cascade_backrefs=True, load_on_pending=False, bake_queries=True, _local_remote_pairs=None, query_class=None, info=None, omit_join=None)
Bases: sqlalchemy.orm.interfaces.StrategizedProperty

Describes an object property that holds a single item or listof items that correspond to a related database table.

Public constructor is the orm.relationship() function.

See also

class Comparator(prop, parentmapper, adapt_to_entity=None, of_type=None)
Bases: sqlalchemy.orm.interfaces.PropComparator

Produce boolean, comparison, and other operators forRelationshipProperty attributes.

See the documentation for PropComparator for a briefoverview of ORM level operator definition.

See also

PropComparator

ColumnProperty.Comparator

- <code>__eq__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__)
-

Implement the == operator.

In a many-to-one context, such as:

MyClass.some_prop == <some object>

this will typically produce aclause such as:

mytable.related_id == <some id>

Where <some id> is the primary key of the givenobject.

The == operator provides partial functionality for non-many-to-one comparisons:

Comparisons against collections are not supported.Use contains().

Compared to a scalar one-to-many, will produce aclause that compares the target columns in the parent tothe given target.

Compared to a scalar many-to-many, an aliasof the association table will be rendered aswell, forming a natural join that is part of themain body of the query. This will not work forqueries that go beyond simple AND conjunctions ofcomparisons, such as those which use OR. Useexplicit joins, outerjoins, orhas() formore comprehensive non-many-to-one scalarmembership tests.

Comparisons against None given in a one-to-manyor many-to-many context produce a NOT EXISTS clause.

- <code>__init__</code>(_prop_, _parentmapper_, _adapt_to_entity=None_, _of_type=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__init__)
-

Construction of RelationshipProperty.Comparatoris internal to the ORM’s attribute mechanics.

- <code>__le__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__le__)
-

inherited from thele()method ofColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

- <code>__lt__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__lt__)
-

inherited from thelt()method ofColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

- <code>__ne__</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__)
-

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 thegiven object.

The != operator provides partial functionality for non-many-to-one comparisons:

Comparisons against collections are not supported.Usecontains()in conjunction with not_().

Compared to a scalar one-to-many, will produce aclause that compares the target columns in the parent tothe given target.

Compared to a scalar many-to-many, an aliasof the association table will be rendered aswell, forming a natural join that is part of themain body of the query. This will not work forqueries that go beyond simple AND conjunctions ofcomparisons, such as those which use OR. Useexplicit joins, outerjoins, orhas() inconjunction with not_() formore comprehensive non-many-to-one scalarmembership tests.

Comparisons against None given in a one-to-manyor many-to-many context produce an EXISTS clause.

- <code>adapt_to_entity</code>(_adapt_to_entity_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.adapt_to_entity)
-

Return a copy of this PropComparator which will use the givenAliasedInsp to produce corresponding expressions.

- _property _<code>adapter</code>[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.adapter)
-

Produce a callable that adapts column expressionsto suit an aliased version of this comparator.

- <code>all_</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.all_)
-

inherited from theall_()method ofColumnOperators

Produce a all_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
 
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

- <code>any</code>(_criterion=None_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any)
-

Produce an expression that tests a collection againstparticular 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 any() usesa correlated subquery, its performance is not nearly asgood when compared against large target tables as that ofusing a join.

any() is particularlyuseful 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)

any() is onlyvalid for collections, i.e. a relationship()that has uselist=True. For scalar references,use has().

- <code>any_</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any_)
-

inherited from theany_()method ofColumnOperators

Produce a any_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
 
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

- <code>asc</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.asc)
-

inherited from theasc()method ofColumnOperators

Produce a asc() clause against theparent object.

- <code>between</code>(_cleft_, _cright_, _symmetric=False_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.between)
-

inherited from thebetween()method ofColumnOperators

Produce a between() clause againstthe parent object, given the lower and upper range.

- <code>bool_op</code>(_opstring_, _precedence=0_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.bool_op)
-

inherited from thebool_op()method ofOperators

Return a custom boolean operator.

This method is shorthand for callingOperators.op() and passing theOperators.op.is_comparisonflag with True.

New in version 1.2.0b3.

See also

- <code>collate</code>(_collation_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.collate)
-

inherited from thecollate()method ofColumnOperators

Produce a collate() clause againstthe parent object, given the collation string.

See also

- <code>concat</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.concat)
-

inherited from theconcat()method ofColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b,or uses the concat() operator on MySQL.

- <code>contains</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains)
-

Return a simple expression that tests a collection forcontainment of a particular item.

contains() isonly valid for a collection, i.e. arelationship() that implementsone-to-many or many-to-many with uselist=True.

When used in a simple one-to-many context, anexpression like:

MyClass.contains(other)

Produces a clause like:

mytable.id == <some id>

Where <some id> is the value of the foreign keyattribute on other which refers to the primarykey of its parent object. From this it follows thatcontains() isvery useful when used with simple one-to-manyoperations.

For many-to-many operations, the behavior ofcontains()has more caveats. The association table will berendered in the statement, producing an “implicit”join, that is, includes multiple tables in the FROMclause 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 thatcontains()will not work with many-to-many collections whenused in queries that move beyond simple ANDconjunctions, such as multiplecontains()expressions joined by OR. In such cases subqueries orexplicit “outer joins” will need to be used instead.See any() fora less-performant alternative using EXISTS, or referto Query.outerjoin() as well as Querying with Joinsfor more details on constructing outer joins.

- <code>desc</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.desc)
-

inherited from thedesc()method ofColumnOperators

Produce a desc() clause against theparent object.

- <code>distinct</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.distinct)
-

inherited from thedistinct()method ofColumnOperators

Produce a distinct() clause against theparent object.

- <code>endswith</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.endswith)
-

inherited from theendswith()method ofColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the endof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.endswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.endswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  - Parameters
  - 
    -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.endswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

- <code>entity</code>[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.entity)
-

The target entity referred to by thisRelationshipProperty.Comparator.

This is either a Mapper or AliasedInspobject.

This is the “target” or “remote” side of therelationship().

- <code>has</code>(_criterion=None_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has)
-

Produce an expression that tests a scalar reference againstparticular 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 has() usesa correlated subquery, its performance is not nearly asgood when compared against large target tables as that ofusing a join.

has() is onlyvalid for scalar references, i.e. a relationship()that has uselist=False. For collection references,use any().

- <code>ilike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.ilike)
-

inherited from theilike()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 ESCAPEkeyword, e.g.:

somecolumn.ilike("foo/%bar", escape="/")

See also

- <code>in_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.in_)
-

Produce an IN clause - this is not implementedfor relationship()-based attributes at this time.

- <code>is_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.is_)
-

inherited from theis_()method ofColumnOperators

Implement the IS operator.

See also

- <code>is_distinct_from</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.is_distinct_from)
-

inherited from theis_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”.

New in version 1.1.

- <code>isnot</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.isnot)
-

inherited from theisnot()method ofColumnOperators

Implement the IS NOT operator.

See also

- <code>isnot_distinct_from</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.isnot_distinct_from)
-

inherited from theisnot_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”.

New in version 1.1.

- <code>like</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.like)
-

inherited from thelike()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 ESCAPEkeyword, e.g.:

somecolumn.like("foo/%bar", escape="/")

See also

- <code>mapper</code>[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.mapper)
-

The target Mapper referred to by thisRelationshipProperty.Comparator.

This is the “target” or “remote” side of therelationship().

- <code>match</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.match)
-

inherited from thematch()method ofColumnOperators

Implements a database-specific ‘match’ operator.

match() attempts to resolve toa MATCH-like function or operator provided by the backend.Examples include:

PostgreSQL - renders x @@ to_tsquery(y)

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

Oracle - renders CONTAINS(x, y)

other backends may provide special implementations.

Backends without any special implementation will emitthe operator as “MATCH”. This is compatible with SQLite, forexample.

- <code>notilike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.notilike)
-

inherited from thenotilike()method ofColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation withColumnOperators.ilike(), i.e. ~x.ilike(y).

See also

- <code>notin_</code>(_other_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.notin_)
-

inherited from thenotin_()method ofColumnOperators

implement the NOT IN operator.

This is equivalent to using negation withColumnOperators.in_(), i.e. ~x.in_(y).

Changed in version 1.2: The ColumnOperators.in_() andColumnOperators.notin_() operatorsnow produce a “static” expression for an empty IN sequenceby default.

See also

- <code>notlike</code>(_other_, _escape=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.notlike)
-

inherited from thenotlike()method ofColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation withColumnOperators.like(), i.e. ~x.like(y).

See also

- <code>nullsfirst</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.nullsfirst)
-

inherited from thenullsfirst()method ofColumnOperators

Produce a nullsfirst() clause against theparent object.

- <code>nullslast</code>()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.nullslast)
-

inherited from thenullslast()method ofColumnOperators

Produce a nullslast() clause against theparent object.

- <code>of_type</code>(_cls_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.of_type)
-

Redefine this object in terms of a polymorphic subclass.

See PropComparator.of_type() for an example.

- <code>op</code>(_opstring_, _precedence=0_, _is_comparison=False_, _return_type=None_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.op)
-

inherited from theop()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. Forexample:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

  - Parameters
  - 
    -

operator – a string which will be output as the infix operatorbetween this element and the expression passed to thegenerated function.

is_comparison –

New in version 0.9.2: - added theOperators.op.is_comparison flag.

return_type –

New in version 1.2.0b3: - added theOperators.op.return_type argument.

See also

- <code>operate</code>(_op_, _*other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.operate)
-

inherited from theoperate()method ofOperators

Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))

  - Parameters
  - 
    -

op – Operator callable.

*other – the ‘other’ side of the operation. Willbe a single scalar for most operations.

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

- <code>reverse_operate</code>(_op_, _other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.reverse_operate)
-

inherited from thereverse_operate()method ofOperators

Reverse operate on an argument.

Usage is the same as operate().

- <code>startswith</code>(_other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.Comparator.startswith)
-

inherited from thestartswith()method ofColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the startof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.startswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.startswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  - Parameters
  - 
    -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.startswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

Mapper.all_orm_descriptors

eq()

inherited from the eq() method of object

Return self==value.

init(argument, secondary=None, primaryjoin=None, secondaryjoin=None, foreign_keys=None, uselist=None, order_by=False, backref=None, back_populates=None, post_update=False, cascade=False, extension=None, viewonly=False, lazy='select', collection_class=None, passive_deletes=False, passive_updates=True, remote_side=None, enable_typechecks=True, join_depth=None, comparator_factory=None, single_parent=False, innerjoin=False, distinct_target_key=None, doc=None, active_history=False, cascade_backrefs=True, load_on_pending=False, bake_queries=True, localremote_pairs=None, query_class=None, info=None, omit_join=None)[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.properties.RelationshipProperty.init__)
Construct a new RelationshipProperty object.

This constructor is mirrored as a public API function; see relationship() for a full usage and argument description.

le()

inherited from the le() method of object

Return self<=value.

lt()

inherited from the lt() method of object

Return self

class sqlalchemy.orm.descriptorprops.SynonymProperty(_name, map_column=None, descriptor=None, comparator_factory=None, doc=None, info=None)
Bases: sqlalchemy.orm.descriptor_props.DescriptorProperty
- eq()

inherited from the eq() method of object

Return self==value.

init(name, map_column=None, descriptor=None, comparator_factory=None, doc=None, info=None)
Construct a new SynonymProperty object.

This constructor is mirrored as a public API function; see synonym() for a full usage and argument description.

le()

inherited from the le() method of object

Return self<=value.

lt()

inherited from the lt() method of object

Return self

class sqlalchemy.orm.query.QueryContext(query)
class sqlalchemy.orm.attributes.QueryableAttribute(class__, _key, impl=None, comparator=None, parententity=None, of_type=None)
Bases: sqlalchemy.orm.base._MappedAttribute, sqlalchemy.orm.base.InspectionAttr, sqlalchemy.orm.interfaces.PropComparator

Base class for descriptor objects that interceptattribute events on behalf of a MapperPropertyobject. The actual MapperProperty is accessiblevia the QueryableAttribute.propertyattribute.

See also

InstrumentedAttribute

MapperProperty

Mapper.attrs

eq(other)

inherited from theeq()method ofColumnOperators

Implement the == operator.

In a column context, produces the clause a = b.If the target is None, produces a IS NULL.

le(other)

inherited from thele()method ofColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

lt(other)

inherited from thelt()method ofColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

ne(other)

inherited from thene()method ofColumnOperators

Implement the != operator.

In a column context, produces the clause a != b.If the target is None, produces a IS NOT NULL.

adaptto_entity(_adapt_to_entity)
Return a copy of this PropComparator which will use the givenAliasedInsp to produce corresponding expressions.
property adapter
Produce a callable that adapts column expressionsto suit an aliased version of this comparator.
all()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.attributes.QueryableAttribute.all)

inherited from theall_()method ofColumnOperators

Produce a all_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
 
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

any(criterion=None, **kwargs)

inherited from theany()method ofPropComparator

Return true if this collection contains any member that meets thegiven criterion.

The usual implementation of any() isRelationshipProperty.Comparator.any().

- Parameters
- 
  -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

any()[](https://docs.sqlalchemy.org/en/13/orm/#sqlalchemy.orm.attributes.QueryableAttribute.any)

inherited from theany_()method ofColumnOperators

Produce a any_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
 
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

asc()

inherited from theasc()method ofColumnOperators

Produce a asc() clause against theparent object.

between(cleft, cright, symmetric=False)

inherited from thebetween()method ofColumnOperators

Produce a between() clause againstthe parent object, given the lower and upper range.

boolop(_opstring, precedence=0)

inherited from thebool_op()method ofOperators

Return a custom boolean operator.

This method is shorthand for callingOperators.op() and passing theOperators.op.is_comparisonflag with True.

New in version 1.2.0b3.

See also

collate(collation)

inherited from thecollate()method ofColumnOperators

Produce a collate() clause againstthe parent object, given the collation string.

See also

concat(other)

inherited from theconcat()method ofColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b,or uses the concat() operator on MySQL.

contains(other, **kwargs)

inherited from thecontains()method ofColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middleof a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.contains("foobar"))

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.contains("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

desc()

inherited from thedesc()method ofColumnOperators

Produce a desc() clause against theparent object.

distinct()

inherited from thedistinct()method ofColumnOperators

Produce a distinct() clause against theparent object.

endswith(other, **kwargs)

inherited from theendswith()method ofColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the endof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.endswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.endswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.endswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also

extensiontype = symbol('NOTEXTENSION')
has(criterion=None, **kwargs)

inherited from thehas()method ofPropComparator

Return true if this element references a member which meets thegiven criterion.

The usual implementation of has() isRelationshipProperty.Comparator.has().

- Parameters
- 
  -

criterion – an optional ClauseElement formulated against themember class’ table or attributes.

**kwargs – key/value pairs corresponding to member classattribute names which will be compared via equality to thecorresponding values.

ilike(other, escape=None)

inherited from theilike()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 ESCAPEkeyword, e.g.:

somecolumn.ilike("foo/%bar", escape="/")

See also

in(_other)

inherited from thein_()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 ofbound parameters the same length as the list given:

WHERE COL IN (?, ?, ?)

A list of tuples may be provided if the comparison is against atuple_() 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 a “false” expression,e.g.:

WHERE 1 != 1

This “false” expression has historically had different behaviorsin older SQLAlchemy versions, seecreate_engine.empty_in_strategy for behavioral options.

Changed in version 1.2: simplified the behavior of “empty in”expressions

A bound parameter, e.g. bindparam(), may be used if itincludes the bindparam.expanding flag:

stmt.where(column.in_(bindparam('value', expanding=True)))

In this calling form, the expression renders a special non-SQLplaceholder expression that looks like:

WHERE COL IN ([EXPANDING_value])

This placeholder expression is intercepted at statement executiontime to be converted into the variable number of bound parameterform 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. OnSQLite this would be:

WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

New in version 1.3: “expanding” bound parameters now supportempty lists

a select() construct, which is usually a correlatedscalar 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 a bindparam() construct that includes thebindparam.expanding flag set to True.

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 directlyto a schema-level Column object, this attributewill return the SchemaItem.info dictionary associatedwith the core-level Column object.

If the attribute is a ColumnProperty but is mapped toany other kind of SQL expression other than a Column,the attribute will refer to the MapperProperty.infodictionary associated directly with the ColumnProperty,assuming the SQL expression itself does not have its own .infoattribute (which should be the case, unless a user-defined SQLconstruct has defined one).

If the attribute refers to any other kind ofMapperProperty, including RelationshipProperty,the attribute will refer to the MapperProperty.infodictionary associated with that MapperProperty.

To access the MapperProperty.info dictionary of theMapperProperty unconditionally, including for aColumnProperty that’s associated directly with aschema.Column, the attribute can be referred to usingQueryableAttribute.property attribute, asMyClass.someattribute.property.info.

See also

SchemaItem.info

MapperProperty.info

is(_other)

inherited from theis_()method ofColumnOperators

Implement the IS operator.

See also

isdistinct_from(_other)

inherited from theis_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”.

New in version 1.1.

isnot(other)

inherited from theisnot()method ofColumnOperators

Implement the IS NOT operator.

See also

isnotdistinct_from(_other)

inherited from theisnot_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”.

New in version 1.1.

like(other, escape=None)

inherited from thelike()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 ESCAPEkeyword, e.g.:

somecolumn.like("foo/%bar", escape="/")

See also

match(other, **kwargs)

inherited from thematch()method ofColumnOperators

Implements a database-specific ‘match’ operator.

match() attempts to resolve toa MATCH-like function or operator provided by the backend.Examples include:

PostgreSQL - renders x @@ to_tsquery(y)

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

Oracle - renders CONTAINS(x, y)

other backends may provide special implementations.

Backends without any special implementation will emitthe operator as “MATCH”. This is compatible with SQLite, forexample.

notilike(other, escape=None)

inherited from thenotilike()method ofColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation withColumnOperators.ilike(), i.e. ~x.ilike(y).

See also

notin(_other)

inherited from thenotin_()method ofColumnOperators

implement the NOT IN operator.

This is equivalent to using negation withColumnOperators.in_(), i.e. ~x.in_(y).

Changed in version 1.2: The ColumnOperators.in_() andColumnOperators.notin_() operatorsnow produce a “static” expression for an empty IN sequenceby default.

See also

notlike(other, escape=None)

inherited from thenotlike()method ofColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation withColumnOperators.like(), i.e. ~x.like(y).

See also

nullsfirst()

inherited from thenullsfirst()method ofColumnOperators

Produce a nullsfirst() clause against theparent object.

nullslast()

inherited from thenullslast()method ofColumnOperators

Produce a nullslast() clause against theparent object.

oftype(_cls)
Redefine this object in terms of a polymorphic subclass.

Returns a new PropComparator from which further criterion can beevaluated.

e.g.:

query.join(Company.employees.of_type(Engineer)).\
   filter(Engineer.name=='foo')

- Parameters
-

class_ – a class or mapper indicating that criterion will beagainst this specific subclass.

op(opstring, precedence=0, is_comparison=False, return_type=None)

inherited from theop()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. Forexample:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

- Parameters
- 
  -

operator – a string which will be output as the infix operatorbetween this element and the expression passed to thegenerated function.

is_comparison –

New in version 0.9.2: - added theOperators.op.is_comparison flag.

return_type –

New in version 1.2.0b3: - added theOperators.op.return_type argument.

See also

operate(op, *other, **kwargs)
Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))

- Parameters
- 
  -

op – Operator callable.

*other – the ‘other’ side of the operation. Willbe a single scalar for most operations.

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

parent
Return an inspection instance representing the parent.

This will be either an instance of Mapperor AliasedInsp, depending upon the natureof the parent entity which this attribute is associatedwith.

property
Return the MapperProperty associated with thisQueryableAttribute.

Return values here will commonly be instances ofColumnProperty or RelationshipProperty.

reverseoperate(_op, other, **kwargs)
Reverse operate on an argument.

Usage is the same as operate().

startswith(other, **kwargs)

inherited from thestartswith()method ofColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the startof 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 expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.startswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.startswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

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".

New in version 1.2.

escape –

An expression such as:

somecolumn.startswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.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.

See also