~ubuntu-branches/debian/sid/sqlalchemy/sid

A quick walkthrough of the basic relational patterns. In this section we

illustrate the classical mapping using :func:`mapper` in conjunction with

:func:`relationship`. Then (by popular demand), we illustrate the declarative

form using the :mod:`~sqlalchemy.ext.declarative` module.

 

Note that :func:`.relationship` is historically known as

:func:`.relation` in older versions of SQLAlchemy.

A one to many relationship places a foreign key in the child table referencing

the parent. SQLAlchemy creates the relationship as a collection on the parent

object containing instances of the child object.

 

.. sourcecode:: python+sql

 

    parent_table = Table('parent', metadata,

        Column('id', Integer, primary_key=True))

 

    child_table = Table('child', metadata,

        Column('id', Integer, primary_key=True),

        Column('parent_id', Integer, ForeignKey('parent.id'))

    )

 

    class Parent(object):

        pass

 

    class Child(object):

        pass

 

    mapper(Parent, parent_table, properties={

        'children': relationship(Child)

    })

 

    mapper(Child, child_table)

 

To establish a bi-directional relationship in one-to-many, where the "reverse" side is a many to one, specify the ``backref`` option:

 

.. sourcecode:: python+sql

 

    mapper(Parent, parent_table, properties={

        'children': relationship(Child, backref='parent')

    })

 

    mapper(Child, child_table)

Declarative::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

 

    class Parent(Base):

        __tablename__ = 'parent'

        id = Column(Integer, primary_key=True)

        children = relationship("Child", backref="parent")

 

    class Child(Base):

        __tablename__ = 'child'

        id = Column(Integer, primary_key=True)

        parent_id = Column(Integer, ForeignKey('parent.id'))

 

 

The mapping setup is identical to one-to-many, however SQLAlchemy creates the

relationship as a scalar attribute on the parent object referencing a single

instance of the child object.

 

.. sourcecode:: python+sql

 

    parent_table = Table('parent', metadata,

        Column('id', Integer, primary_key=True),

        Column('child_id', Integer, ForeignKey('child.id')))

 

    child_table = Table('child', metadata,

        Column('id', Integer, primary_key=True),

        )

 

    class Parent(object):

        pass

 

    class Child(object):

        pass

 

    mapper(Parent, parent_table, properties={

        'child': relationship(Child)

    })

 

    mapper(Child, child_table)

 

Backref behavior is available here as well, where ``backref="parents"`` will

place a one-to-many collection on the ``Child`` class::

 

    mapper(Parent, parent_table, properties={

        'child': relationship(Child, backref="parents")

    })

 

Declarative::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    class Child(Base):

        __tablename__ = 'child'

        id = Column(Integer, primary_key=True)

 

One To One is essentially a bi-directional relationship with a scalar

    parent_table = Table('parent', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    child_table = Table('child', metadata,

        Column('id', Integer, primary_key=True),

        Column('parent_id', Integer, ForeignKey('parent.id'))

    )

 

    mapper(Parent, parent_table, properties={

        'child': relationship(Child, uselist=False, backref='parent')

    })

 

    mapper(Child, child_table)

    from sqlalchemy.orm import backref

 

    parent_table = Table('parent', metadata,

        Column('id', Integer, primary_key=True),

        Column('child_id', Integer, ForeignKey('child.id'))

    )

 

    child_table = Table('child', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    mapper(Parent, parent_table, properties={

        'child': relationship(Child, backref=backref('parent', uselist=False))

    })

 

    mapper(Child, child_table)

 

The second example above as declarative::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

 

:func:`.relationship`.

 

.. sourcecode:: python+sql

 

    left_table = Table('left', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    right_table = Table('right', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    association_table = Table('association', metadata,

    mapper(Parent, left_table, properties={

        'children': relationship(Child, secondary=association_table)

    })

 

    mapper(Child, right_table)

 

For a bi-directional relationship, both sides of the relationship contain a

the same ``secondary`` argument for the reverse relationship:

 

.. sourcecode:: python+sql

 

    mapper(Parent, left_table, properties={

        'children': relationship(Child, secondary=association_table, 

                                        backref='parents')

    })

 

With declarative, we still use the :class:`.Table` for the ``secondary`` 

argument.  A class is not mapped to this table, so it remains in its 

plain schematic form::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

many-to-one.

 

.. sourcecode:: python+sql

 

    left_table = Table('left', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    right_table = Table('right', metadata,

        Column('id', Integer, primary_key=True)

    )

 

    association_table = Table('association', metadata,

        Column('left_id', Integer, ForeignKey('left.id'), primary_key=True),

        Column('right_id', Integer, ForeignKey('right.id'), primary_key=True),

        Column('data', String(50))

    )

 

    mapper(Parent, left_table, properties={

        'children':relationship(Association)

    })

 

    mapper(Association, association_table, properties={

        'child':relationship(Child)

    })

 

    mapper(Child, right_table)

 

The bi-directional version adds backrefs to both relationships:

 

.. sourcecode:: python+sql

 

    mapper(Parent, left_table, properties={

        'children':relationship(Association, backref="parent")

    })

 

    mapper(Association, association_table, properties={

        'child':relationship(Child, backref="parent_assocs")

    })

 

    mapper(Child, right_table)

 

Declarative::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

        children = relationship(Association, backref="parent")

association object:

 

.. sourcecode:: python+sql

provides the :ref:`associationproxy` extension. This

contains a foreign key reference to itself. This is the most common and simple

way to represent hierarchical data in flat tables. The other way is the

"nested sets" model, sometimes called "modified preorder". Despite what many

online articles say about modified preorder, the adjacency list model is

SQLAlchemy commonly refers to an adjacency list relationship as a

**self-referential mapper**. In this example, we'll work with a single table

called ``nodes`` to represent a tree structure::

 

    nodes = Table('nodes', metadata,

        Column('id', Integer, primary_key=True),

        Column('parent_id', Integer, ForeignKey('nodes.id')),

        Column('data', String(50)),

        )

 

A graph such as the following::

SQLAlchemy's :func:`.mapper` configuration for a self-referential one-to-many

relationship is exactly like a "normal" one-to-many relationship. When

SQLAlchemy encounters the foreign key relationship from ``nodes`` to

``nodes``, it assumes one-to-many unless told otherwise:

 

.. sourcecode:: python+sql

 

    # entity class

    class Node(object):

        pass

 

    mapper(Node, nodes, properties={

        'children': relationship(Node)

    })

 

To create a many-to-one relationship from child to parent, an extra indicator

of the "remote side" is added, which contains the

:class:`~sqlalchemy.schema.Column` object or objects indicating the remote

side of the relationship:

 

.. sourcecode:: python+sql

 

    mapper(Node, nodes, properties={

        'parent': relationship(Node, remote_side=[nodes.c.id])

    })

 

And the bi-directional version combines both:

 

.. sourcecode:: python+sql

 

    mapper(Node, nodes, properties={

        'children': relationship(Node, 

                            backref=backref('parent', remote_side=[nodes.c.id])

                        )

    })

 

For comparison, the declarative version typically uses the inline ``id`` 

:class:`.Column` attribute to declare remote_side (note the list form is optional

when the collection is only one column)::

 

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

 

    class Node(Base):

        __tablename__ = 'nodes'

        id = Column(Integer, primary_key=True)

        parent_id = Column(Integer, ForeignKey('nodes.id'))

                        backref=backref('parent', remote_side=id)

                    )

 

Querying self-referential structures is done in the same way as any other

query in SQLAlchemy, such as below, we query for any node whose ``data``

attribute stores the value ``child2``:

 

.. sourcecode:: python+sql

On the subject of joins, i.e. those described in `datamapping_joins`,

self-referential structures require the usage of aliases so that the same

table can be referenced multiple times within the FROM clause of the query.

Aliasing can be done either manually using the ``nodes``

:class:`~sqlalchemy.schema.Table` object as a source of aliases:

 

.. sourcecode:: python+sql

 

    # get all nodes named 'subchild1' with a parent named 'child2'

    nodealias = nodes.alias()

    {sql}session.query(Node).filter(Node.data=='subchild1').\

        filter(and_(Node.parent_id==nodealias.c.id, nodealias.c.data=='child2')).all()

    SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS nodes_data

    FROM nodes, nodes AS nodes_1

    WHERE nodes.data = ? AND nodes.parent_id = nodes_1.id AND nodes_1.data = ?

    ['subchild1', 'child2']

 

or automatically, using ``join()`` with ``aliased=True``:

 

.. sourcecode:: python+sql

 

    # get all nodes named 'subchild1' with a parent named 'child2'

    {sql}session.query(Node).filter(Node.data=='subchild1').\

        join('parent', aliased=True).filter(Node.data=='child2').all()

    SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS nodes_data

    FROM nodes JOIN nodes AS nodes_1 ON nodes_1.id = nodes.parent_id

    WHERE nodes.data = ? AND nodes_1.data = ?

    ['subchild1', 'child2']

 

To add criterion to multiple points along a longer join, use ``from_joinpoint=True``:

 

.. sourcecode:: python+sql

 

    # get all nodes named 'subchild1' with a parent named 'child2' and a grandparent 'root'

    {sql}session.query(Node).filter(Node.data=='subchild1').\

        join('parent', aliased=True).filter(Node.data=='child2').\

        join('parent', aliased=True, from_joinpoint=True).filter(Node.data=='root').all()

    SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS nodes_data

    FROM nodes JOIN nodes AS nodes_1 ON nodes_1.id = nodes.parent_id JOIN nodes AS nodes_2 ON nodes_2.id = nodes_1.parent_id

    WHERE nodes.data = ? AND nodes_1.data = ? AND nodes_2.data = ?

Configuring Eager Loading

~~~~~~~~~~~~~~~~~~~~~~~~~~

child collection can be populated from a single SQL statement, or a second

statement for all collections at once. SQLAlchemy's joined and subquery eager

loading uses aliased tables in all cases when joining to related items, so it

is compatible with self-referential joining. However, to use eager loading

with a self-referential relationship, SQLAlchemy needs to be told how many

levels deep it should join; otherwise the eager load will not take place. This

depth setting is configured via ``join_depth``:

    mapper(Node, nodes, properties={

        'children': relationship(Node, lazy='joined', join_depth=2)

    })

    SELECT nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.data AS nodes_1_data, nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.data AS nodes_2_data, nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS nodes_data

    FROM nodes LEFT OUTER JOIN nodes AS nodes_2 ON nodes.id = nodes_2.parent_id LEFT OUTER JOIN nodes AS nodes_1 ON nodes_2.id = nodes_1.parent_id

Linking relationships with Backref

about the other one, indicating that they should establish "bi-directional"