CODEWITHSUNDEEP
pythondjangopostgresqltesting
AtlasStrategic
AtlasStrategic

Reputation: 317

How to create an external database when setting up Django test environment?

My Django application needs to connect an unmanaged (not by Django) external Postgres database.

For testing my project, I need to create the external DB and tables using raw SQL, and delete the DB afterwards.

From reading the Django documentation, one is able to create your own TestRunner class by using the existing DiscoverRunner class. The setup_test_environment and teardown_test_environment methods could be overridden to execute the SQL in order to create the external DB:

from psycopg2 import connect
from django.test.runner import DiscoverRunner

class CustomTestRunner(DiscoverRunner):
    """
    Test runner that helps to setup external db if doesn`t exist.
    """

    def setup_test_environment(self, *args, **kwargs):
        conn = connect(database='postgres', host='localhost', user='my_user', password='password123')
        try:
            with conn.cursor() as c:
                query = """CREATE DATABASE "test_db" """
                query += """WITH OWNER = my_user """
                query += """ENCODING = 'UTF8' TABLESPACE = pg_default """
                query += """LC_COLLATE = 'en_ZA.UTF-8' LC_CTYPE = 'en_ZA.UTF-8' """
                query += """CONNECTION LIMIT = -1;"""
                c.execute(query)
        except Exception as e:
            print e
            conn.close()

        super(CustomTestRunner, self).setup_test_environment(*args,
            **kwargs)

    def teardown_test_environment(self, *args, **kwargs):
        super(CustomTestRunner, self).teardown_test_environment(*args,
            **kwargs)

        # Delete external database after tests
        try:
            conn = connect(database='postgres', host='localhost', user='my_user', password='password123')
            with conn.cursor() as c:
                query = """DROP DATABASE test_db;"""
                c.execute(query)
        except Exception as e:
            print e
            conn.close()
        conn.close()

However, when I run the tests, I get the following error message:

CREATE DATABASE cannot run inside a transaction block

Is it possible to run and commit SQL within a Django TestRunner? If not, where is the appropriate place to create such an external DB?

Upvotes: 4

Views: 1024

Answers (1)

Derek Kwok
Derek Kwok

Reputation: 13058

Some database commands such as CREATE DATABASE can't run within a transaction. You'll need to set the proper isolation level on your database connection:

conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

See http://initd.org/psycopg/docs/extensions.html#isolation-level-constants.

It is also possible to run and commit SQL within a test runner. The more appropriate places to do this would be in the setup_databses and teardown_databases methods. Here's an example

def setup_databases(self, **kwargs):
    conn = connect(database='postgres', host='localhost', ...)
    try:        
        # set the proper isolation level so CREATE DATABASE works
        conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        with conn.cursor() as cur:
            # execute SQL in here
            query = 'create database TEST_DB'
            cur.execute(query)
    finally:
        conn.close()

    return super(CustomTestRunner, self).setup_databases(**kwargs)

The implementation for teardown_databases should be quite similar.

Upvotes: 1

Related Questions