associationproxy is used to create a read/write view of a
target attribute across a relationship. It essentially conceals
the usage of a “middle” attribute between two endpoints, and
can be used to cherry-pick fields from a collection of
related objects or to reduce the verbosity of using the association
object pattern. Applied creatively, the association proxy allows
the construction of sophisticated collections and dictionary
views of virtually any geometry, persisted to the database using
standard, transparently configured relational patterns.
Consider a many-to-many mapping between two classes, User and Keyword.
Each User can have any number of Keyword objects, and vice-versa
(the many-to-many pattern is described at Many To Many):
from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
kw = relationship("Keyword", secondary=lambda: userkeywords_table)
def __init__(self, name):
self.name = name
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keyword
userkeywords_table = Table('userkeywords', Base.metadata,
Column('user_id', Integer, ForeignKey("user.id"),
primary_key=True),
Column('keyword_id', Integer, ForeignKey("keyword.id"),
primary_key=True)
)Reading and manipulating the collection of “keyword” strings associated
with User requires traversal from each collection element to the .keyword
attribute, which can be awkward:
>>> user = User('jek')
>>> user.kw.append(Keyword('cheese inspector'))
>>> print(user.kw)
[<__main__.Keyword object at 0x12bf830>]
>>> print(user.kw[0].keyword)
cheese inspector
>>> print([keyword.keyword for keyword in user.kw])
['cheese inspector']The association_proxy is applied to the User class to produce
a “view” of the kw relationship, which only exposes the string
value of .keyword associated with each Keyword object:
from sqlalchemy.ext.associationproxy import association_proxy
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
kw = relationship("Keyword", secondary=lambda: userkeywords_table)
def __init__(self, name):
self.name = name
# proxy the 'keyword' attribute from the 'kw' relationship
keywords = association_proxy('kw', 'keyword')We can now reference the .keywords collection as a listing of strings,
which is both readable and writable. New Keyword objects are created
for us transparently:
>>> user = User('jek')
>>> user.keywords.append('cheese inspector')
>>> user.keywords
['cheese inspector']
>>> user.keywords.append('snack ninja')
>>> user.kw
[<__main__.Keyword object at 0x12cdd30>, <__main__.Keyword object at 0x12cde30>]The AssociationProxy object produced by the association_proxy() function
is an instance of a Python descriptor.
It is always declared with the user-defined class being mapped, regardless of
whether Declarative or classical mappings via the mapper() function are used.
The proxy functions by operating upon the underlying mapped attribute or collection in response to operations, and changes made via the proxy are immediately apparent in the mapped attribute, as well as vice versa. The underlying attribute remains fully accessible.
When first accessed, the association proxy performs introspection operations on the target collection so that its behavior corresponds correctly. Details such as if the locally proxied attribute is a collection (as is typical) or a scalar reference, as well as if the collection acts like a set, list, or dictionary is taken into account, so that the proxy should act just like the underlying collection or attribute does.
When a list append() event (or set add(), dictionary __setitem__(), or scalar assignment event) is intercepted by the association proxy, it instantiates a new instance of the “intermediary” object using its constructor, passing as a single argument the given value. In our example above, an operation like:
user.keywords.append('cheese inspector')Is translated by the association proxy into the operation:
user.kw.append(Keyword('cheese inspector'))The example works here because we have designed the constructor for Keyword
to accept a single positional argument, keyword. For those cases where a
single-argument constructor isn’t feasible, the association proxy’s creational
behavior can be customized using the creator argument, which references a
callable (i.e. Python function) that will produce a new object instance given the
singular argument. Below we illustrate this using a lambda as is typical:
class User(Base):
# ...
# use Keyword(keyword=kw) on append() events
keywords = association_proxy('kw', 'keyword',
creator=lambda kw: Keyword(keyword=kw))The creator function accepts a single argument in the case of a list-
or set- based collection, or a scalar attribute. In the case of a dictionary-based
collection, it accepts two arguments, “key” and “value”. An example
of this is below in Proxying to Dictionary Based Collections.
The “association object” pattern is an extended form of a many-to-many relationship, and is described at Association Object. Association proxies are useful for keeping “association objects” out of the way during regular use.
Suppose our userkeywords table above had additional columns
which we’d like to map explicitly, but in most cases we don’t
require direct access to these attributes. Below, we illustrate
a new mapping which introduces the UserKeyword class, which
is mapped to the userkeywords table illustrated earlier.
This class adds an additional column special_key, a value which
we occasionally want to access, but not in the usual case. We
create an association proxy on the User class called
keywords, which will bridge the gap from the user_keywords
collection of User to the .keyword attribute present on each
UserKeyword:
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
# association proxy of "user_keywords" collection
# to "keyword" attribute
keywords = association_proxy('user_keywords', 'keyword')
def __init__(self, name):
self.name = name
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
special_key = Column(String(50))
# bidirectional attribute/collection of "user"/"user_keywords"
user = relationship(User,
backref=backref("user_keywords",
cascade="all, delete-orphan")
)
# reference to the "Keyword" object
keyword = relationship("Keyword")
def __init__(self, keyword=None, user=None, special_key=None):
self.user = user
self.keyword = keyword
self.special_key = special_key
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)With the above configuration, we can operate upon the .keywords
collection of each User object, and the usage of UserKeyword
is concealed:
>>> user = User('log')
>>> for kw in (Keyword('new_from_blammo'), Keyword('its_big')):
... user.keywords.append(kw)
...
>>> print(user.keywords)
[Keyword('new_from_blammo'), Keyword('its_big')]Where above, each .keywords.append() operation is equivalent to:
>>> user.user_keywords.append(UserKeyword(Keyword('its_heavy')))The UserKeyword association object has two attributes here which are populated;
the .keyword attribute is populated directly as a result of passing
the Keyword object as the first argument. The .user argument is then
assigned as the UserKeyword object is appended to the User.user_keywords
collection, where the bidirectional relationship configured between User.user_keywords
and UserKeyword.user results in a population of the UserKeyword.user attribute.
The special_key argument above is left at its default value of None.
For those cases where we do want special_key to have a value, we
create the UserKeyword object explicitly. Below we assign all three
attributes, where the assignment of .user has the effect of the UserKeyword
being appended to the User.user_keywords collection:
>>> UserKeyword(Keyword('its_wood'), user, special_key='my special key')The association proxy returns to us a collection of Keyword objects represented
by all these operations:
>>> user.keywords
[Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]The association proxy can proxy to dictionary based collections as well. SQLAlchemy
mappings usually use the attribute_mapped_collection() collection type to
create dictionary collections, as well as the extended techniques described in
Custom Dictionary-Based Collections.
The association proxy adjusts its behavior when it detects the usage of a
dictionary-based collection. When new values are added to the dictionary, the
association proxy instantiates the intermediary object by passing two
arguments to the creation function instead of one, the key and the value. As
always, this creation function defaults to the constructor of the intermediary
class, and can be customized using the creator argument.
Below, we modify our UserKeyword example such that the User.user_keywords
collection will now be mapped using a dictionary, where the UserKeyword.special_key
argument will be used as the key for the dictionary. We then apply a creator
argument to the User.keywords proxy so that these values are assigned appropriately
when new elements are added to the dictionary:
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
# proxy to 'user_keywords', instantiating UserKeyword
# assigning the new key to 'special_key', values to
# 'keyword'.
keywords = association_proxy('user_keywords', 'keyword',
creator=lambda k, v:
UserKeyword(special_key=k, keyword=v)
)
def __init__(self, name):
self.name = name
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
special_key = Column(String)
# bidirectional user/user_keywords relationships, mapping
# user_keywords with a dictionary against "special_key" as key.
user = relationship(User, backref=backref(
"user_keywords",
collection_class=attribute_mapped_collection("special_key"),
cascade="all, delete-orphan"
)
)
keyword = relationship("Keyword")
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)We illustrate the .keywords collection as a dictionary, mapping the
UserKeyword.string_key value to Keyword objects:
>>> user = User('log')
>>> user.keywords['sk1'] = Keyword('kw1')
>>> user.keywords['sk2'] = Keyword('kw2')
>>> print(user.keywords)
{'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}Given our previous examples of proxying from relationship to scalar
attribute, proxying across an association object, and proxying dictionaries,
we can combine all three techniques together to give User
a keywords dictionary that deals strictly with the string value
of special_key mapped to the string keyword. Both the UserKeyword
and Keyword classes are entirely concealed. This is achieved by building
an association proxy on User that refers to an association proxy
present on UserKeyword:
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
# the same 'user_keywords'->'keyword' proxy as in
# the basic dictionary example
keywords = association_proxy(
'user_keywords',
'keyword',
creator=lambda k, v:
UserKeyword(special_key=k, keyword=v)
)
def __init__(self, name):
self.name = name
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'),
primary_key=True)
special_key = Column(String)
user = relationship(User, backref=backref(
"user_keywords",
collection_class=attribute_mapped_collection("special_key"),
cascade="all, delete-orphan"
)
)
# the relationship to Keyword is now called
# 'kw'
kw = relationship("Keyword")
# 'keyword' is changed to be a proxy to the
# 'keyword' attribute of 'Keyword'
keyword = association_proxy('kw', 'keyword')
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keywordUser.keywords is now a dictionary of string to string, where
UserKeyword and Keyword objects are created and removed for us
transparently using the association proxy. In the example below, we illustrate
usage of the assignment operator, also appropriately handled by the
association proxy, to apply a dictionary value to the collection at once:
>>> user = User('log')
>>> user.keywords = {
... 'sk1':'kw1',
... 'sk2':'kw2'
... }
>>> print(user.keywords)
{'sk1': 'kw1', 'sk2': 'kw2'}
>>> user.keywords['sk3'] = 'kw3'
>>> del user.keywords['sk2']
>>> print(user.keywords)
{'sk1': 'kw1', 'sk3': 'kw3'}
>>> # illustrate un-proxied usage
... print(user.user_keywords['sk3'].kw)
<__main__.Keyword object at 0x12ceb90>One caveat with our example above is that because Keyword objects are created
for each dictionary set operation, the example fails to maintain uniqueness for
the Keyword objects on their string name, which is a typical requirement for
a tagging scenario such as this one. For this use case the recipe
UniqueObject, or
a comparable creational strategy, is
recommended, which will apply a “lookup first, then create” strategy to the constructor
of the Keyword class, so that an already existing Keyword is returned if the
given name is already present.
The AssociationProxy features simple SQL construction capabilities
which relate down to the underlying relationship() in use as well
as the target attribute. For example, the RelationshipProperty.Comparator.any()
and RelationshipProperty.Comparator.has() operations are available, and will produce
a “nested” EXISTS clause, such as in our basic association object example:
>>> print(session.query(User).filter(User.keywords.any(keyword='jek')))
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE EXISTS (SELECT 1
FROM user_keyword
WHERE user.id = user_keyword.user_id AND (EXISTS (SELECT 1
FROM keyword
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)))For a proxy to a scalar attribute, __eq__() is supported:
>>> print(session.query(UserKeyword).filter(UserKeyword.keyword == 'jek'))
SELECT user_keyword.*
FROM user_keyword
WHERE EXISTS (SELECT 1
FROM keyword
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)and .contains() is available for a proxy to a scalar collection:
>>> print(session.query(User).filter(User.keywords.contains('jek')))
SELECT user.*
FROM user
WHERE EXISTS (SELECT 1
FROM userkeywords, keyword
WHERE user.id = userkeywords.user_id
AND keyword.id = userkeywords.keyword_id
AND keyword.keyword = :keyword_1)AssociationProxy can be used with Query.join() somewhat manually
using the attr attribute in a star-args context:
q = session.query(User).join(*User.keywords.attr)attr is composed of AssociationProxy.local_attr and AssociationProxy.remote_attr,
which are just synonyms for the actual proxied attributes, and can also
be used for querying:
uka = aliased(UserKeyword)
ka = aliased(Keyword)
q = session.query(User).\
join(uka, User.keywords.local_attr).\
join(ka, User.keywords.remote_attr)New in version 1.3.
Given a mapping as:
class A(Base):
__tablename__ = 'test_a'
id = Column(Integer, primary_key=True)
ab = relationship(
'AB', backref='a', uselist=False)
b = association_proxy(
'ab', 'b', creator=lambda b: AB(b=b),
cascade_scalar_deletes=True)
class B(Base):
__tablename__ = 'test_b'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref='b', cascade='all, delete-orphan')
class AB(Base):
__tablename__ = 'test_ab'
a_id = Column(Integer, ForeignKey(A.id), primary_key=True)
b_id = Column(Integer, ForeignKey(B.id), primary_key=True)An assignment to A.b will generate an AB object:
a.b = B()The A.b association is scalar, and includes use of the flag
AssociationProxy.cascade_scalar_deletes. When set, setting A.b
to None will remove A.ab as well:
a.b = None
assert a.ab is NoneWhen AssociationProxy.cascade_scalar_deletes is not set,
the association object a.ab above would remain in place.
Note that this is not the behavior for collection-based association proxies; in that case, the intermediary association object is always removed when members of the proxied collection are removed. Whether or not the row is deleted depends on the relationship cascade setting.
See also
sqlalchemy.ext.associationproxy.association_proxy(target_collection, attr, **kw)¶Return a Python property implementing a view of a target attribute which references an attribute on members of the target.
The returned value is an instance of AssociationProxy.
Implements a Python property representing a relationship as a collection of simpler values, or a scalar value. The proxied property will mimic the collection type of the target (list, dict or set), or, in the case of a one to one relationship, a simple scalar value.
| Parameters: |
|
|---|
sqlalchemy.ext.associationproxy.AssociationProxy(target_collection, attr, creator=None, getset_factory=None, proxy_factory=None, proxy_bulk_set=None, info=None, cascade_scalar_deletes=False)¶Bases: sqlalchemy.orm.base.InspectionAttrInfo
A descriptor that presents a read/write view of an object attribute.
__eq__¶__eq__ attribute of objectReturn self==value.
__init__(target_collection, attr, creator=None, getset_factory=None, proxy_factory=None, proxy_bulk_set=None, info=None, cascade_scalar_deletes=False)¶Construct a new AssociationProxy.
The association_proxy() function is provided as the usual
entrypoint here, though AssociationProxy can be instantiated
and/or subclassed directly.
| Parameters: |
|
|---|
__le__¶__le__ attribute of objectReturn self<=value.
__lt__¶__lt__ attribute of objectReturn self<value.
__ne__¶__ne__ attribute of objectReturn self!=value.
extension_type = symbol('ASSOCIATION_PROXY')¶for_class(class_, obj=None)¶Return the internal state local to a specific mapped class.
E.g., given a class User:
class User(Base):
# ...
keywords = association_proxy('kws', 'keyword')If we access this AssociationProxy from
Mapper.all_orm_descriptors, and we want to view the
target class for this proxy as mapped by User:
inspect(User).all_orm_descriptors["keywords"].for_class(User).target_classThis returns an instance of AssociationProxyInstance that
is specific to the User class. The AssociationProxy
object remains agnostic of its parent class.
| Parameters: |
|---|
New in version 1.3: - AssociationProxy no longer stores
any state specific to a particular parent class; the state is now
stored in per-class AssociationProxyInstance objects.
info¶info attribute of InspectionAttrInfoInfo dictionary associated with the object, allowing user-defined
data to be associated with this InspectionAttr.
The dictionary is generated when first accessed. Alternatively,
it can be specified as a constructor argument to the
column_property(), relationship(), or composite()
functions.
Changed in version 1.0.0: MapperProperty.info is also
available on extension types via the
InspectionAttrInfo.info attribute, so that it can apply
to a wider variety of ORM and extension constructs.
is_aliased_class = False¶is_attribute = True¶is_clause_element = False¶is_instance = False¶is_mapper = False¶is_property = False¶is_selectable = False¶sqlalchemy.ext.associationproxy.AssociationProxyInstance(parent, owning_class, target_class, value_attr)¶A per-class object that serves class- and object-specific results.
This is used by AssociationProxy when it is invoked
in terms of a specific class or instance of a class, i.e. when it is
used as a regular Python descriptor.
When referring to the AssociationProxy as a normal Python
descriptor, the AssociationProxyInstance is the object that
actually serves the information. Under normal circumstances, its presence
is transparent:
>>> User.keywords.scalar
FalseIn the special case that the AssociationProxy object is being
accessed directly, in order to get an explicit handle to the
AssociationProxyInstance, use the
AssociationProxy.for_class() method:
proxy_state = inspect(User).all_orm_descriptors["keywords"].for_class(User)
# view if proxy object is scalar or not
>>> proxy_state.scalar
FalseNew in version 1.3.
__eq__¶__eq__ attribute of objectReturn self==value.
__le__¶__le__ attribute of objectReturn self<=value.
__lt__¶__lt__ attribute of objectReturn self<value.
__ne__¶__ne__ attribute of objectReturn self!=value.
any(criterion=None, **kwargs)¶Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
attr¶Return a tuple of (local_attr, remote_attr).
This attribute is convenient when specifying a join
using Query.join() across two relationships:
sess.query(Parent).join(*Parent.proxied.attr)delete(obj)¶for_proxy(parent, owning_class, parent_instance)¶get(obj)¶has(criterion=None, **kwargs)¶Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
info¶local_attr¶The ‘local’ class attribute referenced by this
AssociationProxyInstance.
remote_attr¶The ‘remote’ class attribute referenced by this
AssociationProxyInstance.
scalar¶Return True if this AssociationProxyInstance
proxies a scalar relationship on the local side.
set(obj, values)¶target_class = None¶The intermediary class handled by this
AssociationProxyInstance.
Intercepted append/set/assignment events will result in the generation of new instances of this class.
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance(parent, owning_class, target_class, value_attr)¶Bases: sqlalchemy.ext.associationproxy.AssociationProxyInstance
an AssociationProxyInstance that has an object as a target.
__le__¶__le__ attribute of objectReturn self<=value.
__lt__¶__lt__ attribute of objectReturn self<value.
any(criterion=None, **kwargs)¶any() method of AssociationProxyInstanceProduce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
attr¶attr attribute of AssociationProxyInstanceReturn a tuple of (local_attr, remote_attr).
This attribute is convenient when specifying a join
using Query.join() across two relationships:
sess.query(Parent).join(*Parent.proxied.attr)contains(obj)¶Produce a proxied ‘contains’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
, RelationshipProperty.Comparator.has(),
and/or RelationshipProperty.Comparator.contains()
operators of the underlying proxied attributes.
has(criterion=None, **kwargs)¶has() method of AssociationProxyInstanceProduce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
local_attr¶local_attr attribute of AssociationProxyInstanceThe ‘local’ class attribute referenced by this
AssociationProxyInstance.
remote_attr¶remote_attr attribute of AssociationProxyInstanceThe ‘remote’ class attribute referenced by this
AssociationProxyInstance.
scalar¶scalar attribute of AssociationProxyInstanceReturn True if this AssociationProxyInstance
proxies a scalar relationship on the local side.
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance(parent, owning_class, target_class, value_attr)¶Bases: sqlalchemy.sql.operators.ColumnOperators, sqlalchemy.ext.associationproxy.AssociationProxyInstance
an AssociationProxyInstance that has a database column as a
target.
__le__(other)¶__le__() method of ColumnOperatorsImplement the <= operator.
In a column context, produces the clause a <= b.
__lt__(other)¶__lt__() method of ColumnOperatorsImplement the < operator.
In a column context, produces the clause a < b.
__ne__(other)¶__ne__() method of ColumnOperatorsImplement the != operator.
In a column context, produces the clause a != b.
If the target is None, produces a IS NOT NULL.
all_()¶all_() method of ColumnOperatorsProduce a all_() clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against 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_()New in version 1.1.
any(criterion=None, **kwargs)¶any() method of AssociationProxyInstanceProduce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
any_()¶any_() method of ColumnOperatorsProduce a any_() clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against 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_()New in version 1.1.
asc()¶asc() method of ColumnOperatorsProduce a asc() clause against the
parent object.
attr¶attr attribute of AssociationProxyInstanceReturn a tuple of (local_attr, remote_attr).
This attribute is convenient when specifying a join
using Query.join() across two relationships:
sess.query(Parent).join(*Parent.proxied.attr)between(cleft, cright, symmetric=False)¶between() method of ColumnOperatorsProduce a between() clause against
the parent object, given the lower and upper range.
bool_op(opstring, precedence=0)¶Return a custom boolean operator.
This method is shorthand for calling
Operators.op() and passing the
Operators.op.is_comparison
flag with True.
New in version 1.2.0b3.
See also
collate(collation)¶collate() method of ColumnOperatorsProduce a collate() clause against
the parent object, given the collation string.
See also
concat(other)¶concat() method of ColumnOperatorsImplement the ‘concat’ operator.
In a column context, produces the clause a || b,
or uses the concat() operator on MySQL.
contains(other, **kwargs)¶contains() method of ColumnOperatorsImplement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle of a string value:
column LIKE '%' || <other> || '%'E.g.:
stmt = select([sometable]).\
where(sometable.c.column.contains("foobar"))Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.contains.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.contains.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
| Parameters: |
|
|---|
desc()¶desc() method of ColumnOperatorsProduce a desc() clause against the
parent object.
distinct()¶distinct() method of ColumnOperatorsProduce a distinct() clause against the
parent object.
endswith(other, **kwargs)¶endswith() method of ColumnOperatorsImplement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end of a string value:
column LIKE '%' || <other>E.g.:
stmt = select([sometable]).\
where(sometable.c.column.endswith("foobar"))Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.endswith.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.endswith.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
| Parameters: |
|
|---|
has(criterion=None, **kwargs)¶has() method of AssociationProxyInstanceProduce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product
using the RelationshipProperty.Comparator.any()
and/or RelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
ilike(other, escape=None)¶ilike() method of ColumnOperatorsImplement 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 otherE.g.:
stmt = select([sometable]).\
where(sometable.c.column.ilike("%foobar%"))| Parameters: |
|---|
See also
in_(other)¶in_() method of ColumnOperatorsImplement the in operator.
In a column context, produces the clause column IN <other>.
The given parameter other may be:
A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)A list of tuples may be provided if the comparison is against a
tuple_() containing multiple expressions:
from sqlalchemy import tuple_
stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))An empty list, e.g.:
stmt.where(column.in_([]))In this calling form, the expression renders a “false” expression, e.g.:
WHERE 1 != 1This “false” expression has historically had different behaviors
in older SQLAlchemy versions, see
create_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 it
includes the bindparam.expanding flag:
stmt.where(column.in_(bindparam('value', expanding=True)))In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})The database would be passed a bound parameter for each value:
WHERE COL IN (?, ?, ?)New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)New in version 1.3: “expanding” bound parameters now support empty lists
a select() construct, which is usually a correlated
scalar select:
stmt.where(
column.in_(
select([othertable.c.y]).
where(table.c.x == othertable.c.x)
)
)In this calling form, ColumnOperators.in_() renders as given:
WHERE COL IN (SELECT othertable.y
FROM othertable WHERE othertable.x = table.x)| Parameters: | other¶ – a list of literals, a select() construct,
or a bindparam() construct that includes the
bindparam.expanding flag set to True. |
|---|
is_(other)¶is_() method of ColumnOperatorsImplement the IS operator.
Normally, IS is generated automatically when comparing to a
value of None, which resolves to NULL. However, explicit
usage of IS may be desirable if comparing to boolean values
on certain platforms.
See also
is_distinct_from(other)¶is_distinct_from() method of ColumnOperatorsImplement 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)¶isnot() method of ColumnOperatorsImplement the IS NOT operator.
Normally, IS NOT is generated automatically when comparing to a
value of None, which resolves to NULL. However, explicit
usage of IS NOT may be desirable if comparing to boolean values
on certain platforms.
See also
isnot_distinct_from(other)¶isnot_distinct_from() method of ColumnOperatorsImplement 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)¶like() method of ColumnOperatorsImplement the like operator.
In a column context, produces the expression:
a LIKE otherE.g.:
stmt = select([sometable]).\
where(sometable.c.column.like("%foobar%"))| Parameters: |
|---|
See also
local_attr¶local_attr attribute of AssociationProxyInstanceThe ‘local’ class attribute referenced by this
AssociationProxyInstance.
match(other, **kwargs)¶match() method of ColumnOperatorsImplements a database-specific ‘match’ operator.
match() attempts to resolve to
a MATCH-like function or operator provided by the backend.
Examples include:
x @@ to_tsquery(y)MATCH (x) AGAINST (y IN BOOLEAN MODE)CONTAINS(x, y)notilike(other, escape=None)¶notilike() method of ColumnOperatorsimplement the NOT ILIKE operator.
This is equivalent to using negation with
ColumnOperators.ilike(), i.e. ~x.ilike(y).
See also
notin_(other)¶notin_() method of ColumnOperatorsimplement the NOT IN operator.
This is equivalent to using negation with
ColumnOperators.in_(), i.e. ~x.in_(y).
In the case that other is an empty sequence, the compiler
produces an “empty not in” expression. This defaults to the
expression “1 = 1” to produce true in all cases. The
create_engine.empty_in_strategy may be used to
alter this behavior.
Changed in version 1.2: The ColumnOperators.in_() and
ColumnOperators.notin_() operators
now produce a “static” expression for an empty IN sequence
by default.
See also
notlike(other, escape=None)¶notlike() method of ColumnOperatorsimplement the NOT LIKE operator.
This is equivalent to using negation with
ColumnOperators.like(), i.e. ~x.like(y).
See also
nullsfirst()¶nullsfirst() method of ColumnOperatorsProduce a nullsfirst() clause against the
parent object.
nullslast()¶nullslast() method of ColumnOperatorsProduce a nullslast() clause against the
parent object.
op(opstring, precedence=0, is_comparison=False, return_type=None)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)produces:
somecolumn * 5This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)is a bitwise AND of the value in somecolumn.
| Parameters: |
|
|---|
operate(op, *other, **kwargs)¶Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower() to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))| Parameters: |
|---|
remote_attr¶remote_attr attribute of AssociationProxyInstanceThe ‘remote’ class attribute referenced by this
AssociationProxyInstance.
reverse_operate(op, other, **kwargs)¶reverse_operate() method of OperatorsReverse operate on an argument.
Usage is the same as operate().
scalar¶scalar attribute of AssociationProxyInstanceReturn True if this AssociationProxyInstance
proxies a scalar relationship on the local side.
startswith(other, **kwargs)¶startswith() method of ColumnOperatorsImplement the startswith operator.
Produces a LIKE expression that tests against a match for the start of a string value:
column LIKE <other> || '%'E.g.:
stmt = select([sometable]).\
where(sometable.c.column.startswith("foobar"))Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.startswith.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.startswith.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
| Parameters: |
|
|---|
sqlalchemy.ext.associationproxy.ASSOCIATION_PROXY = symbol('ASSOCIATION_PROXY')¶InspectionAttr that’sAssociationProxy.Is assigned to the InspectionAttr.extension_type
attribute.