Database testing in python, postgresql

pg_tmp(1) is a utility intended to make this task easy. Here is how you might start up a new connection with SQLAlchemy:

from subprocess import check_output
from sqlalchemy import create_engine

url = check_output(['pg_tmp', '-t'])
engine = create_engine(url)

This will spin up a new database that is automatically destroyed in 60 seconds. If a connection is open pg_tmp will wait until all active connections are closed.

Have you tried testing.postgresql?

You can use nose to write your tests, then just use SQLAlchemy to create and clean the test database in your setup/teardown methods.