SQLAlchemy 1.1 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
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 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.string_key
value to Keyword
objects:
>>> user = User('log')
>>> user.keywords['sk1'] = Keyword('kw1')
>>> user.keywords['sk2'] = Keyword('kw2')
>>> print(user.keywords)
{'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}
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)
)
def __init__(self, name):
self.name = name
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'),
primary_key=True)
special_key = Column(String)
user = relationship(User, backref=backref(
"user_keywords",
collection_class=attribute_mapped_collection("special_key"),
cascade="all, delete-orphan"
)
)
# the relationship to Keyword is now called
# 'kw'
kw = relationship("Keyword")
# 'keyword' is changed to be a proxy to the
# 'keyword' attribute of 'Keyword'
keyword = association_proxy('kw', 'keyword')
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = 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 relate down to the underlying relationship()
in use as well as the target attribute. For example, the RelationshipProperty.Comparator.any()
and RelationshipProperty.Comparator.has()
operations are available, and will produce a “nested” EXISTS clause, such as in our basic association object example:
>>> print(session.query(User).filter(User.keywords.any(keyword='jek')))
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE EXISTS (SELECT 1
FROM user_keyword
WHERE user.id = user_keyword.user_id AND (EXISTS (SELECT 1
FROM keyword
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)))
For a proxy to a scalar attribute, __eq__()
is supported:
>>> print(session.query(UserKeyword).filter(UserKeyword.keyword == 'jek'))
SELECT user_keyword.*
FROM user_keyword
WHERE EXISTS (SELECT 1
FROM keyword
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)
and .contains()
is available for a proxy to a scalar collection:
>>> print(session.query(User).filter(User.keywords.contains('jek')))
SELECT user.*
FROM user
WHERE EXISTS (SELECT 1
FROM userkeywords, keyword
WHERE user.id = userkeywords.user_id
AND keyword.id = userkeywords.keyword_id
AND keyword.keyword = :keyword_1)
AssociationProxy
can be used with Query.join()
somewhat manually using the attr
attribute in a star-args context:
q = session.query(User).join(*User.keywords.attr)
New in version 0.7.3: attr
attribute in a star-args context.
attr
is composed of AssociationProxy.local_attr
and AssociationProxy.remote_attr
, which are just synonyms for the actual proxied attributes, and can also be used for querying:
uka = aliased(UserKeyword)
ka = aliased(Keyword)
q = session.query(User).\
join(uka, User.keywords.local_attr).\
join(ka, User.keywords.remote_attr)
New in version 0.7.3: AssociationProxy.local_attr
and AssociationProxy.remote_attr
, synonyms for the actual proxied attributes, and usable for querying.
API Documentation¶
-
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
.
- target_collection¶ – Name of the attribute we’ll proxy to. This attribute is typically mapped by
- class
sqlalchemy.ext.associationproxy.
AssociationProxy
(target_collection, attr, creator=None, getset_factory=None, proxy_factory=None, proxy_bulk_set=None, info=None)¶ Bases:
sqlalchemy.orm.base.InspectionAttrInfo
A descriptor that presents a read/write view of an object attribute.
-
__init__
(target_collection, attr, creator=None, getset_factory=None, proxy_factory=None, proxy_bulk_set=None, info=None)¶ 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.
- 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.
- target_collection¶ – Name of the collection we’ll proxy to, usually created with
-
any
(criterion=None, **kwargs)¶ Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product using the
RelationshipProperty.Comparator.any()
and/orRelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
-
attr
¶ Return a tuple of
(local_attr, remote_attr)
.This attribute is convenient when specifying a join using
Query.join()
across two relationships:sess.query(Parent).join(*Parent.proxied.attr)
New in version 0.7.3.
See also:
-
contains
(obj)¶ Produce a proxied ‘contains’ expression using EXISTS.
This expression will be a composed product using the
RelationshipProperty.Comparator.any()
,RelationshipProperty.Comparator.has()
, and/orRelationshipProperty.Comparator.contains()
operators of the underlying proxied attributes.
-
extension_type
= symbol('ASSOCIATION_PROXY')¶
-
has
(criterion=None, **kwargs)¶ Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product using the
RelationshipProperty.Comparator.any()
and/orRelationshipProperty.Comparator.has()
operators of the underlying proxied attributes.
-
info
¶ - inherited from the
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.New in version 0.8: Added support for .info to all
MapperProperty
subclasses.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.
-
is_aliased_class
= False¶
-
is_attribute
= False¶
-
is_clause_element
= False¶
-
is_instance
= False¶
-
is_mapper
= False¶
-
is_property
= False¶
-
is_selectable
= False¶
-
local_attr
¶ The ‘local’
MapperProperty
referenced by thisAssociationProxy
.New in version 0.7.3.
See also:
-
remote_attr
¶ The ‘remote’
MapperProperty
referenced by thisAssociationProxy
.New in version 0.7.3.
See also:
-
scalar
¶ Return
True
if thisAssociationProxy
proxies a scalar relationship on the local side.
-
target_class
¶ The intermediary class handled by this
AssociationProxy
.Intercepted append/set/assignment events will result in the generation of new instances of this class.
-
-
sqlalchemy.ext.associationproxy.
ASSOCIATION_PROXY
= symbol('ASSOCIATION_PROXY')¶