tutorial.txt

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

Lets turn off the database echoing for a moment, and try out a few methods on `Query`.  The two methods used to narrow results are `filter()` and `filter_by()`, and the two most common methods used to load results are `all()` and `first()`.   The `get()` method is used for a quick lookup by primary key.  `filter_by()` works with keyword arguments, and `filter()` works with `ClauseElement` objects, which are constructed by using `Column` objects inside of Python expressions, in the same way as we did with our SQL select example in the previous section of this tutorial.  Using `ClauseElement` structures to query objects is more verbose but more flexible:

    {python}
    >>> metadata.bind.echo = False
    >>> print query.filter(User.c.user_id==3).all()
    [User(u'Fred',None)]
    >>> print query.get(2)
    User(u'Tom',None)
    >>> print query.filter_by(user_name='Mary').first()
    User(u'Mary',u'secure')
    >>> print query.filter(User.c.password==None).first()
    User(u'Tom',None)
    >>> print query.count()
    4

Notice that our `User` class has a special attribute `c` attached to it.  This 'c' represents the columns on the User's mapper's Table object.  Saying `User.c.user_name` is synonymous with saying `users_table.c.user_name`, recalling that `User` is the Python class and `users_table` is our `Table` object.

### Making Changes {@name=changes}

With a little experience in loading objects, lets see what it's like to make changes.  First, lets create a new user "Ed".  We do this by just constructing the new object.  Then, we just add it to the session:

    {python}
    >>> ed = User()
    >>> ed.user_name = 'Ed'
    >>> ed.password = 'edspassword'
    >>> session.save(ed)
    >>> ed in session
    True

Lets also make a few changes on some of the objects in the database.  We will load them with our `Query` object, and then change some things.

    {python}
    >>> mary = query.filter_by(user_name='Mary').first()
    >>> harry = query.filter_by(user_name='Harry').first()
    >>> mary.password = 'marysnewpassword'
    >>> harry.password = 'harrysnewpassword'
    
At the moment, nothing has been saved to the database; all of our changes are in memory only.  What happens if some other part of the application also tries to load 'Mary' from the database and make some changes before we had a chance to save it ?  Assuming that the same `Session` is used, loading 'Mary' from the database a second time will issue a second query in order locate the primary key of 'Mary', but will *return the same object instance as the one already loaded*.  This behavior is due to an important property of the `Session` known as the **identity map**:

    {python}
    >>> mary2 = query.filter_by(user_name='Mary').first()
    >>> mary is mary2
    True
    
With the identity map, a single `Session` can be relied upon to keep all loaded instances straight.

As far as the issue of the same object being modified in two different Sessions, that's an issue of concurrency detection; SQLAlchemy does some basic concurrency checks when saving objects, with the option for a stronger check using version ids.  See [advdatamapping_arguments](rel:advdatamapping_arguments) for more details.

### Saving {@name=saving}

With a new user "ed" and some changes made on "Mary" and "Harry", lets also mark "Fred" as deleted:

    {python}
    >>> fred = query.filter_by(user_name='Fred').first()
    >>> session.delete(fred)
    
Then to send all of our changes to the database, we `flush()` the Session.  Lets turn echo back on to see this happen!:

    {python}
    >>> metadata.bind.echo = True
    >>> session.flush()
    BEGIN
    UPDATE users SET password=? WHERE users.user_id = ?
    ['marysnewpassword', 1]
    UPDATE users SET password=? WHERE users.user_id = ?
    ['harrysnewpassword', 4]
    INSERT INTO users (user_name, password) VALUES (?, ?)
    ['Ed', 'edspassword']
    DELETE FROM users WHERE users.user_id = ?
    [3]
    COMMIT

### Relationships

When our User object contains relationships to other kinds of information, such as a list of email addresses, we can indicate this by using a function when creating the `Mapper` called `relation()`.  While there is a lot you can do with relations, we'll cover a simple one here.  First, recall that our `users` table has a foreign key relationship to another table called `email_addresses`.  A single row in `email_addresses` has a column `user_id` that references a row in the `users` table; since many rows in the `email_addresses` table can reference a single row in `users`, this is called a *one to many* relationship.

To illustrate this relationship, we will start with a new mapper configuration.  Since our `User` class has a mapper assigned to it, we want to discard it and start over again.  So we issue the `clear_mappers()` function first, which removes all mapping associations from classes:

    {python}
    >>> clear_mappers()
    
When removing mappers, it is usually best to remove all mappings at the same time, since mappers usually have relationships to each other which will become invalid if only part of the mapper collection is removed.  In practice, a particular mapping setup will usually remain throughout the lifetime of an application.  Clearing out the mappers and making new ones is a practice that is generally limited to writing mapper unit tests and experimenting from the console.
    
Next, we want to create a class/mapping that corresponds to the `email_addresses` table.  We will create a new class `Address` which represents a single row in the `email_addresses` table, and a corresponding `Mapper` which will associate the `Address` class with the `email_addresses` table:

    {python}
    >>> class Address(object):
    ...     def __init__(self, email_address):
    ...         self.email_address = email_address
    ...     def __repr__(self):
    ...         return "%s(%r)" % (
    ...            self.__class__.__name__, self.email_address)    
    >>> mapper(Address, email_addresses_table) # doctest: +ELLIPSIS
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    
We then create a mapper for the `User` class which contains a relationship to the `Address` class using the `relation()` function:

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

Since we've made new mappers, we have to throw away the old `Query` object and get a new one:

    >>> query = session.query(User)
    
The `relation()` function takes either a class or a Mapper as its first argument, and has many options to further control its behavior.  When this mapping relationship is used, each new `User` instance will contain an attribute called `addresses`.  SQLAlchemy will automatically determine that this relationship is a one-to-many relationship, and will subsequently create `addresses` as a list.  When a new `User` is created, this list will begin as empty.

The order in which the mapping definitions for `User` and `Address` is created is *not significant*.  When the `mapper()` function is called, it creates an *uncompiled* mapping record corresponding to the given class/table combination.  When the mappers are first used, the entire collection of mappers created up until that point will be compiled, which involves the establishment of class instrumentation as well as the resolution of all mapping relationships.  

Lets try out this new mapping configuration, and see what we get for the email addresses already in the database.  Since we have made a new mapping configuration, it's best that we clear out our `Session`, which is currently holding onto every `User` object we have already loaded:

    {python}
    >>> session.clear()

We can then treat the `addresses` attribute on each `User` object like a regular list:

    {python}
    >>> mary = query.filter_by(user_name='Mary').first() # doctest: +NORMALIZE_WHITESPACE
    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 
    LIMIT 1 OFFSET 0
    ['Mary']
    >>> print [a for a in mary.addresses]
    SELECT email_addresses.address_id AS email_addresses_address_id, email_addresses.email_address AS email_addresses_email_address, email_addresses.user_id AS email_addresses_user_id 
    FROM email_addresses 
    WHERE ? = email_addresses.user_id ORDER BY email_addresses.oid
    [1]
    [Address(u'[e-mail]')]

Adding to the list is just as easy.  New `Address` objects will be detected and saved when we `flush` the Session:

    {python}
    >>> mary.addresses.append(Address('[e-mail]'))
    >>> session.flush() # doctest: +NORMALIZE_WHITESPACE
    BEGIN
    INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
    ['[e-mail]', 1]
    COMMIT



Main documentation for using mappers:  [datamapping](rel:datamapping)

### Transactions

You may have noticed from the example above that when we say `session.flush()`, SQLAlchemy indicates the names `BEGIN` and `COMMIT` to indicate a transaction with the database.  The `flush()` method, since it may execute many statements in a row, will automatically use a transaction in order to execute these instructions.  But what if we want to use `flush()` inside of a larger transaction?  The easiest way is to use a "transactional" session; that is, when the session is created, you're automatically in a transaction which you can commit or rollback at any time.  As a bonus, it offers the ability to call `flush()` for you, whenever a query is issued; that way whatever changes you've made can be returned right back (and since it's all in a transaction, nothing gets committed until you tell it so).

Below, we create a session with `autoflush=True`, which implies that it's transactional.  We can query for things as soon as they are created without the need for calling `flush()`.  At the end, we call `commit()` to persist everything permanently.

    {python}
    >>> metadata.bind.echo = False
    >>> session = create_session(autoflush=True)
    >>> (ed, harry, mary) = session.query(User).filter(
    ...         User.c.user_name.in_(['Ed', 'Harry', 'Mary'])
    ...     ).order_by(User.c.user_name).all()  # doctest: +NORMALIZE_WHITESPACE
    >>> del mary.addresses[1]
    >>> harry_address = Address('[e-mail]')
    >>> harry.addresses.append(harry_address) 
    >>> session.query(User).join('addresses').filter_by(email_address='[e-mail]').first() # doctest: +NORMALIZE_WHITESPACE
    User(u'Harry',u'harrysnewpassword')
    >>> session.commit()

Main documentation:  [unitofwork](rel:unitofwork)

Next Steps
----------

That covers a quick tour through the basic idea of SQLAlchemy, in its simplest form.  Beyond that, one should familiarize oneself with the basics of Sessions, the various patterns that can be used to define different kinds of Mappers and relations among them, the rudimentary SQL types that are available when constructing Tables, and the basics of Engines, SQL statements, and database Connections.