tutorial.txt

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

Tutorial
========
This tutorial provides a relatively simple walking tour through the basic concepts of SQLAlchemy.  You may wish to skip it and dive into the [main manual][manual] which is more reference-oriented.  The examples in this tutorial comprise a fully working interactive Python session, and are guaranteed to be functioning courtesy of [doctest][].

[doctest]: http://www.python.org/doc/lib/module-doctest.html
[manual]: rel:metadata

Installation
------------

### Installing SQLAlchemy {@name=sqlalchemy}

Installing SQLAlchemy from scratch is most easily achieved with [setuptools][].  ([setuptools installation][install setuptools]). Just run this from the command-line:
    
    # easy_install SQLAlchemy

This command will download the latest version of SQLAlchemy from the [Python Cheese Shop][cheese] and install it to your system.

[setuptools]: http://peak.telecommunity.com/DevCenter/setuptools
[install setuptools]: http://peak.telecommunity.com/DevCenter/EasyInstall#installation-instructions
[cheese]: http://cheeseshop.python.org/pypi/SQLAlchemy

Otherwise, you can install from the distribution using the `setup.py` script:

    # python setup.py install

### Installing a Database API {@name=dbms}

SQLAlchemy is designed to operate with a [DBAPI](http://www.python.org/doc/peps/pep-0249/) implementation built for a particular database, and includes support for the most popular databases. If you have one of the [supported DBAPI implementations](rel:dbengine_supported), you can proceed to the following section. Otherwise [SQLite][] is an easy-to-use database to get started with, which works with plain files or in-memory databases.

SQLite is included with Python 2.5 and greater.

If you are working with Python 2.3 or 2.4, SQLite and the Python API for SQLite can be installed from the following packages:

  * [pysqlite][] - Python interface for SQLite
  * [SQLite library](http://sqlite.org)

Note that the SQLite library download is not required with Windows, as the Windows Pysqlite library already includes it linked in.  Pysqlite and SQLite can also be installed on Linux or FreeBSD via pre-made [packages][pysqlite packages] or [from sources][pysqlite].

[sqlite]: http://sqlite.org/
[pysqlite]: http://pysqlite.org/
[pysqlite packages]: http://initd.org/tracker/pysqlite/wiki/PysqlitePackages

Getting Started {@name=gettingstarted}
--------------------------
### Checking the Version
 
**Note:  This tutorial is oriented towards version 0.4 of SQLAlchemy. ** Check the version of SQLAlchemy you have installed via:

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

### Imports

To start connecting to databases and begin issuing queries, we want to import the base of SQLAlchemy's functionality, which is provided under the module name of `sqlalchemy`.  For the purposes of this tutorial, we will import its full list of symbols into our own local namespace.  

    {python}
    >>> from sqlalchemy import *

Note that importing using the `*` operator pulls all the names from `sqlalchemy` into the local module namespace, which in a real application can produce name conflicts.  Therefore it's recommended in practice to either import the individual symbols desired (i.e. `from sqlalchemy import Table, Column`) or to import under a distinct namespace (i.e. `import sqlalchemy as sa`).

### Connecting to the Database

After our imports, the next thing we need is a handle to the desired database, represented by an `Engine` object.  This object handles the business of managing connections and dealing with the specifics of a particular database.  Below, we will make a SQLite connection to a file-based database called "tutorial.db".

    {python}
    >>> db = create_engine('sqlite:///tutorial.db')
    
Technically, the above statement did not make an actual connection to the SQLite database just yet.  As soon as we begin working with the engine, it will start creating connections.  In the case of SQLite, the `tutorial.db` file will actually be created at the moment it is first used, if the file does not exist already.

For full information on creating database engines, including those for SQLite and others, see [dbengine](rel:dbengine).

SQLAlchemy is Two Libraries in One {@name=twoinone}
----------------------------------------------------

Now that the basics of installing SQLAlchemy and connecting to our database are established, we can start getting in to actually doing something.  But first, a little bit of explanation is required.

A central concept of SQLAlchemy is that it actually contains two distinct areas of functionality, one of which builds upon the other.  One is a **SQL Construction Language** and the other is an **Object Relational Mapper** ("ORM" for short).  The SQL construction language allows you to construct objects called `ClauseElements` which represent SQL expressions.  These ClauseElements can then be executed against any database, where they are **compiled** into strings that are appropriate for the target database, and return an object called a `ResultProxy`, which is essentially a result set object that acts very much like a deluxe version of the dbapi `cursor` object.

The Object Relational Mapper (ORM) is a set of tools completely distinct from the SQL Construction Language which serve the purpose of mapping Python object instances into database rows, providing a rich selection interface with which to retrieve instances from tables as well as a comprehensive solution to persisting changes on those instances back into the database.  When working with the ORM, its underlying workings as well as its public API make extensive use of the SQL Construction Language, however the general theory of operation is slightly different.  Instead of working with database rows directly, you work with your own user-defined classes and object instances.  Additionally, the method of issuing queries to the database is different, as the ORM handles the job of generating most of the SQL required, and instead requires more information about what kind of class instances you'd like to load and where you'd like to put them.

Where SA is somewhat unique, more powerful, and slightly more complicated is that the two areas of functionality can be mixed together in many ways.  A key strategy to working with SA effectively is to have a solid awareness of these two distinct toolsets, and which concepts of SA belong to each - even some publications have confused the SQL Construction Language with the ORM.  The key difference between the two is that when you're working with cursor-like result sets it's the SQL Construction Language, and when working with collections of your own class instances it's the Object Relational Mapper.

This tutorial will first focus on the basic configuration that is common to using both the SQL Construction Language as well as the ORM, which is to declare information about your database called **table metadata**.  This will be followed by some constructed SQL examples, and then into usage of the ORM utilizing the same data we established in the SQL construction examples.

Working with Database Objects {@name=schemasql}
-----------------------------------------------

### Defining Metadata, Binding to Engines {@name=metadata}

Configuring SQLAlchemy for your database consists of creating objects called `Tables`, each of which represent an actual table in the database.  A collection of `Table` objects resides in a `MetaData` object which is essentially a table collection.  We will create a `MetaData` and connect it to our `Engine` (connecting a schema object to an Engine is called *binding*):

    {python}
    >>> metadata = MetaData()
    >>> metadata.bind = db

An equivalent operation is to create the `MetaData` object directly with the Engine:

    {python}
    >>> metadata = MetaData(db)
    
Now, when we tell "metadata" about the tables in our database, we can issue CREATE statements for those tables, as well as execute SQL statements derived from them, without needing to open or close any connections; that will be all done automatically.

Note that SQLALchemy allows us to use explicit connection objects for everything, if we wanted to, and there are reasons why you might want to do this.  But for the purposes of this tutorial, using `bind` removes the need for us to deal with explicit connections.

### Creating a Table {@name=table_creating}

With `metadata` as our established home for tables, lets make a Table for it:

    {python}
    >>> users_table = Table('users', metadata,
    ...     Column('user_id', Integer, primary_key=True),
    ...     Column('user_name', String(40)),
    ...     Column('password', String(15))
    ... )

As you might have guessed, we have just defined a table named `users` which has three columns: `user_id` (which is a primary key column), `user_name` and `password`. Currently it is just an object that doesn't necessarily correspond to an existing table in our database.  To actually create the table, we use the `create()` method.  To make it interesting, we will have SQLAlchemy echo the SQL statements it sends to the database, by setting the `echo` flag on the `Engine` associated with our `MetaData`:

    {python}
    >>> metadata.bind.echo = True
    >>> users_table.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    CREATE TABLE users (
        user_id INTEGER NOT NULL,
        user_name VARCHAR(40),
        password VARCHAR(15),
        PRIMARY KEY (user_id)
    )
    ...

Alternatively, the `users` table might already exist (such as, if you're running examples from this tutorial for the second time), in which case you can just skip the `create()` method call. You can even skip defining the individual columns in the `users` table and ask SQLAlchemy to load its definition from the database:

    {python}
    >>> users_table = Table('users', metadata, autoload=True)
    >>> list(users_table.columns)[0].name
    'user_id'

Loading a table's columns from the database is called **reflection**.  Documentation on table metadata, including reflection, is available in [metadata](rel:metadata).

### Inserting Rows

Inserting is achieved via the `insert()` method, which defines a *clause object* (known as a `ClauseElement`) representing an INSERT statement:

    {python}
    >>> i = users_table.insert()
    >>> i # doctest:+ELLIPSIS
    <sqlalchemy.sql.Insert object at 0x...>
    >>> # the string form of the Insert object is a generic SQL representation
    >>> print i
    INSERT INTO users (user_id, user_name, password) VALUES (?, ?, ?)

Since we created this insert statement object from the `users` table which is bound to our `Engine`, the statement itself is also bound to the `Engine`, and supports executing itself.  The `execute()` method of the clause object will *compile* the object into a string according to the underlying *dialect* of the Engine to which the statement is bound, and will then execute the resulting statement.  

    {python}
    >>> # insert a single row
    >>> i.execute(user_name='Mary', password='secure') # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    INSERT INTO users (user_name, password) VALUES (?, ?)
    ['Mary', 'secure']
    COMMIT
    <sqlalchemy.engine.base.ResultProxy object at 0x...>

    >>> # insert multiple rows simultaneously
    >>> i.execute({'user_name':'Tom'}, {'user_name':'Fred'}, {'user_name':'Harry'}) # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE
    INSERT INTO users (user_name) VALUES (?)
    [['Tom'], ['Fred'], ['Harry']]
    COMMIT
    <sqlalchemy.engine.base.ResultProxy object at 0x...>


Note that the `VALUES` clause of each `INSERT` statement was automatically adjusted to correspond to the parameters sent to the `execute()` method.  This is because the compilation step of a `ClauseElement` takes into account not just the constructed SQL object and the specifics of the type of database being used, but the execution parameters sent along as well.

When constructing clause objects, SQLAlchemy will bind all literal values into bind parameters.  On the construction side, bind parameters are always treated as named parameters.  At compilation time, SQLAlchemy will convert them into their proper format, based on the paramstyle of the underlying DBAPI.  This works equally well for all named and positional bind parameter formats described in the DBAPI specification.

Documentation on inserting: [sql_insert](rel:sql_insert).