mappers.txt

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

    mapper(User, users_table, order_by=users_table.c.user_id)
    
    # order by multiple items
    mapper(User, users_table, order_by=[users_table.c.user_id, users_table.c.user_name.desc()])

"order_by" can also be specified with queries, overriding all other per-engine/per-mapper orderings:

    {python}
    # order by a column
    l = query.filter(User.user_name=='fred').order_by(User.user_id).all()
    
    # order by multiple criterion
    l = query.filter(User.user_name=='fred').order_by([User.user_id, User.user_name.desc()])

The "order_by" property can also be specified on a `relation()` which will control the ordering of the collection:

    {python}
    mapper(Address, addresses_table)
    
    # order address objects by address id
    mapper(User, users_table, properties = {
        'addresses' : relation(Address, order_by=addresses_table.c.address_id)
    })
    
Note that when using eager loaders with relations, the tables used by the eager load's join are anonymously aliased.  You can only order by these columns if you specify it at the `relation()` level.  To control ordering at the query level based on a related table, you `join()` to that relation, then order by it:

    {python}
    session.query(User).join('addresses').order_by(Address.street)

#### Mapping Class Inheritance Hierarchies {@name=inheritance}

SQLAlchemy supports three forms of inheritance:  *single table inheritance*, where several types of classes are stored in one table, *concrete table inheritance*, where each type of class is stored in its own table, and *joined table inheritance*, where the parent/child classes are stored in their own tables that are joined together in a select.  Whereas support for single and joined table inheritance is strong, concrete table inheritance is a less common scenario with some particular problems so is not quite as flexible.

When mappers are configured in an inheritance relationship, SQLAlchemy has the ability to load elements "polymorphically", meaning that a single query can return objects of multiple types.

For the following sections, assume this class relationship:

    {python}
    class Employee(object):
        def __init__(self, name):
            self.name = name
        def __repr__(self):
            return self.__class__.__name__ + " " + self.name

    class Manager(Employee):
        def __init__(self, name, manager_data):
            self.name = name
            self.manager_data = manager_data
        def __repr__(self):
            return self.__class__.__name__ + " " + self.name + " " +  self.manager_data

    class Engineer(Employee):
        def __init__(self, name, engineer_info):
            self.name = name
            self.engineer_info = engineer_info
        def __repr__(self):
            return self.__class__.__name__ + " " + self.name + " " +  self.engineer_info

##### Joined Table Inheritance {@name=joined}

In joined table inheritance, each class along a particular classes' list of parents is represented by a unique table.  The total set of attributes for a particular instance is represented as a join along all tables in its inheritance path.  Here, we first define a table to represent the `Employee` class.  This table will contain a primary key column (or columns), and a column for each attribute that's represented by `Employee`.  In this case it's just `name`:

    {python}
    employees = Table('employees', metadata, 
       Column('employee_id', Integer, primary_key=True),
       Column('name', String(50)),
       Column('type', String(30), nullable=False)
    )

The table also has a column called `type`.  It is strongly advised in both single- and joined- table inheritance scenarios that the root table contains a column whose sole purpose is that of the **discriminator**; it stores a value which indicates the type of object represented within the row.  The column may be of any desired datatype.  While there are some "tricks" to work around the requirement that there be a discriminator column, they are more complicated to configure when one wishes to load polymorphically.

Next we define individual tables for each of `Engineer` and `Manager`, which each contain columns that represent the attributes unique to the subclass they represent.  Each table also must contain a primary key column (or columns), and in most cases a foreign key reference to the parent table.  It is  standard practice that the same column is used for both of these roles, and that the column is also named the same as that of the parent table.  However this is optional in SQLAlchemy; separate columns may be used for primary key and parent-relation, the column may be named differently than that of the parent, and even a custom join condition can be specified between parent and child tables instead of using a foreign key.  In joined table inheritance, the primary key of an instance is always represented by the primary key of the base table only (new in SQLAlchemy 0.4).

    {python}
    engineers = Table('engineers', metadata, 
       Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
       Column('engineer_info', String(50)),
    )

    managers = Table('managers', metadata, 
       Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
       Column('manager_data', String(50)),
    )

We then configure mappers as usual, except we use some additional arguments to indicate the inheritance relationship, the polymorphic discriminator column, and the **polymorphic identity** of each class; this is the value that will be stored in the polymorphic discriminator column.

    {python}
    mapper(Employee, employees, polymorphic_on=employees.c.type, polymorphic_identity='employee')
    mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
    mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

And that's it.  Querying against `Employee` will return a combination of `Employee`, `Engineer` and `Manager` objects.

###### Optimizing Joined Table Loads {@name=optimizing}

When loading fresh from the database, the joined-table setup above will query from the parent table first, then for each row will issue a second query to the child table.  For example, for a load of five rows with `Employee` id 3, `Manager` ids 1 and 5 and `Engineer` ids 2 and 4, will produce queries along the lines of this example:

    {python}
    session.query(Employee).all()
    {opensql}
    SELECT employees.employee_id AS employees_employee_id, employees.name AS employees_name, employees.type AS employees_type 
    FROM employees ORDER BY employees.oid
    []
    SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data 
    FROM managers 
    WHERE ? = managers.employee_id
    [5]
    SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info 
    FROM engineers 
    WHERE ? = engineers.employee_id
    [2]
    SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info 
    FROM engineers 
    WHERE ? = engineers.employee_id
    [4]
    SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data 
    FROM managers 
    WHERE ? = managers.employee_id
    [1]

The above query works well for a `get()` operation, since it limits the queries to only the tables directly involved in fetching a single instance.  For instances which are already present in the session, the secondary table load is not needed.  However, the above loading style is not efficient for loading large groups of objects, as it incurs separate queries for each parent row.

One way to reduce the number of "secondary" loads of child rows is to "defer" them, using `polymorphic_fetch='deferred'`:

    {python}
    mapper(Employee, employees, polymorphic_on=employees.c.type, \
        polymorphic_identity='employee', polymorphic_fetch='deferred')
    mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
    mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

The above configuration queries in the same manner as earlier, except the load of each "secondary" table occurs only when attributes referencing those columns are first referenced on the loaded instance.  This style of loading is very efficient for cases where large selects of items occur, but a detailed "drill down" of extra inherited properties is less common.

More commonly, an all-at-once load may be achieved by constructing a query which combines all three tables together, and adding it to the mapper configuration as its `select_table`, which is an arbitrary selectable which the mapper will use for load operations (it has no impact on save operations).  Any selectable can be used for this, such as a UNION of tables.  For joined table inheritance, the easiest method is to use OUTER JOIN:

    {python}
    join = employees.outerjoin(engineers).outerjoin(managers)

    mapper(Employee, employees, polymorphic_on=employees.c.type, \
        polymorphic_identity='employee', select_table=join)
    mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
    mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

Which produces a query like the following:

    {python}
    session.query(Employee).all()
    {opensql}
    SELECT employees.employee_id AS employees_employee_id, engineers.employee_id AS engineers_employee_id, managers.employee_id AS managers_employee_id, employees.name AS employees_name, employees.type AS employees_type, engineers.engineer_info AS engineers_engineer_info, managers.manager_data AS managers_manager_data 
    FROM employees LEFT OUTER JOIN engineers ON employees.employee_id = engineers.employee_id LEFT OUTER JOIN managers ON employees.employee_id = managers.employee_id ORDER BY employees.oid
    []    

##### Single Table Inheritance

Single table inheritance is where the attributes of the base class as well as all subclasses are represented within a single table.  A column is present in the table for every attribute mapped to the base class and all subclasses; the columns which correspond to a single subclass are nullable.  This configuration looks much like joined-table inheritance except there's only one table.  In this case, a `type` column is required, as there would be no other way to discriminate between classes.  The table is specified in the base mapper only; for the inheriting classes, leave their `table` parameter blank:

    {python}
    employees_table = Table('employees', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('manager_data', String(50)),
        Column('engineer_info', String(50)),
        Column('type', String(20), nullable=False)
    )
    
    employee_mapper = mapper(Employee, employees_table, \
        polymorphic_on=employees_table.c.type, polymorphic_identity='employee')
    manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager')
    engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer')

Note that the mappers for the derived classes Manager and Engineer omit the specification of their associated table, as it is inherited from the employee_mapper. Omitting the table specification for derived mappers in single-table inheritance is required.

##### Concrete Table Inheritance

This form of inheritance maps each class to a distinct table, as below:

    {python}
    employees_table = Table('employees', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
    )

    managers_table = Table('managers', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('manager_data', String(50)),
    )

    engineers_table = Table('engineers', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('engineer_info', String(50)),
    )

Notice in this case there is no `type` column.  If polymorphic loading is not required, there's no advantage to using `inherits` here; you just define a separate mapper for each class.

    {python}
    mapper(Employee, employees_table)
    mapper(Manager, managers_table)
    mapper(Engineer, engineers_table)

To load polymorphically, the `select_table` argument is currently required.  In this case we must construct a UNION of all three tables.  SQLAlchemy includes a helper function to create these called `polymorphic_union`, which will map all the different columns into a structure of selects with the same numbers and names of columns, and also generate a virtual `type` column for each subselect:

    {python}
    pjoin = polymorphic_union({
        'employee':employees_table,
        'manager':managers_table,
        'engineer':engineers_table
    }, 'type', 'pjoin')

    employee_mapper = mapper(Employee, employees_table, select_table=pjoin, \
        polymorphic_on=pjoin.c.type, polymorphic_identity='employee')
    manager_mapper = mapper(Manager, managers_table, inherits=employee_mapper, \
        concrete=True, polymorphic_identity='manager')
    engineer_mapper = mapper(Engineer, engineers_table, inherits=employee_mapper, \
        concrete=True, polymorphic_identity='engineer')

Upon select, the polymorphic union produces a query like this:

    {python}
    session.query(Employee).all()
    {opensql}
    SELECT pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.employee_id AS pjoin_employee_id, 
    pjoin.name AS pjoin_name, pjoin.engineer_info AS pjoin_engineer_info 
    FROM (
        SELECT employees.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name, 
        CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type 
        FROM employees 
    UNION ALL 
        SELECT managers.employee_id AS employee_id, managers.manager_data AS manager_data, managers.name AS name, 
        CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type 
        FROM managers 
    UNION ALL 
        SELECT engineers.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name, 
        engineers.engineer_info AS engineer_info, 'engineer' AS type 
        FROM engineers
    ) AS pjoin ORDER BY pjoin.oid
    []

##### Using Relations with Inheritance {@name=relations}

Both joined-table and single table inheritance scenarios produce mappings which are usable in relation() functions; that is, it's possible to map a parent object to a child object which is polymorphic.  Similarly, inheriting mappers can have `relation()`s of their own at any level, which are inherited to each child class.  The only requirement for relations is that there is a table relationship between parent and child.  An example is the following modification to the joined table inheritance example, which sets a bi-directional relationship between `Employee` and `Company`:

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