ormtutorial.txt

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

    {sql}>>> query.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.name LIKE ? ORDER BY users.oid
    ['%ed']
    {stop}[<User('ed','Ed Jones', 'f8s7ccs')>, <User('fred','Fred Flinstone', 'blah')>]

`first()` applies a limit of one and returns the first result as a scalar:

    {python}
    {sql}>>> query.first()
    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 LIKE ? ORDER BY users.oid 
     LIMIT 1 OFFSET 0
    ['%ed']
    {stop}<User('ed','Ed Jones', 'f8s7ccs')>

and `one()`, applies a limit of *two*, and if not exactly one row returned (no more, no less), raises an error:

    {python}
    {sql}>>> try:  
    ...     user = query.one() 
    ... except Exception, e: 
    ...     print e
    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 LIKE ? ORDER BY users.oid 
     LIMIT 2 OFFSET 0
    ['%ed']
    {stop}Multiple rows returned for one()

All `Query` methods that don't return a result instead return a new `Query` object, with modifications applied.  Therefore you can call many query methods successively to build up the criterion you want:

    {python}
    {sql}>>> session.query(User).filter(User.id<2).filter_by(name='ed').\
    ...     filter(User.fullname=='Ed Jones').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 < ? AND users.name = ? AND users.fullname = ? ORDER BY users.oid
    [2, 'ed', 'Ed Jones']
    {stop}[<User('ed','Ed Jones', 'f8s7ccs')>]

If you need to use other conjunctions besides `AND`, all SQL conjunctions are available explicitly within expressions, such as `and_()` and `or_()`, when using `filter()`:

    {python}
    >>> from sqlalchemy import and_, or_
    
    {sql}>>> session.query(User).filter(
    ...    and_(User.id<224, or_(User.name=='ed', User.name=='wendy'))
    ...    ).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 < ? AND (users.name = ? OR users.name = ?) ORDER BY users.oid
    [224, 'ed', 'wendy']
    {stop}[<User('ed','Ed Jones', 'f8s7ccs')>, <User('wendy','Wendy Williams', 'foobar')>]
    
You also have full ability to use literal strings to construct SQL.  For a single criterion, use a string with `filter()`:

    {python}
    {sql}>>> for user in session.query(User).filter("id<224").all():
    ...     print user.name
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users 
    WHERE id<224 ORDER BY users.oid
    []
    {stop}ed
    wendy
    mary
    fred
    
Bind parameters can be specified with string-based SQL, using a colon.  To specify the values, use the `params()` method:

    {python}
    {sql}>>> session.query(User).filter("id<:value and name=:name").\
    ...     params(value=224, name='fred').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 id<? and name=? ORDER BY users.oid 
    LIMIT 2 OFFSET 0
    [224, 'fred']
    {stop}<User('fred','Fred Flinstone', 'blah')>

Note that when we use constructed SQL expressions, bind parameters are generated for us automatically; we don't need to worry about them.
       
To use an entirely string-based statement, using `from_statement()`; just ensure that the columns clause of the statement contains the column names normally used by the mapper (below illustrated using an asterisk):

    {python}
    {sql}>>> session.query(User).from_statement("SELECT * FROM users where name=:name").params(name='ed').all()
    SELECT * FROM users where name=?
    ['ed']
    {stop}[<User('ed','Ed Jones', 'f8s7ccs')>]

`from_statement()` can also accomodate full `select()` constructs.  These are described in the [sql](rel:sql):

    {python}
    >>> from sqlalchemy import select, func
    
    {sql}>>> session.query(User).from_statement(
    ...     select(
    ...            [users_table], 
    ...            select([func.max(users_table.c.name)]).label('maxuser')==users_table.c.name) 
    ...    ).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 (SELECT max(users.name) 
    FROM users) = users.name
    []
    {stop}[<User('wendy','Wendy Williams', 'foobar')>]
    
There's also a way to combine scalar results with objects, using `add_column()`.  This is often used for functions and aggregates.  When `add_column()` (or its cousin `add_entity()`, described later) is used, tuples are returned:

    {python}
    {sql}>>> for r in session.query(User).\
    ...     add_column(select([func.max(users_table.c.name)]).label('maxuser')):
    ...     print r
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, (SELECT max(users.name) 
    FROM users) AS maxuser 
    FROM users ORDER BY users.oid
    []
    {stop}(<User('ed','Ed Jones', 'f8s7ccs')>, u'wendy')
    (<User('wendy','Wendy Williams', 'foobar')>, u'wendy')
    (<User('mary','Mary Contrary', 'xxg527')>, u'wendy')
    (<User('fred','Fred Flinstone', 'blah')>, u'wendy')

## Building a One-to-Many Relation {@name=onetomany}

We've spent a lot of time dealing with just one class, and one table.  Let's now look at how SQLAlchemy deals with two tables, which have a relationship to each other.   Let's say that the users in our system also can store any number of email addresses associated with their username.  This implies a basic one to many association from the `users_table` to a new table which stores email addresses, which we will call `addresses`.  We will also create a relationship between this new table to the users table, using a `ForeignKey`:

    {python}
    >>> from sqlalchemy import ForeignKey
    
    >>> addresses_table = Table('addresses', metadata, 
    ...     Column('id', Integer, primary_key=True),
    ...     Column('email_address', String(100), nullable=False),
    ...     Column('user_id', Integer, ForeignKey('users.id')))
    
Another call to `create_all()` will skip over our `users` table and build just the new `addresses` table:

    {python}
    {sql}>>> metadata.create_all(engine) # doctest: +NORMALIZE_WHITESPACE
    PRAGMA table_info("users")
    {}
    PRAGMA table_info("addresses")
    {}
    CREATE TABLE addresses (
        id INTEGER NOT NULL, 
        email_address VARCHAR(100) NOT NULL, 
        user_id INTEGER, 
        PRIMARY KEY (id), 
         FOREIGN KEY(user_id) REFERENCES users (id)
    )
    {}
    COMMIT

For our ORM setup, we're going to start all over again.  We will first close out our `Session` and clear all `Mapper` objects:

    {python}
    >>> from sqlalchemy.orm import clear_mappers
    >>> session.close()
    >>> clear_mappers()
    
Our `User` class, still around, reverts to being just a plain old class.  Lets create an `Address` class to represent a user's email address:

    {python}
    >>> class Address(object):
    ...     def __init__(self, email_address):
    ...         self.email_address = email_address
    ...
    ...     def __repr__(self):
    ...         return "<Address('%s')>" % self.email_address

Now comes the fun part.  We define a mapper for each class, and associate them using a function called `relation()`.  We can define each mapper in any order we want:

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

Above, the new thing we see is that `User` has defined a relation named `addresses`, which will reference a list of `Address` objects.  How does it know it's a list ?  SQLAlchemy figures it out for you, based on the foreign key relationship between `users_table` and `addresses_table`.  

## Working with Related Objects and Backreferences {@name=relation_backref}

Now when we create a `User`, it automatically has this collection present:

    {python}
    >>> jack = User('jack', 'Jack Bean', 'gjffdd')
    >>> jack.addresses
    []
    
We are free to add `Address` objects, and the `session` will take care of everything for us.

    {python}
    >>> jack.addresses.append(Address(email_address='[e-mail]'))
    >>> jack.addresses.append(Address(email_address='[e-mail]'))
    
Before we save into the `Session`, lets examine one other thing that's happened here.  The `addresses` collection is present on our `User` because we added a `relation()` with that name.  But also within the `relation()` function is the keyword `backref`.  This keyword indicates that we wish to make a **bi-directional relationship**.  What this basically means is that not only did we generate a one-to-many relationship called `addresses` on the `User` class, we also generated a **many-to-one** relationship on the `Address` class.  This relationship is self-updating, without any data being flushed to the database, as we can see on one of Jack's addresses:

    {python}
    >>> jack.addresses[1]
    <Address('[e-mail]')>
    
    >>> jack.addresses[1].user
    <User('jack','Jack Bean', 'gjffdd')>
    
Let's save into the session, then close out the session and create a new one...so that we can see how `Jack` and his email addresses come back to us:

    {python}
    >>> session.save(jack)
    {sql}>>> session.commit()
    BEGIN
    INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
    ['jack', 'Jack Bean', 'gjffdd']
    INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
    ['[e-mail]', 5]
    INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
    ['[e-mail]', 5]
    COMMIT
    
    >>> session = Session()
    
Querying for Jack, we get just Jack back.  No SQL is yet issued for for Jack's addresses:

    {python}
    {sql}>>> jack = session.query(User).filter_by(name='jack').one()
    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.name = ? ORDER BY users.oid 
     LIMIT 2 OFFSET 0
    ['jack']
    
    >>> jack
    <User('jack','Jack Bean', 'gjffdd')>
    
Let's look at the `addresses` collection.  Watch the SQL:

    {python}
    {sql}>>> jack.addresses
    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]')>]
    
When we accessed the `addresses` collection, SQL was suddenly issued.  This is an example of a **lazy loading relation**.

If you want to reduce the number of queries (dramatically, in many cases), we can apply an **eager load** to the query operation.  We clear out the session to ensure that a full reload occurs:

    {python}
    >>> session.clear()
    
Then apply an **option** to the query, indicating that we'd like `addresses` to load "eagerly".  SQLAlchemy then constructs a join between the `users` and `addresses` tables:

    {python}
    >>> from sqlalchemy.orm import eagerload
    
    {sql}>>> jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE
    SELECT anon_1.users_id AS anon_1_users_id, anon_1.users_name AS anon_1_users_name, 
    anon_1.users_fullname AS anon_1_users_fullname, anon_1.users_password AS anon_1_users_password, 
    addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, 
    addresses_1.user_id AS addresses_1_user_id 
        FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, 
        users.password AS users_password, users.oid AS users_oid 
        FROM users 
        WHERE users.name = ? ORDER BY users.oid 
         LIMIT 2 OFFSET 0) AS anon_1 LEFT OUTER JOIN addresses AS addresses_1 
         ON anon_1.users_id = addresses_1.user_id ORDER BY anon_1.oid, addresses_1.oid
        ['jack']
    
    >>> jack
    <User('jack','Jack Bean', 'gjffdd')>
    
    >>> jack.addresses
    [<Address('[e-mail]')>, <Address('[e-mail]')>]
    
If you think that query is elaborate, it is !  But SQLAlchemy is just getting started.  Note that when using eager loading, *nothing* changes as far as the ultimate results returned.  The "loading strategy", as it's called, is designed to be completely transparent in all cases, and is for optimization purposes only.  Any query criterion you use to load objects, including ordering, limiting, other joins, etc., should return identical results regardless of the combination of lazily- and eagerly- loaded relationships present.