diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-12-17 16:44:40 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-12-17 16:44:40 -0500 |
commit | de518958c4bf4002db7ef4ffd0eaf73efaacfc25 (patch) | |
tree | 38bc21c3b4463675b6998f3ba182f4da6129fc09 | |
parent | aa0c20b804d3504df9bf60d6be3d7f3d68e680c1 (diff) | |
download | sqlalchemy-de518958c4bf4002db7ef4ffd0eaf73efaacfc25.tar.gz |
- edits, new breakoutsimprove_toc
21 files changed, 2154 insertions, 2117 deletions
diff --git a/doc/build/core/api_basics.rst b/doc/build/core/api_basics.rst index 4e77d81c1..e56a1117b 100644 --- a/doc/build/core/api_basics.rst +++ b/doc/build/core/api_basics.rst @@ -5,7 +5,7 @@ Core API Basics .. toctree:: :maxdepth: 2 - events + event inspection interfaces exceptions diff --git a/doc/build/core/engines_connections.rst b/doc/build/core/engines_connections.rst index 21d03b472..f163a7629 100644 --- a/doc/build/core/engines_connections.rst +++ b/doc/build/core/engines_connections.rst @@ -8,3 +8,4 @@ Engine and Connection Use engines connections pooling + events diff --git a/doc/build/index.rst b/doc/build/index.rst index e7192a2fe..8b60ef9b9 100644 --- a/doc/build/index.rst +++ b/doc/build/index.rst @@ -34,7 +34,7 @@ of Python objects, proceed first to the tutorial. :doc:`Relationship Configuration <orm/relationships>` | * **Configuration Extensions:** - :doc:`Declarative Extension <orm/extensions/declarative>` | + :doc:`Declarative Extension <orm/extensions/declarative/index>` | :doc:`Association Proxy <orm/extensions/associationproxy>` | :doc:`Hybrid Attributes <orm/extensions/hybrid>` | :doc:`Automap <orm/extensions/automap>` | diff --git a/doc/build/orm/composites.rst b/doc/build/orm/composites.rst new file mode 100644 index 000000000..1c42564b1 --- /dev/null +++ b/doc/build/orm/composites.rst @@ -0,0 +1,160 @@ +.. module:: sqlalchemy.orm + +.. _mapper_composite: + +Composite Column Types +======================= + +Sets of columns can be associated with a single user-defined datatype. The ORM +provides a single attribute which represents the group of columns using the +class you provide. + +.. versionchanged:: 0.7 + Composites have been simplified such that + they no longer "conceal" the underlying column based attributes. Additionally, + in-place mutation is no longer automatic; see the section below on + enabling mutability to support tracking of in-place changes. + +.. versionchanged:: 0.9 + Composites will return their object-form, rather than as individual columns, + when used in a column-oriented :class:`.Query` construct. See :ref:`migration_2824`. + +A simple example represents pairs of columns as a ``Point`` object. +``Point`` represents such a pair as ``.x`` and ``.y``:: + + class Point(object): + def __init__(self, x, y): + self.x = x + self.y = y + + def __composite_values__(self): + return self.x, self.y + + def __repr__(self): + return "Point(x=%r, y=%r)" % (self.x, self.y) + + def __eq__(self, other): + return isinstance(other, Point) and \ + other.x == self.x and \ + other.y == self.y + + def __ne__(self, other): + return not self.__eq__(other) + +The requirements for the custom datatype class are that it have a constructor +which accepts positional arguments corresponding to its column format, and +also provides a method ``__composite_values__()`` which returns the state of +the object as a list or tuple, in order of its column-based attributes. It +also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test +the equality of two instances. + +We will create a mapping to a table ``vertice``, which represents two points +as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column` +objects. Then, the :func:`.composite` function is used to assign new +attributes that will represent sets of columns via the ``Point`` class:: + + from sqlalchemy import Column, Integer + from sqlalchemy.orm import composite + from sqlalchemy.ext.declarative import declarative_base + + Base = declarative_base() + + class Vertex(Base): + __tablename__ = 'vertice' + + id = Column(Integer, primary_key=True) + x1 = Column(Integer) + y1 = Column(Integer) + x2 = Column(Integer) + y2 = Column(Integer) + + start = composite(Point, x1, y1) + end = composite(Point, x2, y2) + +A classical mapping above would define each :func:`.composite` +against the existing table:: + + mapper(Vertex, vertice_table, properties={ + 'start':composite(Point, vertice_table.c.x1, vertice_table.c.y1), + 'end':composite(Point, vertice_table.c.x2, vertice_table.c.y2), + }) + +We can now persist and use ``Vertex`` instances, as well as query for them, +using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances: + +.. sourcecode:: python+sql + + >>> v = Vertex(start=Point(3, 4), end=Point(5, 6)) + >>> session.add(v) + >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4)) + {sql}>>> print q.first().start + BEGIN (implicit) + INSERT INTO vertice (x1, y1, x2, y2) VALUES (?, ?, ?, ?) + (3, 4, 5, 6) + SELECT vertice.id AS vertice_id, + vertice.x1 AS vertice_x1, + vertice.y1 AS vertice_y1, + vertice.x2 AS vertice_x2, + vertice.y2 AS vertice_y2 + FROM vertice + WHERE vertice.x1 = ? AND vertice.y1 = ? + LIMIT ? OFFSET ? + (3, 4, 1, 0) + {stop}Point(x=3, y=4) + +.. autofunction:: composite + + +Tracking In-Place Mutations on Composites +----------------------------------------- + +In-place changes to an existing composite value are +not tracked automatically. Instead, the composite class needs to provide +events to its parent object explicitly. This task is largely automated +via the usage of the :class:`.MutableComposite` mixin, which uses events +to associate each user-defined composite object with all parent associations. +Please see the example in :ref:`mutable_composites`. + +.. versionchanged:: 0.7 + In-place changes to an existing composite value are no longer + tracked automatically; the functionality is superseded by the + :class:`.MutableComposite` class. + +.. _composite_operations: + +Redefining Comparison Operations for Composites +----------------------------------------------- + +The "equals" comparison operation by default produces an AND of all +corresponding columns equated to one another. This can be changed using +the ``comparator_factory`` argument to :func:`.composite`, where we +specify a custom :class:`.CompositeProperty.Comparator` class +to define existing or new operations. +Below we illustrate the "greater than" operator, implementing +the same expression that the base "greater than" does:: + + from sqlalchemy.orm.properties import CompositeProperty + from sqlalchemy import sql + + class PointComparator(CompositeProperty.Comparator): + def __gt__(self, other): + """redefine the 'greater than' operation""" + + return sql.and_(*[a>b for a, b in + zip(self.__clause_element__().clauses, + other.__composite_values__())]) + + class Vertex(Base): + ___tablename__ = 'vertice' + + id = Column(Integer, primary_key=True) + x1 = Column(Integer) + y1 = Column(Integer) + x2 = Column(Integer) + y2 = Column(Integer) + + start = composite(Point, x1, y1, + comparator_factory=PointComparator) + end = composite(Point, x2, y2, + comparator_factory=PointComparator) + diff --git a/doc/build/orm/extensions/declarative.rst b/doc/build/orm/extensions/declarative.rst deleted file mode 100644 index 7d9e634b5..000000000 --- a/doc/build/orm/extensions/declarative.rst +++ /dev/null @@ -1,33 +0,0 @@ -.. _declarative_toplevel: - -Declarative -=========== - -.. automodule:: sqlalchemy.ext.declarative - -API Reference -------------- - -.. autofunction:: declarative_base - -.. autofunction:: as_declarative - -.. autoclass:: declared_attr - :members: - -.. autofunction:: sqlalchemy.ext.declarative.api._declarative_constructor - -.. autofunction:: has_inherited_table - -.. autofunction:: synonym_for - -.. autofunction:: comparable_using - -.. autofunction:: instrument_declarative - -.. autoclass:: AbstractConcreteBase - -.. autoclass:: ConcreteBase - -.. autoclass:: DeferredReflection - :members: diff --git a/doc/build/orm/extensions/declarative/api.rst b/doc/build/orm/extensions/declarative/api.rst new file mode 100644 index 000000000..67b66a970 --- /dev/null +++ b/doc/build/orm/extensions/declarative/api.rst @@ -0,0 +1,114 @@ +.. automodule:: sqlalchemy.ext.declarative + +=============== +Declarative API +=============== + +API Reference +============= + +.. autofunction:: declarative_base + +.. autofunction:: as_declarative + +.. autoclass:: declared_attr + :members: + +.. autofunction:: sqlalchemy.ext.declarative.api._declarative_constructor + +.. autofunction:: has_inherited_table + +.. autofunction:: synonym_for + +.. autofunction:: comparable_using + +.. autofunction:: instrument_declarative + +.. autoclass:: AbstractConcreteBase + +.. autoclass:: ConcreteBase + +.. autoclass:: DeferredReflection + :members: + + +Special Directives +------------------ + +``__declare_last__()`` +~~~~~~~~~~~~~~~~~~~~~~ + +The ``__declare_last__()`` hook allows definition of +a class level function that is automatically called by the +:meth:`.MapperEvents.after_configured` event, which occurs after mappings are +assumed to be completed and the 'configure' step has finished:: + + class MyClass(Base): + @classmethod + def __declare_last__(cls): + "" + # do something with mappings + +.. versionadded:: 0.7.3 + +``__declare_first__()`` +~~~~~~~~~~~~~~~~~~~~~~~ + +Like ``__declare_last__()``, but is called at the beginning of mapper +configuration via the :meth:`.MapperEvents.before_configured` event:: + + class MyClass(Base): + @classmethod + def __declare_first__(cls): + "" + # do something before mappings are configured + +.. versionadded:: 0.9.3 + +.. _declarative_abstract: + +``__abstract__`` +~~~~~~~~~~~~~~~~~~~ + +``__abstract__`` causes declarative to skip the production +of a table or mapper for the class entirely. A class can be added within a +hierarchy in the same way as mixin (see :ref:`declarative_mixins`), allowing +subclasses to extend just from the special class:: + + class SomeAbstractBase(Base): + __abstract__ = True + + def some_helpful_method(self): + "" + + @declared_attr + def __mapper_args__(cls): + return {"helpful mapper arguments":True} + + class MyMappedClass(SomeAbstractBase): + "" + +One possible use of ``__abstract__`` is to use a distinct +:class:`.MetaData` for different bases:: + + Base = declarative_base() + + class DefaultBase(Base): + __abstract__ = True + metadata = MetaData() + + class OtherBase(Base): + __abstract__ = True + metadata = MetaData() + +Above, classes which inherit from ``DefaultBase`` will use one +:class:`.MetaData` as the registry of tables, and those which inherit from +``OtherBase`` will use a different one. The tables themselves can then be +created perhaps within distinct databases:: + + DefaultBase.metadata.create_all(some_engine) + OtherBase.metadata_create_all(some_other_engine) + +.. versionadded:: 0.7.3 + + diff --git a/doc/build/orm/extensions/declarative/basic_use.rst b/doc/build/orm/extensions/declarative/basic_use.rst new file mode 100644 index 000000000..10b79e5a6 --- /dev/null +++ b/doc/build/orm/extensions/declarative/basic_use.rst @@ -0,0 +1,133 @@ +========= +Basic Use +========= + +SQLAlchemy object-relational configuration involves the +combination of :class:`.Table`, :func:`.mapper`, and class +objects to define a mapped class. +:mod:`~sqlalchemy.ext.declarative` allows all three to be +expressed at once within the class declaration. As much as +possible, regular SQLAlchemy schema and ORM constructs are +used directly, so that configuration between "classical" ORM +usage and declarative remain highly similar. + +As a simple example:: + + from sqlalchemy.ext.declarative import declarative_base + + Base = declarative_base() + + class SomeClass(Base): + __tablename__ = 'some_table' + id = Column(Integer, primary_key=True) + name = Column(String(50)) + +Above, the :func:`declarative_base` callable returns a new base class from +which all mapped classes should inherit. When the class definition is +completed, a new :class:`.Table` and :func:`.mapper` will have been generated. + +The resulting table and mapper are accessible via +``__table__`` and ``__mapper__`` attributes on the +``SomeClass`` class:: + + # access the mapped Table + SomeClass.__table__ + + # access the Mapper + SomeClass.__mapper__ + +Defining Attributes +=================== + +In the previous example, the :class:`.Column` objects are +automatically named with the name of the attribute to which they are +assigned. + +To name columns explicitly with a name distinct from their mapped attribute, +just give the column a name. Below, column "some_table_id" is mapped to the +"id" attribute of `SomeClass`, but in SQL will be represented as +"some_table_id":: + + class SomeClass(Base): + __tablename__ = 'some_table' + id = Column("some_table_id", Integer, primary_key=True) + +Attributes may be added to the class after its construction, and they will be +added to the underlying :class:`.Table` and +:func:`.mapper` definitions as appropriate:: + + SomeClass.data = Column('data', Unicode) + SomeClass.related = relationship(RelatedInfo) + +Classes which are constructed using declarative can interact freely +with classes that are mapped explicitly with :func:`.mapper`. + +It is recommended, though not required, that all tables +share the same underlying :class:`~sqlalchemy.schema.MetaData` object, +so that string-configured :class:`~sqlalchemy.schema.ForeignKey` +references can be resolved without issue. + +Accessing the MetaData +======================= + +The :func:`declarative_base` base class contains a +:class:`.MetaData` object where newly defined +:class:`.Table` objects are collected. This object is +intended to be accessed directly for +:class:`.MetaData`-specific operations. Such as, to issue +CREATE statements for all tables:: + + engine = create_engine('sqlite://') + Base.metadata.create_all(engine) + +:func:`declarative_base` can also receive a pre-existing +:class:`.MetaData` object, which allows a +declarative setup to be associated with an already +existing traditional collection of :class:`~sqlalchemy.schema.Table` +objects:: + + mymetadata = MetaData() + Base = declarative_base(metadata=mymetadata) + + +Class Constructor +================= + +As a convenience feature, the :func:`declarative_base` sets a default +constructor on classes which takes keyword arguments, and assigns them +to the named attributes:: + + e = Engineer(primary_language='python') + +Mapper Configuration +==================== + +Declarative makes use of the :func:`~.orm.mapper` function internally +when it creates the mapping to the declared table. The options +for :func:`~.orm.mapper` are passed directly through via the +``__mapper_args__`` class attribute. As always, arguments which reference +locally mapped columns can reference them directly from within the +class declaration:: + + from datetime import datetime + + class Widget(Base): + __tablename__ = 'widgets' + + id = Column(Integer, primary_key=True) + timestamp = Column(DateTime, nullable=False) + + __mapper_args__ = { + 'version_id_col': timestamp, + 'version_id_generator': lambda v:datetime.now() + } + + +.. _declarative_sql_expressions: + +Defining SQL Expressions +======================== + +See :ref:`mapper_sql_expressions` for examples on declaratively +mapping attributes to SQL expressions. + diff --git a/doc/build/orm/extensions/declarative/index.rst b/doc/build/orm/extensions/declarative/index.rst new file mode 100644 index 000000000..dc4f392f3 --- /dev/null +++ b/doc/build/orm/extensions/declarative/index.rst @@ -0,0 +1,32 @@ +.. _declarative_toplevel: + +=========== +Declarative +=========== + +The Declarative system is the typically used system provided by the SQLAlchemy +ORM in order to define classes mapped to relational database tables. However, +as noted in :ref:`classical_mapping`, Declarative is in fact a series of +extensions that ride on top of the SQLAlchemy :func:`.mapper` construct. + +While the documentation typically refers to Declarative for most examples, +the following sections will provide detailed information on how the +Declarative API interacts with the basic :func:`.mapper` and Core :class:`.Table` +systems, as well as how sophisticated patterns can be built using systems +such as mixins. + + +.. toctree:: + :maxdepth: 2 + + basic_use + relationships + table_config + inheritance + mixins + api + + + + + diff --git a/doc/build/orm/extensions/declarative/inheritance.rst b/doc/build/orm/extensions/declarative/inheritance.rst new file mode 100644 index 000000000..684b07bfd --- /dev/null +++ b/doc/build/orm/extensions/declarative/inheritance.rst @@ -0,0 +1,318 @@ +.. _declarative_inheritance: + +Inheritance Configuration +========================= + +Declarative supports all three forms of inheritance as intuitively +as possible. The ``inherits`` mapper keyword argument is not needed +as declarative will determine this from the class itself. The various +"polymorphic" keyword arguments are specified using ``__mapper_args__``. + +Joined Table Inheritance +~~~~~~~~~~~~~~~~~~~~~~~~ + +Joined table inheritance is defined as a subclass that defines its own +table:: + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __tablename__ = 'engineers' + __mapper_args__ = {'polymorphic_identity': 'engineer'} + id = Column(Integer, ForeignKey('people.id'), primary_key=True) + primary_language = Column(String(50)) + +Note that above, the ``Engineer.id`` attribute, since it shares the +same attribute name as the ``Person.id`` attribute, will in fact +represent the ``people.id`` and ``engineers.id`` columns together, +with the "Engineer.id" column taking precedence if queried directly. +To provide the ``Engineer`` class with an attribute that represents +only the ``engineers.id`` column, give it a different attribute name:: + + class Engineer(Person): + __tablename__ = 'engineers' + __mapper_args__ = {'polymorphic_identity': 'engineer'} + engineer_id = Column('id', Integer, ForeignKey('people.id'), + primary_key=True) + primary_language = Column(String(50)) + + +.. versionchanged:: 0.7 joined table inheritance favors the subclass + column over that of the superclass, such as querying above + for ``Engineer.id``. Prior to 0.7 this was the reverse. + +.. _declarative_single_table: + +Single Table Inheritance +~~~~~~~~~~~~~~~~~~~~~~~~ + +Single table inheritance is defined as a subclass that does not have +its own table; you just leave out the ``__table__`` and ``__tablename__`` +attributes:: + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __mapper_args__ = {'polymorphic_identity': 'engineer'} + primary_language = Column(String(50)) + +When the above mappers are configured, the ``Person`` class is mapped +to the ``people`` table *before* the ``primary_language`` column is +defined, and this column will not be included in its own mapping. +When ``Engineer`` then defines the ``primary_language`` column, the +column is added to the ``people`` table so that it is included in the +mapping for ``Engineer`` and is also part of the table's full set of +columns. Columns which are not mapped to ``Person`` are also excluded +from any other single or joined inheriting classes using the +``exclude_properties`` mapper argument. Below, ``Manager`` will have +all the attributes of ``Person`` and ``Manager`` but *not* the +``primary_language`` attribute of ``Engineer``:: + + class Manager(Person): + __mapper_args__ = {'polymorphic_identity': 'manager'} + golf_swing = Column(String(50)) + +The attribute exclusion logic is provided by the +``exclude_properties`` mapper argument, and declarative's default +behavior can be disabled by passing an explicit ``exclude_properties`` +collection (empty or otherwise) to the ``__mapper_args__``. + +Resolving Column Conflicts +^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Note above that the ``primary_language`` and ``golf_swing`` columns +are "moved up" to be applied to ``Person.__table__``, as a result of their +declaration on a subclass that has no table of its own. A tricky case +comes up when two subclasses want to specify *the same* column, as below:: + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __mapper_args__ = {'polymorphic_identity': 'engineer'} + start_date = Column(DateTime) + + class Manager(Person): + __mapper_args__ = {'polymorphic_identity': 'manager'} + start_date = Column(DateTime) + +Above, the ``start_date`` column declared on both ``Engineer`` and ``Manager`` +will result in an error:: + + sqlalchemy.exc.ArgumentError: Column 'start_date' on class + <class '__main__.Manager'> conflicts with existing + column 'people.start_date' + +In a situation like this, Declarative can't be sure +of the intent, especially if the ``start_date`` columns had, for example, +different types. A situation like this can be resolved by using +:class:`.declared_attr` to define the :class:`.Column` conditionally, taking +care to return the **existing column** via the parent ``__table__`` if it +already exists:: + + from sqlalchemy.ext.declarative import declared_attr + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __mapper_args__ = {'polymorphic_identity': 'engineer'} + + @declared_attr + def start_date(cls): + "Start date column, if not present already." + return Person.__table__.c.get('start_date', Column(DateTime)) + + class Manager(Person): + __mapper_args__ = {'polymorphic_identity': 'manager'} + + @declared_attr + def start_date(cls): + "Start date column, if not present already." + return Person.__table__.c.get('start_date', Column(DateTime)) + +Above, when ``Manager`` is mapped, the ``start_date`` column is +already present on the ``Person`` class. Declarative lets us return +that :class:`.Column` as a result in this case, where it knows to skip +re-assigning the same column. If the mapping is mis-configured such +that the ``start_date`` column is accidentally re-assigned to a +different table (such as, if we changed ``Manager`` to be joined +inheritance without fixing ``start_date``), an error is raised which +indicates an existing :class:`.Column` is trying to be re-assigned to +a different owning :class:`.Table`. + +.. versionadded:: 0.8 :class:`.declared_attr` can be used on a non-mixin + class, and the returned :class:`.Column` or other mapped attribute + will be applied to the mapping as any other attribute. Previously, + the resulting attribute would be ignored, and also result in a warning + being emitted when a subclass was created. + +.. versionadded:: 0.8 :class:`.declared_attr`, when used either with a + mixin or non-mixin declarative class, can return an existing + :class:`.Column` already assigned to the parent :class:`.Table`, + to indicate that the re-assignment of the :class:`.Column` should be + skipped, however should still be mapped on the target class, + in order to resolve duplicate column conflicts. + +The same concept can be used with mixin classes (see +:ref:`declarative_mixins`):: + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class HasStartDate(object): + @declared_attr + def start_date(cls): + return cls.__table__.c.get('start_date', Column(DateTime)) + + class Engineer(HasStartDate, Person): + __mapper_args__ = {'polymorphic_identity': 'engineer'} + + class Manager(HasStartDate, Person): + __mapper_args__ = {'polymorphic_identity': 'manager'} + +The above mixin checks the local ``__table__`` attribute for the column. +Because we're using single table inheritance, we're sure that in this case, +``cls.__table__`` refers to ``People.__table__``. If we were mixing joined- +and single-table inheritance, we might want our mixin to check more carefully +if ``cls.__table__`` is really the :class:`.Table` we're looking for. + +Concrete Table Inheritance +~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Concrete is defined as a subclass which has its own table and sets the +``concrete`` keyword argument to ``True``:: + + class Person(Base): + __tablename__ = 'people' + id = Column(Integer, primary_key=True) + name = Column(String(50)) + + class Engineer(Person): + __tablename__ = 'engineers' + __mapper_args__ = {'concrete':True} + id = Column(Integer, primary_key=True) + primary_language = Column(String(50)) + name = Column(String(50)) + +Usage of an abstract base class is a little less straightforward as it +requires usage of :func:`~sqlalchemy.orm.util.polymorphic_union`, +which needs to be created with the :class:`.Table` objects +before the class is built:: + + engineers = Table('engineers', Base.metadata, + Column('id', Integer, primary_key=True), + Column('name', String(50)), + Column('primary_language', String(50)) + ) + managers = Table('managers', Base.metadata, + Column('id', Integer, primary_key=True), + Column('name', String(50)), + Column('golf_swing', String(50)) + ) + + punion = polymorphic_union({ + 'engineer':engineers, + 'manager':managers + }, 'type', 'punion') + + class Person(Base): + __table__ = punion + __mapper_args__ = {'polymorphic_on':punion.c.type} + + class Engineer(Person): + __table__ = engineers + __mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True} + + class Manager(Person): + __table__ = managers + __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True} + +.. _declarative_concrete_helpers: + +Using the Concrete Helpers +^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Helper classes provides a simpler pattern for concrete inheritance. +With these objects, the ``__declare_first__`` helper is used to configure the +"polymorphic" loader for the mapper after all subclasses have been declared. + +.. versionadded:: 0.7.3 + +An abstract base can be declared using the +:class:`.AbstractConcreteBase` class:: + + from sqlalchemy.ext.declarative import AbstractConcreteBase + + class Employee(AbstractConcreteBase, Base): + pass + +To have a concrete ``employee`` table, use :class:`.ConcreteBase` instead:: + + from sqlalchemy.ext.declarative import ConcreteBase + + class Employee(ConcreteBase, Base): + __tablename__ = 'employee' + employee_id = Column(Integer, primary_key=True) + name = Column(String(50)) + __mapper_args__ = { + 'polymorphic_identity':'employee', + 'concrete':True} + + +Either ``Employee`` base can be used in the normal fashion:: + + class Manager(Employee): + __tablename__ = 'manager' + employee_id = Column(Integer, primary_key=True) + name = Column(String(50)) + manager_data = Column(String(40)) + __mapper_args__ = { + 'polymorphic_identity':'manager', + 'concrete':True} + + class Engineer(Employee): + __tablename__ = 'engineer' + employee_id = Column(Integer, primary_key=True) + name = Column(String(50)) + engineer_info = Column(String(40)) + __mapper_args__ = {'polymorphic_identity':'engineer', + 'concrete':True} + + +The :class:`.AbstractConcreteBase` class is itself mapped, and can be +used as a target of relationships:: + + class Company(Base): + __tablename__ = 'company' + + id = Column(Integer, primary_key=True) + employees = relationship("Employee", + primaryjoin="Company.id == Employee.company_id") + + +.. versionchanged:: 0.9.3 Support for use of :class:`.AbstractConcreteBase` + as the target of a :func:`.relationship` has been improved. + +It can also be queried directly:: + + for employee in session.query(Employee).filter(Employee.name == 'qbert'): + print(employee) + diff --git a/doc/build/orm/extensions/declarative/mixins.rst b/doc/build/orm/extensions/declarative/mixins.rst new file mode 100644 index 000000000..d64477649 --- /dev/null +++ b/doc/build/orm/extensions/declarative/mixins.rst @@ -0,0 +1,541 @@ +.. _declarative_mixins: + +Mixin and Custom Base Classes +============================== + +A common need when using :mod:`~sqlalchemy.ext.declarative` is to +share some functionality, such as a set of common columns, some common +table options, or other mapped properties, across many +classes. The standard Python idioms for this is to have the classes +inherit from a base which includes these common features. + +When using :mod:`~sqlalchemy.ext.declarative`, this idiom is allowed +via the usage of a custom declarative base class, as well as a "mixin" class +which is inherited from in addition to the primary base. Declarative +includes several helper features to make this work in terms of how +mappings are declared. An example of some commonly mixed-in +idioms is below:: + + from sqlalchemy.ext.declarative import declared_attr + + class MyMixin(object): + + @declared_attr + def __tablename__(cls): + return cls.__name__.lower() + + __table_args__ = {'mysql_engine': 'InnoDB'} + __mapper_args__= {'always_refresh': True} + + id = Column(Integer, primary_key=True) + + class MyModel(MyMixin, Base): + name = Column(String(1000)) + +Where above, the class ``MyModel`` will contain an "id" column +as the primary key, a ``__tablename__`` attribute that derives +from the name of the class itself, as well as ``__table_args__`` +and ``__mapper_args__`` defined by the ``MyMixin`` mixin class. + +There's no fixed convention over whether ``MyMixin`` precedes +``Base`` or not. Normal Python method resolution rules apply, and +the above example would work just as well with:: + + class MyModel(Base, MyMixin): + name = Column(String(1000)) + +This works because ``Base`` here doesn't define any of the +variables that ``MyMixin`` defines, i.e. ``__tablename__``, +``__table_args__``, ``id``, etc. If the ``Base`` did define +an attribute of the same name, the class placed first in the +inherits list would determine which attribute is used on the +newly defined class. + +Augmenting the Base +~~~~~~~~~~~~~~~~~~~ + +In addition to using a pure mixin, most of the techniques in this +section can also be applied to the base class itself, for patterns that +should apply to all classes derived from a particular base. This is achieved +using the ``cls`` argument of the :func:`.declarative_base` function:: + + from sqlalchemy.ext.declarative import declared_attr + + class Base(object): + @declared_attr + def __tablename__(cls): + return cls.__name__.lower() + + __table_args__ = {'mysql_engine': 'InnoDB'} + + id = Column(Integer, primary_key=True) + + from sqlalchemy.ext.declarative import declarative_base + + Base = declarative_base(cls=Base) + + class MyModel(Base): + name = Column(String(1000)) + +Where above, ``MyModel`` and all other classes that derive from ``Base`` will +have a table name derived from the class name, an ``id`` primary key column, +as well as the "InnoDB" engine for MySQL. + +Mixing in Columns +~~~~~~~~~~~~~~~~~ + +The most basic way to specify a column on a mixin is by simple +declaration:: + + class TimestampMixin(object): + created_at = Column(DateTime, default=func.now()) + + class MyModel(TimestampMixin, Base): + __tablename__ = 'test' + + id = Column(Integer, primary_key=True) + name = Column(String(1000)) + +Where above, all declarative classes that include ``TimestampMixin`` +will also have a column ``created_at`` that applies a timestamp to +all row insertions. + +Those familiar with the SQLAlchemy expression language know that +the object identity of clause elements defines their role in a schema. +Two ``Table`` objects ``a`` and ``b`` may both have a column called +``id``, but the way these are differentiated is that ``a.c.id`` +and ``b.c.id`` are two distinct Python objects, referencing their +parent tables ``a`` and ``b`` respectively. + +In the case of the mixin column, it seems that only one +:class:`.Column` object is explicitly created, yet the ultimate +``created_at`` column above must exist as a distinct Python object +for each separate destination class. To accomplish this, the declarative +extension creates a **copy** of each :class:`.Column` object encountered on +a class that is detected as a mixin. + +This copy mechanism is limited to simple columns that have no foreign +keys, as a :class:`.ForeignKey` itself contains references to columns +which can't be properly recreated at this level. For columns that +have foreign keys, as well as for the variety of mapper-level constructs +that require destination-explicit context, the +:class:`~.declared_attr` decorator is provided so that +patterns common to many classes can be defined as callables:: + + from sqlalchemy.ext.declarative import declared_attr + + class ReferenceAddressMixin(object): + @declared_attr + def address_id(cls): + return Column(Integer, ForeignKey('address.id')) + + class User(ReferenceAddressMixin, Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + +Where above, the ``address_id`` class-level callable is executed at the +point at which the ``User`` class is constructed, and the declarative +extension can use the resulting :class:`.Column` object as returned by +the method without the need to copy it. + +.. versionchanged:: > 0.6.5 + Rename 0.6.5 ``sqlalchemy.util.classproperty`` + into :class:`~.declared_attr`. + +Columns generated by :class:`~.declared_attr` can also be +referenced by ``__mapper_args__`` to a limited degree, currently +by ``polymorphic_on`` and ``version_id_col``; the declarative extension +will resolve them at class construction time:: + + class MyMixin: + @declared_attr + def type_(cls): + return Column(String(50)) + + __mapper_args__= {'polymorphic_on':type_} + + class MyModel(MyMixin, Base): + __tablename__='test' + id = Column(Integer, primary_key=True) + + +Mixing in Relationships +~~~~~~~~~~~~~~~~~~~~~~~ + +Relationships created by :func:`~sqlalchemy.orm.relationship` are provided +with declarative mixin classes exclusively using the +:class:`.declared_attr` approach, eliminating any ambiguity +which could arise when copying a relationship and its possibly column-bound +contents. Below is an example which combines a foreign key column and a +relationship so that two classes ``Foo`` and ``Bar`` can both be configured to +reference a common target class via many-to-one:: + + class RefTargetMixin(object): + @declared_attr + def target_id(cls): + return Column('target_id', ForeignKey('target.id')) + + @declared_attr + def target(cls): + return relationship("Target") + + class Foo(RefTargetMixin, Base): + __tablename__ = 'foo' + id = Column(Integer, primary_key=True) + + class Bar(RefTargetMixin, Base): + __tablename__ = 'bar' + id = Column(Integer, primary_key=True) + + class Target(Base): + __tablename__ = 'target' + id = Column(Integer, primary_key=True) + + +Using Advanced Relationship Arguments (e.g. ``primaryjoin``, etc.) +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +:func:`~sqlalchemy.orm.relationship` definitions which require explicit +primaryjoin, order_by etc. expressions should in all but the most +simplistic cases use **late bound** forms +for these arguments, meaning, using either the string form or a lambda. +The reason for this is that the related :class:`.Column` objects which are to +be configured using ``@declared_attr`` are not available to another +``@declared_attr`` attribute; while the methods will work and return new +:class:`.Column` objects, those are not the :class:`.Column` objects that +Declarative will be using as it calls the methods on its own, thus using +*different* :class:`.Column` objects. + +The canonical example is the primaryjoin condition that depends upon +another mixed-in column:: + + class RefTargetMixin(object): + @declared_attr + def target_id(cls): + return Column('target_id', ForeignKey('target.id')) + + @declared_attr + def target(cls): + return relationship(Target, + primaryjoin=Target.id==cls.target_id # this is *incorrect* + ) + +Mapping a class using the above mixin, we will get an error like:: + + sqlalchemy.exc.InvalidRequestError: this ForeignKey's parent column is not + yet associated with a Table. + +This is because the ``target_id`` :class:`.Column` we've called upon in our +``target()`` method is not the same :class:`.Column` that declarative is +actually going to map to our table. + +The condition above is resolved using a lambda:: + + class RefTargetMixin(object): + @declared_attr + def target_id(cls): + return Column('target_id', ForeignKey('target.id')) + + @declared_attr + def target(cls): + return relationship(Target, + primaryjoin=lambda: Target.id==cls.target_id + ) + +or alternatively, the string form (which ultimately generates a lambda):: + + class RefTargetMixin(object): + @declared_attr + def target_id(cls): + return Column('target_id', ForeignKey('target.id')) + + @declared_attr + def target(cls): + return relationship("Target", + primaryjoin="Target.id==%s.target_id" % cls.__name__ + ) + +Mixing in deferred(), column_property(), and other MapperProperty classes +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Like :func:`~sqlalchemy.orm.relationship`, all +:class:`~sqlalchemy.orm.interfaces.MapperProperty` subclasses such as +:func:`~sqlalchemy.orm.deferred`, :func:`~sqlalchemy.orm.column_property`, +etc. ultimately involve references to columns, and therefore, when +used with declarative mixins, have the :class:`.declared_attr` +requirement so that no reliance on copying is needed:: + + class SomethingMixin(object): + + @declared_attr + def dprop(cls): + return deferred(Column(Integer)) + + class Something(SomethingMixin, Base): + __tablename__ = "something" + +The :func:`.column_property` or other construct may refer +to other columns from the mixin. These are copied ahead of time before +the :class:`.declared_attr` is invoked:: + + class SomethingMixin(object): + x = Column(Integer) + + y = Column(Integer) + + @declared_attr + def x_plus_y(cls): + return column_property(cls.x + cls.y) + + +.. versionchanged:: 1.0.0 mixin columns are copied to the final mapped class + so that :class:`.declared_attr` methods can access the actual column + that will be mapped. + +Mixing in Association Proxy and Other Attributes +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Mixins can specify user-defined attributes as well as other extension +units such as :func:`.association_proxy`. The usage of +:class:`.declared_attr` is required in those cases where the attribute must +be tailored specifically to the target subclass. An example is when +constructing multiple :func:`.association_proxy` attributes which each +target a different type of child object. Below is an +:func:`.association_proxy` / mixin example which provides a scalar list of +string values to an implementing class:: + + from sqlalchemy import Column, Integer, ForeignKey, String + from sqlalchemy.orm import relationship + from sqlalchemy.ext.associationproxy import association_proxy + from sqlalchemy.ext.declarative import declarative_base, declared_attr + + Base = declarative_base() + + class HasStringCollection(object): + @declared_attr + def _strings(cls): + class StringAttribute(Base): + __tablename__ = cls.string_table_name + id = Column(Integer, primary_key=True) + value = Column(String(50), nullable=False) + parent_id = Column(Integer, + ForeignKey('%s.id' % cls.__tablename__), + nullable=False) + def __init__(self, value): + self.value = value + + return relationship(StringAttribute) + + @declared_attr + def strings(cls): + return association_proxy('_strings', 'value') + + class TypeA(HasStringCollection, Base): + __tablename__ = 'type_a' + string_table_name = 'type_a_strings' + id = Column(Integer(), primary_key=True) + + class TypeB(HasStringCollection, Base): + __tablename__ = 'type_b' + string_table_name = 'type_b_strings' + id = Column(Integer(), primary_key=True) + +Above, the ``HasStringCollection`` mixin produces a :func:`.relationship` +which refers to a newly generated class called ``StringAttribute``. The +``StringAttribute`` class is generated with its own :class:`.Table` +definition which is local to the parent class making usage of the +``HasStringCollection`` mixin. It also produces an :func:`.association_proxy` +object which proxies references to the ``strings`` attribute onto the ``value`` +attribute of each ``StringAttribute`` instance. + +``TypeA`` or ``TypeB`` can be instantiated given the constructor +argument ``strings``, a list of strings:: + + ta = TypeA(strings=['foo', 'bar']) + tb = TypeA(strings=['bat', 'bar']) + +This list will generate a collection +of ``StringAttribute`` objects, which are persisted into a table that's +local to either the ``type_a_strings`` or ``type_b_strings`` table:: + + >>> print ta._strings + [<__main__.StringAttribute object at 0x10151cd90>, + <__main__.StringAttribute object at 0x10151ce10>] + +When constructing the :func:`.association_proxy`, the +:class:`.declared_attr` decorator must be used so that a distinct +:func:`.association_proxy` object is created for each of the ``TypeA`` +and ``TypeB`` classes. + +.. versionadded:: 0.8 :class:`.declared_attr` is usable with non-mapped + attributes, including user-defined attributes as well as + :func:`.association_proxy`. + + +Controlling table inheritance with mixins +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +The ``__tablename__`` attribute may be used to provide a function that +will determine the name of the table used for each class in an inheritance +hierarchy, as well as whether a class has its own distinct table. + +This is achieved using the :class:`.declared_attr` indicator in conjunction +with a method named ``__tablename__()``. Declarative will always +invoke :class:`.declared_attr` for the special names +``__tablename__``, ``__mapper_args__`` and ``__table_args__`` +function **for each mapped class in the hierarchy**. The function therefore +needs to expect to receive each class individually and to provide the +correct answer for each. + +For example, to create a mixin that gives every class a simple table +name based on class name:: + + from sqlalchemy.ext.declarative import declared_attr + + class Tablename: + @declared_attr + def __tablename__(cls): + return cls.__name__.lower() + + class Person(Tablename, Base): + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __tablename__ = None + __mapper_args__ = {'polymorphic_identity': 'engineer'} + primary_language = Column(String(50)) + +Alternatively, we can modify our ``__tablename__`` function to return +``None`` for subclasses, using :func:`.has_inherited_table`. This has +the effect of those subclasses being mapped with single table inheritance +agaisnt the parent:: + + from sqlalchemy.ext.declarative import declared_attr + from sqlalchemy.ext.declarative import has_inherited_table + + class Tablename(object): + @declared_attr + def __tablename__(cls): + if has_inherited_table(cls): + return None + return cls.__name__.lower() + + class Person(Tablename, Base): + id = Column(Integer, primary_key=True) + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + primary_language = Column(String(50)) + __mapper_args__ = {'polymorphic_identity': 'engineer'} + +.. _mixin_inheritance_columns: + +Mixing in Columns in Inheritance Scenarios +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +In constrast to how ``__tablename__`` and other special names are handled when +used with :class:`.declared_attr`, when we mix in columns and properties (e.g. +relationships, column properties, etc.), the function is +invoked for the **base class only** in the hierarchy. Below, only the +``Person`` class will receive a column +called ``id``; the mapping will fail on ``Engineer``, which is not given +a primary key:: + + class HasId(object): + @declared_attr + def id(cls): + return Column('id', Integer, primary_key=True) + + class Person(HasId, Base): + __tablename__ = 'person' + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __tablename__ = 'engineer' + primary_language = Column(String(50)) + __mapper_args__ = {'polymorphic_identity': 'engineer'} + +It is usually the case in joined-table inheritance that we want distinctly +named columns on each subclass. However in this case, we may want to have +an ``id`` column on every table, and have them refer to each other via +foreign key. We can achieve this as a mixin by using the +:attr:`.declared_attr.cascading` modifier, which indicates that the +function should be invoked **for each class in the hierarchy**, just like +it does for ``__tablename__``:: + + class HasId(object): + @declared_attr.cascading + def id(cls): + if has_inherited_table(cls): + return Column('id', + Integer, + ForeignKey('person.id'), primary_key=True) + else: + return Column('id', Integer, primary_key=True) + + class Person(HasId, Base): + __tablename__ = 'person' + discriminator = Column('type', String(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + + class Engineer(Person): + __tablename__ = 'engineer' + primary_language = Column(String(50)) + __mapper_args__ = {'polymorphic_identity': 'engineer'} + + +.. versionadded:: 1.0.0 added :attr:`.declared_attr.cascading`. + +Combining Table/Mapper Arguments from Multiple Mixins +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +In the case of ``__table_args__`` or ``__mapper_args__`` +specified with declarative mixins, you may want to combine +some parameters from several mixins with those you wish to +define on the class iteself. The +:class:`.declared_attr` decorator can be used +here to create user-defined collation routines that pull +from multiple collections:: + + from sqlalchemy.ext.declarative import declared_attr + + class MySQLSettings(object): + __table_args__ = {'mysql_engine':'InnoDB'} + + class MyOtherMixin(object): + __table_args__ = {'info':'foo'} + + class MyModel(MySQLSettings, MyOtherMixin, Base): + __tablename__='my_model' + + @declared_attr + def __table_args__(cls): + args = dict() + args.update(MySQLSettings.__table_args__) + args.update(MyOtherMixin.__table_args__) + return args + + id = Column(Integer, primary_key=True) + +Creating Indexes with Mixins +~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +To define a named, potentially multicolumn :class:`.Index` that applies to all +tables derived from a mixin, use the "inline" form of :class:`.Index` and +establish it as part of ``__table_args__``:: + + class MyMixin(object): + a = Column(Integer) + b = Column(Integer) + + @declared_attr + def __table_args__(cls): + return (Index('test_idx_%s' % cls.__tablename__, 'a', 'b'),) + + class MyModel(MyMixin, Base): + __tablename__ = 'atable' + c = Column(Integer,primary_key=True) diff --git a/doc/build/orm/extensions/declarative/relationships.rst b/doc/build/orm/extensions/declarative/relationships.rst new file mode 100644 index 000000000..fb53c28bb --- /dev/null +++ b/doc/build/orm/extensions/declarative/relationships.rst @@ -0,0 +1,138 @@ +.. _declarative_configuring_relationships: + +========================= +Configuring Relationships +========================= + +Relationships to other classes are done in the usual way, with the added +feature that the class specified to :func:`~sqlalchemy.orm.relationship` +may be a string name. The "class registry" associated with ``Base`` +is used at mapper compilation time to resolve the name into the actual +class object, which is expected to have been defined once the mapper +configuration is used:: + + class User(Base): + __tablename__ = 'users' + + id = Column(Integer, primary_key=True) + name = Column(String(50)) + addresses = relationship("Address", backref="user") + + class Address(Base): + __tablename__ = 'addresses' + + id = Column(Integer, primary_key=True) + email = Column(String(50)) + user_id = Column(Integer, ForeignKey('users.id')) + +Column constructs, since they are just that, are immediately usable, +as below where we define a primary join condition on the ``Address`` +class using them:: + + class Address(Base): + __tablename__ = 'addresses' + + id = Column(Integer, primary_key=True) + email = Column(String(50)) + user_id = Column(Integer, ForeignKey('users.id')) + user = relationship(User, primaryjoin=user_id == User.id) + +In addition to the main argument for :func:`~sqlalchemy.orm.relationship`, +other arguments which depend upon the columns present on an as-yet +undefined class may also be specified as strings. These strings are +evaluated as Python expressions. The full namespace available within +this evaluation includes all classes mapped for this declarative base, +as well as the contents of the ``sqlalchemy`` package, including +expression functions like :func:`~sqlalchemy.sql.expression.desc` and +:attr:`~sqlalchemy.sql.expression.func`:: + + class User(Base): + # .... + addresses = relationship("Address", + order_by="desc(Address.email)", + primaryjoin="Address.user_id==User.id") + +For the case where more than one module contains a class of the same name, +string class names can also be specified as module-qualified paths +within any of these string expressions:: + + class User(Base): + # .... + addresses = relationship("myapp.model.address.Address", + order_by="desc(myapp.model.address.Address.email)", + primaryjoin="myapp.model.address.Address.user_id==" + "myapp.model.user.User.id") + +The qualified path can be any partial path that removes ambiguity between +the names. For example, to disambiguate between +``myapp.model.address.Address`` and ``myapp.model.lookup.Address``, +we can specify ``address.Address`` or ``lookup.Address``:: + + class User(Base): + # .... + addresses = relationship("address.Address", + order_by="desc(address.Address.email)", + primaryjoin="address.Address.user_id==" + "User.id") + +.. versionadded:: 0.8 + module-qualified paths can be used when specifying string arguments + with Declarative, in order to specify specific modules. + +Two alternatives also exist to using string-based attributes. A lambda +can also be used, which will be evaluated after all mappers have been +configured:: + + class User(Base): + # ... + addresses = relationship(lambda: Address, + order_by=lambda: desc(Address.email), + primaryjoin=lambda: Address.user_id==User.id) + +Or, the relationship can be added to the class explicitly after the classes +are available:: + + User.addresses = relationship(Address, + primaryjoin=Address.user_id==User.id) + + + +.. _declarative_many_to_many: + +Configuring Many-to-Many Relationships +====================================== + +Many-to-many relationships are also declared in the same way +with declarative as with traditional mappings. The +``secondary`` argument to +:func:`.relationship` is as usual passed a +:class:`.Table` object, which is typically declared in the +traditional way. The :class:`.Table` usually shares +the :class:`.MetaData` object used by the declarative base:: + + keywords = Table( + 'keywords', Base.metadata, + Column('author_id', Integer, ForeignKey('authors.id')), + Column('keyword_id', Integer, ForeignKey('keywords.id')) + ) + + class Author(Base): + __tablename__ = 'authors' + id = Column(Integer, primary_key=True) + keywords = relationship("Keyword", secondary=keywords) + +Like other :func:`~sqlalchemy.orm.relationship` arguments, a string is accepted +as well, passing the string name of the table as defined in the +``Base.metadata.tables`` collection:: + + class Author(Base): + __tablename__ = 'authors' + id = Column(Integer, primary_key=True) + keywords = relationship("Keyword", secondary="keywords") + +As with traditional mapping, its generally not a good idea to use +a :class:`.Table` as the "secondary" argument which is also mapped to +a class, unless the :func:`.relationship` is declared with ``viewonly=True``. +Otherwise, the unit-of-work system may attempt duplicate INSERT and +DELETE statements against the underlying table. + diff --git a/doc/build/orm/extensions/declarative/table_config.rst b/doc/build/orm/extensions/declarative/table_config.rst new file mode 100644 index 000000000..9a621e6dd --- /dev/null +++ b/doc/build/orm/extensions/declarative/table_config.rst @@ -0,0 +1,143 @@ +.. _declarative_table_args: + +=================== +Table Configuration +=================== + +Table arguments other than the name, metadata, and mapped Column +arguments are specified using the ``__table_args__`` class attribute. +This attribute accommodates both positional as well as keyword +arguments that are normally sent to the +:class:`~sqlalchemy.schema.Table` constructor. +The attribute can be specified in one of two forms. One is as a +dictionary:: + + class MyClass(Base): + __tablename__ = 'sometable' + __table_args__ = {'mysql_engine':'InnoDB'} + +The other, a tuple, where each argument is positional +(usually constraints):: + + class MyClass(Base): + __tablename__ = 'sometable' + __table_args__ = ( + ForeignKeyConstraint(['id'], ['remote_table.id']), + UniqueConstraint('foo'), + ) + +Keyword arguments can be specified with the above form by +specifying the last argument as a dictionary:: + + class MyClass(Base): + __tablename__ = 'sometable' + __table_args__ = ( + ForeignKeyConstraint(['id'], ['remote_table.id']), + UniqueConstraint('foo'), + {'autoload':True} + ) + +Using a Hybrid Approach with __table__ +======================================= + +As an alternative to ``__tablename__``, a direct +:class:`~sqlalchemy.schema.Table` construct may be used. The +:class:`~sqlalchemy.schema.Column` objects, which in this case require +their names, will be added to the mapping just like a regular mapping +to a table:: + + class MyClass(Base): + __table__ = Table('my_table', Base.metadata, + Column('id', Integer, primary_key=True), + Column('name', String(50)) + ) + +``__table__`` provides a more focused point of control for establishing +table metadata, while still getting most of the benefits of using declarative. +An application that uses reflection might want to load table metadata elsewhere +and pass it to declarative classes:: + + from sqlalchemy.ext.declarative import declarative_base + + Base = declarative_base() + Base.metadata.reflect(some_engine) + + class User(Base): + __table__ = metadata.tables['user'] + + class Address(Base): + __table__ = metadata.tables['address'] + +Some configuration schemes may find it more appropriate to use ``__table__``, +such as those which already take advantage of the data-driven nature of +:class:`.Table` to customize and/or automate schema definition. + +Note that when the ``__table__`` approach is used, the object is immediately +usable as a plain :class:`.Table` within the class declaration body itself, +as a Python class is only another syntactical block. Below this is illustrated +by using the ``id`` column in the ``primaryjoin`` condition of a +:func:`.relationship`:: + + class MyClass(Base): + __table__ = Table('my_table', Base.metadata, + Column('id', Integer, primary_key=True), + Column('name', String(50)) + ) + + widgets = relationship(Widget, + primaryjoin=Widget.myclass_id==__table__.c.id) + +Similarly, mapped attributes which refer to ``__table__`` can be placed inline, +as below where we assign the ``name`` column to the attribute ``_name``, +generating a synonym for ``name``:: + + from sqlalchemy.ext.declarative import synonym_for + + class MyClass(Base): + __table__ = Table('my_table', Base.metadata, + Column('id', Integer, primary_key=True), + Column('name', String(50)) + ) + + _name = __table__.c.name + + @synonym_for("_name") + def name(self): + return "Name: %s" % _name + +Using Reflection with Declarative +================================= + +It's easy to set up a :class:`.Table` that uses ``autoload=True`` +in conjunction with a mapped class:: + + class MyClass(Base): + __table__ = Table('mytable', Base.metadata, + autoload=True, autoload_with=some_engine) + +However, one improvement that can be made here is to not +require the :class:`.Engine` to be available when classes are +being first declared. To achieve this, use the +:class:`.DeferredReflection` mixin, which sets up mappings +only after a special ``prepare(engine)`` step is called:: + + from sqlalchemy.ext.declarative import declarative_base, DeferredReflection + + Base = declarative_base(cls=DeferredReflection) + + class Foo(Base): + __tablename__ = 'foo' + bars = relationship("Bar") + + class Bar(Base): + __tablename__ = 'bar' + + # illustrate overriding of "bar.foo_id" to have + # a foreign key constraint otherwise not + # reflected, such as when using MySQL + foo_id = Column(Integer, ForeignKey('foo.id')) + + Base.prepare(e) + +.. versionadded:: 0.8 + Added :class:`.DeferredReflection`. diff --git a/doc/build/orm/extensions/index.rst b/doc/build/orm/extensions/index.rst index 65836f13a..f7f58e381 100644 --- a/doc/build/orm/extensions/index.rst +++ b/doc/build/orm/extensions/index.rst @@ -17,7 +17,7 @@ behavior. In particular the "Horizontal Sharding", "Hybrid Attributes", and associationproxy automap - declarative + declarative/index mutable orderinglist horizontal_shard diff --git a/doc/build/orm/mapped_attributes.rst b/doc/build/orm/mapped_attributes.rst new file mode 100644 index 000000000..2e7e9b3eb --- /dev/null +++ b/doc/build/orm/mapped_attributes.rst @@ -0,0 +1,340 @@ +.. module:: sqlalchemy.orm + +Changing Attribute Behavior +============================ + +.. _simple_validators: + +Simple Validators +----------------- + +A quick way to add a "validation" routine to an attribute is to use the +:func:`~sqlalchemy.orm.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 + +.. versionchanged:: 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, :func:`.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``, +the ``validate_address()`` function would *not* be emitted, even though an append +occurs to ``some_user.addresses`` - the event is caused by a backref. + +Note that the :func:`~.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, +described at :class:`~.AttributeEvents`. + +.. autofunction:: validates + +.. _mapper_hybrids: + +Using Descriptors and Hybrids +----------------------------- + +A more comprehensive way to produce modified behavior for an attribute is to +use :term:`descriptors`. These are commonly used in Python using the ``property()`` +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`` +descriptor and into the ``_email`` mapped attribute, the class level +``EmailAddress.email`` attribute does not have the usual expression semantics +usable with :class:`.Query`. To provide these, we instead use the +:mod:`~sqlalchemy.ext.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 + +The ``.email`` attribute, in addition to providing getter/setter behavior when we have an +instance of ``EmailAddress``, also provides a SQL expression when used at the class level, +that is, from the ``EmailAddress`` class directly: + +.. sourcecode:: python+sql + + from sqlalchemy.orm import Session + session = Session() + + {sql}address = session.query(EmailAddress).\ + filter(EmailAddress.email == 'address@example.com').\ + one() + SELECT address.email AS address_email, address.id AS address_id + FROM address + WHERE address.email = ? + ('address@example.com',) + {stop} + + address.email = 'otheraddress@example.com' + {sql}session.commit() + UPDATE address SET email=? WHERE address.id = ? + ('otheraddress@example.com', 1) + COMMIT + {stop} + +The :class:`~.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 +:meth:`.hybrid_property.expression` modifier. Such as, if we wanted to add a +host name automatically, we might define two sets of string manipulation +logic:: + + 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 ``email`` property of an instance of ``EmailAddress`` +will return the value of the ``_email`` attribute, removing or adding the +hostname ``@example.com`` from the value. When we query against the ``email`` +attribute, a SQL function is rendered which produces the same effect: + +.. sourcecode:: python+sql + + {sql}address = 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') + {stop} + +Read more about Hybrids at :ref:`hybrids_toplevel`. + +.. _synonyms: + +Synonyms +-------- + +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, ``.job_status`` and +``.status`` that will behave as one attribute, both at the expression +level:: + + >>> 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') + +The :func:`.synonym` can be used for any kind of mapped attribute that +subclasses :class:`.MapperProperty`, including mapped columns and relationships, +as well as synonyms themselves. + +Beyond a simple mirror, :func:`.synonym` can also be made to reference +a user-defined :term:`descriptor`. We can supply our +``status`` synonym with a ``@property``:: + + 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 +using the :func:`.synonym_for` decorator:: + + 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 + +While the :func:`.synonym` is useful for simple mirroring, the use case +of augmenting attribute behavior with descriptors is better handled in modern +usage using the :ref:`hybrid attribute <mapper_hybrids>` feature, which +is more oriented towards Python descriptors. Technically, a :func:`.synonym` +can do everything that a :class:`.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. + +.. autofunction:: synonym + +.. _custom_comparators: + +Operator Customization +---------------------- + +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 +itself called ``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 :ref:`types_operators` for a description. + +ORM level functions like :func:`.column_property`, :func:`.relationship`, +and :func:`.composite` also provide for operator redefinition at the ORM +level, by passing a :class:`.PropComparator` subclass to the ``comparator_factory`` +argument of each function. Customization of operators at this level is a +rare use case. See the documentation at :class:`.PropComparator` +for an overview. + diff --git a/doc/build/orm/mapped_sql_expr.rst b/doc/build/orm/mapped_sql_expr.rst new file mode 100644 index 000000000..1ae5b1285 --- /dev/null +++ b/doc/build/orm/mapped_sql_expr.rst @@ -0,0 +1,208 @@ +.. module:: sqlalchemy.orm + +.. _mapper_sql_expressions: + +SQL Expressions as Mapped Attributes +===================================== + +Attributes on a mapped class can be linked to SQL expressions, which can +be used in queries. + +Using a Hybrid +-------------- + +The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called +"hybrid attribute", +described in the section :ref:`hybrids_toplevel`. The hybrid provides +for an expression that works at both the Python level as well as at the +SQL expression level. For example, below we map a class ``User``, +containing attributes ``firstname`` and ``lastname``, and include a hybrid that +will provide for us the ``fullname``, which is the string concatenation of the two:: + + from sqlalchemy.ext.hybrid import hybrid_property + + class User(Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + firstname = Column(String(50)) + lastname = Column(String(50)) + + @hybrid_property + def fullname(self): + return self.firstname + " " + self.lastname + +Above, the ``fullname`` attribute is interpreted at both the instance and +class level, so that it is available from an instance:: + + some_user = session.query(User).first() + print some_user.fullname + +as well as usable wtihin queries:: + + some_user = session.query(User).filter(User.fullname == "John Smith").first() + +The string concatenation example is a simple one, where the Python expression +can be dual purposed at the instance and class level. Often, the SQL expression +must be distinguished from the Python expression, which can be achieved using +:meth:`.hybrid_property.expression`. Below we illustrate the case where a conditional +needs to be present inside the hybrid, using the ``if`` statement in Python and the +:func:`.sql.expression.case` construct for SQL expressions:: + + from sqlalchemy.ext.hybrid import hybrid_property + from sqlalchemy.sql import case + + class User(Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + firstname = Column(String(50)) + lastname = Column(String(50)) + + @hybrid_property + def fullname(self): + if self.firstname is not None: + return self.firstname + " " + self.lastname + else: + return self.lastname + + @fullname.expression + def fullname(cls): + return case([ + (cls.firstname != None, cls.firstname + " " + cls.lastname), + ], else_ = cls.lastname) + +.. _mapper_column_property_sql_expressions: + +Using column_property +--------------------- + +The :func:`.orm.column_property` function can be used to map a SQL +expression in a manner similar to a regularly mapped :class:`.Column`. +With this technique, the attribute is loaded +along with all other column-mapped attributes at load time. This is in some +cases an advantage over the usage of hybrids, as the value can be loaded +up front at the same time as the parent row of the object, particularly if +the expression is one which links to other tables (typically as a correlated +subquery) to access data that wouldn't normally be +available on an already loaded object. + +Disadvantages to using :func:`.orm.column_property` for SQL expressions include that +the expression must be compatible with the SELECT statement emitted for the class +as a whole, and there are also some configurational quirks which can occur +when using :func:`.orm.column_property` from declarative mixins. + +Our "fullname" example can be expressed using :func:`.orm.column_property` as +follows:: + + from sqlalchemy.orm import column_property + + class User(Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + firstname = Column(String(50)) + lastname = Column(String(50)) + fullname = column_property(firstname + " " + lastname) + +Correlated subqueries may be used as well. Below we use the :func:`.select` +construct to create a SELECT that links together the count of ``Address`` +objects available for a particular ``User``:: + + from sqlalchemy.orm import column_property + from sqlalchemy import select, func + from sqlalchemy import Column, Integer, String, ForeignKey + + from sqlalchemy.ext.declarative import declarative_base + + Base = declarative_base() + + class Address(Base): + __tablename__ = 'address' + id = Column(Integer, primary_key=True) + user_id = Column(Integer, ForeignKey('user.id')) + + class User(Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + address_count = column_property( + select([func.count(Address.id)]).\ + where(Address.user_id==id).\ + correlate_except(Address) + ) + +In the above example, we define a :func:`.select` construct like the following:: + + select([func.count(Address.id)]).\ + where(Address.user_id==id).\ + correlate_except(Address) + +The meaning of the above statement is, select the count of ``Address.id`` rows +where the ``Address.user_id`` column is equated to ``id``, which in the context +of the ``User`` class is the :class:`.Column` named ``id`` (note that ``id`` is +also the name of a Python built in function, which is not what we want to use +here - if we were outside of the ``User`` class definition, we'd use ``User.id``). + +The :meth:`.select.correlate_except` directive indicates that each element in the +FROM clause of this :func:`.select` may be omitted from the FROM list (that is, correlated +to the enclosing SELECT statement against ``User``) except for the one corresponding +to ``Address``. This isn't strictly necessary, but prevents ``Address`` from +being inadvertently omitted from the FROM list in the case of a long string +of joins between ``User`` and ``Address`` tables where SELECT statements against +``Address`` are nested. + +If import issues prevent the :func:`.column_property` from being defined +inline with the class, it can be assigned to the class after both +are configured. In Declarative this has the effect of calling :meth:`.Mapper.add_property` +to add an additional property after the fact:: + + User.address_count = column_property( + select([func.count(Address.id)]).\ + where(Address.user_id==User.id) + ) + +For many-to-many relationships, use :func:`.and_` to join the fields of the +association table to both tables in a relation, illustrated +here with a classical mapping:: + + from sqlalchemy import and_ + + mapper(Author, authors, properties={ + 'book_count': column_property( + select([func.count(books.c.id)], + and_( + book_authors.c.author_id==authors.c.id, + book_authors.c.book_id==books.c.id + ))) + }) + +Using a plain descriptor +------------------------- + +In cases where a SQL query more elaborate than what :func:`.orm.column_property` +or :class:`.hybrid_property` can provide must be emitted, a regular Python +function accessed as an attribute can be used, assuming the expression +only needs to be available on an already-loaded instance. The function +is decorated with Python's own ``@property`` decorator to mark it as a read-only +attribute. Within the function, :func:`.object_session` +is used to locate the :class:`.Session` corresponding to the current object, +which is then used to emit a query:: + + from sqlalchemy.orm import object_session + from sqlalchemy import select, func + + class User(Base): + __tablename__ = 'user' + id = Column(Integer, primary_key=True) + firstname = Column(String(50)) + lastname = Column(String(50)) + + @property + def address_count(self): + return object_session(self).\ + scalar( + select([func.count(Address.id)]).\ + where(Address.user_id==self.id) + ) + +The plain descriptor approach is useful as a last resort, but is less performant +in the usual case than both the hybrid and column property approaches, in that +it needs to emit a SQL query upon each access. + diff --git a/doc/build/orm/mapper_config.rst b/doc/build/orm/mapper_config.rst index 8439a2df2..b68c2331d 100644 --- a/doc/build/orm/mapper_config.rst +++ b/doc/build/orm/mapper_config.rst @@ -14,7 +14,7 @@ know how to construct and use rudimentary mappers and relationships. :maxdepth: 2 classical - mapping_columns + scalar_mapping inheritance nonstandard_mappings versioning diff --git a/doc/build/orm/mapping_columns.rst b/doc/build/orm/mapping_columns.rst index 0ea7b697b..b36bfd2f1 100644 --- a/doc/build/orm/mapping_columns.rst +++ b/doc/build/orm/mapping_columns.rst @@ -1,11 +1,7 @@ .. module:: sqlalchemy.orm -================= -Mapping Columns -================= - -Customizing Column Properties -============================== +Mapping Table Columns +===================== The default behavior of :func:`~.orm.mapper` is to assemble all the columns in the mapped :class:`.Table` into mapped object attributes, each of which are @@ -224,706 +220,3 @@ should be included or excluded:: functions. - -.. _mapper_sql_expressions: - -SQL Expressions as Mapped Attributes -===================================== - -Attributes on a mapped class can be linked to SQL expressions, which can -be used in queries. - -Using a Hybrid --------------- - -The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called -"hybrid attribute", -described in the section :ref:`hybrids_toplevel`. The hybrid provides -for an expression that works at both the Python level as well as at the -SQL expression level. For example, below we map a class ``User``, -containing attributes ``firstname`` and ``lastname``, and include a hybrid that -will provide for us the ``fullname``, which is the string concatenation of the two:: - - from sqlalchemy.ext.hybrid import hybrid_property - - class User(Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - firstname = Column(String(50)) - lastname = Column(String(50)) - - @hybrid_property - def fullname(self): - return self.firstname + " " + self.lastname - -Above, the ``fullname`` attribute is interpreted at both the instance and -class level, so that it is available from an instance:: - - some_user = session.query(User).first() - print some_user.fullname - -as well as usable wtihin queries:: - - some_user = session.query(User).filter(User.fullname == "John Smith").first() - -The string concatenation example is a simple one, where the Python expression -can be dual purposed at the instance and class level. Often, the SQL expression -must be distinguished from the Python expression, which can be achieved using -:meth:`.hybrid_property.expression`. Below we illustrate the case where a conditional -needs to be present inside the hybrid, using the ``if`` statement in Python and the -:func:`.sql.expression.case` construct for SQL expressions:: - - from sqlalchemy.ext.hybrid import hybrid_property - from sqlalchemy.sql import case - - class User(Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - firstname = Column(String(50)) - lastname = Column(String(50)) - - @hybrid_property - def fullname(self): - if self.firstname is not None: - return self.firstname + " " + self.lastname - else: - return self.lastname - - @fullname.expression - def fullname(cls): - return case([ - (cls.firstname != None, cls.firstname + " " + cls.lastname), - ], else_ = cls.lastname) - -.. _mapper_column_property_sql_expressions: - -Using column_property ---------------------- - -The :func:`.orm.column_property` function can be used to map a SQL -expression in a manner similar to a regularly mapped :class:`.Column`. -With this technique, the attribute is loaded -along with all other column-mapped attributes at load time. This is in some -cases an advantage over the usage of hybrids, as the value can be loaded -up front at the same time as the parent row of the object, particularly if -the expression is one which links to other tables (typically as a correlated -subquery) to access data that wouldn't normally be -available on an already loaded object. - -Disadvantages to using :func:`.orm.column_property` for SQL expressions include that -the expression must be compatible with the SELECT statement emitted for the class -as a whole, and there are also some configurational quirks which can occur -when using :func:`.orm.column_property` from declarative mixins. - -Our "fullname" example can be expressed using :func:`.orm.column_property` as -follows:: - - from sqlalchemy.orm import column_property - - class User(Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - firstname = Column(String(50)) - lastname = Column(String(50)) - fullname = column_property(firstname + " " + lastname) - -Correlated subqueries may be used as well. Below we use the :func:`.select` -construct to create a SELECT that links together the count of ``Address`` -objects available for a particular ``User``:: - - from sqlalchemy.orm import column_property - from sqlalchemy import select, func - from sqlalchemy import Column, Integer, String, ForeignKey - - from sqlalchemy.ext.declarative import declarative_base - - Base = declarative_base() - - class Address(Base): - __tablename__ = 'address' - id = Column(Integer, primary_key=True) - user_id = Column(Integer, ForeignKey('user.id')) - - class User(Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - address_count = column_property( - select([func.count(Address.id)]).\ - where(Address.user_id==id).\ - correlate_except(Address) - ) - -In the above example, we define a :func:`.select` construct like the following:: - - select([func.count(Address.id)]).\ - where(Address.user_id==id).\ - correlate_except(Address) - -The meaning of the above statement is, select the count of ``Address.id`` rows -where the ``Address.user_id`` column is equated to ``id``, which in the context -of the ``User`` class is the :class:`.Column` named ``id`` (note that ``id`` is -also the name of a Python built in function, which is not what we want to use -here - if we were outside of the ``User`` class definition, we'd use ``User.id``). - -The :meth:`.select.correlate_except` directive indicates that each element in the -FROM clause of this :func:`.select` may be omitted from the FROM list (that is, correlated -to the enclosing SELECT statement against ``User``) except for the one corresponding -to ``Address``. This isn't strictly necessary, but prevents ``Address`` from -being inadvertently omitted from the FROM list in the case of a long string -of joins between ``User`` and ``Address`` tables where SELECT statements against -``Address`` are nested. - -If import issues prevent the :func:`.column_property` from being defined -inline with the class, it can be assigned to the class after both -are configured. In Declarative this has the effect of calling :meth:`.Mapper.add_property` -to add an additional property after the fact:: - - User.address_count = column_property( - select([func.count(Address.id)]).\ - where(Address.user_id==User.id) - ) - -For many-to-many relationships, use :func:`.and_` to join the fields of the -association table to both tables in a relation, illustrated -here with a classical mapping:: - - from sqlalchemy import and_ - - mapper(Author, authors, properties={ - 'book_count': column_property( - select([func.count(books.c.id)], - and_( - book_authors.c.author_id==authors.c.id, - book_authors.c.book_id==books.c.id - ))) - }) - -Using a plain descriptor -------------------------- - -In cases where a SQL query more elaborate than what :func:`.orm.column_property` -or :class:`.hybrid_property` can provide must be emitted, a regular Python -function accessed as an attribute can be used, assuming the expression -only needs to be available on an already-loaded instance. The function -is decorated with Python's own ``@property`` decorator to mark it as a read-only -attribute. Within the function, :func:`.object_session` -is used to locate the :class:`.Session` corresponding to the current object, -which is then used to emit a query:: - - from sqlalchemy.orm import object_session - from sqlalchemy import select, func - - class User(Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - firstname = Column(String(50)) - lastname = Column(String(50)) - - @property - def address_count(self): - return object_session(self).\ - scalar( - select([func.count(Address.id)]).\ - where(Address.user_id==self.id) - ) - -The plain descriptor approach is useful as a last resort, but is less performant -in the usual case than both the hybrid and column property approaches, in that -it needs to emit a SQL query upon each access. - -Changing Attribute Behavior -============================ - -.. _simple_validators: - -Simple Validators ------------------ - -A quick way to add a "validation" routine to an attribute is to use the -:func:`~sqlalchemy.orm.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 - -.. versionchanged:: 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, :func:`.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``, -the ``validate_address()`` function would *not* be emitted, even though an append -occurs to ``some_user.addresses`` - the event is caused by a backref. - -Note that the :func:`~.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, -described at :class:`~.AttributeEvents`. - -.. autofunction:: validates - -.. _mapper_hybrids: - -Using Descriptors and Hybrids ------------------------------ - -A more comprehensive way to produce modified behavior for an attribute is to -use :term:`descriptors`. These are commonly used in Python using the ``property()`` -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`` -descriptor and into the ``_email`` mapped attribute, the class level -``EmailAddress.email`` attribute does not have the usual expression semantics -usable with :class:`.Query`. To provide these, we instead use the -:mod:`~sqlalchemy.ext.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 - -The ``.email`` attribute, in addition to providing getter/setter behavior when we have an -instance of ``EmailAddress``, also provides a SQL expression when used at the class level, -that is, from the ``EmailAddress`` class directly: - -.. sourcecode:: python+sql - - from sqlalchemy.orm import Session - session = Session() - - {sql}address = session.query(EmailAddress).\ - filter(EmailAddress.email == 'address@example.com').\ - one() - SELECT address.email AS address_email, address.id AS address_id - FROM address - WHERE address.email = ? - ('address@example.com',) - {stop} - - address.email = 'otheraddress@example.com' - {sql}session.commit() - UPDATE address SET email=? WHERE address.id = ? - ('otheraddress@example.com', 1) - COMMIT - {stop} - -The :class:`~.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 -:meth:`.hybrid_property.expression` modifier. Such as, if we wanted to add a -host name automatically, we might define two sets of string manipulation -logic:: - - 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 ``email`` property of an instance of ``EmailAddress`` -will return the value of the ``_email`` attribute, removing or adding the -hostname ``@example.com`` from the value. When we query against the ``email`` -attribute, a SQL function is rendered which produces the same effect: - -.. sourcecode:: python+sql - - {sql}address = 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') - {stop} - -Read more about Hybrids at :ref:`hybrids_toplevel`. - -.. _synonyms: - -Synonyms --------- - -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, ``.job_status`` and -``.status`` that will behave as one attribute, both at the expression -level:: - - >>> 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') - -The :func:`.synonym` can be used for any kind of mapped attribute that -subclasses :class:`.MapperProperty`, including mapped columns and relationships, -as well as synonyms themselves. - -Beyond a simple mirror, :func:`.synonym` can also be made to reference -a user-defined :term:`descriptor`. We can supply our -``status`` synonym with a ``@property``:: - - 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 -using the :func:`.synonym_for` decorator:: - - 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 - -While the :func:`.synonym` is useful for simple mirroring, the use case -of augmenting attribute behavior with descriptors is better handled in modern -usage using the :ref:`hybrid attribute <mapper_hybrids>` feature, which -is more oriented towards Python descriptors. Technically, a :func:`.synonym` -can do everything that a :class:`.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. - -.. autofunction:: synonym - -.. _custom_comparators: - -Operator Customization ----------------------- - -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 -itself called ``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 :ref:`types_operators` for a description. - -ORM level functions like :func:`.column_property`, :func:`.relationship`, -and :func:`.composite` also provide for operator redefinition at the ORM -level, by passing a :class:`.PropComparator` subclass to the ``comparator_factory`` -argument of each function. Customization of operators at this level is a -rare use case. See the documentation at :class:`.PropComparator` -for an overview. - -.. _mapper_composite: - -Composite Column Types -======================= - -Sets of columns can be associated with a single user-defined datatype. The ORM -provides a single attribute which represents the group of columns using the -class you provide. - -.. versionchanged:: 0.7 - Composites have been simplified such that - they no longer "conceal" the underlying column based attributes. Additionally, - in-place mutation is no longer automatic; see the section below on - enabling mutability to support tracking of in-place changes. - -.. versionchanged:: 0.9 - Composites will return their object-form, rather than as individual columns, - when used in a column-oriented :class:`.Query` construct. See :ref:`migration_2824`. - -A simple example represents pairs of columns as a ``Point`` object. -``Point`` represents such a pair as ``.x`` and ``.y``:: - - class Point(object): - def __init__(self, x, y): - self.x = x - self.y = y - - def __composite_values__(self): - return self.x, self.y - - def __repr__(self): - return "Point(x=%r, y=%r)" % (self.x, self.y) - - def __eq__(self, other): - return isinstance(other, Point) and \ - other.x == self.x and \ - other.y == self.y - - def __ne__(self, other): - return not self.__eq__(other) - -The requirements for the custom datatype class are that it have a constructor -which accepts positional arguments corresponding to its column format, and -also provides a method ``__composite_values__()`` which returns the state of -the object as a list or tuple, in order of its column-based attributes. It -also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test -the equality of two instances. - -We will create a mapping to a table ``vertice``, which represents two points -as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column` -objects. Then, the :func:`.composite` function is used to assign new -attributes that will represent sets of columns via the ``Point`` class:: - - from sqlalchemy import Column, Integer - from sqlalchemy.orm import composite - from sqlalchemy.ext.declarative import declarative_base - - Base = declarative_base() - - class Vertex(Base): - __tablename__ = 'vertice' - - id = Column(Integer, primary_key=True) - x1 = Column(Integer) - y1 = Column(Integer) - x2 = Column(Integer) - y2 = Column(Integer) - - start = composite(Point, x1, y1) - end = composite(Point, x2, y2) - -A classical mapping above would define each :func:`.composite` -against the existing table:: - - mapper(Vertex, vertice_table, properties={ - 'start':composite(Point, vertice_table.c.x1, vertice_table.c.y1), - 'end':composite(Point, vertice_table.c.x2, vertice_table.c.y2), - }) - -We can now persist and use ``Vertex`` instances, as well as query for them, -using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances: - -.. sourcecode:: python+sql - - >>> v = Vertex(start=Point(3, 4), end=Point(5, 6)) - >>> session.add(v) - >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4)) - {sql}>>> print q.first().start - BEGIN (implicit) - INSERT INTO vertice (x1, y1, x2, y2) VALUES (?, ?, ?, ?) - (3, 4, 5, 6) - SELECT vertice.id AS vertice_id, - vertice.x1 AS vertice_x1, - vertice.y1 AS vertice_y1, - vertice.x2 AS vertice_x2, - vertice.y2 AS vertice_y2 - FROM vertice - WHERE vertice.x1 = ? AND vertice.y1 = ? - LIMIT ? OFFSET ? - (3, 4, 1, 0) - {stop}Point(x=3, y=4) - -.. autofunction:: composite - - -Tracking In-Place Mutations on Composites ------------------------------------------ - -In-place changes to an existing composite value are -not tracked automatically. Instead, the composite class needs to provide -events to its parent object explicitly. This task is largely automated -via the usage of the :class:`.MutableComposite` mixin, which uses events -to associate each user-defined composite object with all parent associations. -Please see the example in :ref:`mutable_composites`. - -.. versionchanged:: 0.7 - In-place changes to an existing composite value are no longer - tracked automatically; the functionality is superseded by the - :class:`.MutableComposite` class. - -.. _composite_operations: - -Redefining Comparison Operations for Composites ------------------------------------------------ - -The "equals" comparison operation by default produces an AND of all -corresponding columns equated to one another. This can be changed using -the ``comparator_factory`` argument to :func:`.composite`, where we -specify a custom :class:`.CompositeProperty.Comparator` class -to define existing or new operations. -Below we illustrate the "greater than" operator, implementing -the same expression that the base "greater than" does:: - - from sqlalchemy.orm.properties import CompositeProperty - from sqlalchemy import sql - - class PointComparator(CompositeProperty.Comparator): - def __gt__(self, other): - """redefine the 'greater than' operation""" - - return sql.and_(*[a>b for a, b in - zip(self.__clause_element__().clauses, - other.__composite_values__())]) - - class Vertex(Base): - ___tablename__ = 'vertice' - - id = Column(Integer, primary_key=True) - x1 = Column(Integer) - y1 = Column(Integer) - x2 = Column(Integer) - y2 = Column(Integer) - - start = composite(Point, x1, y1, - comparator_factory=PointComparator) - end = composite(Point, x2, y2, - comparator_factory=PointComparator) - diff --git a/doc/build/orm/relationship_api.rst b/doc/build/orm/relationship_api.rst index 7a517a9dd..03045f698 100644 --- a/doc/build/orm/relationship_api.rst +++ b/doc/build/orm/relationship_api.rst @@ -1,3 +1,4 @@ +.. automodule:: sqlalchemy.orm Relationships API ----------------- diff --git a/doc/build/orm/relationships.rst b/doc/build/orm/relationships.rst index 1012ef8ae..6fea107a7 100644 --- a/doc/build/orm/relationships.rst +++ b/doc/build/orm/relationships.rst @@ -19,4 +19,5 @@ of collections via :func:`relationship`. join_conditions collections relationship_persistence + relationship_api diff --git a/doc/build/orm/scalar_mapping.rst b/doc/build/orm/scalar_mapping.rst new file mode 100644 index 000000000..65efd5dbd --- /dev/null +++ b/doc/build/orm/scalar_mapping.rst @@ -0,0 +1,18 @@ +.. module:: sqlalchemy.orm + +=============================== +Mapping Columns and Expressions +=============================== + +The following sections discuss how table columns and SQL expressions are +mapped to individual object attributes. + +.. toctree:: + :maxdepth: 2 + + mapping_columns + mapped_sql_expr + mapped_attributes + composites + + diff --git a/lib/sqlalchemy/ext/declarative/__init__.py b/lib/sqlalchemy/ext/declarative/__init__.py index 2b611252a..cbde6f9d2 100644 --- a/lib/sqlalchemy/ext/declarative/__init__.py +++ b/lib/sqlalchemy/ext/declarative/__init__.py @@ -5,1377 +5,6 @@ # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -""" -Synopsis -======== - -SQLAlchemy object-relational configuration involves the -combination of :class:`.Table`, :func:`.mapper`, and class -objects to define a mapped class. -:mod:`~sqlalchemy.ext.declarative` allows all three to be -expressed at once within the class declaration. As much as -possible, regular SQLAlchemy schema and ORM constructs are -used directly, so that configuration between "classical" ORM -usage and declarative remain highly similar. - -As a simple example:: - - from sqlalchemy.ext.declarative import declarative_base - - Base = declarative_base() - - class SomeClass(Base): - __tablename__ = 'some_table' - id = Column(Integer, primary_key=True) - name = Column(String(50)) - -Above, the :func:`declarative_base` callable returns a new base class from -which all mapped classes should inherit. When the class definition is -completed, a new :class:`.Table` and :func:`.mapper` will have been generated. - -The resulting table and mapper are accessible via -``__table__`` and ``__mapper__`` attributes on the -``SomeClass`` class:: - - # access the mapped Table - SomeClass.__table__ - - # access the Mapper - SomeClass.__mapper__ - -Defining Attributes -=================== - -In the previous example, the :class:`.Column` objects are -automatically named with the name of the attribute to which they are -assigned. - -To name columns explicitly with a name distinct from their mapped attribute, -just give the column a name. Below, column "some_table_id" is mapped to the -"id" attribute of `SomeClass`, but in SQL will be represented as -"some_table_id":: - - class SomeClass(Base): - __tablename__ = 'some_table' - id = Column("some_table_id", Integer, primary_key=True) - -Attributes may be added to the class after its construction, and they will be -added to the underlying :class:`.Table` and -:func:`.mapper` definitions as appropriate:: - - SomeClass.data = Column('data', Unicode) - SomeClass.related = relationship(RelatedInfo) - -Classes which are constructed using declarative can interact freely -with classes that are mapped explicitly with :func:`.mapper`. - -It is recommended, though not required, that all tables -share the same underlying :class:`~sqlalchemy.schema.MetaData` object, -so that string-configured :class:`~sqlalchemy.schema.ForeignKey` -references can be resolved without issue. - -Accessing the MetaData -======================= - -The :func:`declarative_base` base class contains a -:class:`.MetaData` object where newly defined -:class:`.Table` objects are collected. This object is -intended to be accessed directly for -:class:`.MetaData`-specific operations. Such as, to issue -CREATE statements for all tables:: - - engine = create_engine('sqlite://') - Base.metadata.create_all(engine) - -:func:`declarative_base` can also receive a pre-existing -:class:`.MetaData` object, which allows a -declarative setup to be associated with an already -existing traditional collection of :class:`~sqlalchemy.schema.Table` -objects:: - - mymetadata = MetaData() - Base = declarative_base(metadata=mymetadata) - - -.. _declarative_configuring_relationships: - -Configuring Relationships -========================= - -Relationships to other classes are done in the usual way, with the added -feature that the class specified to :func:`~sqlalchemy.orm.relationship` -may be a string name. The "class registry" associated with ``Base`` -is used at mapper compilation time to resolve the name into the actual -class object, which is expected to have been defined once the mapper -configuration is used:: - - class User(Base): - __tablename__ = 'users' - - id = Column(Integer, primary_key=True) - name = Column(String(50)) - addresses = relationship("Address", backref="user") - - class Address(Base): - __tablename__ = 'addresses' - - id = Column(Integer, primary_key=True) - email = Column(String(50)) - user_id = Column(Integer, ForeignKey('users.id')) - -Column constructs, since they are just that, are immediately usable, -as below where we define a primary join condition on the ``Address`` -class using them:: - - class Address(Base): - __tablename__ = 'addresses' - - id = Column(Integer, primary_key=True) - email = Column(String(50)) - user_id = Column(Integer, ForeignKey('users.id')) - user = relationship(User, primaryjoin=user_id == User.id) - -In addition to the main argument for :func:`~sqlalchemy.orm.relationship`, -other arguments which depend upon the columns present on an as-yet -undefined class may also be specified as strings. These strings are -evaluated as Python expressions. The full namespace available within -this evaluation includes all classes mapped for this declarative base, -as well as the contents of the ``sqlalchemy`` package, including -expression functions like :func:`~sqlalchemy.sql.expression.desc` and -:attr:`~sqlalchemy.sql.expression.func`:: - - class User(Base): - # .... - addresses = relationship("Address", - order_by="desc(Address.email)", - primaryjoin="Address.user_id==User.id") - -For the case where more than one module contains a class of the same name, -string class names can also be specified as module-qualified paths -within any of these string expressions:: - - class User(Base): - # .... - addresses = relationship("myapp.model.address.Address", - order_by="desc(myapp.model.address.Address.email)", - primaryjoin="myapp.model.address.Address.user_id==" - "myapp.model.user.User.id") - -The qualified path can be any partial path that removes ambiguity between -the names. For example, to disambiguate between -``myapp.model.address.Address`` and ``myapp.model.lookup.Address``, -we can specify ``address.Address`` or ``lookup.Address``:: - - class User(Base): - # .... - addresses = relationship("address.Address", - order_by="desc(address.Address.email)", - primaryjoin="address.Address.user_id==" - "User.id") - -.. versionadded:: 0.8 - module-qualified paths can be used when specifying string arguments - with Declarative, in order to specify specific modules. - -Two alternatives also exist to using string-based attributes. A lambda -can also be used, which will be evaluated after all mappers have been -configured:: - - class User(Base): - # ... - addresses = relationship(lambda: Address, - order_by=lambda: desc(Address.email), - primaryjoin=lambda: Address.user_id==User.id) - -Or, the relationship can be added to the class explicitly after the classes -are available:: - - User.addresses = relationship(Address, - primaryjoin=Address.user_id==User.id) - - - -.. _declarative_many_to_many: - -Configuring Many-to-Many Relationships -====================================== - -Many-to-many relationships are also declared in the same way -with declarative as with traditional mappings. The -``secondary`` argument to -:func:`.relationship` is as usual passed a -:class:`.Table` object, which is typically declared in the -traditional way. The :class:`.Table` usually shares -the :class:`.MetaData` object used by the declarative base:: - - keywords = Table( - 'keywords', Base.metadata, - Column('author_id', Integer, ForeignKey('authors.id')), - Column('keyword_id', Integer, ForeignKey('keywords.id')) - ) - - class Author(Base): - __tablename__ = 'authors' - id = Column(Integer, primary_key=True) - keywords = relationship("Keyword", secondary=keywords) - -Like other :func:`~sqlalchemy.orm.relationship` arguments, a string is accepted -as well, passing the string name of the table as defined in the -``Base.metadata.tables`` collection:: - - class Author(Base): - __tablename__ = 'authors' - id = Column(Integer, primary_key=True) - keywords = relationship("Keyword", secondary="keywords") - -As with traditional mapping, its generally not a good idea to use -a :class:`.Table` as the "secondary" argument which is also mapped to -a class, unless the :func:`.relationship` is declared with ``viewonly=True``. -Otherwise, the unit-of-work system may attempt duplicate INSERT and -DELETE statements against the underlying table. - -.. _declarative_sql_expressions: - -Defining SQL Expressions -======================== - -See :ref:`mapper_sql_expressions` for examples on declaratively -mapping attributes to SQL expressions. - -.. _declarative_table_args: - -Table Configuration -=================== - -Table arguments other than the name, metadata, and mapped Column -arguments are specified using the ``__table_args__`` class attribute. -This attribute accommodates both positional as well as keyword -arguments that are normally sent to the -:class:`~sqlalchemy.schema.Table` constructor. -The attribute can be specified in one of two forms. One is as a -dictionary:: - - class MyClass(Base): - __tablename__ = 'sometable' - __table_args__ = {'mysql_engine':'InnoDB'} - -The other, a tuple, where each argument is positional -(usually constraints):: - - class MyClass(Base): - __tablename__ = 'sometable' - __table_args__ = ( - ForeignKeyConstraint(['id'], ['remote_table.id']), - UniqueConstraint('foo'), - ) - -Keyword arguments can be specified with the above form by -specifying the last argument as a dictionary:: - - class MyClass(Base): - __tablename__ = 'sometable' - __table_args__ = ( - ForeignKeyConstraint(['id'], ['remote_table.id']), - UniqueConstraint('foo'), - {'autoload':True} - ) - -Using a Hybrid Approach with __table__ -======================================= - -As an alternative to ``__tablename__``, a direct -:class:`~sqlalchemy.schema.Table` construct may be used. The -:class:`~sqlalchemy.schema.Column` objects, which in this case require -their names, will be added to the mapping just like a regular mapping -to a table:: - - class MyClass(Base): - __table__ = Table('my_table', Base.metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50)) - ) - -``__table__`` provides a more focused point of control for establishing -table metadata, while still getting most of the benefits of using declarative. -An application that uses reflection might want to load table metadata elsewhere -and pass it to declarative classes:: - - from sqlalchemy.ext.declarative import declarative_base - - Base = declarative_base() - Base.metadata.reflect(some_engine) - - class User(Base): - __table__ = metadata.tables['user'] - - class Address(Base): - __table__ = metadata.tables['address'] - -Some configuration schemes may find it more appropriate to use ``__table__``, -such as those which already take advantage of the data-driven nature of -:class:`.Table` to customize and/or automate schema definition. - -Note that when the ``__table__`` approach is used, the object is immediately -usable as a plain :class:`.Table` within the class declaration body itself, -as a Python class is only another syntactical block. Below this is illustrated -by using the ``id`` column in the ``primaryjoin`` condition of a -:func:`.relationship`:: - - class MyClass(Base): - __table__ = Table('my_table', Base.metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50)) - ) - - widgets = relationship(Widget, - primaryjoin=Widget.myclass_id==__table__.c.id) - -Similarly, mapped attributes which refer to ``__table__`` can be placed inline, -as below where we assign the ``name`` column to the attribute ``_name``, -generating a synonym for ``name``:: - - from sqlalchemy.ext.declarative import synonym_for - - class MyClass(Base): - __table__ = Table('my_table', Base.metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50)) - ) - - _name = __table__.c.name - - @synonym_for("_name") - def name(self): - return "Name: %s" % _name - -Using Reflection with Declarative -================================= - -It's easy to set up a :class:`.Table` that uses ``autoload=True`` -in conjunction with a mapped class:: - - class MyClass(Base): - __table__ = Table('mytable', Base.metadata, - autoload=True, autoload_with=some_engine) - -However, one improvement that can be made here is to not -require the :class:`.Engine` to be available when classes are -being first declared. To achieve this, use the -:class:`.DeferredReflection` mixin, which sets up mappings -only after a special ``prepare(engine)`` step is called:: - - from sqlalchemy.ext.declarative import declarative_base, DeferredReflection - - Base = declarative_base(cls=DeferredReflection) - - class Foo(Base): - __tablename__ = 'foo' - bars = relationship("Bar") - - class Bar(Base): - __tablename__ = 'bar' - - # illustrate overriding of "bar.foo_id" to have - # a foreign key constraint otherwise not - # reflected, such as when using MySQL - foo_id = Column(Integer, ForeignKey('foo.id')) - - Base.prepare(e) - -.. versionadded:: 0.8 - Added :class:`.DeferredReflection`. - -Mapper Configuration -==================== - -Declarative makes use of the :func:`~.orm.mapper` function internally -when it creates the mapping to the declared table. The options -for :func:`~.orm.mapper` are passed directly through via the -``__mapper_args__`` class attribute. As always, arguments which reference -locally mapped columns can reference them directly from within the -class declaration:: - - from datetime import datetime - - class Widget(Base): - __tablename__ = 'widgets' - - id = Column(Integer, primary_key=True) - timestamp = Column(DateTime, nullable=False) - - __mapper_args__ = { - 'version_id_col': timestamp, - 'version_id_generator': lambda v:datetime.now() - } - -.. _declarative_inheritance: - -Inheritance Configuration -========================= - -Declarative supports all three forms of inheritance as intuitively -as possible. The ``inherits`` mapper keyword argument is not needed -as declarative will determine this from the class itself. The various -"polymorphic" keyword arguments are specified using ``__mapper_args__``. - -Joined Table Inheritance -~~~~~~~~~~~~~~~~~~~~~~~~ - -Joined table inheritance is defined as a subclass that defines its own -table:: - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __tablename__ = 'engineers' - __mapper_args__ = {'polymorphic_identity': 'engineer'} - id = Column(Integer, ForeignKey('people.id'), primary_key=True) - primary_language = Column(String(50)) - -Note that above, the ``Engineer.id`` attribute, since it shares the -same attribute name as the ``Person.id`` attribute, will in fact -represent the ``people.id`` and ``engineers.id`` columns together, -with the "Engineer.id" column taking precedence if queried directly. -To provide the ``Engineer`` class with an attribute that represents -only the ``engineers.id`` column, give it a different attribute name:: - - class Engineer(Person): - __tablename__ = 'engineers' - __mapper_args__ = {'polymorphic_identity': 'engineer'} - engineer_id = Column('id', Integer, ForeignKey('people.id'), - primary_key=True) - primary_language = Column(String(50)) - - -.. versionchanged:: 0.7 joined table inheritance favors the subclass - column over that of the superclass, such as querying above - for ``Engineer.id``. Prior to 0.7 this was the reverse. - -.. _declarative_single_table: - -Single Table Inheritance -~~~~~~~~~~~~~~~~~~~~~~~~ - -Single table inheritance is defined as a subclass that does not have -its own table; you just leave out the ``__table__`` and ``__tablename__`` -attributes:: - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __mapper_args__ = {'polymorphic_identity': 'engineer'} - primary_language = Column(String(50)) - -When the above mappers are configured, the ``Person`` class is mapped -to the ``people`` table *before* the ``primary_language`` column is -defined, and this column will not be included in its own mapping. -When ``Engineer`` then defines the ``primary_language`` column, the -column is added to the ``people`` table so that it is included in the -mapping for ``Engineer`` and is also part of the table's full set of -columns. Columns which are not mapped to ``Person`` are also excluded -from any other single or joined inheriting classes using the -``exclude_properties`` mapper argument. Below, ``Manager`` will have -all the attributes of ``Person`` and ``Manager`` but *not* the -``primary_language`` attribute of ``Engineer``:: - - class Manager(Person): - __mapper_args__ = {'polymorphic_identity': 'manager'} - golf_swing = Column(String(50)) - -The attribute exclusion logic is provided by the -``exclude_properties`` mapper argument, and declarative's default -behavior can be disabled by passing an explicit ``exclude_properties`` -collection (empty or otherwise) to the ``__mapper_args__``. - -Resolving Column Conflicts -^^^^^^^^^^^^^^^^^^^^^^^^^^ - -Note above that the ``primary_language`` and ``golf_swing`` columns -are "moved up" to be applied to ``Person.__table__``, as a result of their -declaration on a subclass that has no table of its own. A tricky case -comes up when two subclasses want to specify *the same* column, as below:: - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __mapper_args__ = {'polymorphic_identity': 'engineer'} - start_date = Column(DateTime) - - class Manager(Person): - __mapper_args__ = {'polymorphic_identity': 'manager'} - start_date = Column(DateTime) - -Above, the ``start_date`` column declared on both ``Engineer`` and ``Manager`` -will result in an error:: - - sqlalchemy.exc.ArgumentError: Column 'start_date' on class - <class '__main__.Manager'> conflicts with existing - column 'people.start_date' - -In a situation like this, Declarative can't be sure -of the intent, especially if the ``start_date`` columns had, for example, -different types. A situation like this can be resolved by using -:class:`.declared_attr` to define the :class:`.Column` conditionally, taking -care to return the **existing column** via the parent ``__table__`` if it -already exists:: - - from sqlalchemy.ext.declarative import declared_attr - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __mapper_args__ = {'polymorphic_identity': 'engineer'} - - @declared_attr - def start_date(cls): - "Start date column, if not present already." - return Person.__table__.c.get('start_date', Column(DateTime)) - - class Manager(Person): - __mapper_args__ = {'polymorphic_identity': 'manager'} - - @declared_attr - def start_date(cls): - "Start date column, if not present already." - return Person.__table__.c.get('start_date', Column(DateTime)) - -Above, when ``Manager`` is mapped, the ``start_date`` column is -already present on the ``Person`` class. Declarative lets us return -that :class:`.Column` as a result in this case, where it knows to skip -re-assigning the same column. If the mapping is mis-configured such -that the ``start_date`` column is accidentally re-assigned to a -different table (such as, if we changed ``Manager`` to be joined -inheritance without fixing ``start_date``), an error is raised which -indicates an existing :class:`.Column` is trying to be re-assigned to -a different owning :class:`.Table`. - -.. versionadded:: 0.8 :class:`.declared_attr` can be used on a non-mixin - class, and the returned :class:`.Column` or other mapped attribute - will be applied to the mapping as any other attribute. Previously, - the resulting attribute would be ignored, and also result in a warning - being emitted when a subclass was created. - -.. versionadded:: 0.8 :class:`.declared_attr`, when used either with a - mixin or non-mixin declarative class, can return an existing - :class:`.Column` already assigned to the parent :class:`.Table`, - to indicate that the re-assignment of the :class:`.Column` should be - skipped, however should still be mapped on the target class, - in order to resolve duplicate column conflicts. - -The same concept can be used with mixin classes (see -:ref:`declarative_mixins`):: - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class HasStartDate(object): - @declared_attr - def start_date(cls): - return cls.__table__.c.get('start_date', Column(DateTime)) - - class Engineer(HasStartDate, Person): - __mapper_args__ = {'polymorphic_identity': 'engineer'} - - class Manager(HasStartDate, Person): - __mapper_args__ = {'polymorphic_identity': 'manager'} - -The above mixin checks the local ``__table__`` attribute for the column. -Because we're using single table inheritance, we're sure that in this case, -``cls.__table__`` refers to ``People.__table__``. If we were mixing joined- -and single-table inheritance, we might want our mixin to check more carefully -if ``cls.__table__`` is really the :class:`.Table` we're looking for. - -Concrete Table Inheritance -~~~~~~~~~~~~~~~~~~~~~~~~~~ - -Concrete is defined as a subclass which has its own table and sets the -``concrete`` keyword argument to ``True``:: - - class Person(Base): - __tablename__ = 'people' - id = Column(Integer, primary_key=True) - name = Column(String(50)) - - class Engineer(Person): - __tablename__ = 'engineers' - __mapper_args__ = {'concrete':True} - id = Column(Integer, primary_key=True) - primary_language = Column(String(50)) - name = Column(String(50)) - -Usage of an abstract base class is a little less straightforward as it -requires usage of :func:`~sqlalchemy.orm.util.polymorphic_union`, -which needs to be created with the :class:`.Table` objects -before the class is built:: - - engineers = Table('engineers', Base.metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50)), - Column('primary_language', String(50)) - ) - managers = Table('managers', Base.metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50)), - Column('golf_swing', String(50)) - ) - - punion = polymorphic_union({ - 'engineer':engineers, - 'manager':managers - }, 'type', 'punion') - - class Person(Base): - __table__ = punion - __mapper_args__ = {'polymorphic_on':punion.c.type} - - class Engineer(Person): - __table__ = engineers - __mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True} - - class Manager(Person): - __table__ = managers - __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True} - -.. _declarative_concrete_helpers: - -Using the Concrete Helpers -^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -Helper classes provides a simpler pattern for concrete inheritance. -With these objects, the ``__declare_first__`` helper is used to configure the -"polymorphic" loader for the mapper after all subclasses have been declared. - -.. versionadded:: 0.7.3 - -An abstract base can be declared using the -:class:`.AbstractConcreteBase` class:: - - from sqlalchemy.ext.declarative import AbstractConcreteBase - - class Employee(AbstractConcreteBase, Base): - pass - -To have a concrete ``employee`` table, use :class:`.ConcreteBase` instead:: - - from sqlalchemy.ext.declarative import ConcreteBase - - class Employee(ConcreteBase, Base): - __tablename__ = 'employee' - employee_id = Column(Integer, primary_key=True) - name = Column(String(50)) - __mapper_args__ = { - 'polymorphic_identity':'employee', - 'concrete':True} - - -Either ``Employee`` base can be used in the normal fashion:: - - class Manager(Employee): - __tablename__ = 'manager' - employee_id = Column(Integer, primary_key=True) - name = Column(String(50)) - manager_data = Column(String(40)) - __mapper_args__ = { - 'polymorphic_identity':'manager', - 'concrete':True} - - class Engineer(Employee): - __tablename__ = 'engineer' - employee_id = Column(Integer, primary_key=True) - name = Column(String(50)) - engineer_info = Column(String(40)) - __mapper_args__ = {'polymorphic_identity':'engineer', - 'concrete':True} - - -The :class:`.AbstractConcreteBase` class is itself mapped, and can be -used as a target of relationships:: - - class Company(Base): - __tablename__ = 'company' - - id = Column(Integer, primary_key=True) - employees = relationship("Employee", - primaryjoin="Company.id == Employee.company_id") - - -.. versionchanged:: 0.9.3 Support for use of :class:`.AbstractConcreteBase` - as the target of a :func:`.relationship` has been improved. - -It can also be queried directly:: - - for employee in session.query(Employee).filter(Employee.name == 'qbert'): - print(employee) - - -.. _declarative_mixins: - -Mixin and Custom Base Classes -============================== - -A common need when using :mod:`~sqlalchemy.ext.declarative` is to -share some functionality, such as a set of common columns, some common -table options, or other mapped properties, across many -classes. The standard Python idioms for this is to have the classes -inherit from a base which includes these common features. - -When using :mod:`~sqlalchemy.ext.declarative`, this idiom is allowed -via the usage of a custom declarative base class, as well as a "mixin" class -which is inherited from in addition to the primary base. Declarative -includes several helper features to make this work in terms of how -mappings are declared. An example of some commonly mixed-in -idioms is below:: - - from sqlalchemy.ext.declarative import declared_attr - - class MyMixin(object): - - @declared_attr - def __tablename__(cls): - return cls.__name__.lower() - - __table_args__ = {'mysql_engine': 'InnoDB'} - __mapper_args__= {'always_refresh': True} - - id = Column(Integer, primary_key=True) - - class MyModel(MyMixin, Base): - name = Column(String(1000)) - -Where above, the class ``MyModel`` will contain an "id" column -as the primary key, a ``__tablename__`` attribute that derives -from the name of the class itself, as well as ``__table_args__`` -and ``__mapper_args__`` defined by the ``MyMixin`` mixin class. - -There's no fixed convention over whether ``MyMixin`` precedes -``Base`` or not. Normal Python method resolution rules apply, and -the above example would work just as well with:: - - class MyModel(Base, MyMixin): - name = Column(String(1000)) - -This works because ``Base`` here doesn't define any of the -variables that ``MyMixin`` defines, i.e. ``__tablename__``, -``__table_args__``, ``id``, etc. If the ``Base`` did define -an attribute of the same name, the class placed first in the -inherits list would determine which attribute is used on the -newly defined class. - -Augmenting the Base -~~~~~~~~~~~~~~~~~~~ - -In addition to using a pure mixin, most of the techniques in this -section can also be applied to the base class itself, for patterns that -should apply to all classes derived from a particular base. This is achieved -using the ``cls`` argument of the :func:`.declarative_base` function:: - - from sqlalchemy.ext.declarative import declared_attr - - class Base(object): - @declared_attr - def __tablename__(cls): - return cls.__name__.lower() - - __table_args__ = {'mysql_engine': 'InnoDB'} - - id = Column(Integer, primary_key=True) - - from sqlalchemy.ext.declarative import declarative_base - - Base = declarative_base(cls=Base) - - class MyModel(Base): - name = Column(String(1000)) - -Where above, ``MyModel`` and all other classes that derive from ``Base`` will -have a table name derived from the class name, an ``id`` primary key column, -as well as the "InnoDB" engine for MySQL. - -Mixing in Columns -~~~~~~~~~~~~~~~~~ - -The most basic way to specify a column on a mixin is by simple -declaration:: - - class TimestampMixin(object): - created_at = Column(DateTime, default=func.now()) - - class MyModel(TimestampMixin, Base): - __tablename__ = 'test' - - id = Column(Integer, primary_key=True) - name = Column(String(1000)) - -Where above, all declarative classes that include ``TimestampMixin`` -will also have a column ``created_at`` that applies a timestamp to -all row insertions. - -Those familiar with the SQLAlchemy expression language know that -the object identity of clause elements defines their role in a schema. -Two ``Table`` objects ``a`` and ``b`` may both have a column called -``id``, but the way these are differentiated is that ``a.c.id`` -and ``b.c.id`` are two distinct Python objects, referencing their -parent tables ``a`` and ``b`` respectively. - -In the case of the mixin column, it seems that only one -:class:`.Column` object is explicitly created, yet the ultimate -``created_at`` column above must exist as a distinct Python object -for each separate destination class. To accomplish this, the declarative -extension creates a **copy** of each :class:`.Column` object encountered on -a class that is detected as a mixin. - -This copy mechanism is limited to simple columns that have no foreign -keys, as a :class:`.ForeignKey` itself contains references to columns -which can't be properly recreated at this level. For columns that -have foreign keys, as well as for the variety of mapper-level constructs -that require destination-explicit context, the -:class:`~.declared_attr` decorator is provided so that -patterns common to many classes can be defined as callables:: - - from sqlalchemy.ext.declarative import declared_attr - - class ReferenceAddressMixin(object): - @declared_attr - def address_id(cls): - return Column(Integer, ForeignKey('address.id')) - - class User(ReferenceAddressMixin, Base): - __tablename__ = 'user' - id = Column(Integer, primary_key=True) - -Where above, the ``address_id`` class-level callable is executed at the -point at which the ``User`` class is constructed, and the declarative -extension can use the resulting :class:`.Column` object as returned by -the method without the need to copy it. - -.. versionchanged:: > 0.6.5 - Rename 0.6.5 ``sqlalchemy.util.classproperty`` - into :class:`~.declared_attr`. - -Columns generated by :class:`~.declared_attr` can also be -referenced by ``__mapper_args__`` to a limited degree, currently -by ``polymorphic_on`` and ``version_id_col``; the declarative extension -will resolve them at class construction time:: - - class MyMixin: - @declared_attr - def type_(cls): - return Column(String(50)) - - __mapper_args__= {'polymorphic_on':type_} - - class MyModel(MyMixin, Base): - __tablename__='test' - id = Column(Integer, primary_key=True) - - -Mixing in Relationships -~~~~~~~~~~~~~~~~~~~~~~~ - -Relationships created by :func:`~sqlalchemy.orm.relationship` are provided -with declarative mixin classes exclusively using the -:class:`.declared_attr` approach, eliminating any ambiguity -which could arise when copying a relationship and its possibly column-bound -contents. Below is an example which combines a foreign key column and a -relationship so that two classes ``Foo`` and ``Bar`` can both be configured to -reference a common target class via many-to-one:: - - class RefTargetMixin(object): - @declared_attr - def target_id(cls): - return Column('target_id', ForeignKey('target.id')) - - @declared_attr - def target(cls): - return relationship("Target") - - class Foo(RefTargetMixin, Base): - __tablename__ = 'foo' - id = Column(Integer, primary_key=True) - - class Bar(RefTargetMixin, Base): - __tablename__ = 'bar' - id = Column(Integer, primary_key=True) - - class Target(Base): - __tablename__ = 'target' - id = Column(Integer, primary_key=True) - - -Using Advanced Relationship Arguments (e.g. ``primaryjoin``, etc.) -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -:func:`~sqlalchemy.orm.relationship` definitions which require explicit -primaryjoin, order_by etc. expressions should in all but the most -simplistic cases use **late bound** forms -for these arguments, meaning, using either the string form or a lambda. -The reason for this is that the related :class:`.Column` objects which are to -be configured using ``@declared_attr`` are not available to another -``@declared_attr`` attribute; while the methods will work and return new -:class:`.Column` objects, those are not the :class:`.Column` objects that -Declarative will be using as it calls the methods on its own, thus using -*different* :class:`.Column` objects. - -The canonical example is the primaryjoin condition that depends upon -another mixed-in column:: - - class RefTargetMixin(object): - @declared_attr - def target_id(cls): - return Column('target_id', ForeignKey('target.id')) - - @declared_attr - def target(cls): - return relationship(Target, - primaryjoin=Target.id==cls.target_id # this is *incorrect* - ) - -Mapping a class using the above mixin, we will get an error like:: - - sqlalchemy.exc.InvalidRequestError: this ForeignKey's parent column is not - yet associated with a Table. - -This is because the ``target_id`` :class:`.Column` we've called upon in our -``target()`` method is not the same :class:`.Column` that declarative is -actually going to map to our table. - -The condition above is resolved using a lambda:: - - class RefTargetMixin(object): - @declared_attr - def target_id(cls): - return Column('target_id', ForeignKey('target.id')) - - @declared_attr - def target(cls): - return relationship(Target, - primaryjoin=lambda: Target.id==cls.target_id - ) - -or alternatively, the string form (which ultimately generates a lambda):: - - class RefTargetMixin(object): - @declared_attr - def target_id(cls): - return Column('target_id', ForeignKey('target.id')) - - @declared_attr - def target(cls): - return relationship("Target", - primaryjoin="Target.id==%s.target_id" % cls.__name__ - ) - -Mixing in deferred(), column_property(), and other MapperProperty classes -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -Like :func:`~sqlalchemy.orm.relationship`, all -:class:`~sqlalchemy.orm.interfaces.MapperProperty` subclasses such as -:func:`~sqlalchemy.orm.deferred`, :func:`~sqlalchemy.orm.column_property`, -etc. ultimately involve references to columns, and therefore, when -used with declarative mixins, have the :class:`.declared_attr` -requirement so that no reliance on copying is needed:: - - class SomethingMixin(object): - - @declared_attr - def dprop(cls): - return deferred(Column(Integer)) - - class Something(SomethingMixin, Base): - __tablename__ = "something" - -The :func:`.column_property` or other construct may refer -to other columns from the mixin. These are copied ahead of time before -the :class:`.declared_attr` is invoked:: - - class SomethingMixin(object): - x = Column(Integer) - - y = Column(Integer) - - @declared_attr - def x_plus_y(cls): - return column_property(cls.x + cls.y) - - -.. versionchanged:: 1.0.0 mixin columns are copied to the final mapped class - so that :class:`.declared_attr` methods can access the actual column - that will be mapped. - -Mixing in Association Proxy and Other Attributes -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -Mixins can specify user-defined attributes as well as other extension -units such as :func:`.association_proxy`. The usage of -:class:`.declared_attr` is required in those cases where the attribute must -be tailored specifically to the target subclass. An example is when -constructing multiple :func:`.association_proxy` attributes which each -target a different type of child object. Below is an -:func:`.association_proxy` / mixin example which provides a scalar list of -string values to an implementing class:: - - from sqlalchemy import Column, Integer, ForeignKey, String - from sqlalchemy.orm import relationship - from sqlalchemy.ext.associationproxy import association_proxy - from sqlalchemy.ext.declarative import declarative_base, declared_attr - - Base = declarative_base() - - class HasStringCollection(object): - @declared_attr - def _strings(cls): - class StringAttribute(Base): - __tablename__ = cls.string_table_name - id = Column(Integer, primary_key=True) - value = Column(String(50), nullable=False) - parent_id = Column(Integer, - ForeignKey('%s.id' % cls.__tablename__), - nullable=False) - def __init__(self, value): - self.value = value - - return relationship(StringAttribute) - - @declared_attr - def strings(cls): - return association_proxy('_strings', 'value') - - class TypeA(HasStringCollection, Base): - __tablename__ = 'type_a' - string_table_name = 'type_a_strings' - id = Column(Integer(), primary_key=True) - - class TypeB(HasStringCollection, Base): - __tablename__ = 'type_b' - string_table_name = 'type_b_strings' - id = Column(Integer(), primary_key=True) - -Above, the ``HasStringCollection`` mixin produces a :func:`.relationship` -which refers to a newly generated class called ``StringAttribute``. The -``StringAttribute`` class is generated with its own :class:`.Table` -definition which is local to the parent class making usage of the -``HasStringCollection`` mixin. It also produces an :func:`.association_proxy` -object which proxies references to the ``strings`` attribute onto the ``value`` -attribute of each ``StringAttribute`` instance. - -``TypeA`` or ``TypeB`` can be instantiated given the constructor -argument ``strings``, a list of strings:: - - ta = TypeA(strings=['foo', 'bar']) - tb = TypeA(strings=['bat', 'bar']) - -This list will generate a collection -of ``StringAttribute`` objects, which are persisted into a table that's -local to either the ``type_a_strings`` or ``type_b_strings`` table:: - - >>> print ta._strings - [<__main__.StringAttribute object at 0x10151cd90>, - <__main__.StringAttribute object at 0x10151ce10>] - -When constructing the :func:`.association_proxy`, the -:class:`.declared_attr` decorator must be used so that a distinct -:func:`.association_proxy` object is created for each of the ``TypeA`` -and ``TypeB`` classes. - -.. versionadded:: 0.8 :class:`.declared_attr` is usable with non-mapped - attributes, including user-defined attributes as well as - :func:`.association_proxy`. - - -Controlling table inheritance with mixins -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -The ``__tablename__`` attribute may be used to provide a function that -will determine the name of the table used for each class in an inheritance -hierarchy, as well as whether a class has its own distinct table. - -This is achieved using the :class:`.declared_attr` indicator in conjunction -with a method named ``__tablename__()``. Declarative will always -invoke :class:`.declared_attr` for the special names -``__tablename__``, ``__mapper_args__`` and ``__table_args__`` -function **for each mapped class in the hierarchy**. The function therefore -needs to expect to receive each class individually and to provide the -correct answer for each. - -For example, to create a mixin that gives every class a simple table -name based on class name:: - - from sqlalchemy.ext.declarative import declared_attr - - class Tablename: - @declared_attr - def __tablename__(cls): - return cls.__name__.lower() - - class Person(Tablename, Base): - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __tablename__ = None - __mapper_args__ = {'polymorphic_identity': 'engineer'} - primary_language = Column(String(50)) - -Alternatively, we can modify our ``__tablename__`` function to return -``None`` for subclasses, using :func:`.has_inherited_table`. This has -the effect of those subclasses being mapped with single table inheritance -agaisnt the parent:: - - from sqlalchemy.ext.declarative import declared_attr - from sqlalchemy.ext.declarative import has_inherited_table - - class Tablename(object): - @declared_attr - def __tablename__(cls): - if has_inherited_table(cls): - return None - return cls.__name__.lower() - - class Person(Tablename, Base): - id = Column(Integer, primary_key=True) - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - primary_language = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'engineer'} - -.. _mixin_inheritance_columns: - -Mixing in Columns in Inheritance Scenarios -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -In constrast to how ``__tablename__`` and other special names are handled when -used with :class:`.declared_attr`, when we mix in columns and properties (e.g. -relationships, column properties, etc.), the function is -invoked for the **base class only** in the hierarchy. Below, only the -``Person`` class will receive a column -called ``id``; the mapping will fail on ``Engineer``, which is not given -a primary key:: - - class HasId(object): - @declared_attr - def id(cls): - return Column('id', Integer, primary_key=True) - - class Person(HasId, Base): - __tablename__ = 'person' - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __tablename__ = 'engineer' - primary_language = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'engineer'} - -It is usually the case in joined-table inheritance that we want distinctly -named columns on each subclass. However in this case, we may want to have -an ``id`` column on every table, and have them refer to each other via -foreign key. We can achieve this as a mixin by using the -:attr:`.declared_attr.cascading` modifier, which indicates that the -function should be invoked **for each class in the hierarchy**, just like -it does for ``__tablename__``:: - - class HasId(object): - @declared_attr.cascading - def id(cls): - if has_inherited_table(cls): - return Column('id', - Integer, - ForeignKey('person.id'), primary_key=True) - else: - return Column('id', Integer, primary_key=True) - - class Person(HasId, Base): - __tablename__ = 'person' - discriminator = Column('type', String(50)) - __mapper_args__ = {'polymorphic_on': discriminator} - - class Engineer(Person): - __tablename__ = 'engineer' - primary_language = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'engineer'} - - -.. versionadded:: 1.0.0 added :attr:`.declared_attr.cascading`. - -Combining Table/Mapper Arguments from Multiple Mixins -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -In the case of ``__table_args__`` or ``__mapper_args__`` -specified with declarative mixins, you may want to combine -some parameters from several mixins with those you wish to -define on the class iteself. The -:class:`.declared_attr` decorator can be used -here to create user-defined collation routines that pull -from multiple collections:: - - from sqlalchemy.ext.declarative import declared_attr - - class MySQLSettings(object): - __table_args__ = {'mysql_engine':'InnoDB'} - - class MyOtherMixin(object): - __table_args__ = {'info':'foo'} - - class MyModel(MySQLSettings, MyOtherMixin, Base): - __tablename__='my_model' - - @declared_attr - def __table_args__(cls): - args = dict() - args.update(MySQLSettings.__table_args__) - args.update(MyOtherMixin.__table_args__) - return args - - id = Column(Integer, primary_key=True) - -Creating Indexes with Mixins -~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -To define a named, potentially multicolumn :class:`.Index` that applies to all -tables derived from a mixin, use the "inline" form of :class:`.Index` and -establish it as part of ``__table_args__``:: - - class MyMixin(object): - a = Column(Integer) - b = Column(Integer) - - @declared_attr - def __table_args__(cls): - return (Index('test_idx_%s' % cls.__tablename__, 'a', 'b'),) - - class MyModel(MyMixin, Base): - __tablename__ = 'atable' - c = Column(Integer,primary_key=True) - -Special Directives -================== - -``__declare_last__()`` -~~~~~~~~~~~~~~~~~~~~~~ - -The ``__declare_last__()`` hook allows definition of -a class level function that is automatically called by the -:meth:`.MapperEvents.after_configured` event, which occurs after mappings are -assumed to be completed and the 'configure' step has finished:: - - class MyClass(Base): - @classmethod - def __declare_last__(cls): - "" - # do something with mappings - -.. versionadded:: 0.7.3 - -``__declare_first__()`` -~~~~~~~~~~~~~~~~~~~~~~~ - -Like ``__declare_last__()``, but is called at the beginning of mapper -configuration via the :meth:`.MapperEvents.before_configured` event:: - - class MyClass(Base): - @classmethod - def __declare_first__(cls): - "" - # do something before mappings are configured - -.. versionadded:: 0.9.3 - -.. _declarative_abstract: - -``__abstract__`` -~~~~~~~~~~~~~~~~~~~ - -``__abstract__`` causes declarative to skip the production -of a table or mapper for the class entirely. A class can be added within a -hierarchy in the same way as mixin (see :ref:`declarative_mixins`), allowing -subclasses to extend just from the special class:: - - class SomeAbstractBase(Base): - __abstract__ = True - - def some_helpful_method(self): - "" - - @declared_attr - def __mapper_args__(cls): - return {"helpful mapper arguments":True} - - class MyMappedClass(SomeAbstractBase): - "" - -One possible use of ``__abstract__`` is to use a distinct -:class:`.MetaData` for different bases:: - - Base = declarative_base() - - class DefaultBase(Base): - __abstract__ = True - metadata = MetaData() - - class OtherBase(Base): - __abstract__ = True - metadata = MetaData() - -Above, classes which inherit from ``DefaultBase`` will use one -:class:`.MetaData` as the registry of tables, and those which inherit from -``OtherBase`` will use a different one. The tables themselves can then be -created perhaps within distinct databases:: - - DefaultBase.metadata.create_all(some_engine) - OtherBase.metadata_create_all(some_other_engine) - -.. versionadded:: 0.7.3 - -Class Constructor -================= - -As a convenience feature, the :func:`declarative_base` sets a default -constructor on classes which takes keyword arguments, and assigns them -to the named attributes:: - - e = Engineer(primary_language='python') - -Sessions -======== - -Note that ``declarative`` does nothing special with sessions, and is -only intended as an easier way to configure mappers and -:class:`~sqlalchemy.schema.Table` objects. A typical application -setup using :class:`~sqlalchemy.orm.scoping.scoped_session` might look like:: - - engine = create_engine('postgresql://scott:tiger@localhost/test') - Session = scoped_session(sessionmaker(autocommit=False, - autoflush=False, - bind=engine)) - Base = declarative_base() - -Mapped instances then make usage of -:class:`~sqlalchemy.orm.session.Session` in the usual way. - -""" - from .api import declarative_base, synonym_for, comparable_using, \ instrument_declarative, ConcreteBase, AbstractConcreteBase, \ DeclarativeMeta, DeferredReflection, has_inherited_table,\ |