Association Proxy¶

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.

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

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.

Creation of New Values¶

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.

Simplifying Association Objects¶

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')]

Proxying to Dictionary Based Collections¶

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.special_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')}

Composite Association Proxies¶

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

    # another proxy that is directly column-targeted
    special_keys = association_proxy("user_keywords", "special_key")

    def __init__(self, name):
        self.name = name

class UserKeyword(Base):
    __tablename__ = 'user_keyword'
    user_id = Column(ForeignKey('user.id'), primary_key=True)
    keyword_id = Column(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 = keyword

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

Querying with Association Proxies¶

The AssociationProxy features simple SQL construction capabilities which work at the class level in a similar way as other ORM-mapped attributes. Class-bound attributes such as User.keywords and User.special_keys in the preceding example will provide for a SQL generating construct when accessed at the class level.

Note

The primary purpose of the association proxy extension is to allow for improved persistence and object-access patterns with mapped object instances that are already loaded. The class-bound querying feature is of limited use and will not replace the need to refer to the underlying attributes when constructing SQL queries with JOINs, eager loading options, etc.

The SQL generated takes the form of a correlated subquery against the EXISTS SQL operator so that it can be used in a WHERE clause without the need for additional modifications to the enclosing query. If the immediate target of an association proxy is a mapped column expression, standard column operators can be used which will be embedded in the subquery. For example a straight equality operator:

>>> print(session.query(User).filter(User.special_keys == "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 user_keyword.special_key = :special_key_1)

a LIKE operator:

>>> print(session.query(User).filter(User.special_keys.like("%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 user_keyword.special_key LIKE :special_key_1)

For association proxies where the immediate target is a related object or collection, or another association proxy or attribute on the related object, relationship-oriented operators can be used instead, such as PropComparator.has() and PropComparator.any(). The User.keywords attribute is in fact two association proxies linked together, so when using this proxy for generating SQL phrases, we get two levels of EXISTS subqueries:

>>> print(session.query(User).filter(User.keywords.any(Keyword.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)))

This is not the most efficient form of SQL, so while association proxies can be convenient for generating WHERE criteria quickly, SQL results should be inspected and “unrolled” into explicit JOIN criteria for best use, especially when chaining association proxies together.

Changed in version 1.3: Association proxy features distinct querying modes based on the type of target. See AssociationProxy now provides standard column operators for a column-oriented target.

Cascading Scalar Deletes¶

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 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 when members of the proxied collection are removed. Whether or not the row is deleted depends on the relationship cascade setting.

See also

Cascades

API Documentation¶

Object Name	Description
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.
ASSOCIATION_PROXY
AssociationProxy	A descriptor that presents a read/write view of an object attribute.
AssociationProxyInstance	A per-class object that serves class- and object-specific results.
ColumnAssociationProxyInstance	an `AssociationProxyInstance` that has a database column as a target.
ObjectAssociationProxyInstance	an `AssociationProxyInstance` that has an object as a target.

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

target_collection¶ – Name of the attribute we’ll proxy to. This attribute is typically mapped by relationship() to link to a target collection, but can also be a many-to-one or non-scalar relationship.
attr¶ –
Attribute on the associated instance or instances we’ll proxy for.

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

If the relationship is one-to-one or otherwise uselist=False, then simply: getattr(obj, attr)
creator¶ –
optional.

When new items are added to this proxied collection, new instances of the class collected by the target collection will be created. For list and set collections, the target class constructor will be called with the ‘value’ for the new 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.

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

If you have an associated object with multiple attributes, you may set up multiple association proxies mapping to different attributes. See the unit tests for examples, and for examples of how creator() functions can be used to construct the scalar relationship on-demand in this situation.
**kw¶ – Passes along any other keyword arguments to AssociationProxy.

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

A descriptor that presents a read/write view of an object attribute.

Members

__init__(), extension_type, for_class(), info, is_aliased_class, is_attribute, is_clause_element, is_instance, is_mapper, is_property, is_selectable

Class signature

class sqlalchemy.ext.associationproxy.AssociationProxy (sqlalchemy.orm.base.InspectionAttrInfo)

method sqlalchemy.ext.associationproxy.AssociationProxy.__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:

target_collection¶ – Name of the collection we’ll proxy to, usually created with relationship().
attr¶ – Attribute on the collected instances we’ll proxy for. For example, given a target collection of [obj1, obj2], a list created by this proxy property would look like [getattr(obj1, attr), getattr(obj2, attr)]
creator¶ –
Optional. When new items are added to this proxied collection, new instances of the class collected by the target collection will be created. For list and set collections, the target class constructor will be called with the ‘value’ for the new 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.
cascade_scalar_deletes¶ –
when True, indicates that setting the proxied value to None, or deleting it via del, should also remove the source object. Only applies to scalar attributes. Normally, removing the proxied target will not remove the proxy source, as this object may have other state that is still to be kept.

New in version 1.3.

See also

Cascading Scalar Deletes - complete usage example
getset_factory¶ –
Optional. Proxied attribute access is automatically handled by routines that get and set values based on the attr argument for this proxy.

If you would like to customize this behavior, you may supply a getset_factory callable that produces a tuple of getter and setter functions. The factory is called with two arguments, the abstract type of the underlying collection and this proxy instance.
proxy_factory¶ – Optional. The type of collection to emulate is determined by sniffing the target collection. If your collection type can’t be determined by duck typing or you’d like to use a different collection implementation, you may supply a factory function to produce those collections. Only applicable to non-scalar relationships.
proxy_bulk_set¶ – Optional, use with proxy_factory. See the _set() method for details.
info¶ –
optional, will be assigned to AssociationProxy.info if present.

New in version 1.0.9.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.extension_type = symbol('ASSOCIATION_PROXY')¶: The extension type, if any. Defaults to NOT_EXTENSION

See also

HYBRID_METHOD

HYBRID_PROPERTY

ASSOCIATION_PROXY

method sqlalchemy.ext.associationproxy.AssociationProxy.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_class

This returns an instance of AssociationProxyInstance that is specific to the User class. The AssociationProxy object remains agnostic of its parent class.

Parameters:

class_¶ – the class that we are returning state for.
obj¶ – optional, an instance of the class that is required if the attribute refers to a polymorphic target, e.g. where we have to look at the type of the actual destination object to get the complete path.

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.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.info¶

inherited from the InspectionAttrInfo.info attribute of InspectionAttrInfo

Info dictionary associated with the object, allowing user-defined data to be associated with this InspectionAttr.

The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the column_property(), relationship(), 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.

See also

QueryableAttribute.info

SchemaItem.info

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_aliased_class = False¶: inherited from the InspectionAttr.is_aliased_class attribute of InspectionAttr

True if this object is an instance of AliasedClass.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_attribute = True¶

True if this object is a Python descriptor.

This can refer to one of many types. Usually a QueryableAttribute which handles attributes events on behalf of a MapperProperty. But can also be an extension type such as AssociationProxy or hybrid_property. The InspectionAttr.extension_type will refer to a constant identifying the specific subtype.

See also

Mapper.all_orm_descriptors

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_clause_element = False¶: inherited from the InspectionAttr.is_clause_element attribute of InspectionAttr

True if this object is an instance of ClauseElement.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_instance = False¶: inherited from the InspectionAttr.is_instance attribute of InspectionAttr

True if this object is an instance of InstanceState.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_mapper = False¶: inherited from the InspectionAttr.is_mapper attribute of InspectionAttr

True if this object is an instance of Mapper.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_property = False¶: inherited from the InspectionAttr.is_property attribute of InspectionAttr

True if this object is an instance of MapperProperty.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_selectable = False¶: inherited from the InspectionAttr.is_selectable attribute of InspectionAttr

Return True if this object is an instance of Selectable.

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 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
False

In 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
False

Members

any(), attr, delete(), for_proxy(), get(), has(), info, local_attr, remote_attr, scalar, set(), target_class

New in version 1.3.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.any(criterion=None, **kwargs)¶

Produce a proxied ‘any’ expression using EXISTS.

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

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.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)

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.delete(obj)¶

classmethod sqlalchemy.ext.associationproxy.AssociationProxyInstance.for_proxy(parent, owning_class, parent_instance)¶

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.get(obj)¶

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.has(criterion=None, **kwargs)¶

Produce a proxied ‘has’ expression using EXISTS.

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

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.info¶

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.local_attr¶: The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.remote_attr¶: The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.scalar¶: Return True if this AssociationProxyInstance proxies a scalar relationship on the local side.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.set(obj, values)¶

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.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.

class sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance(parent, owning_class, target_class, value_attr)¶

an AssociationProxyInstance that has an object as a target.

Members

any(), attr, contains(), has(), local_attr, remote_attr, scalar, target_class

Class signature

class sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance (sqlalchemy.ext.associationproxy.AssociationProxyInstance)

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.any(criterion=None, **kwargs)¶

inherited from the AssociationProxyInstance.any() method of AssociationProxyInstance

Produce a proxied ‘any’ expression using EXISTS.

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

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.attr¶

inherited from the AssociationProxyInstance.attr attribute of AssociationProxyInstance

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)

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.contains(obj)¶

Produce a proxied ‘contains’ expression using EXISTS.

This expression will be a composed product using the Comparator.any(), Comparator.has(), and/or Comparator.contains() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.has(criterion=None, **kwargs)¶

inherited from the AssociationProxyInstance.has() method of AssociationProxyInstance

Produce a proxied ‘has’ expression using EXISTS.

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

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.local_attr¶: inherited from the AssociationProxyInstance.local_attr attribute of AssociationProxyInstance

The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.remote_attr¶: inherited from the AssociationProxyInstance.remote_attr attribute of AssociationProxyInstance

The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.scalar¶: inherited from the AssociationProxyInstance.scalar attribute of AssociationProxyInstance

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

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.target_class = None¶

inherited from the AssociationProxyInstance.target_class attribute of AssociationProxyInstance

The intermediary class handled by this AssociationProxyInstance.

Intercepted append/set/assignment events will result in the generation of new instances of this class.

class sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance(parent, owning_class, target_class, value_attr)¶

an AssociationProxyInstance that has a database column as a target.

Members

__le__(), __lt__(), __ne__(), all_(), any(), any_(), asc(), attr, between(), bool_op(), collate(), concat(), contains(), desc(), distinct(), endswith(), has(), ilike(), in_(), is_(), is_distinct_from(), isnot(), isnot_distinct_from(), like(), local_attr, match(), notilike(), notin_(), notlike(), nullsfirst(), nullslast(), op(), operate(), remote_attr, reverse_operate(), scalar, startswith(), target_class, timetuple

Class signature

class sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance (sqlalchemy.sql.operators.ColumnOperators, sqlalchemy.ext.associationproxy.AssociationProxyInstance)

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__le__(other)¶

inherited from the sqlalchemy.sql.operators.ColumnOperators.__le__ method of ColumnOperators

Implement the <= operator.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__lt__(other)¶

inherited from the sqlalchemy.sql.operators.ColumnOperators.__lt__ method of ColumnOperators

Implement the < operator.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__ne__(other)¶

inherited from the sqlalchemy.sql.operators.ColumnOperators.__ne__ method of ColumnOperators

Implement the != operator.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.all_()¶

inherited from the ColumnOperators.all_() method of ColumnOperators

Produce 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_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.any(criterion=None, **kwargs)¶

inherited from the AssociationProxyInstance.any() method of AssociationProxyInstance

Produce a proxied ‘any’ expression using EXISTS.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.any_()¶

inherited from the ColumnOperators.any_() method of ColumnOperators

Produce 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_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.asc()¶: inherited from the ColumnOperators.asc() method of ColumnOperators

Produce a asc() clause against the parent object.

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.attr¶

inherited from the AssociationProxyInstance.attr attribute of AssociationProxyInstance

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)

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.between(cleft, cright, symmetric=False)¶: inherited from the ColumnOperators.between() method of ColumnOperators

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bool_op(opstring, precedence=0)¶

inherited from the Operators.bool_op() method of Operators

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

Operators.op()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.collate(collation)¶: inherited from the ColumnOperators.collate() method of ColumnOperators

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

See also

collate()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.concat(other)¶

inherited from the ColumnOperators.concat() method of ColumnOperators

Implement the ‘concat’ operator.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.contains(other, **kwargs)¶

inherited from the ColumnOperators.contains() method of ColumnOperators

Implement the ‘contains’ operator.

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

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

E.g.:

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

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, 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:

other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.contains.autoescape flag is set to True.
autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

An expression such as:
```
somecolumn.contains("foo%bar", autoescape=True)
```
Will render as:
```
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
```
With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: The ColumnOperators.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 the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

An expression such as:
```
somecolumn.contains("foo/%bar", escape="^")
```
Will render as:
```
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
```
The parameter may also be combined with ColumnOperators.contains.autoescape:
```
somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
```
Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.desc()¶: inherited from the ColumnOperators.desc() method of ColumnOperators

Produce a desc() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.distinct()¶: inherited from the ColumnOperators.distinct() method of ColumnOperators

Produce a distinct() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.endswith(other, **kwargs)¶

inherited from the ColumnOperators.endswith() method of ColumnOperators

Implement the ‘endswith’ operator.

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

column LIKE '%' || <other>

E.g.:

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

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, 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:

other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.
autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

An expression such as:
```
somecolumn.endswith("foo%bar", autoescape=True)
```
Will render as:
```
somecolumn LIKE '%' || :param ESCAPE '/'
```
With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: The ColumnOperators.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¶ –
a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

An expression such as:
```
somecolumn.endswith("foo/%bar", escape="^")
```
Will render as:
```
somecolumn LIKE '%' || :param ESCAPE '^'
```
The parameter may also be combined with ColumnOperators.endswith.autoescape:
```
somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
```
Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.has(criterion=None, **kwargs)¶

inherited from the AssociationProxyInstance.has() method of AssociationProxyInstance

Produce a proxied ‘has’ expression using EXISTS.

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.ilike(other, escape=None)¶

inherited from the ColumnOperators.ilike() method of ColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

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

Parameters:

other¶ – expression to be compared
escape¶ –
optional escape character, renders the ESCAPE keyword, e.g.:
```
somecolumn.ilike("foo/%bar", escape="/")
```

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.in_(other)¶

inherited from the ColumnOperators.in_() method of ColumnOperators

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:

A list of literal values, e.g.:
```
stmt.where(column.in_([1, 2, 3]))
```
In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
```
WHERE COL IN (?, ?, ?)
```

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

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

An empty list, e.g.:
```
stmt.where(column.in_([]))
```
In this calling form, the expression renders a “false” expression, e.g.:
```
WHERE 1 != 1
```
This “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.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_(other)¶

inherited from the ColumnOperators.is_() method of ColumnOperators

Implement 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

ColumnOperators.isnot()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_distinct_from(other)¶

inherited from the ColumnOperators.is_distinct_from() method of ColumnOperators

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.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.isnot(other)¶

inherited from the ColumnOperators.isnot() method of ColumnOperators

Implement 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

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.isnot_distinct_from(other)¶

inherited from the ColumnOperators.isnot_distinct_from() method of ColumnOperators

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.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.like(other, escape=None)¶

inherited from the ColumnOperators.like() method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

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

Parameters:

other¶ – expression to be compared
escape¶ –
optional escape character, renders the ESCAPE keyword, e.g.:
```
somecolumn.like("foo/%bar", escape="/")
```

See also

ColumnOperators.ilike()

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.local_attr¶: inherited from the AssociationProxyInstance.local_attr attribute of AssociationProxyInstance

The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.match(other, **kwargs)¶

inherited from the ColumnOperators.match() method of ColumnOperators

Implements a database-specific ‘match’ operator.

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

PostgreSQL - renders x @@ 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 emit the operator as “MATCH”. This is compatible with SQLite, for example.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notilike(other, escape=None)¶

inherited from the ColumnOperators.notilike() method of ColumnOperators

implement the NOT ILIKE operator.

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

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notin_(other)¶

inherited from the ColumnOperators.notin_() method of ColumnOperators

implement the NOT IN operator.

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

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. 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

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notlike(other, escape=None)¶

inherited from the ColumnOperators.notlike() method of ColumnOperators

implement the NOT LIKE operator.

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

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nullsfirst()¶: inherited from the ColumnOperators.nullsfirst() method of ColumnOperators

Produce a nullsfirst() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nullslast()¶: inherited from the ColumnOperators.nullslast() method of ColumnOperators

Produce a nullslast() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.op(opstring, precedence=0, is_comparison=False, return_type=None)¶

inherited from the Operators.op() method of Operators

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

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

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

is a bitwise AND of the value in somecolumn.

Parameters:

operator¶ – a string which will be output as the infix operator between this element and the expression passed to the generated function.
precedence¶ – precedence to apply to the operator, when parenthesizing expressions. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of 0 is lower than all operators except for the comma (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will 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 boolean true/false value, like ==, >, etc. This flag should be set so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.

New in version 0.9.2: - added the Operators.op.is_comparison flag.
return_type¶ –
a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify Operators.op.is_comparison will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

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

See also

Redefining and Creating New Operators

Using custom operators in join conditions

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.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:

op¶ – Operator callable.
*other¶ – the ‘other’ side of the operation. Will be a single scalar for most operations.
**kwargs¶ – modifiers. These may be passed by special operators such as ColumnOperators.contains().

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.remote_attr¶: inherited from the AssociationProxyInstance.remote_attr attribute of AssociationProxyInstance

The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.reverse_operate(op, other, **kwargs)¶

inherited from the Operators.reverse_operate() method of Operators

Reverse operate on an argument.

Usage is the same as operate().

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.scalar¶: inherited from the AssociationProxyInstance.scalar attribute of AssociationProxyInstance

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

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.startswith(other, **kwargs)¶

inherited from the ColumnOperators.startswith() method of ColumnOperators

Implement the startswith operator.

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

column LIKE <other> || '%'

E.g.:

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

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, 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:

other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.
autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

An expression such as:
```
somecolumn.startswith("foo%bar", autoescape=True)
```
Will render as:
```
somecolumn LIKE :param || '%' ESCAPE '/'
```
With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: The ColumnOperators.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¶ –
a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

An expression such as:
```
somecolumn.startswith("foo/%bar", escape="^")
```
Will render as:
```
somecolumn LIKE :param || '%' ESCAPE '^'
```
The parameter may also be combined with ColumnOperators.startswith.autoescape:
```
somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
```
Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.target_class = None¶

inherited from the AssociationProxyInstance.target_class attribute of AssociationProxyInstance

The intermediary class handled by this AssociationProxyInstance.

Intercepted append/set/assignment events will result in the generation of new instances of this class.

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.timetuple = None¶: inherited from the ColumnOperators.timetuple attribute of ColumnOperators

Hack, allows datetime objects to be compared on the LHS.

sqlalchemy.ext.associationproxy.ASSOCIATION_PROXY = symbol('ASSOCIATION_PROXY')¶

Symbol indicating an InspectionAttr that’s: of type AssociationProxy.

Is assigned to the InspectionAttr.extension_type attribute.

SQLAlchemy 1.3 Documentation

SQLAlchemy 1.3 Documentation

SQLAlchemy ORM

Project Versions

Association Proxy¶

Simplifying Scalar Collections¶

Creation of New Values¶

Simplifying Association Objects¶

Proxying to Dictionary Based Collections¶

Composite Association Proxies¶

Querying with Association Proxies¶

Cascading Scalar Deletes¶

API Documentation¶