mappers.txt

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

[alpha_api]: javascript:alphaApi()
[alpha_implementation]: javascript:alphaImplementation()

Mapper Configuration {@name=advdatamapping}
======================

This section references most major configurational patterns involving the [mapper()](rel:docstrings_sqlalchemy.orm_modfunc_mapper) and [relation()](rel:docstrings_sqlalchemy.orm_modfunc_relation) functions.  It assumes you've worked through the [datamapping](rel:datamapping) and know how to construct and use rudimentary mappers and relations.

### Mapper Configuration

Full API documentation for the ORM:

[docstrings_sqlalchemy.orm](rel:docstrings_sqlalchemy.orm).

Options for the `mapper()` function:

[docstrings_sqlalchemy.orm_modfunc_mapper](rel:docstrings_sqlalchemy.orm_modfunc_mapper).

#### Customizing Column Properties {@name=columns}

The default behavior of a `mapper` is to assemble all the columns in the mapped `Table` into mapped object attributes.  This behavior can be modified in several ways, as well as enhanced by SQL expressions.

To load only a part of the columns referenced by a table as attributes, use the `include_properties` and `exclude_properties` arguments:

    {python}
    mapper(User, users_table, include_properties=['user_id', 'user_name'])
    
    mapper(Address, addresses_table, exclude_properties=['street', 'city', 'state', 'zip'])
    
To change the name of the attribute mapped to a particular column, place the `Column` object in the `properties` dictionary with the desired key:

    {python}
    mapper(User, users_table, properties={ 
       'id' : users_table.c.user_id,
       'name' : users_table.c.user_name,
    })

To change the names of all attributes using a prefix, use the `column_prefix` option.  This is useful for classes which wish to add their own `property` accessors:

    {python}
    mapper(User, users_table, column_prefix='_')
    
The above will place attribute names such as `_user_id`, `_user_name`, `_password` etc. on the mapped `User` class.
    
To place multiple columns which are known to be "synonymous" based on foreign key relationship or join condition into the same mapped attribute, put  them together using a list, as below where we map to a `Join`:

    {python}
    # join users and addresses
    usersaddresses = sql.join(users_table, addresses_table, \
        users_table.c.user_id == addresses_table.c.user_id)
        
    mapper(User, usersaddresses, 
       properties = {
               'id':[users_table.c.user_id, addresses_table.c.user_id],
          })

#### Deferred Column Loading {@name=deferred}

This feature allows particular columns of a table to not be loaded by default, instead being loaded later on when first referenced.  It is essentially "column-level lazy loading".   This feature is useful when one wants to avoid loading a large text or binary field into memory when it's not needed.  Individual columns can be lazy loaded by themselves or placed into groups that lazy-load together.

    {python}
    book_excerpts = Table('books', db, 
        Column('book_id', Integer, primary_key=True),
        Column('title', String(200), nullable=False),
        Column('summary', String(2000)),
        Column('excerpt', String),
        Column('photo', Binary)
      )

    class Book(object):
        pass

    # define a mapper that will load each of 'excerpt' and 'photo' in 
    # separate, individual-row SELECT statements when each attribute
    # is first referenced on the individual object instance
    mapper(Book, book_excerpts, properties = {
      'excerpt' : deferred(book_excerpts.c.excerpt),
      'photo' : deferred(book_excerpts.c.photo)
    })

Deferred columns can be placed into groups so that they load together:

    {python}
    book_excerpts = Table('books', db, 
      Column('book_id', Integer, primary_key=True),
      Column('title', String(200), nullable=False),
      Column('summary', String(2000)),
      Column('excerpt', String),
      Column('photo1', Binary),
      Column('photo2', Binary),
      Column('photo3', Binary)
    )

    class Book(object):
      pass

    # define a mapper with a 'photos' deferred group.  when one photo is referenced,
    # all three photos will be loaded in one SELECT statement.  The 'excerpt' will 
    # be loaded separately when it is first referenced.
    mapper(Book, book_excerpts, properties = {
      'excerpt' : deferred(book_excerpts.c.excerpt),
      'photo1' : deferred(book_excerpts.c.photo1, group='photos'),
      'photo2' : deferred(book_excerpts.c.photo2, group='photos'),
      'photo3' : deferred(book_excerpts.c.photo3, group='photos')
    })

You can defer or undefer columns at the `Query` level using the `defer` and `undefer` options:

    {python}
    query = session.query(Book)
    query.options(defer('summary')).all()
    query.options(undefer('excerpt')).all()

And an entire "deferred group", i.e. which uses the `group` keyword argument to `deferred()`, can be undeferred using `undefer_group()`, sending in the group name:

    {python}
    query = session.query(Book)
    query.options(undefer_group('photos')).all()

#### SQL Expressions as Mapped Attributes {@name=expressions}

To add a SQL clause composed of local or external columns as a read-only, mapped column attribute, use the `column_property()` function.  Any scalar-returning `ClauseElement` may be used, as long as it has a `name` attribute; usually, you'll want to call `label()` to give it a specific name:

    {python}
    mapper(User, users_table, properties={
        'fullname' : column_property(
            (users_table.c.firstname + " " + users_table.c.lastname).label('fullname')
        )
    })

Correlated subqueries may be used as well:
    
    {python}
    mapper(User, users_table, properties={
        'address_count' : column_property(
                select(
                    [func.count(addresses_table.c.address_id)], 
                    addresses_table.c.user_id==users_table.c.user_id
                ).label('address_count')
            )
    })
    
#### Overriding Attribute Behavior with Synonyms {@name=overriding}

A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute.  As of 0.4.2, the `synonym()` construct provides an easy way to do this in conjunction with a normal Python `property` constructs.  Below, we re-map the `email` column of our mapped table to a custom attribute setter/getter, mapping the actual column to the property named `_email`:

    {python}
    class MyAddress(object):
       def _set_email(self, email):
          self._email = email
       def _get_email(self):
          return self._email
       email = property(_get_email, _set_email)
    
    mapper(MyAddress, addresses_table, properties = {
        'email':synonym('_email', map_column=True)
    })

The `email` attribute is now usable in the same way as any other mapped attribute, including filter expressions, get/set operations, etc.:

    {python}
    address = sess.query(MyAddress).filter(MyAddress.email == 'some address').one()

    address.email = 'some other address'
    sess.flush()
    
    q = sess.query(MyAddress).filter_by(email='some other address')

If the mapped class does not provide a property, the `synonym()` construct will create a default getter/setter object automatically.

#### Composite Column Types {@name=composite}

Sets of columns can be associated with a single datatype.  The ORM treats the group of columns like a single column which accepts and returns objects using the custom datatype you provide.  In this example, we'll create a table `vertices` which stores a pair of x/y coordinates, and a custom datatype `Point` which is a composite type of an x and y column:

    {python}
    vertices = Table('vertices', metadata, 
        Column('id', Integer, primary_key=True),
        Column('x1', Integer),
        Column('y1', Integer),
        Column('x2', Integer),
        Column('y2', Integer),
        )

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:
    
    {python}
    class Point(object):
        def __init__(self, x, y):
            self.x = x
            self.y = y
        def __composite_values__(self):
            return [self.x, self.y]            
        def __eq__(self, other):
            return other.x == self.x and other.y == self.y
        def __ne__(self, other):
            return not self.__eq__(other)

Setting up the mapping uses the `composite()` function:


    {python}
    class Vertex(object):
        pass
        
    mapper(Vertex, vertices, properties={
        'start':composite(Point, vertices.c.x1, vertices.c.y1),
        'end':composite(Point, vertices.c.x2, vertices.c.y2)
    })

We can now use the `Vertex` instances as well as querying as though the `start` and `end` attributes are regular scalar attributes:

    {python}
    sess = Session()
    v = Vertex(Point(3, 4), Point(5, 6))
    sess.save(v)
    
    v2 = sess.query(Vertex).filter(Vertex.start == Point(3, 4))
    
The "equals" comparison operation by default produces an AND of all corresponding columns equated to one another.  If you'd like to override this, or define the behavior of other SQL operators for your new type, the `composite()` function accepts an extension object of type `sqlalchemy.orm.PropComparator`:

    {python}
    from sqlalchemy.orm import PropComparator
    from sqlalchemy import sql
    
    class PointComparator(PropComparator):
        def __gt__(self, other):
            """define the 'greater than' operation"""

            return sql.and_(*[a>b for a, b in
                              zip(self.prop.columns,
                                  other.__composite_values__())])
                                  
    maper(Vertex, vertices, properties={
        'start':composite(Point, vertices.c.x1, vertices.c.y1, comparator=PointComparator),
        'end':composite(Point, vertices.c.x2, vertices.c.y2, comparator=PointComparator)
    })

#### Controlling Ordering {@name=orderby}

By default, mappers will attempt to ORDER BY the "oid" column of a table, or the first primary key column, when selecting rows.  This can be modified in several ways.

The "order_by" parameter can be sent to a mapper, overriding the per-engine ordering if any.  A value of None means that the mapper should not use any ordering.  A non-None value, which can be a column, an `asc` or `desc` clause, or an array of either one, indicates the ORDER BY clause that should be added to all select queries:

    {python}
    # disable all ordering
    mapper(User, users_table, order_by=None)

    # order by a column