ormtutorial.txt

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

An eagerload targeting across multiple relations can use dot separated names:

    {python}
    query.options(eagerload('orders'), eagerload('orders.items'), eagerload('orders.items.keywords'))
    
To roll up the above three individual `eagerload()` calls into one, use `eagerload_all()`:

    {python}
    query.options(eagerload_all('orders.items.keywords'))
    
## Querying with Joins {@name=joins}

Which brings us to the next big topic.  What if we want to create joins that *do* change the results ?  For that, another `Query` tornado is coming....

One way to join two tables together is just to compose a SQL expression.   Below we make one up using the `id` and `user_id` attributes on our mapped classes:

    {python}
    {sql}>>> session.query(User).filter(User.id==Address.user_id).\
    ...         filter(Address.email_address=='[e-mail]').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users, addresses 
    WHERE users.id = addresses.user_id AND addresses.email_address = ? ORDER BY users.oid
    ['[e-mail]']
    {stop}[<User('jack','Jack Bean', 'gjffdd')>]

Or we can make a real JOIN construct; below we use the `join()` function available on `Table` to create a `Join` object, then tell the `Query` to use it as our FROM clause:

    {python}
    {sql}>>> session.query(User).select_from(users_table.join(addresses_table)).\
    ...         filter(Address.email_address=='[e-mail]').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users JOIN addresses ON users.id = addresses.user_id 
    WHERE addresses.email_address = ? ORDER BY users.oid
    ['[e-mail]']
    {stop}[<User('jack','Jack Bean', 'gjffdd')>]

Note that the `join()` construct has no problem figuring out the correct join condition between `users_table` and `addresses_table`..the `ForeignKey` we constructed says it all.

The easiest way to join is automatically, using the `join()` method on `Query`.  Just give this method the path from A to B, using the name of a mapped relationship directly:

    {python}
    {sql}>>> session.query(User).join('addresses').\
    ...     filter(Address.email_address=='[e-mail]').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users JOIN addresses ON users.id = addresses.user_id 
    WHERE addresses.email_address = ? ORDER BY users.oid
    ['[e-mail]']
    {stop}[<User('jack','Jack Bean', 'gjffdd')>]

By "A to B", we mean a single relation name or a path of relations.  In our case we only have `User->addresses->Address` configured, but if we had a setup like `A->bars->B->bats->C->widgets->D`, a join along all four entities would look like:

    {python}
    session.query(Foo).join(['bars', 'bats', 'widgets']).filter(...)
    
Each time `join()` is called on `Query`, the **joinpoint** of the query is moved to be that of the endpoint of the join.  As above, when we joined from `users_table` to `addresses_table`, all subsequent criterion used by `filter_by()` are against the `addresses` table.  When you `join()` again, the joinpoint starts back from the root.  We can also backtrack to the beginning explicitly using `reset_joinpoint()`.  This instruction will place the joinpoint back at the root `users` table, where subsequent `filter_by()` criterion are again against `users`:

    {python}
    {sql}>>> session.query(User).join('addresses').\
    ...     filter_by(email_address='[e-mail]').\
    ...     reset_joinpoint().filter_by(name='jack').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users JOIN addresses ON users.id = addresses.user_id 
    WHERE addresses.email_address = ? AND users.name = ? ORDER BY users.oid
    ['[e-mail]', 'jack']
    {stop}[<User('jack','Jack Bean', 'gjffdd')>]

In all cases, we can get the `User` and the matching `Address` objects back at the same time, by telling the session we want both.  This returns the results as a list of tuples:

    {python}
    {sql}>>> session.query(User).add_entity(Address).join('addresses').\
    ...     filter(Address.email_address=='[e-mail]').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
    FROM users JOIN addresses ON users.id = addresses.user_id 
    WHERE addresses.email_address = ? ORDER BY users.oid
    ['[e-mail]']
    {stop}[(<User('jack','Jack Bean', 'gjffdd')>, <Address('[e-mail]')>)]

Another common scenario is the need to join on the same table more than once.  For example, if we want to find a `User` who has two distinct email addresses, both `[e-mail]` as well as `[e-mail]`, we need to join to the `Addresses` table twice.  SQLAlchemy does provide `Alias` objects which can accomplish this; but far easier is just to tell `join()` to alias for you:

    {python}
    {sql}>>> session.query(User).\
    ...     join('addresses', aliased=True).filter(Address.email_address=='[e-mail]').\
    ...     join('addresses', aliased=True).filter(Address.email_address=='[e-mail]').all()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id 
    WHERE addresses_1.email_address = ? AND addresses_2.email_address = ? ORDER BY users.oid
    ['[e-mail]', '[e-mail]']
    {stop}[<User('jack','Jack Bean', 'gjffdd')>]

The key thing which occurred above is that our SQL criterion were **aliased** as appropriate corresponding to the alias generated in the most recent `join()` call.

The next section describes some "higher level" operators, including `any()` and `has()`, which make patterns like joining to multiple aliases unnecessary in most cases.

### Relation Operators

A summary of all operators usable on relations:

* Filter on explicit column criterion, combined with a join.  Column criterion can make usage of all supported SQL operators and expression constructs:

        {python}
        {sql}>>> session.query(User).join('addresses').\
        ...    filter(Address.email_address=='[e-mail]').all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users JOIN addresses ON users.id = addresses.user_id 
        WHERE addresses.email_address = ? ORDER BY users.oid
        ['[e-mail]']
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

    Criterion placed in `filter()` usually correspond to the last `join()` call; if the join was specified with `aliased=True`, class-level criterion against the join's target (or targets) will be appropriately aliased as well.  

        {python}
        {sql}>>> session.query(User).join('addresses', aliased=True).\
        ...    filter(Address.email_address=='[e-mail]').all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
        WHERE addresses_1.email_address = ? ORDER BY users.oid
        ['[e-mail]']
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

* Filter_by on key=value criterion, combined with a join.  Same as `filter()` on column criterion except keyword arguments are used.

        {python}
        {sql}>>> session.query(User).join('addresses').\
        ...    filter_by(email_address='[e-mail]').all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users JOIN addresses ON users.id = addresses.user_id 
        WHERE addresses.email_address = ? ORDER BY users.oid
        ['[e-mail]']
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]
    
* Filter on explicit column criterion using `any()` (for collections) or `has()` (for scalar relations).  This is a more succinct method than joining, as an `EXISTS` subquery is generated automatically.  `any()` means, "find all parent items where any child item of its collection meets this criterion":

        {python}
        {sql}>>> session.query(User).\
        ...    filter(User.addresses.any(Address.email_address=='[e-mail]')).all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE EXISTS (SELECT 1 
        FROM addresses 
        WHERE users.id = addresses.user_id AND addresses.email_address = ?) ORDER BY users.oid
        ['[e-mail]']
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

    `has()` means, "find all parent items where the child item meets this criterion":

        {python}
        {sql}>>> session.query(Address).\
        ...    filter(Address.user.has(User.name=='jack')).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE EXISTS (SELECT 1 
        FROM users 
        WHERE users.id = addresses.user_id AND users.name = ?) ORDER BY addresses.oid
        ['jack']
        {stop}[<Address('[e-mail]')>, <Address('[e-mail]')>]

    Both `has()` and `any()` also accept keyword arguments which are interpreted against the child classes' attributes:

        {python}
        {sql}>>> session.query(User).\
        ...    filter(User.addresses.any(email_address='[e-mail]')).all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE EXISTS (SELECT 1 
        FROM addresses 
        WHERE users.id = addresses.user_id AND addresses.email_address = ?) ORDER BY users.oid
        ['[e-mail]']
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]
    
* Filter_by on instance identity criterion.  When comparing to a related instance, `filter_by()` will in most cases not need to reference the child table, since a child instance already contains enough information with which to generate criterion against the parent table.  `filter_by()` uses an equality comparison for all relationship types.  For many-to-one and one-to-one, this represents all objects which reference the given child object:

        {python}
        # locate a user
        {sql}>>> user = session.query(User).filter(User.name=='jack').one() #doctest: +NORMALIZE_WHITESPACE
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE users.name = ? ORDER BY users.oid 
        LIMIT 2 OFFSET 0
        ['jack']
        {stop}
        
        # use the user in a filter_by() expression
        {sql}>>> session.query(Address).filter_by(user=user).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE ? = addresses.user_id ORDER BY addresses.oid
        [5]
        {stop}[<Address('[e-mail]')>, <Address('[e-mail]')>]

    For one-to-many and many-to-many, it represents all objects which contain the given child object in the related collection:

        {python}
        # locate an address
        {sql}>>> address = session.query(Address).\
        ...    filter(Address.email_address=='[e-mail]').one() #doctest: +NORMALIZE_WHITESPACE
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE addresses.email_address = ? ORDER BY addresses.oid 
        LIMIT 2 OFFSET 0
        {stop}['[e-mail]']
    
        # use the address in a filter_by expression
        {sql}>>> session.query(User).filter_by(addresses=address).all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE users.id = ? ORDER BY users.oid
        [5]
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

* Select instances with a particular parent.  This is the "reverse" operation of filtering by instance identity criterion; the criterion is against a relation pointing *to* the desired class, instead of one pointing *from* it.  This will utilize the same "optimized" query criterion, usually not requiring any joins:

        {python}
        {sql}>>> session.query(Address).with_parent(user, property='addresses').all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE ? = addresses.user_id ORDER BY addresses.oid
        [5]
        {stop}[<Address('[e-mail]')>, <Address('[e-mail]')>]
        
* Filter on a many-to-one/one-to-one instance identity criterion.  The class-level `==` operator will act the same as `filter_by()` for a scalar relation:

        {python}
        {sql}>>> session.query(Address).filter(Address.user==user).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE ? = addresses.user_id ORDER BY addresses.oid
        [5]
        {stop}[<Address('[e-mail]')>, <Address('[e-mail]')>]

    whereas the `!=` operator will generate a negated EXISTS clause:

        {python}
        {sql}>>> session.query(Address).filter(Address.user!=user).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE NOT (EXISTS (SELECT 1 
        FROM users 
        WHERE users.id = addresses.user_id AND users.id = ?)) ORDER BY addresses.oid
        [5]
        {stop}[]

    a comparison to `None` also generates an IS NULL clause for a many-to-one relation:

        {python}
        {sql}>>> session.query(Address).filter(Address.user==None).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE addresses.user_id IS NULL ORDER BY addresses.oid
        []
        {stop}[]

* Filter on a one-to-many instance identity criterion.  The `contains()` operator returns all parent objects which contain the given object as one of its collection members:

        {python}
        {sql}>>> session.query(User).filter(User.addresses.contains(address)).all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE users.id = ? ORDER BY users.oid
        [5]
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

* Filter on a multiple one-to-many instance identity criterion.  The `==` operator can be used with a collection-based attribute against a list of items, which will generate multiple `EXISTS` clauses:

        {python}
        {sql}>>> addresses = session.query(Address).filter(Address.user==user).all()
        SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
        FROM addresses 
        WHERE ? = addresses.user_id ORDER BY addresses.oid
        [5]
        {stop}
        
        {sql}>>> session.query(User).filter(User.addresses == addresses).all()
        SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
        FROM users 
        WHERE (EXISTS (SELECT 1 
        FROM addresses 
        WHERE users.id = addresses.user_id AND addresses.id = ?)) AND (EXISTS (SELECT 1 
        FROM addresses 
        WHERE users.id = addresses.user_id AND addresses.id = ?)) ORDER BY users.oid
        [1, 2]
        {stop}[<User('jack','Jack Bean', 'gjffdd')>]

## Deleting