SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- 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
association_proxy()
AssociationProxy
AssociationProxy.__init__()
AssociationProxy.extension_type
AssociationProxy.for_class()
AssociationProxy.info
AssociationProxy.is_aliased_class
AssociationProxy.is_attribute
AssociationProxy.is_clause_element
AssociationProxy.is_instance
AssociationProxy.is_mapper
AssociationProxy.is_property
AssociationProxy.is_selectable
AssociationProxyInstance
AssociationProxyInstance.any()
AssociationProxyInstance.attr
AssociationProxyInstance.delete()
AssociationProxyInstance.for_proxy()
AssociationProxyInstance.get()
AssociationProxyInstance.has()
AssociationProxyInstance.info
AssociationProxyInstance.local_attr
AssociationProxyInstance.remote_attr
AssociationProxyInstance.scalar
AssociationProxyInstance.set()
AssociationProxyInstance.target_class
ObjectAssociationProxyInstance
ObjectAssociationProxyInstance.any()
ObjectAssociationProxyInstance.attr
ObjectAssociationProxyInstance.contains()
ObjectAssociationProxyInstance.has()
ObjectAssociationProxyInstance.local_attr
ObjectAssociationProxyInstance.remote_attr
ObjectAssociationProxyInstance.scalar
ObjectAssociationProxyInstance.target_class
ColumnAssociationProxyInstance
ColumnAssociationProxyInstance.__le__()
ColumnAssociationProxyInstance.__lt__()
ColumnAssociationProxyInstance.__ne__()
ColumnAssociationProxyInstance.all_()
ColumnAssociationProxyInstance.any()
ColumnAssociationProxyInstance.any_()
ColumnAssociationProxyInstance.asc()
ColumnAssociationProxyInstance.attr
ColumnAssociationProxyInstance.between()
ColumnAssociationProxyInstance.bool_op()
ColumnAssociationProxyInstance.collate()
ColumnAssociationProxyInstance.concat()
ColumnAssociationProxyInstance.contains()
ColumnAssociationProxyInstance.desc()
ColumnAssociationProxyInstance.distinct()
ColumnAssociationProxyInstance.endswith()
ColumnAssociationProxyInstance.has()
ColumnAssociationProxyInstance.ilike()
ColumnAssociationProxyInstance.in_()
ColumnAssociationProxyInstance.is_()
ColumnAssociationProxyInstance.is_distinct_from()
ColumnAssociationProxyInstance.isnot()
ColumnAssociationProxyInstance.isnot_distinct_from()
ColumnAssociationProxyInstance.like()
ColumnAssociationProxyInstance.local_attr
ColumnAssociationProxyInstance.match()
ColumnAssociationProxyInstance.notilike()
ColumnAssociationProxyInstance.notin_()
ColumnAssociationProxyInstance.notlike()
ColumnAssociationProxyInstance.nullsfirst()
ColumnAssociationProxyInstance.nullslast()
ColumnAssociationProxyInstance.op()
ColumnAssociationProxyInstance.operate()
ColumnAssociationProxyInstance.remote_attr
ColumnAssociationProxyInstance.reverse_operate()
ColumnAssociationProxyInstance.scalar
ColumnAssociationProxyInstance.startswith()
ColumnAssociationProxyInstance.target_class
ColumnAssociationProxyInstance.timetuple
ASSOCIATION_PROXY
- Automap
- Baked Queries
- Declarative
- Mutation Tracking
- Ordering List
- Horizontal Sharding
- Hybrid Attributes
- Indexable
- Alternate Class Instrumentation
- Association Proxy¶
- ORM Examples
Project Versions
- Previous: ORM Extensions
- Next: Automap
- Up: Home
- On this page:
- 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
association_proxy()
AssociationProxy
AssociationProxy.__init__()
AssociationProxy.extension_type
AssociationProxy.for_class()
AssociationProxy.info
AssociationProxy.is_aliased_class
AssociationProxy.is_attribute
AssociationProxy.is_clause_element
AssociationProxy.is_instance
AssociationProxy.is_mapper
AssociationProxy.is_property
AssociationProxy.is_selectable
AssociationProxyInstance
AssociationProxyInstance.any()
AssociationProxyInstance.attr
AssociationProxyInstance.delete()
AssociationProxyInstance.for_proxy()
AssociationProxyInstance.get()
AssociationProxyInstance.has()
AssociationProxyInstance.info
AssociationProxyInstance.local_attr
AssociationProxyInstance.remote_attr
AssociationProxyInstance.scalar
AssociationProxyInstance.set()
AssociationProxyInstance.target_class
ObjectAssociationProxyInstance
ObjectAssociationProxyInstance.any()
ObjectAssociationProxyInstance.attr
ObjectAssociationProxyInstance.contains()
ObjectAssociationProxyInstance.has()
ObjectAssociationProxyInstance.local_attr
ObjectAssociationProxyInstance.remote_attr
ObjectAssociationProxyInstance.scalar
ObjectAssociationProxyInstance.target_class
ColumnAssociationProxyInstance
ColumnAssociationProxyInstance.__le__()
ColumnAssociationProxyInstance.__lt__()
ColumnAssociationProxyInstance.__ne__()
ColumnAssociationProxyInstance.all_()
ColumnAssociationProxyInstance.any()
ColumnAssociationProxyInstance.any_()
ColumnAssociationProxyInstance.asc()
ColumnAssociationProxyInstance.attr
ColumnAssociationProxyInstance.between()
ColumnAssociationProxyInstance.bool_op()
ColumnAssociationProxyInstance.collate()
ColumnAssociationProxyInstance.concat()
ColumnAssociationProxyInstance.contains()
ColumnAssociationProxyInstance.desc()
ColumnAssociationProxyInstance.distinct()
ColumnAssociationProxyInstance.endswith()
ColumnAssociationProxyInstance.has()
ColumnAssociationProxyInstance.ilike()
ColumnAssociationProxyInstance.in_()
ColumnAssociationProxyInstance.is_()
ColumnAssociationProxyInstance.is_distinct_from()
ColumnAssociationProxyInstance.isnot()
ColumnAssociationProxyInstance.isnot_distinct_from()
ColumnAssociationProxyInstance.like()
ColumnAssociationProxyInstance.local_attr
ColumnAssociationProxyInstance.match()
ColumnAssociationProxyInstance.notilike()
ColumnAssociationProxyInstance.notin_()
ColumnAssociationProxyInstance.notlike()
ColumnAssociationProxyInstance.nullsfirst()
ColumnAssociationProxyInstance.nullslast()
ColumnAssociationProxyInstance.op()
ColumnAssociationProxyInstance.operate()
ColumnAssociationProxyInstance.remote_attr
ColumnAssociationProxyInstance.reverse_operate()
ColumnAssociationProxyInstance.scalar
ColumnAssociationProxyInstance.startswith()
ColumnAssociationProxyInstance.target_class
ColumnAssociationProxyInstance.timetuple
ASSOCIATION_PROXY
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
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. |
A descriptor that presents a read/write view of an object attribute. |
|
A per-class object that serves class- and object-specific results. |
|
an |
|
an |
- 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, thoughAssociationProxy
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 viadel
, 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
-
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
fromMapper.all_orm_descriptors
, and we want to view the target class for this proxy as mapped byUser
:inspect(User).all_orm_descriptors["keywords"].for_class(User).target_class
This returns an instance of
AssociationProxyInstance
that is specific to theUser
class. TheAssociationProxy
object remains agnostic of its parent class.- Parameters:
New in version 1.3: -
AssociationProxy
no longer stores any state specific to a particular parent class; the state is now stored in per-classAssociationProxyInstance
objects.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
info¶ inherited from the
InspectionAttrInfo.info
attribute ofInspectionAttrInfo
Info dictionary associated with the object, allowing user-defined data to be associated with this
InspectionAttr
.The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the
column_property()
,relationship()
, orcomposite()
functions.Changed in version 1.0.0:
MapperProperty.info
is also available on extension types via theInspectionAttrInfo.info
attribute, so that it can apply to a wider variety of ORM and extension constructs.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_aliased_class = False¶ inherited from the
InspectionAttr.is_aliased_class
attribute ofInspectionAttr
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 aMapperProperty
. But can also be an extension type such asAssociationProxy
orhybrid_property
. TheInspectionAttr.extension_type
will refer to a constant identifying the specific subtype.See also
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_clause_element = False¶ inherited from the
InspectionAttr.is_clause_element
attribute ofInspectionAttr
True if this object is an instance of
ClauseElement
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_instance = False¶ inherited from the
InspectionAttr.is_instance
attribute ofInspectionAttr
True if this object is an instance of
InstanceState
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_mapper = False¶ inherited from the
InspectionAttr.is_mapper
attribute ofInspectionAttr
True if this object is an instance of
Mapper
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_property = False¶ inherited from the
InspectionAttr.is_property
attribute ofInspectionAttr
True if this object is an instance of
MapperProperty
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_selectable = False¶ inherited from the
InspectionAttr.is_selectable
attribute ofInspectionAttr
Return True if this object is an instance of
Selectable
.
-
method
- 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, theAssociationProxyInstance
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 theAssociationProxyInstance
, use theAssociationProxy.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/orComparator.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)
-
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/orComparator.has()
operators of the underlying proxied attributes.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
local_attr¶ The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
remote_attr¶ The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
scalar¶ Return
True
if thisAssociationProxyInstance
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.
-
method
- 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 ofAssociationProxyInstance
Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
attr¶ inherited from the
AssociationProxyInstance.attr
attribute ofAssociationProxyInstance
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)
-
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/orComparator.contains()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
has(criterion=None, **kwargs)¶ inherited from the
AssociationProxyInstance.has()
method ofAssociationProxyInstance
Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
local_attr¶ inherited from the
AssociationProxyInstance.local_attr
attribute ofAssociationProxyInstance
The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
remote_attr¶ inherited from the
AssociationProxyInstance.remote_attr
attribute ofAssociationProxyInstance
The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
scalar¶ inherited from the
AssociationProxyInstance.scalar
attribute ofAssociationProxyInstance
Return
True
if thisAssociationProxyInstance
proxies a scalar relationship on the local side.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
target_class = None¶ inherited from the
AssociationProxyInstance.target_class
attribute ofAssociationProxyInstance
The intermediary class handled by this
AssociationProxyInstance
.Intercepted append/set/assignment events will result in the generation of new instances of this class.
-
method
- 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 ofColumnOperators
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 ofColumnOperators
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 ofColumnOperators
Implement the
!=
operator.In a column context, produces the clause
a != b
. If the target isNone
, producesa IS NOT NULL
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
all_()¶ inherited from the
ColumnOperators.all_()
method ofColumnOperators
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_()
New in version 1.1.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
any(criterion=None, **kwargs)¶ inherited from the
AssociationProxyInstance.any()
method ofAssociationProxyInstance
Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
any_()¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
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_()
New in version 1.1.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
asc()¶ inherited from the
ColumnOperators.asc()
method ofColumnOperators
Produce a
asc()
clause against the parent object.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
attr¶ inherited from the
AssociationProxyInstance.attr
attribute ofAssociationProxyInstance
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)
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
between(cleft, cright, symmetric=False)¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
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 ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True.New in version 1.2.0b3.
See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
collate(collation)¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
concat(other)¶ inherited from the
ColumnOperators.concat()
method ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
operator on MySQL.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
contains(other, **kwargs)¶ inherited from the
ColumnOperators.contains()
method ofColumnOperators
Implement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle of a string value:
column LIKE '%' || <other> || '%'
E.g.:
stmt = select([sometable]).\ where(sometable.c.column.contains("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.contains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.contains.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.contains.autoescape
flag is set to True.autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.contains("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
desc()¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
distinct()¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
Produce a
distinct()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
endswith(other, **kwargs)¶ inherited from the
ColumnOperators.endswith()
method ofColumnOperators
Implement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end of a string value:
column LIKE '%' || <other>
E.g.:
stmt = select([sometable]).\ where(sometable.c.column.endswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.endswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.endswith.autoescape
flag is set to True.autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.endswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param ESCAPE '/'
With the value of
:param
as"foo/%bar"
.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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
has(criterion=None, **kwargs)¶ inherited from the
AssociationProxyInstance.has()
method ofAssociationProxyInstance
Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
ilike(other, escape=None)¶ inherited from the
ColumnOperators.ilike()
method ofColumnOperators
Implement the
ilike
operator, e.g. case insensitive LIKE.In a column context, produces an expression either of the form:
lower(a) LIKE lower(other)
Or on backends that support the ILIKE operator:
a ILIKE other
E.g.:
stmt = select([sometable]).\ where(sometable.c.column.ilike("%foobar%"))
- Parameters:
See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
in_(other)¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
Implement the
in
operator.In a column context, produces the clause
column IN <other>
.The given parameter
other
may be:A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))
In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)
A list of tuples may be provided if the comparison is against a
tuple_()
containing multiple expressions:from sqlalchemy import tuple_ stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
An empty list, e.g.:
stmt.where(column.in_([]))
In this calling form, the expression renders 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 thebindparam.expanding
flag:stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})
The database would be passed a bound parameter for each value:
WHERE COL IN (?, ?, ?)
New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support empty lists
a
select()
construct, which is usually a correlated scalar select:stmt.where( column.in_( select([othertable.c.y]). where(table.c.x == othertable.c.x) ) )
In this calling form,
ColumnOperators.in_()
renders as given:WHERE COL IN (SELECT othertable.y FROM othertable WHERE othertable.x = table.x)
- Parameters:
other¶ – a list of literals, a
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
is_(other)¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
is_distinct_from(other)¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
Implement the
IS DISTINCT FROM
operator.Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.
New in version 1.1.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
isnot(other)¶ inherited from the
ColumnOperators.isnot()
method ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
isnot_distinct_from(other)¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
Implement the
IS NOT DISTINCT FROM
operator.Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.
New in version 1.1.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
like(other, escape=None)¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
Implement the
like
operator.In a column context, produces the expression:
a LIKE other
E.g.:
stmt = select([sometable]).\ where(sometable.c.column.like("%foobar%"))
- Parameters:
See also
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
local_attr¶ inherited from the
AssociationProxyInstance.local_attr
attribute ofAssociationProxyInstance
The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
match(other, **kwargs)¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
Implements a database-specific ‘match’ operator.
ColumnOperators.match()
attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:PostgreSQL - renders
x @@ 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 ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
notin_(other)¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
implement the
NOT IN
operator.This is equivalent to using negation with
ColumnOperators.in_()
, i.e.~x.in_(y)
.In the case that
other
is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.Changed in version 1.2: The
ColumnOperators.in_()
andColumnOperators.notin_()
operators now produce a “static” expression for an empty IN sequence by default.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
notlike(other, escape=None)¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.See also
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
nullsfirst()¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nullsfirst()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
nullslast()¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
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 ofOperators
Produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in
somecolumn
.- Parameters:
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 (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.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 specifyOperators.op.is_comparison
will resolve toBoolean
, 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.
-
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 applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other): return op(func.lower(self), func.lower(other))
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
remote_attr¶ inherited from the
AssociationProxyInstance.remote_attr
attribute ofAssociationProxyInstance
The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
reverse_operate(op, other, **kwargs)¶ inherited from the
Operators.reverse_operate()
method ofOperators
Reverse operate on an argument.
Usage is the same as
operate()
.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
scalar¶ inherited from the
AssociationProxyInstance.scalar
attribute ofAssociationProxyInstance
Return
True
if thisAssociationProxyInstance
proxies a scalar relationship on the local side.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
startswith(other, **kwargs)¶ inherited from the
ColumnOperators.startswith()
method ofColumnOperators
Implement the
startswith
operator.Produces a LIKE expression that tests against a match for the start of a string value:
column LIKE <other> || '%'
E.g.:
stmt = select([sometable]).\ where(sometable.c.column.startswith("foobar"))
Since the operator uses
LIKE
, wildcard characters"%"
and"_"
that are present inside the <other> expression will behave like wildcards as well. For literal string values, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.startswith.escape
parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.- Parameters:
other¶ – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters
%
and_
are not escaped by default unless theColumnOperators.startswith.autoescape
flag is set to True.autoescape¶ –
boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of
"%"
,"_"
and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.An expression such as:
somecolumn.startswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE :param || '%' ESCAPE '/'
With the value of
:param
as"foo/%bar"
.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 theColumnOperators.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.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
target_class = None¶ inherited from the
AssociationProxyInstance.target_class
attribute ofAssociationProxyInstance
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 ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
method
- 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.- Symbol indicating an
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:14 PM