mappers.txt

SQLAlchemy. 经典的Python ORM框架。学习必看。

    class Parent(object):
        pass

    class Child(object):
        pass

    mapper(Parent, parent_table, properties={
        'child':relation(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.

##### One To One {@name=onetoone}

One To One is essentially a bi-directional relationship with a scalar attribute on both sides.  To achieve this, the `uselist=False` flag indicates the placement of a scalar attribute instead of a collection on the "many" side of the relationship.  To convert one-to-many into one-to-one:

    {python}
    mapper(Parent, parent_table, properties={
        'child':relation(Child, uselist=False, backref='parent')
    })

Or to turn many-to-one into one-to-one:

    {python}
    mapper(Parent, parent_table, properties={
        'child':relation(Child, backref=backref('parent', uselist=False))
    })

##### Many To Many {@name=manytomany}

Many to Many adds an association table between two classes.  The association table is indicated by the `secondary` argument to `relation()`.

    {python}
    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')),
        Column('right_id', Integer, ForeignKey('right.id')),
        )
        
    mapper(Parent, left_table, properties={
        'children':relation(Child, secondary=association_table)
    })
    
    mapper(Child, right_table)

For a bi-directional relationship, both sides of the relation contain a collection by default, which can be modified on either side via the `uselist` flag to be scalar.  The `backref` keyword will automatically use the same `secondary` argument for the reverse relation:

    {python}
    mapper(Parent, left_table, properties={
        'children':relation(Child, secondary=association_table, backref='parents')
    })
    
##### Association Object

The association object pattern is a variant on many-to-many:  it specifically is used when your association table contains additional columns beyond those which are foreign keys to the left and right tables.  Instead of using the `secondary` argument, you map a new class directly to the association table.  The left side of the relation references the association object via one-to-many, and the association class references the right side via many-to-one.  

    {python}
    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':relation(Association)
    })
    
    mapper(Association, association_table, properties={
        'child':relation(Child)
    })
    
    mapper(Child, right_table)

The bi-directional version adds backrefs to both relations:

    {python}
    mapper(Parent, left_table, properties={
        'children':relation(Association, backref="parent")
    })

    mapper(Association, association_table, properties={
        'child':relation(Child, backref="parent_assocs")
    })

    mapper(Child, right_table)

Working with the association pattern in its direct form requires that child objects are associated with an association instance before being appended to the parent; similarly, access from parent to child goes through the association object:

    {python}
    # create parent, append a child via association
    p = Parent()
    a = Association()
    a.child = Child()
    p.children.append(a)
    
    # iterate through child objects via association, including association 
    # attributes
    for assoc in p.children:
        print assoc.data
        print assoc.child

To enhance the association object pattern such that direct access to the `Association` object is optional, SQLAlchemy provides the [plugins_associationproxy](rel:plugins_associationproxy).

**Important Note**:  it is strongly advised that the `secondary` table argument not be combined with the Association Object pattern, unless the `relation()` which contains the `secondary` argument is marked `viewonly=True`.  Otherwise, SQLAlchemy may persist conflicting data to the underlying association table since it is represented by two conflicting mappings.  The Association Proxy pattern should be favored in the case where access to the underlying association data is only sometimes needed.

#### Adjacency List Relationships {@name=selfreferential}

The **adjacency list** pattern is a common relational pattern whereby a table 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 probably the most appropriate pattern for the large majority of hierarchical storage needs, for reasons of concurrency, reduced complexity, and that modified preorder has little advantage over an application which can fully load subtrees into the application space.

SQLAlchemy commonly refers to an adjacency list relation as a **self-referential mapper**.  In this example, we'll work with a single table called `treenodes` to represent a tree structure:

    {python}
    nodes = Table('treenodes', metadata,
        Column('id', Integer, primary_key=True),
        Column('parent_id', Integer, ForeignKey('treenodes.id')),
        Column('data', String(50)),
        )

A graph such as the following:

    {diagram}
    root --+---> child1
           +---> child2 --+--> subchild1
           |              +--> subchild2
           +---> child3    

Would be represented with data such as:

    {diagram}
    id       parent_id     data
    ---      -------       ----
    1        NULL          root
    2        1             child1
    3        1             child2
    4        3             subchild1
    5        3             subchild2
    6        1             child3
    
SQLAlchemy's `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 relation from `treenodes` to `treenodes`, it assumes one-to-many unless told otherwise:    
    
    {python}
    # entity class
    class Node(object):
        pass

    mapper(Node, nodes, properties={
        'children':relation(Node)
    })
    
To create a many-to-one relationship from child to parent, an extra indicator of the "remote side" is added, which contains the `Column` object or objects indicating the remote side of the relation:

    {python}
    mapper(Node, nodes, properties={
        'parent':relation(Node, remote_side=[nodes.c.id])
    })
    
And the bi-directional version combines both:

    {python}
    mapper(Node, nodes, properties={
        'children':relation(Node, backref=backref('parent', remote_side=[nodes.c.id]))
    })

There are several examples included with SQLAlchemy illustrating self-referential strategies; these include [basic_tree.py](http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py) and [optimized_al.py](http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/elementtree/optimized_al.py), the latter of which illustrates how to persist and search XML documents in conjunction with [ElementTree](http://effbot.org/zone/element-index.htm).

##### Self-Referential Query Strategies {@name=query}

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` attrbibute stores the value `child2`:

    {python}
    # get all nodes named 'child2'
    sess.query(Node).filter(Node.data=='child2')

On the subject of joins, i.e. those described in [datamapping_joins](rel: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` `Table` object as a source of aliases:

    {python}
    # get all nodes named 'subchild1' with a parent named 'child2'
    nodealias = nodes.alias()
    {sql}sess.query(Node).filter(Node.data=='subchild1').\
        filter(and_(Node.parent_id==nodealias.c.id, nodealias.c.data=='child2')).all()
    SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data 
    FROM treenodes, treenodes AS treenodes_1 
    WHERE treenodes.data = ? AND treenodes.parent_id = treenodes_1.id AND treenodes_1.data = ? ORDER BY treenodes.oid
    ['subchild1', 'child2']

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

    {python}
    # get all nodes named 'subchild1' with a parent named 'child2'
    {sql}sess.query(Node).filter(Node.data=='subchild1').\
        join('parent', aliased=True).filter(Node.data=='child2').all()
    SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data 
    FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id 
    WHERE treenodes.data = ? AND treenodes_1.data = ? ORDER BY treenodes.oid
    ['subchild1', 'child2']

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

    {python}
    # get all nodes named 'subchild1' with a parent named 'child2' and a grandparent 'root'
    {sql}sess.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 treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data 
    FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id JOIN treenodes AS treenodes_2 ON treenodes_2.id = treenodes_1.parent_id 
    WHERE treenodes.data = ? AND treenodes_1.data = ? AND treenodes_2.data = ? ORDER BY treenodes.oid
    ['subchild1', 'child2', 'root']

##### Configuring Eager Loading {@name=eagerloading}

Eager loading of relations occurs using joins or outerjoins from parent to child table during a normal query operation, such that the parent and its child collection can be populated from a single SQL statement.  SQLAlchemy's 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 relation, 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`:

    {python}
    mapper(Node, nodes, properties={
        'children':relation(Node, lazy=False, join_depth=2)
    })
    
    {sql}session.query(Node).all()
    SELECT treenodes_1.id AS treenodes_1_id, treenodes_1.parent_id AS treenodes_1_parent_id, treenodes_1.data AS treenodes_1_data, treenodes_2.id AS treenodes_2_id, treenodes_2.parent_id AS treenodes_2_parent_id, treenodes_2.data AS treenodes_2_data, treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data 
    FROM treenodes LEFT OUTER JOIN treenodes AS treenodes_2 ON treenodes.id = treenodes_2.parent_id LEFT OUTER JOIN treenodes AS treenodes_1 ON treenodes_2.id = treenodes_1.parent_id ORDER BY treenodes.oid, treenodes_2.oid, treenodes_1.oid
    []

#### Specifying Alternate Join Conditions to relation() {@name=customjoin}

The `relation()` function uses the foreign key relationship between the parent and child tables to formulate the **primary join condition** between parent and child; in the case of a many-to-many relationship it also formulates the **secondary join condition**.  If you are working with a `Table` which has no `ForeignKey` objects on it (which can be the case when using reflected tables with MySQL), or if the join condition cannot be expressed by a simple foreign key relationship, use the `primaryjoin` and possibly `secondaryjoin` conditions to create the appropriate relationship.

In this example we create a relation `boston_addresses` which will only load the user addresses with a city of "Boston":

    {python}
    class User(object):
        pass
    class Address(object):
        pass
    
    mapper(Address, addresses_table)