tutorial.txt

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

### Selecting

Let's check that the data we have put into `users` table is actually there. The procedure is analogous to the insert example above, except you now call the `select()` method off the `users` table:

    {python}
    >>> s = users_table.select()
    >>> print s
    SELECT users.user_id, users.user_name, users.password 
    FROM users
    >>> r = s.execute()
    SELECT users.user_id, users.user_name, users.password 
    FROM users
    []
    
This time, we won't ignore the return value of `execute()`.  Its an instance of `ResultProxy`, which is a result-holding object that behaves very similarly to the `cursor` object one deals with directly with a database API:

    {python}
    >>> r # doctest:+ELLIPSIS
    <sqlalchemy.engine.base.ResultProxy object at 0x...>
    >>> r.fetchone()
    (1, u'Mary', u'secure')
    >>> r.fetchall()
    [(2, u'Tom', None), (3, u'Fred', None), (4, u'Harry', None)]

Query criterion for the select is specified using Python expressions, using the `Column` objects in the `Table` as a base.  All expressions constructed from `Column` objects are themselves instances of `ClauseElements`, just like the `Select`, `Insert`, and `Table` objects themselves.

    {python}
    >>> r = users_table.select(users_table.c.user_name=='Harry').execute()
    SELECT users.user_id, users.user_name, users.password 
    FROM users 
    WHERE users.user_name = ?
    ['Harry']
    >>> row = r.fetchone()
    >>> print row
    (4, u'Harry', None)
    
Pretty much the full range of standard SQL operations are supported as constructed Python expressions, including joins, ordering, grouping, functions, correlated subqueries, unions, etc. Documentation on selecting: [sql_select](rel:sql_select).

### Working with Rows

You can see that when we print out the rows returned by an execution result, it prints the rows as tuples.  These rows support both the list and dictionary interfaces.  The dictionary interface allows the addressing of columns by string column name, or even the original `Column` object:

    {python}
    >>> row.keys()
    [u'user_id', u'user_name', u'password']
    >>> row['user_id'], row[1], row[users_table.c.password] 
    (4, u'Harry', None)

Addressing the columns in a row based on the original `Column` object is especially handy, as it eliminates the need to work with literal column names altogether.

Result sets also support iteration.  We'll show this with a slightly different form of `select` that allows you to specify the specific columns to be selected:

    {python}
    >>> for row in select([users_table.c.user_id, users_table.c.user_name]).execute(): # doctest:+NORMALIZE_WHITESPACE
    ...     print row
    SELECT users.user_id, users.user_name
    FROM users
    []
    (1, u'Mary')
    (2, u'Tom')
    (3, u'Fred')
    (4, u'Harry')

### Table Relationships {@name=table_relationships}

Lets create a second table, `email_addresses`, which references the `users` table.  To define the relationship between the two tables, we will use the `ForeignKey` construct.  We will also issue the `CREATE` statement for the table:

    {python}
    >>> email_addresses_table = Table('email_addresses', metadata,
    ...     Column('address_id', Integer, primary_key=True),
    ...     Column('email_address', String(100), nullable=False),
    ...     Column('user_id', Integer, ForeignKey('users.user_id')))
    >>> email_addresses_table.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    CREATE TABLE email_addresses (
        address_id INTEGER NOT NULL,
        email_address VARCHAR(100) NOT NULL,
        user_id INTEGER,
        PRIMARY KEY (address_id),
        FOREIGN KEY(user_id) REFERENCES users (user_id)
    )
    ...

Above, the `email_addresses` table is related to the `users` table via the `ForeignKey('users.user_id')`.  The `ForeignKey` constructor can take a `Column` object or a string representing the table and column name.  When using the string argument, the referenced table must exist within the same `MetaData` object; thats where it looks for the other table!

Next, lets put a few rows in:

    {python}
    >>> email_addresses_table.insert().execute(
    ...     {'email_address':'[e-mail]', 'user_id':2},
    ...     {'email_address':'[e-mail]', 'user_id':1}) #doctest:+ELLIPSIS
    INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
    [['[e-mail]', 2], ['[e-mail]', 1]]
    COMMIT
    <sqlalchemy.engine.base.ResultProxy object at 0x...>

With two related tables, we can now construct a join amongst them using the `join` method:

    {python}
    >>> r = users_table.join(email_addresses_table).select(order_by=users_table.c.user_id).execute()
    SELECT users.user_id, users.user_name, users.password, email_addresses.address_id, email_addresses.email_address, email_addresses.user_id 
    FROM users JOIN email_addresses ON users.user_id = email_addresses.user_id ORDER BY users.user_id
    []
    >>> print [row for row in r]
    [(1, u'Mary', u'secure', 2, u'[e-mail]', 1), (2, u'Tom', None, 1, u'[e-mail]', 2)]
    
The `join` method is also a standalone function in the `sqlalchemy` namespace.  The join condition is figured out from the foreign keys of the Table objects given.  The condition (also called the "ON clause") can be specified explicitly, such as in this example which creates a join representing all users that used their email address as their password:

    {python}
    >>> print join(users_table, email_addresses_table, 
    ...     and_(users_table.c.user_id==email_addresses_table.c.user_id, 
    ...     users_table.c.password==email_addresses_table.c.email_address)
    ...     )
    users JOIN email_addresses ON users.user_id = email_addresses.user_id AND users.password = email_addresses.email_address

Working with Object Mappers {@name=orm}
-----------------------------------------------

Now that we have a little bit of Table and SQL operations covered, lets look into SQLAlchemy's ORM (object relational mapper).  With the ORM, you associate Tables (and other *Selectable* units, like queries and table aliases) with Python classes, into units called **Mappers**.  Then you can execute queries that return lists of object instances, instead of result sets.  The object instances themselves are associated with an object called a **Session**, which automatically tracks changes on each object and supports a "save all at once" operation called a **flush**.

To start, we will import the names necessary to use SQLAlchemy's ORM, again using `import *` for simplicities sake, even though we all know that in real life we should be importing individual names via "`from sqlalchemy.orm import symbol1, symbol2, ...`" or "`import sqlalchemy.orm as orm`":

    {python}
    >>> from sqlalchemy.orm import *
    
It should be noted that the above step is technically not needed when working with the 0.3 series of SQLAlchemy; all symbols from the `orm` package are also included in the `sqlalchemy` package.  However, a future release (most likely the 0.4 series) will make the separate `orm` import required in order to use the object relational mapper, so it's a good practice for now.

### Creating a Mapper {@name=mapper}

A Mapper is usually created once per Python class, and at its core primarily means to say, "objects of this class are to be stored as rows in this table".  Lets create a class called `User`, which will represent a user object that is stored in our `users` table:

    {python}
    >>> class User(object):
    ...     def __repr__(self):
    ...        return "%s(%r,%r)" % (
    ...            self.__class__.__name__, self.user_name, self.password)
    
The class is a new style class (i.e. it extends `object`) and does not require a constructor (although one may be provided if desired).  We just have one `__repr__` method on it which will display basic information about the User.  Note that the `__repr__` method references the instance variables `user_name` and `password` which otherwise aren't defined.  While we are free to explicitly define these attributes and treat them normally, this is optional; as SQLAlchemy's `Mapper` construct will manage them for us, since their names correspond to the names of columns in the `users` table.  Lets create a mapper, and observe that these attributes are now defined:

    {python}
    >>> mapper(User, users_table) # doctest: +ELLIPSIS
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    >>> u1 = User()
    >>> print u1.user_name
    None
    >>> print u1.password
    None
    
The `mapper` function returns a new instance of `Mapper`.  As it is the first Mapper we have created for the `User` class, it is known as the classes' *primary mapper*.  We generally don't need to hold onto the return value of the `mapper` function; SA can automatically locate this Mapper as needed when it deals with the `User` class.  

### Obtaining a Session {@name=session}

After you create a Mapper, all operations with that Mapper require the usage of an important object called a `Session`.  All objects loaded or saved by the Mapper must be *attached* to a `Session` object, which represents a kind of "workspace" of objects that are loaded into memory.  A particular object instance can only be attached to one `Session` at a time (but of course can be moved around or detached altogether).

By default, you have to create a `Session` object explicitly before you can load or save objects.  Theres several ways to manage sessions, but the most straightforward is to just create one, which we will do by saying, `create_session()`:

    {python}
    >>> session = create_session()
    >>> session # doctest:+ELLIPSIS
    <sqlalchemy.orm.session.Session object at 0x...>

### The Query Object {@name=query}

The Session has all kinds of methods on it to manage and inspect its collection of objects.  The Session also provides an easy interface which can be used to query the database, by giving you an instance to a `Query` object corresponding to a particular Python class:

    {python}
    >>> query = session.query(User)
    >>> print query.filter_by(user_name='Harry').all()
    SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, users.password AS users_password 
    FROM users 
    WHERE users.user_name = ? ORDER BY users.oid
    ['Harry']
    [User(u'Harry',None)]
    
All querying for objects is performed via an instance of `Query`.  The various `select` methods on an instance of `Mapper` also use an underlying `Query` object to perform the operation.  A `Query` is always bound to a specific `Session`.