Creating databases in SQLAlchemy tests using PostgreSQL

Question

I am creating a Pyramid web application that is built on top of SQLAlchemy and uses only PostgreSQL as the database.

What is the way to have a unit test structure so that

• The database is created once for each test run - not for each test setUp(), because it is too slow for a complex application

• Database tables (re) are created as they will be created during production (for example, starting a migration from Alembic). Any unclean databases are destroyed at the beginning of the test run.

• You can choose your own la test runner py.testif specific functions outside the unittest framework make it easy to write test cases.

Answer 1

Nose test runner supports setup_package()and methods teardown_package(). Here is an excerpt from the documents:

Test packages

Nose

allows you to group tests into test packages. This allows packet level adjustment; for example, if you need to create a database test or other data for your tests, you can create it in the package installation and delete it when you break the package once per test run, instead of creating and breaking it once on the test module or test example.

, / init.py . setup, setup_package, setUp setUpPackage; teardown, teardown_package, tearDown tearDownPackage. .

setup_package(), :

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

, , , , tearDown:

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

setUp, tearDown - , , .