mappers.txt

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

    companies = Table('companies', metadata, 
       Column('company_id', Integer, primary_key=True),
       Column('name', String(50)))

    class Company(object):
        pass
    
    mapper(Company, companies, properties={
       'employees': relation(Employee, backref='company')
    })
       
SQLAlchemy has a lot of experience in this area; the optimized "outer join" approach can be used freely for parent and child relationships, eager loads are fully useable, query aliasing and other tricks are fully supported as well.

In a concrete inheritance scenario, mapping `relation()`s is more difficult since the distinct classes do not share a table.  In this case, you *can* establish a relationship from parent to child if a join condition can be constructed from parent to child, if each child table contains a foreign key to the parent:

    {python}
    companies = Table('companies', metadata, 
       Column('id', Integer, primary_key=True),
       Column('name', String(50)))

    employees_table = Table('employees', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('company_id', Integer, ForeignKey('companies.id'))
    )

    managers_table = Table('managers', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('manager_data', String(50)),
        Column('company_id', Integer, ForeignKey('companies.id'))
    )

    engineers_table = Table('engineers', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('engineer_info', String(50)),
        Column('company_id', Integer, ForeignKey('companies.id'))
    )

    mapper(Employee, employees_table, select_table=pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='employee')
    mapper(Manager, managers_table, inherits=employee_mapper, concrete=True, polymorphic_identity='manager')
    mapper(Engineer, engineers_table, inherits=employee_mapper, concrete=True, polymorphic_identity='engineer')
    mapper(Company, companies, properties={
        'employees':relation(Employee)
    })

Let's crank it up and try loading with an eager load:

    {python}
    session.query(Company).options(eagerload('employees')).all()
    {opensql}
    SELECT anon_1.type AS anon_1_type, anon_1.manager_data AS anon_1_manager_data, anon_1.engineer_info AS anon_1_engineer_info, 
    anon_1.employee_id AS anon_1_employee_id, anon_1.name AS anon_1_name, anon_1.company_id AS anon_1_company_id, 
    companies.id AS companies_id, companies.name AS companies_name 
    FROM companies LEFT OUTER JOIN (SELECT CAST(NULL AS VARCHAR(50)) AS engineer_info, employees.employee_id AS employee_id, 
    CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name, employees.company_id AS company_id, 'employee' AS type 
    FROM employees UNION ALL SELECT CAST(NULL AS VARCHAR(50)) AS engineer_info, managers.employee_id AS employee_id, 
    managers.manager_data AS manager_data, managers.name AS name, managers.company_id AS company_id, 'manager' AS type 
    FROM managers UNION ALL SELECT engineers.engineer_info AS engineer_info, engineers.employee_id AS employee_id, 
    CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name, engineers.company_id AS company_id, 'engineer' AS type 
    FROM engineers) AS anon_1 ON companies.id = anon_1.company_id ORDER BY companies.oid, anon_1.oid
    []

The big limitation with concrete table inheritance is that relation()s placed on each concrete mapper do **not** propagate to child mappers.  If you want to have the same relation()s set up on all concrete mappers, they must be configured manually on each.

#### Mapping a Class against Multiple Tables {@name=joins}

Mappers can be constructed against arbitrary relational units (called `Selectables`) as well as plain `Tables`.  For example, The `join` keyword from the SQL package creates a neat selectable unit comprised of multiple tables, complete with its own composite primary key, which can be passed in to a mapper as the table.

    {python}
    # a class
    class AddressUser(object):
        pass

    # define a Join
    j = join(users_table, addresses_table)
    
    # map to it - the identity of an AddressUser object will be 
    # based on (user_id, address_id) since those are the primary keys involved
    mapper(AddressUser, j, properties={
        'user_id':[users_table.c.user_id, addresses_table.c.user_id]
    })

A second example:

    {python}
    # many-to-many join on an association table
    j = join(users_table, userkeywords, 
            users_table.c.user_id==userkeywords.c.user_id).join(keywords, 
               userkeywords.c.keyword_id==keywords.c.keyword_id)
     
    # a class 
    class KeywordUser(object):
        pass

    # map to it - the identity of a KeywordUser object will be
    # (user_id, keyword_id) since those are the primary keys involved
    mapper(KeywordUser, j, properties={
        'user_id':[users_table.c.user_id, userkeywords.c.user_id],
        'keyword_id':[userkeywords.c.keyword_id, keywords.c.keyword_id]
    })

In both examples above, "composite" columns were added as properties to the mappers; these are aggregations of multiple columns into one mapper property, which instructs the mapper to keep both of those columns set at the same value.

#### Mapping a Class against Arbitrary Selects {@name=selects}

Similar to mapping against a join, a plain select() object can be used with a mapper as well.  Below, an example select which contains two aggregate functions and a group_by is mapped to a class:

    {python}
    s = select([customers, 
                func.count(orders).label('order_count'), 
                func.max(orders.price).label('highest_order')],
                customers.c.customer_id==orders.c.customer_id,
                group_by=[c for c in customers.c]
                ).alias('somealias')
    class Customer(object):
        pass
    
    mapper(Customer, s)
    
Above, the "customers" table is joined against the "orders" table to produce a full row for each customer row, the total count of related rows in the "orders" table, and the highest price in the "orders" table, grouped against the full set of columns in the "customers" table.  That query is then mapped against the Customer class.  New instances of Customer will contain attributes for each column in the "customers" table as well as an "order_count" and "highest_order" attribute.  Updates to the Customer object will only be reflected in the "customers" table and not the "orders" table.  This is because the primary key columns of the "orders" table are not represented in this mapper and therefore the table is not affected by save or delete operations.

#### Multiple Mappers for One Class {@name=multiple}

The first mapper created for a certain class is known as that class's "primary mapper."  Other mappers can be created as well, these come in two varieties.

* **secondary mapper**
    this is a mapper that must be constructed with the keyword argument `non_primary=True`, and represents a load-only mapper.  Objects that are loaded with a secondary mapper will have their save operation processed by the primary mapper.  It is also invalid to add new `relation()`s to a non-primary mapper. To use this mapper with the Session, specify it to the `query` method:

    example:

        {python}
        # primary mapper
        mapper(User, users_table)
    
        # make a secondary mapper to load User against a join
        othermapper = mapper(User, users_table.join(someothertable), non_primary=True)
    
        # select
        result = session.query(othermapper).select()

    The "non primary mapper" is a rarely needed feature of SQLAlchemy; in most cases, the `Query` object can produce any kind of query that's desired.  It's recommended that a straight `Query` be used in place of a non-primary mapper unless the mapper approach is absolutely needed.  Current use cases for the "non primary mapper" are when you want to map the class to a particular select statement or view to which additional query criterion can be added, and for when the particular mapped select statement or view is to be placed in a `relation()` of a parent mapper.

* **entity name mapper**
    this is a mapper that is a fully functioning primary mapper for a class, which is distinguished from the regular primary mapper by an `entity_name` parameter.  Instances loaded with this mapper will be totally managed by this new mapper and have no connection to the original one.  Most methods on `Session` include an optional `entity_name` parameter in order to specify this condition.

    example:

        {python}
        # primary mapper
        mapper(User, users_table)
    
        # make an entity name mapper that stores User objects in another table
        mapper(User, alternate_users_table, entity_name='alt')
    
        # make two User objects
        user1 = User()
        user2 = User()
    
        # save one in in the "users" table
        session.save(user1)
    
        # save the other in the "alternate_users_table"
        session.save(user2, entity_name='alt')
    
        session.flush()
    
        # select from the alternate mapper
        session.query(User, entity_name='alt').select()

    Use the "entity name" mapper when different instances of the same class are persisted in completely different tables.  The "entity name" approach can also perform limited levels of horizontal partitioning as well.   A more comprehensive approach to horizontal partitioning is provided by the Sharding API.

#### Extending Mapper {@name=extending}

Mappers can have functionality augmented or replaced at many points in its execution via the usage of the MapperExtension class.  This class is just a series of "hooks" where various functionality takes place.  An application can make its own MapperExtension objects, overriding only the methods it needs.  Methods that are not overridden return the special value `sqlalchemy.orm.EXT_CONTINUE` to allow processing to continue to the next MapperExtension or simply proceed normally if there are no more extensions.

API documentation for MapperExtension: [docstrings_sqlalchemy.orm_MapperExtension](rel:docstrings_sqlalchemy.orm_MapperExtension)

To use MapperExtension, make your own subclass of it and just send it off to a mapper:

    {python}
    m = mapper(User, users_table, extension=MyExtension())

Multiple extensions will be chained together and processed in order; they are specified as a list:

    {python}
    m = mapper(User, users_table, extension=[ext1, ext2, ext3])

### Relation Configuration {@name=relation}

The full list of options for the `relation()` function:

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

#### Basic Relational Patterns {@name=patterns}

A quick walkthrough of the basic relational patterns.

##### One To Many {@name=onetomany}

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.

    {python}
    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':relation(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:

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

    mapper(Child, child_table)

`Child` will get a `parent` attribute with many-to-one semantics.

##### Many To One {@name=manytoone}

Many to one places a foreign key in the parent table referencing the child.  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.

    {python}
    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),
        )