ormtutorial.txt

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

[alpha_api]: javascript:alphaApi()
[alpha_implementation]: javascript:alphaImplementation()

Object Relational Tutorial {@name=datamapping}
============

In this tutorial we will cover a basic SQLAlchemy object-relational mapping scenario, where we store and retrieve Python objects from a database representation.  The database schema will begin with one table, and will later develop into several.  The tutorial is in doctest format, meaning each `>>>` line represents something you can type at a Python command prompt, and the following text represents the expected return value.  The tutorial has no prerequisites.

## Version Check

A quick check to verify that we are on at least **version 0.4** of SQLAlchemy:

    {python}
    >>> import sqlalchemy
    >>> sqlalchemy.__version__ # doctest:+SKIP
    0.4.0
    
## Connecting

For this tutorial we will use an in-memory-only SQLite database.   This is an easy way to test things without needing to have an actual database defined anywhere.  To connect we use `create_engine()`:

    {python}
    >>> from sqlalchemy import create_engine
    >>> engine = create_engine('sqlite:///:memory:', echo=True)
    
The `echo` flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python's standard `logging` module.  With it enabled, we'll see all the generated SQL produced.  If you are working through this tutorial and want less output generated, set it to `False`.   This tutorial will format the SQL behind a popup window so it doesn't get in our way; just click the "SQL" links to see whats being generated.
    
## Define and Create a Table {@name=tables}

Next we want to tell SQLAlchemy about our tables.  We will start with just a single table called `users`, which will store records for the end-users using our application (lets assume it's a website).  We define our tables all within a catalog called `MetaData`, using the `Table` construct, which resembles regular SQL CREATE TABLE syntax:

    {python}
    >>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey    
    >>> metadata = MetaData()
    >>> users_table = Table('users', metadata,
    ...     Column('id', Integer, primary_key=True),
    ...     Column('name', String(40)),
    ...     Column('fullname', String(100)),
    ...     Column('password', String(15))
    ... )

All about how to define `Table` objects, as well as how to create them from an existing database automatically, is described in [metadata](rel:metadata).

Next, to tell the `MetaData` we'd actually like to create our `users_table` for real inside the SQLite database, we use `create_all()`, passing it the `engine` instance which points to our database.  This will check for the presence of a table first before creating, so it's safe to call multiple times:

    {python}
    {sql}>>> metadata.create_all(engine) # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    PRAGMA table_info("users")
    {}
    CREATE TABLE users (
        id INTEGER NOT NULL, 
        name VARCHAR(40), 
        fullname VARCHAR(100), 
        password VARCHAR(15), 
        PRIMARY KEY (id)
    )
    {}
    COMMIT

So now our database is created, our initial schema is present, and our SQLAlchemy application knows all about the tables and columns in the database; this information is to be re-used by the Object Relational Mapper, as we'll see now.
 
## Define a Python Class to be Mapped {@name=mapping}

So lets create a rudimentary `User` object to be mapped in the database.  This object will for starters have three attributes, `name`, `fullname` and `password`.  It only need subclass Python's built-in `object` class (i.e. it's a new style class).  We will give it a constructor so that it may conveniently be instantiated with its attributes at once, as well as a `__repr__` method so that we can get a nice string representation of it:

    {python}
    >>> class User(object):
    ...     def __init__(self, name, fullname, password):
    ...         self.name = name
    ...         self.fullname = fullname
    ...         self.password = password
    ...
    ...     def __repr__(self):
    ...        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

## Setting up the Mapping

With our `users_table` and `User` class, we now want to map the two together.  That's where the SQLAlchemy ORM package comes in.  We'll use the `mapper` function to create a **mapping** between `users_table` and `User`:

    {python}
    >>> from sqlalchemy.orm import mapper
    >>> mapper(User, users_table) # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    <sqlalchemy.orm.mapper.Mapper object at 0x...>
    
The `mapper()` function creates a new `Mapper` object and stores it away for future reference.  It also **instruments** the attributes on our `User` class, corresponding to the `users_table` table.  The `id`, `name`, `fullname`, and `password` columns in our `users_table` are now instrumented upon our `User` class, meaning it will keep track of all changes to these attributes, and can save and load their values to/from the database.  Lets create our first user, 'Ed Jones', and ensure that the object has all three of these attributes:

    {python}
    >>> ed_user = User('ed', 'Ed Jones', 'edspassword')
    >>> ed_user.name
    'ed'
    >>> ed_user.password
    'edspassword'
    >>> str(ed_user.id)
    'None'
    
What was that last `id` attribute?  That was placed there by the `Mapper`, to track the value of the `id` column in the `users_table`.  Since our `User` doesn't exist in the database, its id is `None`.  When we save the object, it will get populated automatically with its new id.

## Creating a Session

We're now ready to start talking to the database.  The ORM's "handle" to the database is the `Session`.  When we first set up the application, at the same level as our `create_engine()` statement, we define a second object called `Session` (or whatever you want to call it, `create_session`, etc.) which is configured by the `sessionmaker()` function.  This function is configurational and need only be called once.  
    
    {python}
    >>> from sqlalchemy.orm import sessionmaker
    >>> Session = sessionmaker(bind=engine, autoflush=True, transactional=True)

In the case where your application does not yet have an `Engine` when you define your module-level objects, just set it up like this:

    {python}
    >>> Session = sessionmaker(autoflush=True, transactional=True)

Later, when you create your engine with `create_engine()`, connect it to the `Session` using `configure()`:

    {python}
    >>> Session.configure(bind=engine)  # once engine is available
    
This `Session` class will create new `Session` objects which are bound to our database and have the transactional characteristics we've configured.  Whenever you need to have a conversation with the database, you instantiate a `Session`:

    {python}
    >>> session = Session()
    
The above `Session` is associated with our SQLite `engine`, but it hasn't opened any connections yet.  When it's first used, it retrieves a connection from a pool of connections maintained by the `engine`, and holds onto it until we commit all changes and/or close the session object.  Because we configured `transactional=True`, there's also a transaction in progress (one notable exception to this is MySQL, when you use its default table style of MyISAM).  There's options available to modify this behavior but we'll go with this straightforward version to start.    

## Saving Objects

So saving our `User` is as easy as issuing `save()`:

    {python}
    >>> session.save(ed_user)
    
But you'll notice nothing has happened yet.  Well, lets pretend something did, and try to query for our user.  This is done using the `query()` method on `Session`.  We create a new query representing the set of all `User` objects first.  Then we narrow the results by "filtering" down to the user we want; that is, the user whose `name` attribute is `"ed"`.  Finally we call `first()` which tells `Query`, "we'd like the first result in this list".

    {python}
    {sql}>>> session.query(User).filter_by(name='ed').first() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    BEGIN
    INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
    ['ed', 'Ed Jones', 'edspassword']
    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 1 OFFSET 0
    ['ed']
    {stop}<User('ed','Ed Jones', 'edspassword')>

And we get back our new user.  If you view the generated SQL, you'll see that the `Session` issued an `INSERT` statement before querying.  The `Session` stores whatever you put into it in memory, and at certain points it issues a **flush**, which issues SQL to the database to store all pending new objects and changes to existing objects.  You can manually invoke the flush operation using `flush()`; however when the `Session` is configured to `autoflush`, it's usually not needed.

OK, let's do some more operations.  We'll create and save three more users:

    {python}
    >>> session.save(User('wendy', 'Wendy Williams', 'foobar'))
    >>> session.save(User('mary', 'Mary Contrary', 'xxg527'))
    >>> session.save(User('fred', 'Fred Flinstone', 'blah'))

Also, Ed has already decided his password isn't too secure, so lets change it:
    
    {python}
    >>> ed_user.password = 'f8s7ccs'
    
Then we'll permanently store everything thats been changed and added to the database.  We do this via `commit()`:

    {python}
    {sql}>>> session.commit()
    UPDATE users SET password=? WHERE users.id = ?
    ['f8s7ccs', 1]
    INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
    ['wendy', 'Wendy Williams', 'foobar']
    INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
    ['mary', 'Mary Contrary', 'xxg527']
    INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
    ['fred', 'Fred Flinstone', 'blah']
    COMMIT

`commit()` flushes whatever remaining changes remain to the database, and commits the transaction.  The connection resources referenced by the session are now returned to the connection pool.  Subsequent operations with this session will occur in a **new** transaction, which will again re-acquire connection resources when first needed.

If we look at Ed's `id` attribute, which earlier was `None`, it now has a value:

    {python}
    >>> ed_user.id
    1

After each `INSERT` operation, the `Session` assigns all newly generated ids and column defaults to the mapped object instance.  For column defaults which are database-generated and are not part of the table's primary key, they'll be loaded when you first reference the attribute on the instance.

One crucial thing to note about the `Session` is that each object instance is cached within the Session, based on its primary key identifier.  The reason for this cache is not as much for performance as it is for maintaining an **identity map** of instances.  This map guarantees that whenever you work with a particular `User` object in a session, **you always get the same instance back**.  As below, reloading Ed gives us the same instance back:

    {python}
    {sql}>>> ed_user is session.query(User).filter_by(name='ed').one() # 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.name = ? ORDER BY users.oid 
    LIMIT 2 OFFSET 0
    ['ed']
    {stop}True

The `get()` method, which queries based on primary key, will not issue any SQL to the database if the given key is already present:

    {python}
    >>> ed_user is session.query(User).get(ed_user.id)
    True
    
## Querying

A whirlwind tour through querying.

A `Query` is created from the `Session`, relative to a particular class we wish to load.

    {python}
    >>> query = session.query(User)

Once we have a query, we can start loading objects.  The Query object, when first created, represents all the instances of its main class.  You can iterate through it directly:

    {python}
    {sql}>>> for user in session.query(User):
    ...     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 ORDER BY users.oid
    []
    {stop}ed
    wendy
    mary
    fred

...and the SQL will be issued at the point where the query is evaluated as a list.  If you apply array slices before iterating, LIMIT and OFFSET are applied to the query:

    {python}
    {sql}>>> for u in session.query(User)[1:3]: #doctest: +NORMALIZE_WHITESPACE
    ...    print u
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
    FROM users ORDER BY users.oid 
    LIMIT 2 OFFSET 1
    []
    {stop}<User('wendy','Wendy Williams', 'foobar')>
    <User('mary','Mary Contrary', 'xxg527')>

Narrowing the results down is accomplished either with `filter_by()`, which uses keyword arguments:

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

...or `filter()`, which uses SQL expression language constructs.  These allow you to use regular Python operators with the class-level attributes on your mapped class:

    {python}
    {sql}>>> for user in session.query(User).filter(User.name=='ed'):
    ...    print user
    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
    ['ed']
    {stop}<User('ed','Ed Jones', 'f8s7ccs')>

You can also use the `Column` constructs attached to the `users_table` object to construct SQL expressions:

    {python}
    {sql}>>> for user in session.query(User).filter(users_table.c.name=='ed'):
    ...    print user
    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
    ['ed']
    {stop}<User('ed','Ed Jones', 'f8s7ccs')>

Most common SQL operators are available, such as `LIKE`:

    {python}
    {sql}>>> session.query(User).filter(User.name.like('%ed'))[1] # 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 LIKE ? ORDER BY users.oid 
     LIMIT 1 OFFSET 1
    ['%ed']
    {stop}<User('fred','Fred Flinstone', 'blah')>

Note above our array index of `1` placed the appropriate LIMIT/OFFSET and returned a scalar result immediately.

The `all()`, `one()`, and `first()` methods immediately issue SQL without using an iterative context or array index.  `all()` returns a list:

    {python}
    >>> query = session.query(User).filter(User.name.like('%ed'))