Pytest with sqlalchemy db conection terminates in subsequent test case

Viewed 571

I am using a sqlalchemy engine connection as a pytest fixture as a way to test functions that require database interaction. When I run the test cases individually by running pytest tests/filename.py::test_get_token_data or pytest tests/filename.py::test_create the test passes; however, when running the entire test case, pytest tests/filename.py, I get the following error:

E       sqlalchemy.exc.OperationalError: (psycopg2.errors.AdminShutdown) terminating connection due to administrator command
E       server closed the connection unexpectedly
E               This probably means the server terminated abnormally
E               before or while processing the request.
(Background on this error at: http://sqlalche.me/e/13/e3q8)

To my understanding, after each test, the database is suppose to be cleared (which I've confirmed), however, I do not understand the error. My code below.

conftest.py

@pytest.fixture
def db_connection(test_config, admin_db_connection):
    config = test_config()
    engine = sqlalchemy.create_engine(config.DB_URL)
    connection = engine.connect()
    yield connection
    connection.close()

    # clear database
    from psycopg2.extensions import AsIs  # handle SQL quoting

    with admin_db_connection.cursor() as curs:
        curs.execute("drop database %s with (force);", (AsIs(config.DB_NAME),))
        curs.execute(
            "create database %s template vtag_template;", (AsIs(config.DB_NAME),)
        )

filename.py

import sqlalchemy as sa


@pytest.fixture
def db_injection(db_connection):
    with db_connection.begin():
        some_value = db_connection.execute(
            sa.sql.text(
                """insert into general.some_table as t (some_id, name, description, is_active) values (:some_id, :name, :description, :is_active) returning t.some_id;

                """
            ),
            tenant_id='1234',
            description="TEST",
            is_active=True,
            name="TEST",
        )
        tenant_id_ = some_value.first()[0]
         
                
@pytest.fixture
def some_function(db_connection):

    with db_connection.begin():
        some_table = db_connection.execute(
            sa.sql.text(
                """
                SELECT e.name
                FROM general.some_table e
                WHERE e.id = 1234
            """
            )
        )
    return some_table.first()


def test_get_token_data(client, headers_with_token, db_injection, some_function):

    token = some_function.name
    #API is using the same db connection 
    response = client.get(f"/api/v2/{token}", headers=headers_with_token)

    assert response.status_code == 200


def test_create(client, img, b_64, headers_with_token, db_injection):

    items = Items(
        user_id=1234,
        template_id=67,
        b_64=b_64,
    )

    data = {
        "json": json.dumps(asdict(items)),
        "file": ("some_name", img, "multipart/form-data"),
    }

    response = client.post(
        "/api/v2/create",
        data=data,
        follow_redirects=True,
        content_type="multipart/form-data",
        headers=headers_with_token
    )

    assert response.status_code == 200
1 Answers

The issue was due to the application having an unbinded Session being used. Since most of our application us using sqlalchemy's Engine API, we decide with refactor in using raw sql via with engine.begin(). Another alternative solution could have been adding the engine into the session

Related