学步园

The object-relational mapper (ORM) lets you interact with the database using your own object classes rather than writing SQL code.
The SQL expression language has many methods to create customized SQL statements, and the result cursor is more friendly than DBAPI's.
The low-level execute methods accept literal SQL strings if you find something the SQL builder can't do, such as adding a column to an existing table or modifying the column's type. If they return results, you still get the benefit of SQLAlchemy's result cursor.

The first two levels are database neutral, meaning they hide the differences between the databases' SQL dialects. Changing to a different database is merely a matter of supplying a new connection URL. Of course there are limits to this, but SQLAlchemy is 90% easier than rewriting all your SQL queries.

The SQLAlchemy manual should be your next stop for questions not covered here. It's very well written and thorough.

Throughout this chapter, "myapp" refers to your Pylons application's package directory (e.g., MyApp-1.0.1.egg/myapp).

The Pylons development version (which will become Pylons 0.9.7) will ask when you create your application whether you intend to use SQLAlchemy, and will preconfigure it for you. In this case, you'll find that many of the steps below are already done. Pylons 0.9.6 does not do this, so you'll have to make all the changes by hand. Under the Attachments tab on this page you'll find a Pylons 0.9.6.1 application containing the code here. The application won't do anything because we've neglected the user interface, but you can examine the code or paste it into your own application. The Pylons Cookbook contains more advanced database tutorials.

Install SQLAlchemy

We'll assume you've already installed Pylons and have the easy_install command. At the command line, run:

1	easy_install SQLAlchemy

Next you'll have to install a database engine and its Python bindings. If you don't know which one to choose, SQLite is a good one to start with. It's small and easy to install, and Python 2.5 includes bindings for it. Installing the database engine is beyond the scope of this article, but here are the Python bindings you'll need for the most popular engines:

1
2
3

easy_install pysqlite             # If you use SQLite and Python 2.4 (not needed for Python 2.5)
easy_install MySQL-python         # If you use MySQL
easy_install psycopg2             # If you use PostgreSQL

See the Python Package Index (formerly the Cheeseshop) for other database drivers.

Check Your Version

To see which version of SQLAlchemy you have, go to a Python shell and look at sqlalchemy.__version__ :

1
2
3

>>> import sqlalchemy
>>> sqlalchemy.__version__
0.4.3

These instructions assume SQLAlchemy 0.4.2p3 or newer. They will not work with SQLAlchemy 0.3.

Model

Metadata

Create myapp/model/meta.py containing:

"""SQLAlchemy Metadata and Session object"""
from sqlalchemy import MetaData

__all__ = ['engine', 'metadata', 'Session']

# SQLAlchemy database engine.  Updated by model.init_model().
engine = None

# SQLAlchemy session manager.  Updated by model.init_model().
Session = None

# Global metadata. If you have multiple databases with overlapping table 
# names, you'll need a metadata for each database.
metadata = MetaData()

A SQLAlchemy engine is a pool of connections to a particular database. The metadata is an object that will contain your table definitions. The Session is used with the object-relational mapper.

Main model module

Change myapp/model/_init_.py to read:

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

    sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine)

    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

Note that this function sets attributes in a different module. The reason is that these attributes depend on a live database engine, which may not exist when the model is imported. So we call this function to complete the initialization.

transactional=True means all ORM operations will be done within a database transaction. autoflush=True means SQLAlchemy will automatically call Session.flush() to write the changes to the database whenever we commit the transaction by calling Session.commit(). The transactional and autoflush options are normally either both true or both false.

bind=engine tells the ORM session to use that database for all operations. If you're using multiple databases it gets a little more complicated, as we'll see below.

You may of course use other sessionmaker or scoped_session arguments if you wish.

Tables and ORM classes

If you have only a couple simple tables you can put them in the main model module directly. Otherwise you can put them in separate modules, one per table, one per group of tables, or however you wish. Here's a simple table and its ORM class:

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

t_dictionary = sa.Table("Dictionary", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("term", sa.types.String(100), nullable=False),
    sa.Column("definition", sa.types.String, nullable=False),
    )

class Dictionary(object):
    pass

orm.mapper(Dictionary, t_dictionary)

If you've put your tables into separate modules, you can optionally import them into the main model module. This is not required but it allows you to access them in your controllers and in "paster shell" by just importing the model. Examples:

1 2	from myapp.model import dictionary from myapp.model.dictionary import Dictionary

Relation example

Here's an example of a Person and an Address class with a many:many relationship on people.my_addresses. See Relational Databases for Poeople in a Hurry and the SQLAlchemy manual for details.

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

t_people = sa.Table('people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('name', sa.types.String(100)),
    sa.Column('email', sa.types.String(100))
)

t_addresses_people = sa.Table('addresses_people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('person_id', sa.types.Integer, sa.ForeignKey('people.id')),
    sa.Column('address_id', sa.types.Integer, sa.ForeignKey('addresses.id'))
)

t_addresses = sa.Table('addresses', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('address', sa.types.String(100))
)

class Person(object):
    pass

class Address(object):
    pass

orm.mapper(Address, t_addresses)
orm.mapper(Person, t_people, properties = {
    'my_addresses' : orm.relation(Address, secondary = t_addresses_people),
    })

Reflecting tables

If you want SQLAlchemy to read the table structure from existing database tables so you don't have to specify the columns, you'll have to put the table definitions and the mapper calls inside init_model because they depend on a live database connection. The ORM class defintions do not have to be in init_model. So you could do something like:

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta
from myapp.model import records

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

    sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine)

    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

    records.t_record = sa.Table("Record", meta.metadata, autoload=True, autoload_with=engine)
    orm.mapper(records.Record, records.t_record)

Using the model standalone

You now have everything necessary to use the model in a standalone script such as a cron job, or to test it interactively. You just need to create a SQLAlchemy engine and connect it to the model. This example uses a database "test.sqlite" in the current directory:

% python
Python 2.5.1 (r251:54863, Oct  5 2007, 13:36:32)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine("sqlite:///test.sqlite")
>>> from myapp import model
>>> model.init_model(engine)

Now you can use the tables, classes, and Session as described in the SLQAlchemy manual.

The config file

When your Pylons application runs, it needs to know which database to connect to. Normally you put this information in development.ini and activate the model in environment.py. Put the following in development.ini in the [app:main] section, depending on your database,

For SQLite

1	sqlalchemy.url = sqlite:///%(here)s/mydatabasefilename.sqlite

Where mydatabasefilename.db is the path to your SQLite database file. "%(here)s" represents the directory containing the development.ini file. If you're using an absolute path, use four slashes after the colon: "sqlite:////var/lib/myapp/database.sqlite". Don't use a relative path (three slashes) because the current directory could be anything. The example has three slashes because the value of "%(here)s" always starts with a slash (or the platform equivalent; e.g., "C:
foo" on Windows).

For MySQL

1 2	sqlalchemy.url = mysql://username:password@host:port/database sqlalchemy.pool_recycle = 3600

Enter your username, password, host (localhost if it is on your machine), port number (usually 3306) and the name of your database. The second line is an example of setting engine options.

It's important to set "pool_recycle" for MySQL to prevent "MySQL server has gone away" errors. This is because MySQL automatically closes idle database connections without informing the application. Setting the connection lifetime to 3600 seconds (1 hour) ensures that the connections will be expired and recreated before MySQL notices they're idle.

Don't be tempted to use the ".echo" option to enable SQL logging because it may cause duplicate log output. Instead see the "Logging" section below to integrate MySQL logging into Paste's logging system.

For PostgreSQL

1	sqlalchemy.url = postgres://username:password@host:port/database

Enter your username, password, host (localhost if it is on your machine), port number (usually 5432) and the name of your database.

The engine

Put this at the top of myapp/config/environment.py:

1 2	from sqlalchemy import engine_from_config from myapp.model import init_model

And this in the load_environment function:

1 2	engine = engine_from_config(config, 'sqlalchemy.') init_model(engine)

The second argument is the prefix to look for. If you named your keys "sqlalchemy.default.url", you would put "sqlalchemy.default." here. The prefix may be anything, as long as it's consistent between the config file and this function call.

Controller

Add the following to the top of myapp/lib/base.py (the base controller):

1	from myapp.model import meta

And change the .__call__ method to:

def __call__(self, environ, start_response):
    try:
        return WSGIController.__call__(self, environ, start_response)
    finally:
        meta.Session.remove()

The .remove() method is very important! It discards any leftover ORM data in the current web request. Otherwise the stray data will leak into the next request handled by this thread, potentially causing errors or data corruption.

Any per-request behaviors can be configured at this stage. For example, to use just a single database connection per request, which removes all connection pool checkin/checkout overhead, the per-request Session can be configured with a Connection:

def __call__(self, environ, start_response):
    conn = meta.engine.connect()
    meta.Session.configure(bind=conn)
    try:
        return WSGIController.__call__(self, environ, start_response)
    finally:
        meta.Session.remove()
        conn.close()

Note that when using a session with transactional=True, the session holds onto a single connection through the lifespan of each transaction so the above optimization is not as significant.

Building the database

To actually create the tables in the database, you call the metadata's .create_all() method. You can do this interactively or use paster's application initialization feature. To do this, put the code in myapp/websetup.py. After the load_environment() call, put:

from myapp.model import meta
log.info("Creating tables")
meta.metadata.create_all(bind=meta.engine)
log.info("Successfully setup")

Then run the following on the command line:

1	paster setup-app development.ini

Data queries and modifications

Important: this section assumes you're putting the code in a high-level model function. If you're putting it directly into a controller method, you'll have to put a model. prefix in front of every object defined in the model, or import the objects individually. Also note that the Session object here (capital s) is not the same as the Beaker session object (lowercase s) in controllers.

Here's how to enter new data into the database:

mr_jones = Person()
mr_jones.name = 'Mr Jones'
meta.Session.save(mr_jones)
meta.Session.commit()

mr_jones here is an instance of Person. Its properties correspond to the column titles of t_people and contain the data from the selected row. A more sophisticated application would have a Person.__init__ method that automatically sets attributes based on its arguments.

An example of loading a database entry in a controller method, performing a sex change, and saving it:

person_q = meta.Session.query(Person)            # An ORM Query object for accessing the Person table
mr_jones = person_q.filter(Person.name=='Mr Jones').one()
print mr_jones.name                                    # prints 'Mr Jones'
mr_jones.name = 'Mrs Jones'                            # only the object instance is changed here ...
meta.Session.commit()                                  # ... only now is the database updated

To return a list of entries use:

1	all_mr_joneses = person_q.filter(Person.name=='Mr Jones').all()

To get all list of all the people in the table use:

1	everyone = person_q.all()

To retrieve by id:

1	someuser = person_q.get(5)

You can iterate over every person even more simply:

print "All people"
for p in person_q:
    print p.name
print
print "All Mr Joneses:"
for p in person_q.filter(Person.name=='Mr Jones'):
    print p.name

To delete an entry use the following:

1
2
3

mr_jones = person_q.filter(Person.name=='Mr Jones').one()
meta.Session.delete(mr_jones)
meta.Session.commit()

Working with joined objects

Recall that the my_addresses property is a list of Address objects

1	print mr_jones.my_addresses[0].address # prints first address

To add an existing address to 'Mr Jones' we do the following:

address_q = meta.Session.query(Address)
address = address_q.filter(Address.address=='33 Pine Marten Lane, Pleasantville').one()     # Retrieve an existing address
mr_jones.my_addresses.append(new_address)                                      # Add to the list
meta.Session.commit()                                                          # issue updates to the join table

To add an entirely new address to 'Mr Jones' we do the following:

new_address = Address()                                      # Construct an empty address object

new_address.address = '33 Pine Marten Lane, Pleasantville'

mr_jones.m