ormtutorial.txt

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

Let's try to delete `jack` and see how that goes.  We'll mark as deleted in the session, then we'll issue a `count` query to see that no rows remain:

    {python}
    >>> session.delete(jack)
    {sql}>>> session.query(User).filter_by(name='jack').count()
    UPDATE addresses SET user_id=? WHERE addresses.id = ?
    [None, 1]
    UPDATE addresses SET user_id=? WHERE addresses.id = ?
    [None, 2]
    DELETE FROM users WHERE users.id = ?
    [5]
    SELECT count(users.id) 
    FROM users 
    WHERE users.name = ?
    ['jack']
    {stop}0
    
So far, so good.  How about Jack's `Address` objects ?

    {python}
    {sql}>>> session.query(Address).filter(
    ...     Address.email_address.in_(['[e-mail]', '[e-mail]'])
    ...  ).count()
    SELECT count(addresses.id) 
    FROM addresses 
    WHERE addresses.email_address IN (?, ?)
    ['[e-mail]', '[e-mail]']
    {stop}2
    
Uh oh, they're still there !  Analyzing the flush SQL, we can see that the `user_id` column of each address was set to NULL, but the rows weren't deleted.  SQLAlchemy doesn't assume that deletes cascade, you have to tell it so.

So let's rollback our work, and start fresh with new mappers that express the relationship the way we want:

    {python}
    {sql}>>> session.rollback()  # roll back the transaction
    ROLLBACK
    
    >>> session.clear() # clear the session
    >>> clear_mappers() # clear mappers
    
We need to tell the `addresses` relation on `User` that we'd like session.delete() operations to cascade down to the child `Address` objects.  Further, we also want `Address` objects which get detached from their parent `User`, whether or not the parent is deleted, to be deleted.  For these behaviors we use two **cascade options** `delete` and `delete-orphan`, using the string-based `cascade` option to the `relation()` function:

    {python}
    >>> mapper(User, users_table, properties={    # doctest: +ELLIPSIS
    ...     'addresses':relation(Address, backref='user', cascade="all, delete, delete-orphan")
    ... })
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    
    >>> mapper(Address, addresses_table) # doctest: +ELLIPSIS
    <sqlalchemy.orm.mapper.Mapper object at 0x...>

Now when we load Jack, removing an address from his `addresses` collection will result in that `Address` being deleted:

    {python}
    # load Jack by primary key
    {sql}>>> jack = session.query(User).get(jack.id)    #doctest: +NORMALIZE_WHITESPACE
    BEGIN
    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 = ?
    [5]
    {stop}
    
    # remove one Address (lazy load fires off)
    {sql}>>> del jack.addresses[1]  
    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}
    
    # only one address remains
    {sql}>>> session.query(Address).filter(
    ...     Address.email_address.in_(['[e-mail]', '[e-mail]'])
    ... ).count()
    DELETE FROM addresses WHERE addresses.id = ?
    [2]
    SELECT count(addresses.id) 
    FROM addresses 
    WHERE addresses.email_address IN (?, ?)
    ['[e-mail]', '[e-mail]']
    {stop}1
    
Deleting Jack will delete both Jack and his remaining `Address`:

    {python}
    >>> session.delete(jack)
    
    {sql}>>> session.commit()
    DELETE FROM addresses WHERE addresses.id = ?
    [1]
    DELETE FROM users WHERE users.id = ?
    [5]
    COMMIT
    {stop}
    
    {sql}>>> session.query(User).filter_by(name='jack').count()
    BEGIN
    SELECT count(users.id) 
    FROM users 
    WHERE users.name = ?
    ['jack']
    {stop}0
    
    {sql}>>> session.query(Address).filter(
    ...    Address.email_address.in_(['[e-mail]', '[e-mail]'])
    ... ).count()
    SELECT count(addresses.id) 
    FROM addresses 
    WHERE addresses.email_address IN (?, ?)
    ['[e-mail]', '[e-mail]']
    {stop}0

## Building a Many To Many Relation {@name=manytomany}

We're moving into the bonus round here, but lets show off a many-to-many relationship.  We'll sneak in some other features too, just to take a tour.  We'll make our application a blog application, where users can write `BlogPost`s, which have `Keywords` associated with them.

First some new tables:

    {python}
    >>> post_table = Table('posts', metadata, 
    ...        Column('id', Integer, primary_key=True),
    ...        Column('user_id', Integer, ForeignKey('users.id')),
    ...        Column('headline', String(255), nullable=False),
    ...        Column('body', String)
    ...        )
    
    >>> post_keywords = Table('post_keywords', metadata,
    ...        Column('post_id', Integer, ForeignKey('posts.id')),
    ...        Column('keyword_id', Integer, ForeignKey('keywords.id')))
    
    >>> keywords_table = Table('keywords', metadata,
    ...        Column('id', Integer, primary_key=True),
    ...        Column('keyword', String(50), nullable=False, unique=True))
    
    {sql}>>> metadata.create_all(engine) # doctest: +NORMALIZE_WHITESPACE
    PRAGMA table_info("users")
    {}
    PRAGMA table_info("addresses")
    {}
    PRAGMA table_info("posts")
    {}
    PRAGMA table_info("keywords")
    {}
    PRAGMA table_info("post_keywords")
    {}
    CREATE TABLE posts (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        headline VARCHAR(255) NOT NULL, 
        body TEXT, 
        PRIMARY KEY (id), 
         FOREIGN KEY(user_id) REFERENCES users (id)
    )
    {}
    COMMIT
    CREATE TABLE keywords (
        id INTEGER NOT NULL, 
        keyword VARCHAR(50) NOT NULL, 
        PRIMARY KEY (id), 
         UNIQUE (keyword)
    )
    {}
    COMMIT
    CREATE TABLE post_keywords (
        post_id INTEGER, 
        keyword_id INTEGER, 
         FOREIGN KEY(post_id) REFERENCES posts (id), 
         FOREIGN KEY(keyword_id) REFERENCES keywords (id)
    )
    {}
    COMMIT

Then some classes:

    {python}
    >>> class BlogPost(object):
    ...     def __init__(self, headline, body, author):
    ...         self.author = author
    ...         self.headline = headline
    ...         self.body = body
    ...     def __repr__(self):
    ...         return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)
    
    >>> class Keyword(object):
    ...     def __init__(self, keyword):
    ...         self.keyword = keyword
            
And the mappers.  `BlogPost` will reference `User` via its `author` attribute:

    {python}
    >>> from sqlalchemy.orm import backref
    
    >>> mapper(Keyword, keywords_table) # doctest: +ELLIPSIS
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    
    >>> mapper(BlogPost, post_table, properties={   # doctest: +ELLIPSIS
    ...    'author':relation(User, backref=backref('posts', lazy='dynamic')),
    ...    'keywords':relation(Keyword, secondary=post_keywords)
    ... }) 
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    
There's three new things in the above mapper:

  * the `User` relation has a backref, like we've used before, except this time it references a function called `backref()`.  This function is used when yo'd like to specify keyword options for the backwards relationship.
  * the keyword option we specified to `backref()` is `lazy="dynamic"`.  This sets a default **loader strategy** on the attribute, in this case a special strategy that allows partial loading of results.
  * The `keywords` relation uses a keyword argument `secondary` to indicate the **association table** for the many to many relationship from `BlogPost` to `Keyword`.
    
Usage is not too different from what we've been doing.  Let's give Wendy some blog posts:

    {python}
    {sql}>>> wendy = session.query(User).filter_by(name='wendy').one()
    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
    ['wendy']
    
    >>> post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
    >>> session.save(post)
    
We're storing keywords uniquely in the database, but we know that we don't have any yet, so we can just create them:

    {python}
    >>> post.keywords.append(Keyword('wendy'))
    >>> post.keywords.append(Keyword('firstpost'))
    
We can now look up all blog posts with the keyword 'firstpost'.   We'll use a special collection operator `any` to locate "blog posts where any of its keywords has the keyword string 'firstpost'":

    {python}
    {sql}>>> session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all()
    INSERT INTO keywords (keyword) VALUES (?)
    ['wendy']
    INSERT INTO keywords (keyword) VALUES (?)
    ['firstpost']
    INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
    [2, "Wendy's Blog Post", 'This is a test']
    INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
    [[1, 1], [1, 2]]
    SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
    FROM posts 
    WHERE EXISTS (SELECT 1 
    FROM post_keywords, keywords 
    WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?) ORDER BY posts.oid
    ['firstpost']
    {stop}[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]
    
If we want to look up just Wendy's posts, we can tell the query to narrow down to her as a parent:

    {python}
    {sql}>>> session.query(BlogPost).with_parent(wendy).\
    ... filter(BlogPost.keywords.any(keyword='firstpost')).all()
    SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
    FROM posts 
    WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
    FROM post_keywords, keywords 
    WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?)) ORDER BY posts.oid
    [2, 'firstpost']
    {stop}[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

Or we can use Wendy's own `posts` relation, which is a "dynamic" relation, to query straight from there:

    {python}
    {sql}>>> wendy.posts.filter(BlogPost.keywords.any(keyword='firstpost')).all()
    SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
    FROM posts 
    WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
    FROM post_keywords, keywords 
    WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?)) ORDER BY posts.oid
    [2, 'firstpost']
    {stop}[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

## Further Reference 

Generated Documentation for Query: [docstrings_sqlalchemy.orm.query_Query](rel:docstrings_sqlalchemy.orm.query_Query)

ORM Generated Docs: [docstrings_sqlalchemy.orm](rel:docstrings_sqlalchemy.orm)

Further information on mapping setups are in [advdatamapping](rel:advdatamapping).

Further information on working with Sessions: [unitofwork](rel:unitofwork).