Association Proxy

associationproxy is used to create a read/write view of atarget attribute across a relationship. It essentially concealsthe usage of a “middle” attribute between two endpoints, andcan be used to cherry-pick fields from a collection ofrelated objects or to reduce the verbosity of using the associationobject pattern. Applied creatively, the association proxy allowsthe construction of sophisticated collections and dictionaryviews of virtually any geometry, persisted to the database usingstandard, transparently configured relational patterns.

Simplifying Scalar Collections

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):

  1. from sqlalchemy import Column, Integer, String, ForeignKey, Table
  2. from sqlalchemy.orm import relationship
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base()
  6.  
  7. class User(Base):
  8.     __tablename__ = 'user'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String(64))
  11.     kw = relationship("Keyword", secondary=lambda: userkeywords_table)
  12.  
  13.     def __init__(self, name):
  14.         self.name = name
  15.  
  16. class Keyword(Base):
  17.     __tablename__ = 'keyword'
  18.     id = Column(Integer, primary_key=True)
  19.     keyword = Column('keyword', String(64))
  20.  
  21.     def __init__(self, keyword):
  22.         self.keyword = keyword
  23.  
  24. userkeywords_table = Table('userkeywords', Base.metadata,
  25.     Column('user_id', Integer, ForeignKey("user.id"),
  26.            primary_key=True),
  27.     Column('keyword_id', Integer, ForeignKey("keyword.id"),
  28.            primary_key=True)
  29. )

Reading and manipulating the collection of “keyword” strings associatedwith User requires traversal from each collection element to the .keywordattribute, which can be awkward:

  1. >>> user = User('jek')
  2. >>> user.kw.append(Keyword('cheese inspector'))
  3. >>> print(user.kw)
  4. [<__main__.Keyword object at 0x12bf830>]
  5. >>> print(user.kw[0].keyword)
  6. cheese inspector
  7. >>> print([keyword.keyword for keyword in user.kw])
  8. ['cheese inspector']

The association_proxy is applied to the User class to producea “view” of the kw relationship, which only exposes the stringvalue of .keyword associated with each Keyword object:

  1. from sqlalchemy.ext.associationproxy import association_proxy
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.     id = Column(Integer, primary_key=True)
  6.     name = Column(String(64))
  7.     kw = relationship("Keyword", secondary=lambda: userkeywords_table)
  8.  
  9.     def __init__(self, name):
  10.         self.name = name
  11.  
  12.     # proxy the 'keyword' attribute from the 'kw' relationship
  13.     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 createdfor us transparently:

  1. >>> user = User('jek')
  2. >>> user.keywords.append('cheese inspector')
  3. >>> user.keywords
  4. ['cheese inspector']
  5. >>> user.keywords.append('snack ninja')
  6. >>> user.kw
  7. [<__main__.Keyword object at 0x12cdd30>, <__main__.Keyword object at 0x12cde30>]

The AssociationProxy object produced by the association_proxy() functionis an instance of a Python descriptor.It is always declared with the user-defined class being mapped, regardless ofwhether Declarative or classical mappings via the mapper() function are used.

The proxy functions by operating upon the underlying mapped attributeor collection in response to operations, and changes made via the proxy are immediatelyapparent in the mapped attribute, as well as vice versa. The underlyingattribute remains fully accessible.

When first accessed, the association proxy performs introspectionoperations 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 likethe underlying collection or attribute does.

Creation of New Values

When a list append() event (or set add(), dictionary setitem(), or scalarassignment event) is intercepted by the association proxy, it instantiates anew instance of the “intermediary” object using its constructor, passing as asingle argument the given value. In our example above, an operation like:

  1. user.keywords.append('cheese inspector')

Is translated by the association proxy into the operation:

  1. user.kw.append(Keyword('cheese inspector'))

The example works here because we have designed the constructor for Keywordto accept a single positional argument, keyword. For those cases where asingle-argument constructor isn’t feasible, the association proxy’s creationalbehavior can be customized using the creator argument, which references acallable (i.e. Python function) that will produce a new object instance given thesingular argument. Below we illustrate this using a lambda as is typical:

  1. class User(Base):
  2.     # ...
  3.  
  4.     # use Keyword(keyword=kw) on append() events
  5.     keywords = association_proxy('kw', 'keyword',
  6.                     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-basedcollection, it accepts two arguments, “key” and “value”. An exampleof this is below in Proxying to Dictionary Based Collections.

Simplifying Association Objects

The “association object” pattern is an extended form of a many-to-manyrelationship, and is described at Association Object. Associationproxies are useful for keeping “association objects” out of the way duringregular use.

Suppose our userkeywords table above had additional columnswhich we’d like to map explicitly, but in most cases we don’trequire direct access to these attributes. Below, we illustratea new mapping which introduces the UserKeyword class, whichis mapped to the userkeywords table illustrated earlier.This class adds an additional column special_key, a value whichwe occasionally want to access, but not in the usual case. Wecreate an association proxy on the User class calledkeywords, which will bridge the gap from the user_keywordscollection of User to the .keyword attribute present on eachUserKeyword:

  1. from sqlalchemy import Column, Integer, String, ForeignKey
  2. from sqlalchemy.orm import relationship, backref
  3.  
  4. from sqlalchemy.ext.associationproxy import association_proxy
  5. from sqlalchemy.ext.declarative import declarative_base
  6.  
  7. Base = declarative_base()
  8.  
  9. class User(Base):
  10.     __tablename__ = 'user'
  11.     id = Column(Integer, primary_key=True)
  12.     name = Column(String(64))
  13.  
  14.     # association proxy of "user_keywords" collection
  15.     # to "keyword" attribute
  16.     keywords = association_proxy('user_keywords', 'keyword')
  17.  
  18.     def __init__(self, name):
  19.         self.name = name
  20.  
  21. class UserKeyword(Base):
  22.     __tablename__ = 'user_keyword'
  23.     user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
  24.     keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
  25.     special_key = Column(String(50))
  26.  
  27.     # bidirectional attribute/collection of "user"/"user_keywords"
  28.     user = relationship(User,
  29.                 backref=backref("user_keywords",
  30.                                 cascade="all, delete-orphan")
  31.             )
  32.  
  33.     # reference to the "Keyword" object
  34.     keyword = relationship("Keyword")
  35.  
  36.     def __init__(self, keyword=None, user=None, special_key=None):
  37.         self.user = user
  38.         self.keyword = keyword
  39.         self.special_key = special_key
  40.  
  41. class Keyword(Base):
  42.     __tablename__ = 'keyword'
  43.     id = Column(Integer, primary_key=True)
  44.     keyword = Column('keyword', String(64))
  45.  
  46.     def __init__(self, keyword):
  47.         self.keyword = keyword
  48.  
  49.     def __repr__(self):
  50.         return 'Keyword(%s)' % repr(self.keyword)

With the above configuration, we can operate upon the .keywordscollection of each User object, and the usage of UserKeywordis concealed:

  1. >>> user = User('log')
  2. >>> for kw in (Keyword('new_from_blammo'), Keyword('its_big')):
  3. ...     user.keywords.append(kw)
  4. ...
  5. >>> print(user.keywords)
  6. [Keyword('new_from_blammo'), Keyword('its_big')]

Where above, each .keywords.append() operation is equivalent to:

  1. >>> 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 passingthe Keyword object as the first argument. The .user argument is thenassigned as the UserKeyword object is appended to the User.user_keywordscollection, where the bidirectional relationship configured between User.user_keywordsand 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, wecreate the UserKeyword object explicitly. Below we assign all threeattributes, where the assignment of .user has the effect of the UserKeywordbeing appended to the User.user_keywords collection:

  1. >>> UserKeyword(Keyword('its_wood'), user, special_key='my special key')

The association proxy returns to us a collection of Keyword objects representedby all these operations:

  1. >>> user.keywords
  2. [Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]

Proxying to Dictionary Based Collections

The association proxy can proxy to dictionary based collections as well. SQLAlchemymappings usually use the attribute_mapped_collection() collection type tocreate dictionary collections, as well as the extended techniques described inCustom Dictionary-Based Collections.

The association proxy adjusts its behavior when it detects the usage of adictionary-based collection. When new values are added to the dictionary, theassociation proxy instantiates the intermediary object by passing twoarguments to the creation function instead of one, the key and the value. Asalways, this creation function defaults to the constructor of the intermediaryclass, and can be customized using the creator argument.

Below, we modify our UserKeyword example such that the User.user_keywordscollection will now be mapped using a dictionary, where the UserKeyword.special_keyargument will be used as the key for the dictionary. We then apply a creatorargument to the User.keywords proxy so that these values are assigned appropriatelywhen new elements are added to the dictionary:

  1. from sqlalchemy import Column, Integer, String, ForeignKey
  2. from sqlalchemy.orm import relationship, backref
  3. from sqlalchemy.ext.associationproxy import association_proxy
  4. from sqlalchemy.ext.declarative import declarative_base
  5. from sqlalchemy.orm.collections import attribute_mapped_collection
  6.  
  7. Base = declarative_base()
  8.  
  9. class User(Base):
  10.     __tablename__ = 'user'
  11.     id = Column(Integer, primary_key=True)
  12.     name = Column(String(64))
  13.  
  14.     # proxy to 'user_keywords', instantiating UserKeyword
  15.     # assigning the new key to 'special_key', values to
  16.     # 'keyword'.
  17.     keywords = association_proxy('user_keywords', 'keyword',
  18.                     creator=lambda k, v:
  19.                                 UserKeyword(special_key=k, keyword=v)
  20.                 )
  21.  
  22.     def __init__(self, name):
  23.         self.name = name
  24.  
  25. class UserKeyword(Base):
  26.     __tablename__ = 'user_keyword'
  27.     user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
  28.     keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
  29.     special_key = Column(String)
  30.  
  31.     # bidirectional user/user_keywords relationships, mapping
  32.     # user_keywords with a dictionary against "special_key" as key.
  33.     user = relationship(User, backref=backref(
  34.                     "user_keywords",
  35.                     collection_class=attribute_mapped_collection("special_key"),
  36.                     cascade="all, delete-orphan"
  37.                     )
  38.                 )
  39.     keyword = relationship("Keyword")
  40.  
  41. class Keyword(Base):
  42.     __tablename__ = 'keyword'
  43.     id = Column(Integer, primary_key=True)
  44.     keyword = Column('keyword', String(64))
  45.  
  46.     def __init__(self, keyword):
  47.         self.keyword = keyword
  48.  
  49.     def __repr__(self):
  50.         return 'Keyword(%s)' % repr(self.keyword)

We illustrate the .keywords collection as a dictionary, mapping theUserKeyword.string_key value to Keyword objects:

  1. >>> user = User('log')
  2.  
  3. >>> user.keywords['sk1'] = Keyword('kw1')
  4. >>> user.keywords['sk2'] = Keyword('kw2')
  5.  
  6. >>> print(user.keywords)
  7. {'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}

Composite Association Proxies

Given our previous examples of proxying from relationship to scalarattribute, proxying across an association object, and proxying dictionaries,we can combine all three techniques together to give Usera keywords dictionary that deals strictly with the string valueof special_key mapped to the string keyword. Both the UserKeywordand Keyword classes are entirely concealed. This is achieved by buildingan association proxy on User that refers to an association proxypresent on UserKeyword:

  1. from sqlalchemy import Column, Integer, String, ForeignKey
  2. from sqlalchemy.orm import relationship, backref
  3.  
  4. from sqlalchemy.ext.associationproxy import association_proxy
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.orm.collections import attribute_mapped_collection
  7.  
  8. Base = declarative_base()
  9.  
  10. class User(Base):
  11.     __tablename__ = 'user'
  12.     id = Column(Integer, primary_key=True)
  13.     name = Column(String(64))
  14.  
  15.     # the same 'user_keywords'->'keyword' proxy as in
  16.     # the basic dictionary example
  17.     keywords = association_proxy(
  18.                 'user_keywords',
  19.                 'keyword',
  20.                 creator=lambda k, v:
  21.                             UserKeyword(special_key=k, keyword=v)
  22.                 )
  23.  
  24.     def __init__(self, name):
  25.         self.name = name
  26.  
  27. class UserKeyword(Base):
  28.     __tablename__ = 'user_keyword'
  29.     user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
  30.     keyword_id = Column(Integer, ForeignKey('keyword.id'),
  31.                                                     primary_key=True)
  32.     special_key = Column(String)
  33.     user = relationship(User, backref=backref(
  34.             "user_keywords",
  35.             collection_class=attribute_mapped_collection("special_key"),
  36.             cascade="all, delete-orphan"
  37.             )
  38.         )
  39.  
  40.     # the relationship to Keyword is now called
  41.     # 'kw'
  42.     kw = relationship("Keyword")
  43.  
  44.     # 'keyword' is changed to be a proxy to the
  45.     # 'keyword' attribute of 'Keyword'
  46.     keyword = association_proxy('kw', 'keyword')
  47.  
  48. class Keyword(Base):
  49.     __tablename__ = 'keyword'
  50.     id = Column(Integer, primary_key=True)
  51.     keyword = Column('keyword', String(64))
  52.  
  53.     def __init__(self, keyword):
  54.         self.keyword = keyword

User.keywords is now a dictionary of string to string, whereUserKeyword and Keyword objects are created and removed for ustransparently using the association proxy. In the example below, we illustrateusage of the assignment operator, also appropriately handled by theassociation proxy, to apply a dictionary value to the collection at once:

  1. >>> user = User('log')
  2. >>> user.keywords = {
  3. ...     'sk1':'kw1',
  4. ...     'sk2':'kw2'
  5. ... }
  6. >>> print(user.keywords)
  7. {'sk1': 'kw1', 'sk2': 'kw2'}
  8.  
  9. >>> user.keywords['sk3'] = 'kw3'
  10. >>> del user.keywords['sk2']
  11. >>> print(user.keywords)
  12. {'sk1': 'kw1', 'sk3': 'kw3'}
  13.  
  14. >>> # illustrate un-proxied usage
  15. ... print(user.user_keywords['sk3'].kw)
  16. <__main__.Keyword object at 0x12ceb90>

One caveat with our example above is that because Keyword objects are createdfor each dictionary set operation, the example fails to maintain uniqueness forthe Keyword objects on their string name, which is a typical requirement fora tagging scenario such as this one. For this use case the recipeUniqueObject, ora comparable creational strategy, isrecommended, which will apply a “lookup first, then create” strategy to the constructorof the Keyword class, so that an already existing Keyword is returned if thegiven name is already present.

Querying with Association Proxies

The AssociationProxy features simple SQL construction capabilitieswhich relate down to the underlying relationship() in use as wellas the target attribute. For example, the RelationshipProperty.Comparator.any()and RelationshipProperty.Comparator.has() operations are available, and will producea “nested” EXISTS clause, such as in our basic association object example:

  1. >>> print(session.query(User).filter(User.keywords.any(keyword='jek')))
  2. SELECT user.id AS user_id, user.name AS user_name
  3. FROM user
  4. WHERE EXISTS (SELECT 1
  5. FROM user_keyword
  6. WHERE user.id = user_keyword.user_id AND (EXISTS (SELECT 1
  7. FROM keyword
  8. WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)))

For a proxy to a scalar attribute, eq() is supported:

  1. >>> print(session.query(UserKeyword).filter(UserKeyword.keyword == 'jek'))
  2. SELECT user_keyword.*
  3. FROM user_keyword
  4. WHERE EXISTS (SELECT 1
  5.     FROM keyword
  6.     WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)

and .contains() is available for a proxy to a scalar collection:

  1. >>> print(session.query(User).filter(User.keywords.contains('jek')))
  2. SELECT user.*
  3. FROM user
  4. WHERE EXISTS (SELECT 1
  5. FROM userkeywords, keyword
  6. WHERE user.id = userkeywords.user_id
  7.     AND keyword.id = userkeywords.keyword_id
  8.     AND keyword.keyword = :keyword_1)

AssociationProxy can be used with Query.join() somewhat manuallyusing the attr attribute in a star-args context:

  1. 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 alsobe used for querying:

  1. uka = aliased(UserKeyword)
  2. ka = aliased(Keyword)
  3. q = session.query(User).\
  4.         join(uka, User.keywords.local_attr).\
  5.         join(ka, User.keywords.remote_attr)

Cascading Scalar Deletes

New in version 1.3.

Given a mapping as:

  1. class A(Base):
  2.     __tablename__ = 'test_a'
  3.     id = Column(Integer, primary_key=True)
  4.     ab = relationship(
  5.         'AB', backref='a', uselist=False)
  6.     b = association_proxy(
  7.         'ab', 'b', creator=lambda b: AB(b=b),
  8.         cascade_scalar_deletes=True)
  9.  
  10.  
  11. class B(Base):
  12.     __tablename__ = 'test_b'
  13.     id = Column(Integer, primary_key=True)
  14.     ab = relationship('AB', backref='b', cascade='all, delete-orphan')
  15.  
  16.  
  17. class AB(Base):
  18.     __tablename__ = 'test_ab'
  19.     a_id = Column(Integer, ForeignKey(A.id), primary_key=True)
  20.     b_id = Column(Integer, ForeignKey(B.id), primary_key=True)

An assignment to A.b will generate an AB object:

  1. a.b = B()

The A.b association is scalar, and includes use of the flagAssociationProxy.cascade_scalar_deletes. When set, setting A.bto None will remove A.ab as well:

  1. a.b = None
  2. assert a.ab is None

When 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 whenmembers of the proxied collection are removed. Whether or not the row isdeleted depends on the relationship cascade setting.

See also

Cascades

API Documentation

  • sqlalchemy.ext.associationproxy.associationproxy(_target_collection, attr, **kw)
  • Return a Python property implementing a view of a targetattribute which references an attribute on members of thetarget.

The returned value is an instance of AssociationProxy.

Implements a Python property representing a relationship as a collectionof simpler values, or a scalar value. The proxied property will mimicthe collection type of the target (list, dict or set), or, in the case ofa one to one relationship, a simple scalar value.

  • Parameters

    • target_collection – Name of the attribute we’ll proxy to.This attribute is typically mapped byrelationship() to link to a target collection, butcan also be a many-to-one or non-scalar relationship.

    • attr –

Attribute on the associated instance or instances we’llproxy for.

For example, given a target collection of [obj1, obj2], a list createdby this proxy property would look like [getattr(obj1, attr),getattr(obj2, attr)]

If the relationship is one-to-one or otherwise uselist=False, thensimply: getattr(obj, attr)

  1. -

creator –

optional.

When new items are added to this proxied collection, new instances ofthe class collected by the target collection will be created. For listand set collections, the target class constructor will be called withthe ‘value’ for the new instance. For dict types, two arguments arepassed: key and value.

If you want to construct instances differently, supply a _creator_function that takes arguments as above and returns instances.

For scalar relationships, creator() will be called if the target is None.If the target is present, set operations are proxied to setattr() on theassociated object.

If you have an associated object with multiple attributes, you may setup multiple association proxies mapping to different attributes. Seethe unit tests for examples, and for examples of how creator() functionscan be used to construct the scalar relationship on-demand in thissituation.

  1. -

**kw – Passes along any other keyword arguments toAssociationProxy.

  • class 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()

inherited from the eq() method of object

Return 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 usualentrypoint here, though AssociationProxy can be instantiatedand/or subclassed directly.

  1. - Parameters
  2. - 
  3.   -

target_collection – Name of the collection we’ll proxy to,usually created with relationship().

  1.   -

attr – Attribute on the collected instances we’ll proxyfor. For example, given a target collection of [obj1, obj2], alist created by this proxy property would look like[getattr(obj1, attr), getattr(obj2, attr)]

  1.   -

creator –

Optional. When new items are added to this proxiedcollection, new instances of the class collected by the targetcollection will be created. For list and set collections, thetarget class constructor will be called with the ‘value’ for thenew instance. For dict types, two arguments are passed:key and value.

If you want to construct instances differently, supply a ‘creator’function that takes arguments as above and returns instances.

  1.   -

cascade_scalar_deletes –

when True, indicates that settingthe proxied value to None, or deleting it via del, shouldalso remove the source object. Only applies to scalar attributes.Normally, removing the proxied target will not remove the proxysource, as this object may have other state that is still to bekept.

New in version 1.3.

See also

Cascading Scalar Deletes - complete usage example

  1.   -

getset_factory –

Optional. Proxied attribute access isautomatically handled by routines that get and set values based onthe attr argument for this proxy.

If you would like to customize this behavior, you may supply agetset_factory callable that produces a tuple of getter andsetter functions. The factory is called with two arguments, theabstract type of the underlying collection and this proxy instance.

  1.   -

proxy_factory – Optional. The type of collection to emulate isdetermined by sniffing the target collection. If your collectiontype can’t be determined by duck typing or you’d like to use adifferent collection implementation, you may supply a factoryfunction to produce those collections. Only applicable tonon-scalar relationships.

  1.   -

proxy_bulk_set – Optional, use with proxy_factory. Seethe _set() method for details.

  1.   -

info –

optional, will be assigned toAssociationProxy.info if present.

New in version 1.0.9.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class 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 invokedin terms of a specific class or instance of a class, i.e. when it isused as a regular Python descriptor.

When referring to the AssociationProxy as a normal Pythondescriptor, the AssociationProxyInstance is the object thatactually serves the information. Under normal circumstances, its presenceis transparent:

  1. >>> User.keywords.scalar
  2. False

In the special case that the AssociationProxy object is beingaccessed directly, in order to get an explicit handle to theAssociationProxyInstance, use theAssociationProxy.for_class() method:

  1. proxy_state = inspect(User).all_orm_descriptors["keywords"].for_class(User)
  2.  
  3. # view if proxy object is scalar or not
  4. >>> proxy_state.scalar
  5. False

New in version 1.3.

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

an AssociationProxyInstance that has an object as a target.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

an AssociationProxyInstance that has a database column as atarget.

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

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

  1. # postgresql '5 = ALL (somearray)'
  2. expr = 5 == mytable.c.somearray.all_()
  3.  
  4. # mysql '5 = ALL (SELECT value FROM table)'
  5. 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 ofAssociationProxyInstance

Produce a proxied ‘any’ expression using EXISTS.

This expression will be a composed productusing the RelationshipProperty.Comparator.any()and/or RelationshipProperty.Comparator.has()operators of the underlying proxied attributes.

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

  1. # postgresql '5 = ANY (somearray)'
  2. expr = 5 == mytable.c.somearray.any_()
  3.  
  4. # mysql '5 = ANY (SELECT value FROM table)'
  5. 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.

  • property attr
  • Return a tuple of (local_attr, remote_attr).

This attribute is convenient when specifying a joinusing Query.join() across two relationships:

  1. sess.query(Parent).join(*Parent.proxied.attr)

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

  • 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

Operators.op()

  • collate(collation)

inherited from thecollate()method ofColumnOperators

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

See also

collate()

  • 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:

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

E.g.:

  1. stmt = select([sometable]).\
  2.     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.

  1. - Parameters
  2. - 
  3.   -

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.

  1.   -

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:

  1. somecolumn.contains("foo%bar", autoescape=True)

Will render as:

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

  1.   -

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:

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

Will render as:

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

The parameter may also be combined withColumnOperators.contains.autoescape:

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

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

  • 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:

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

E.g.:

  1. stmt = select([sometable]).\
  2.     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.

  1. - Parameters
  2. - 
  3.   -

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.endswith.autoescape flag isset to True.

  1.   -

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:

  1. somecolumn.endswith("foo%bar", autoescape=True)

Will render as:

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

  1.   -

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:

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

Will render as:

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

The parameter may also be combined withColumnOperators.endswith.autoescape:

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

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

  • has(criterion=None, **kwargs)

inherited from thehas()method ofAssociationProxyInstance

Produce a proxied ‘has’ expression using EXISTS.

This expression will be a composed productusing the RelationshipProperty.Comparator.any()and/or RelationshipProperty.Comparator.has()operators of the underlying proxied attributes.

  • 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:

  1. lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

  1. a ILIKE other

E.g.:

  1. stmt = select([sometable]).\
  2.     where(sometable.c.column.ilike("%foobar%"))
  1. - Parameters
  2. - 
  3.   -

other – expression to be compared

  1.   -

escape –

optional escape character, renders the ESCAPEkeyword, e.g.:

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

See also

ColumnOperators.like()

  • 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:

  1. -

A list of literal values, e.g.:

  1. 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:

  1. WHERE COL IN (?, ?, ?)
  1. -

A list of tuples may be provided if the comparison is against atuple_() containing multiple expressions:

  1. from sqlalchemy import tuple_
  2. stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
  1. -

An empty list, e.g.:

  1. stmt.where(column.in_([]))

In this calling form, the expression renders a “false” expression,e.g.:

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

  1. -

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

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

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

  1. 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:

  1. connection.execute(stmt, {"value": [1, 2, 3]})

The database would be passed a bound parameter for each value:

  1. 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:

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

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

  1. -

a select() construct, which is usually a correlatedscalar select:

  1. stmt.where(
  2.     column.in_(
  3.         select([othertable.c.y]).
  4.         where(table.c.x == othertable.c.x)
  5.     )
  6. )

In this calling form, ColumnOperators.in_() renders as given:

  1. WHERE COL IN (SELECT othertable.y
  2. FROM othertable WHERE othertable.x = table.x)
  1. - Parameters
  2. -

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.

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

ColumnOperators.isnot()

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

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

ColumnOperators.is_()

  • 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:

  1. a LIKE other

E.g.:

  1. stmt = select([sometable]).\
  2.     where(sometable.c.column.like("%foobar%"))
  1. - Parameters
  2. - 
  3.   -

other – expression to be compared

  1.   -

escape –

optional escape character, renders the ESCAPEkeyword, e.g.:

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

See also

ColumnOperators.ilike()

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

  • 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:

  1. -

PostgreSQL - renders x @@ to_tsquery(y)

  1. -

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

  1. -

Oracle - renders CONTAINS(x, y)

  1. -

other backends may provide special implementations.

  1. -

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

ColumnOperators.ilike()

  • 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).

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

ColumnOperators.in_()

  • 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

ColumnOperators.like()

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

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

inherited from theop()method ofOperators

produce a generic operator function.

e.g.:

  1. somecolumn.op("*")(5)

produces:

  1. somecolumn * 5

This function can also be used to make bitwise operators explicit. Forexample:

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

is a bitwise AND of the value in somecolumn.

  1. - Parameters
  2. - 
  3.   -

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

  1.   -

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.

  1.   -

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.

  1.   -

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

Redefining and Creating New Operators

Using custom operators in join conditions

  • 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:

  1. class MyComparator(ColumnOperators):
  2.     def operate(self, op, other):
  3.         return op(func.lower(self), func.lower(other))
  1. - Parameters
  2. - 
  3.   -

op – Operator callable.

  1.   -

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

  1.   -

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

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

  • reverseoperate(_op, other, **kwargs)

inherited from thereverse_operate()method ofOperators

Reverse operate on an argument.

Usage is the same as operate().

  • scalar

inherited from thescalarattribute ofAssociationProxyInstance

Return True if this AssociationProxyInstanceproxies a scalar relationship on the local side.

  • 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:

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

E.g.:

  1. stmt = select([sometable]).\
  2.     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.

  1. - Parameters
  2. - 
  3.   -

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.startswith.autoescape flag isset to True.

  1.   -

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:

  1. somecolumn.startswith("foo%bar", autoescape=True)

Will render as:

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

  1.   -

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:

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

Will render as:

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

The parameter may also be combined withColumnOperators.startswith.autoescape:

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

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

  • sqlalchemy.ext.associationproxy.ASSOCIATIONPROXY = symbol('ASSOCIATIONPROXY')

Is assigned to the InspectionAttr.extension_typeattribute.