SQLAlchemy 1.2 Documentation
Changing Attribute Behavior¶
A quick way to add a “validation” routine to an attribute is to use the
validates() decorator. An attribute validator can raise
an exception, halting the process of mutating the attribute’s value, or can
change the given value into something different. Validators, like all
attribute extensions, are only called by normal userland code; they are not
issued when the ORM is populating the object:
from sqlalchemy.orm import validates class EmailAddress(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) email = Column(String) @validates('email') def validate_email(self, key, address): assert '@' in address return address
Changed in version 1.0.0: - validators are no longer triggered within the flush process when the newly fetched values for primary key columns as well as some python- or server-side defaults are fetched. Prior to 1.0, validators may be triggered in those cases as well.
Validators also receive collection append events, when items are added to a collection:
from sqlalchemy.orm import validates class User(Base): # ... addresses = relationship("Address") @validates('addresses') def validate_address(self, key, address): assert '@' in address.email return address
The validation function by default does not get emitted for collection
remove events, as the typical expectation is that a value being discarded
doesn’t require validation. However,
validates() supports reception
of these events by specifying
include_removes=True to the decorator. When
this flag is set, the validation function must receive an additional boolean
argument which if
True indicates that the operation is a removal:
from sqlalchemy.orm import validates class User(Base): # ... addresses = relationship("Address") @validates('addresses', include_removes=True) def validate_address(self, key, address, is_remove): if is_remove: raise ValueError( "not allowed to remove items from the collection") else: assert '@' in address.email return address
The case where mutually dependent validators are linked via a backref
can also be tailored, using the
include_backrefs=False option; this option,
when set to
False, prevents a validation function from emitting if the
event occurs as a result of a backref:
from sqlalchemy.orm import validates class User(Base): # ... addresses = relationship("Address", backref='user') @validates('addresses', include_backrefs=False) def validate_address(self, key, address): assert '@' in address.email return address
Above, if we were to assign to
Address.user as in
some_address.user = some_user,
validate_address() function would not be emitted, even though an append
some_user.addresses - the event is caused by a backref.
Note that the
validates() decorator is a convenience function built on
top of attribute events. An application that requires more control over
configuration of attribute change behavior can make use of this system,
Decorate a method as a ‘validator’ for one or more named properties.
Designates a method as a validator, a method which receives the name of the attribute as well as a value to be assigned, or in the case of a collection, the value to be added to the collection. The function can then raise validation exceptions to halt the process from continuing (where Python’s built-in
AssertionErrorexceptions are reasonable choices), or can modify or replace the value before proceeding. The function should otherwise return the given value.
Note that a validator for a collection cannot issue a load of that collection within the validation routine - this usage raises an assertion to avoid recursion overflows. This is a reentrant condition which is not supported.
- *names¶ – list of attribute names to be validated.
- include_removes¶ –
if True, “remove” events will be sent as well - the validation function must accept an additional argument “is_remove” which will be a boolean.
New in version 0.7.7.
- include_backrefs¶ –
False, the validation function will not emit if the originator is an attribute event related via a backref. This can be used for bi-directional
validates()usage where only one validator should emit per attribute operation.
New in version 0.9.0.
Using Descriptors and Hybrids¶
A more comprehensive way to produce modified behavior for an attribute is to
use descriptors. These are commonly used in Python using the
function. The standard SQLAlchemy technique for descriptors is to create a
plain descriptor, and to have it read/write from a mapped attribute with a
different name. Below we illustrate this using Python 2.6-style properties:
class EmailAddress(Base): __tablename__ = 'email_address' id = Column(Integer, primary_key=True) # name the attribute with an underscore, # different from the column name _email = Column("email", String) # then create an ".email" attribute # to get/set "._email" @property def email(self): return self._email @email.setter def email(self, email): self._email = email
The approach above will work, but there’s more we can add. While our
EmailAddress object will shuttle the value through the
_email mapped attribute, the class level
EmailAddress.email attribute does not have the usual expression semantics
Query. To provide these, we instead use the
hybrid extension as follows:
from sqlalchemy.ext.hybrid import hybrid_property class EmailAddress(Base): __tablename__ = 'email_address' id = Column(Integer, primary_key=True) _email = Column("email", String) @hybrid_property def email(self): return self._email @email.setter def email(self, email): self._email = email
EmailAddress, also provides a SQL expression when used at the class level,
that is, from the
EmailAddress class directly:
from sqlalchemy.orm import Session session = Session() sqladdress = session.query(EmailAddress).\ filter(EmailAddress.email == 'firstname.lastname@example.org').\ one()SELECT address.email AS address_email, address.id AS address_id FROM address WHERE address.email = ? ('email@example.com',)address.email = 'firstname.lastname@example.org' sqlsession.commit()UPDATE address SET email=? WHERE address.id = ? ('email@example.com', 1) COMMIT
hybrid_property also allows us to change the behavior of the
attribute, including defining separate behaviors when the attribute is
accessed at the instance level versus at the class/expression level, using the
hybrid_property.expression() modifier. Such as, if we wanted to add a
host name automatically, we might define two sets of string manipulation
class EmailAddress(Base): __tablename__ = 'email_address' id = Column(Integer, primary_key=True) _email = Column("email", String) @hybrid_property def email(self): """Return the value of _email up until the last twelve characters.""" return self._email[:-12] @email.setter def email(self, email): """Set the value of _email, tacking on the twelve character value @example.com.""" self._email = email + "@example.com" @email.expression def email(cls): """Produce a SQL expression that represents the value of the _email column, minus the last twelve characters.""" return func.substr(cls._email, 0, func.length(cls._email) - 12)
Above, accessing the
will return the value of the
_email attribute, removing or adding the
@example.com from the value. When we query against the
sqladdress = session.query(EmailAddress).filter(EmailAddress.email == 'address').one()SELECT address.email AS address_email, address.id AS address_id FROM address WHERE substr(address.email, ?, length(address.email) - ?) = ? (0, 12, 'address')
Read more about Hybrids at Hybrid Attributes.
Synonyms are a mapper-level construct that allow any attribute on a class to “mirror” another attribute that is mapped.
In the most basic sense, the synonym is an easy way to make a certain attribute available by an additional name:
class MyClass(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) job_status = Column(String(50)) status = synonym("job_status")
The above class
MyClass has two attributes,
.status that will behave as one attribute, both at the expression
>>> print(MyClass.job_status == 'some_status') my_table.job_status = :job_status_1 >>> print(MyClass.status == 'some_status') my_table.job_status = :job_status_1
and at the instance level:
>>> m1 = MyClass(status='x') >>> m1.status, m1.job_status ('x', 'x') >>> m1.job_status = 'y' >>> m1.status, m1.job_status ('y', 'y')
class MyClass(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) status = Column(String(50)) @property def job_status(self): return "Status: " + self.status job_status = synonym("status", descriptor=job_status)
When using Declarative, the above pattern can be expressed more succinctly
from sqlalchemy.ext.declarative import synonym_for class MyClass(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) status = Column(String(50)) @synonym_for("status") @property def job_status(self): return "Status: " + self.status
synonym() is useful for simple mirroring, the use case
of augmenting attribute behavior with descriptors is better handled in modern
usage using the hybrid attribute feature, which
is more oriented towards Python descriptors. Technically, a
can do everything that a
hybrid_property can do, as it also supports
injection of custom SQL capabilities, but the hybrid is more straightforward
to use in more complex situations.
synonym(name, map_column=None, descriptor=None, comparator_factory=None, doc=None, info=None)¶
Denote an attribute name as a synonym to a mapped property, in that the attribute will mirror the value and expression behavior of another attribute.
- name¶ – the name of the existing mapped property. This
can refer to the string name of any
MapperPropertyconfigured on the class, including column-bound attributes and relationships.
- descriptor¶ – a Python descriptor that will be used as a getter (and potentially a setter) when this attribute is accessed at the instance level.
- map_column¶ –
synonym()construct will locate the existing named
MapperPropertybased on the attribute name of this
synonym(), and assign it to a new attribute linked to the name of this
synonym(). That is, given a mapping like:
class MyClass(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) job_status = Column(String(50)) job_status = synonym("_job_status", map_column=True)
The above class
MyClasswill now have the
Columnobject mapped to the attribute named
_job_status, and the attribute named
job_statuswill refer to the synonym itself. This feature is typically used in conjunction with the
descriptorargument in order to link a user-defined descriptor as a “wrapper” for an existing column.
- info¶ –
Optional data dictionary which will be populated into the
InspectionAttr.infoattribute of this object.
New in version 1.0.0.
- comparator_factory¶ –
A subclass of
PropComparatorthat will provide custom comparison behavior at the SQL expression level.
For the use case of providing an attribute which redefines both Python-level and SQL-expression level behavior of an attribute, please refer to the Hybrid attribute introduced at Using Descriptors and Hybrids for a more effective technique.
- name¶ – the name of the existing mapped property. This can refer to the string name of any
The “operators” used by the SQLAlchemy ORM and Core expression language
are fully customizable. For example, the comparison expression
User.name == 'ed' makes usage of an operator built into Python
operator.eq - the actual SQL construct which SQLAlchemy
associates with such an operator can be modified. New
operations can be associated with column expressions as well. The operators
which take place for column expressions are most directly redefined at the
type level - see the
section Redefining and Creating New Operators for a description.
ORM level functions like
composite() also provide for operator redefinition at the ORM
level, by passing a
PropComparator subclass to the
argument of each function. Customization of operators at this level is a
rare use case. See the documentation at
for an overview.