Using Fixture To Test A Pylons + SQLAlchemy App

This explains how to use fixture in the test suite of a simple Address Book application written in Pylons powered by two tables in a SQLite database via SQLAlchemy. If you’re not already familiar with Using DataSet and Using LoadableFixture then you’ll be OK but it wouldn’t hurt to read those docs first. The concepts here will probably also work with similar Python frameworks backed by SQLAlchemy. If you’ve got something working in another framework, please let me know.

(This tutorial was written with Python 2.5.2, fixture 1.3, Pylons 0.9.7, and SQLAlchemy 0.4.8 but may work with other versions.)

Creating An Address Book

First, install Pylons and create a new app as described in Getting Started. This will be an Address Book application so run the command as:

$ paster create -t pylons addressbook

Follow the prompts to use SQLAlchemy as the backend.

Defining The Model

To work with the database you need to define a data model. Place the following code just below the init_model() to define the SQLAlchemy tables and mappers for the Address Book data. The complete module should look like:

"""The application's model objects"""
import sqlalchemy as sa
from sqlalchemy import orm

from addressbook.model import meta

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

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('')),
    sa.Column('address_id', sa.types.Integer, sa.ForeignKey(''))

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):

class Address(object):

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

Take note that by default Pylons sets your sqlalchemy database to sqlite:

# ...
sqlalchemy.url = sqlite:///%(here)s/development.db


For reference, all code shown here is available from the fixture code repository in fixture/examples/pylons_example/addressbook.

Creating A Simple Controller

Create a book controller to show a simple list of addresses:

$ cd /path/to/addressbook
$ paster controller book

This makes the files addressbook/controllers/ and addressbook/tests/functional/ Edit to set it as the default page:

map.connect('/', controller='book', action='index')

(To avoid conflicts with the default page also be sure to remove addressbook/public/index.html.)

Edit addressbook/controllers/ to select some addresses from the database and render a template instead of returning “Hello World”:

import logging

from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to

from addressbook.lib.base import BaseController, render
from addressbook.model import meta, Person

log = logging.getLogger(__name__)

class BookController(BaseController):

    def index(self):
        # c, imported from addressbook/lib/, is automatically 
        # available in your template
        c.persons = meta.Session.query(Person).join('my_addresses')
        return render("/book.mako")

Add the template file as addressbook/templates/book.mako and write some Python code (via Mako) to show some addresses:

Address Book

% for person in c.persons:
    % for address in person.my_addresses:
    % endfor
% endfor

Adding Some Data Sets

You now have a page that lists addresses but you don’t have any address data. Fixture provides an easy way to add data to your models for automated or exploratory testing. Define the following code in a new module at addressbook/datasets/ using a naming scheme where each DataSet subclass is camel case, named after a mapped class in the model but ending in Data (read more about styles here):

from fixture import DataSet

class AddressData(DataSet):
    class joe_in_montego:
        address = "111 St. James St, Montego Bay, Jamaica"
    class joe_in_ny:
        address = "111 S. 2nd Ave, New York, NY"

class PersonData(DataSet):
    class joe_gibbs:
        name = "Joe Gibbs"
        email = ""
        my_addresses = [

This sets up one row to be inserted into the people table and two rows to be inserted into the addresses / addresses_people tables, declaring two addresses for our man Joe Gibbs. See Using DataSet for the details about these classes.

Notice that the DataSet classes mirror the properties we defined above for the mappers. This is because Fixture applies the DataSets to the mapped classes Address and Person respectively to save the data.

Loading Initial Data

If you want to fire up the dev server and start using this data, you just need to place a few lines of code in addressbook/, a Pylons convention for hooking into the paster setup-app devlopment.ini command.

The full code for creating tables and inserting data looks like this in addressbook/

"""Setup the addressbook application"""
import logging

from addressbook.config.environment import load_environment
from addressbook.model import meta

from addressbook import model
from fixture import SQLAlchemyFixture
from import NamedDataStyle
from addressbook.datasets import PersonData

log = logging.getLogger(__name__)

def setup_app(command, conf, vars):
    """Place any commands to setup addressbook here"""
    load_environment(conf.global_conf, conf.local_conf)"Creating tables")
    # Create the tables if they don't already exist
    meta.metadata.create_all(bind=meta.engine)"Successfully setup")
    # load some initial data during setup-app :
    db = SQLAlchemyFixture(
            env=model, style=NamedDataStyle(),
    data ="Inserting initial data")

This will allow you to get started on your Address Book application by running:

$ cd /path/to/addressbook
$ paster setup-app development.ini

Now, start the development server:

paster serve --reload development.ini

And load up in your browser. You should see a rendering of:

Address Book

    <h3>Joe Gibbs</h3>
    <h4>111 St. James St, Montego Bay, Jamaica</h4>
    <h4>111 S. 2nd Ave, New York, NY</h4>

Cool! But what you really wanted was to write some automated tests, right? Fixture makes that just as easy. You can read more about Unit Testing Pylons Apps but as of right now you should already have the file addressbook/tests/functional/, ready and waiting for some test code.

Setting Up The Test Suite

Before running any tests you need to configure the test suite to make a database connection and create tables when the tests start. First, edit test.ini to tell your app to use a different database file so as not to disturb your development environment:

use = config:development.ini

# Add additional test specific configuration options as necessary.
sqlalchemy.url = sqlite:///%(here)s/tests.db


By default Pylons configures your test suite so that the same code run by paster setup-app test.ini is run before your tests start. This can be confusing if you are creating tables and inserting data as mentioned in the previous section so you’ll want to comment that out and replace it with enough code to initialize your models.

Here’s a version of addressbook/tests/ that initializes your Pylons test suite for use with fixture. It creates and drops tables once per test run to reduce unnecessary overhead and exposes a global dbfixture object that other tests can import and use.

"""Pylons application test package

This package assumes the Pylons environment is already loaded, such as
when this script is imported from the `nosetests --with-pylons=test.ini`

This module initializes the application via ``websetup`` (`paster
setup-app`) and provides the base testing objects.
from unittest import TestCase

from paste.deploy import loadapp
from paste.script.appinstall import SetupCommand
from pylons import config, url
from routes.util import URLGenerator
from webtest import TestApp

# additional imports ...
from paste.deploy import appconfig
from addressbook.config.environment import load_environment

import pylons.test

# export dbfixture here for tests :
__all__ = ['environ', 'url', 'TestController', 'dbfixture']

# Invoke websetup with the current config file
##### comment this out so that initial data isn't loaded:
# SetupCommand('setup-app').run([config['__file__']])

##### but add this so that your models get configured:
appconf = appconfig('config:' + config['__file__'])
load_environment(appconf.global_conf, appconf.local_conf)

environ = {}

from addressbook import model
from addressbook.model import meta
from fixture import SQLAlchemyFixture
from import NamedDataStyle

dbfixture = SQLAlchemyFixture(

def setup():

def teardown():

class TestController(TestCase):

    def __init__(self, *args, **kwargs):
        if pylons.test.pylonsapp:
            wsgiapp = pylons.test.pylonsapp
            wsgiapp = loadapp('config:%s' % config['__file__']) = TestApp(wsgiapp)
        url._push_object(URLGenerator(config[''], environ))
        TestCase.__init__(self, *args, **kwargs)
    def setUp(self):
        # remove the session once per test so that 
        # objects do not leak from test to test


Fixture deletes the rows it inserts. If your application inserts rows during a test then you will need to truncate the table or else go back to the strategy of creating / dropping tables per every test.

Defining A Fixture

As illustrated by the test suite initialization code above, a common fixture can be used by all tests. It looks like:

dbfixture = SQLAlchemyFixture(

See Using LoadableFixture for a detailed explanation of fixture objects.

Testing With Data

Now let’s start working with the DataSet objects. Edit addressbook/tests/functional/ so that it looks like this:

from addressbook.model import meta, Person
from addressbook.datasets import PersonData, AddressData
from addressbook.tests import *

class TestBookController(TestController):        

    def setUp(self):
        super(TestBookController, self).setUp() = # AddressData loads implicitly
    def tearDown(self):
        super(TestBookController, self).tearDown()

    def test_index(self):
        response ='book'))
        print response
        assert in response
        assert in response
        assert AddressData.joe_in_montego.address in response
        assert AddressData.joe_in_ny.address in response

Then run the test, which should pass:

$ cd /path/to/addressbook
$ nosetests
Ran 1 test in 0.702s



This code is asserting that the values from the DataSet classes have been rendered on the page, i.e. <h4></h4>. There is more info on using response objects in the WebTest docs (however at the time of this writing Pylons is still using paste.fixture, an earlier form of WebTest).

You’ll notice there is a print statement showing the actual response. By default nose hides stdout for convenience so if you want to see the response just trigger a failure by adding raise AssertionError in the test.

$ nosetests
FAIL: test_index (addressbook.tests.functional.test_book.TestBookController)
Traceback (most recent call last):
  File "/Users/kumar/.../addressbook/tests/functional/", line 16, in test_index
    raise AssertionError
-------------------- >> begin captured stdout << ---------------------
Response: 200
content-type: text/html; charset=utf-8
pragma: no-cache
cache-control: no-cache
Address Book
    <h3>Joe Gibbs</h3>
    <h4>111 St. James St, Montego Bay, Jamaica</h4>
    <h4>111 S. 2nd Ave, New York, NY</h4>

--------------------- >> end captured stdout << ----------------------

Ran 1 test in 0.389s

FAILED (failures=1)

A Note About Session Mappers and Elixir

If you are using Session.mapper(TheClass, the_table) instead of just plain ol’ mapper(...) then you are introducing a potential problem in that your objects will save themselves to the wrong session. You’ll need to fix it by setting save_on_init=False like this:

meta.Session.mapper(Address, t_addresses, save_on_init=False)
meta.Session.mapper(Person, t_people, properties = {...}, save_on_init=False)

For convenience, this is the default behavior in Elixir. If working with Elixir Entities then construct your entities like this:

class Person(Entity):
    name = Field(String(100))
    email = Field(String(100))
    has_many('addresses', of_kind='Address')
    # :

The side effect is that your app will always have to call person.save_or_update() whenever it wants to write data.

Why Do I Keep Getting InvalidRequestError?

If you’ve seen an error during unload like:

UnloadError: InvalidRequestError: Instance 'Person@0x227d130' with key
(<class 'addressbook.model.Person'>, (1,), None) is already persisted with a different identity
(with <addressbook.model.Person object at 0x227d130> in
<PersonData at 0x2272450 with keys ['joe_gibbs']>)

then it probably means you have either called data.setup() twice without calling data.teardown() in between or else you somehow saved the same Person() object to two different sessions. If using an in-memory database be sure you have commented out the code that runs setup-app in tests/ (see above). You also might see this if you forget to set save_on_init=False to your mapped classes (also see above).

Example Source

That’s it! Have fun.

This code is available from the fixture code repository in fixture/examples/pylons_example/addressbook.