SQLAlchemy创建MySQL引擎,使用PostgreSQL在SQLAlchemy测试中创建数据库

I am building a Pyramid web application which is built on the top of SQLAlchemy and solely relies PostgreSQL as its database backend.

What would be a way to have the unit tests structure so that

Database is built once per test run - not on every test setUp() as this is too slow for a complex application

Database tables are (re)created as they would be created in production (e.g. run migrations from Alembic). Any unclean databases are destroyed at the start of the test run.

It is possible to choose a custom test runner á la py.test if a specific features outside the standard library unittest framework makes it easier to write test cases.

解决方案

Nose test runner supports setup_package() and teardown_package() methods. Here's an excerpt from the docs:

nose allows tests to be grouped into test packages. This allows

package-level setup; for instance, if you need to create a test

database or other data fixture for your tests, you may create it in

package setup and remove it in package teardown once per test run,

rather than having to create and tear it down once per test module or

test case.

To create package-level setup and teardown methods, define setup

and/or teardown functions in the init.py of a test package. Setup

methods may be named setup, setup_package, setUp, or setUpPackage;

teardown may be named teardown, teardown_package, tearDown or

tearDownPackage. Execution of tests in a test package begins as soon

as the first test module is loaded from the test package.

In my application I have setup_package() which looks roughly like the following:

def _create_database():

template_engine = sa.create_engine("postgres://postgres@/postgres", echo=False)

conn = template_engine.connect()

conn = conn.execution_options(autocommit=False)

conn.execute("ROLLBACK")

try:

conn.execute("DROP DATABASE %s" % DB_NAME)

except sa.exc.ProgrammingError as e:

# Could not drop the database, probably does not exist

conn.execute("ROLLBACK")

except sa.exc.OperationalError as e:

# Could not drop database because it's being accessed by other users (psql prompt open?)

conn.execute("ROLLBACK")

conn.execute("CREATE DATABASE %s" % DB_NAME)

conn.close()

template_engine.dispose()

def setup_package():

_create_database()

engine = sa.create_engine("postgres://postgres@/%s" % DB_NAME, echo=False)

session = sa.orm.scoped_session(sa.orm.sessionmaker())

session.configure(bind=engine)

Base.metadata.bind = engine

Base.metadata.create_all()

def teardown_package():

# no need to do anything as the old database is dropped at the start of every run

In addition, all test case classes are subclassed from a base class, which, importantly, defines a common tearDown method:

class BaseTest(unittest.TestCase):

def setUp(self):

# This makes things nicer if the previous test fails

# - without this all subsequent tests fail

self.tearDown()

self.config = testing.setUp()

def tearDown(self):

testing.tearDown()

session.expunge_all()

session.rollback()

Subclasses often override base setUp, but there usually no need to override tearDown - by rolling back the transaction it ensures that the next test will start on a completely clean database.