Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating databases in SQLAlchemy tests with PostgreSQL

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.

like image 966
Mikko Ohtamaa Avatar asked Apr 08 '15 06:04

Mikko Ohtamaa


People also ask

Can you use SQLAlchemy with PostgreSQL?

This SQLAlchemy engine is a global object which can be created and configured once and use the same engine object multiple times for different operations. The first step in establishing a connection with the PostgreSQL database is creating an engine object using the create_engine() function of SQLAlchemy.

Can you create a database with SQLAlchemy?

By passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.

What database does SQLAlchemy use?

Since SQLAlchemy relies on the DBAPI specification to interact with databases, the most common database management systems available are supported. PostgreSQL, MySQL, Oracle, Microsoft SQL Server, and SQLite are all examples of engines that we can use alongside with SQLAlchemy.

Is SQLAlchemy worth learning?

SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.


1 Answers

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

Test Packages

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.

like image 88
Sergey Avatar answered Sep 20 '22 11:09

Sergey