python mocking sqlalchemy connection

Question

I have a simple function that connects to a DB and fetches some data.

db.py

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool


def _create_engine(app):
    impac_engine = create_engine(
        app['DB'],
        poolclass=NullPool  # this setting enables NOT to use Pooling, preventing from timeout issues.
    )
    return impac_engine


def get_all_pos(app):
    engine = _create_engine(app)
    qry = """SELECT DISTINCT id, name FROM p_t ORDER BY name ASC"""
    try:
        cursor = engine.execute(qry)
        rows = cursor.fetchall()
        return rows
    except Exception as re:
        raise re

I'm trying to write some test cases by mocking this connection -

tests.py

import unittest
from db import get_all_pos
from unittest.mock import patch
from unittest.mock import Mock


class TestPosition(unittest.TestCase):

    @patch('db.sqlalchemy')
    def test_get_all_pos(self, mock_sqlalchemy):
        mock_sqlalchemy.create_engine = Mock()
        get_all_pos({'DB': 'test'})




if __name__ == '__main__':
    unittest.main()

When I run the above file python tests.py, I get the following error -

   "Could not parse rfc1738 URL from string '%s'" % name
sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'test'

Shouldn't mock_sqlalchemy.create_engine = Mock() give me a mock object and bypass the URL check.

can you elaborate on what the desired assertion is? the test currently has no assertions in it. also you need to mock _specific_ modules inside `sqlalchemy` not the whole library itself so your problem appears to be two-fold — gold_cy, Feb 17 '20 at 21:41
This should be my assertion - mock_requests.create_engine.cursor.fetch_all.assert_called_once()...Should I mock sqlalchemy object and then mock each and every method separately ie; mock_sqlalchemy.create_engine.return_value = mock_engine — user1050619, Feb 17 '20 at 21:50
yes you will have to create the objects that implement the methods that your `engine` does like `execute`. — gold_cy, Feb 17 '20 at 21:56
Your function doesn't use `db.sql_alchemy.create_engine`; it uses `db.create_engine`; that's what you need to mock. — chepner, Feb 18 '20 at 01:25

gold_cy · Accepted Answer · 2020-02-18T01:26:53.203

Another option would be to mock your _create_engine function. Since this is a unit test and we want to test get_all_pos we shouldn't need to rely on the behavior of _create_engine, so we can just patch that like so.

import unittest
import db
from unittest.mock import patch


class TestPosition(unittest.TestCase):

    @patch.object(db, '_create_engine')
    def test_get_all_pos(self, mock_sqlalchemy):
        args = {'DB': 'test'}
        db.get_all_pos(args)
        mock_sqlalchemy.assert_called_once()
        mock_sqlalchemy.assert_called_with({'DB': 'test'})


if __name__ == '__main__':
    unittest.main()

If you want to test certain results you will need to properly set all the corresponding attributes. I would recommend not chaining it into one call so that it is more readable as shown below.

import unittest
import db
from unittest.mock import patch
from unittest.mock import Mock


class Cursor:
    def __init__(self, vals):
        self.vals = vals

    def fetchall(self):
        return self.vals


class TestPosition(unittest.TestCase):

    @patch.object(db, '_create_engine')
    def test_get_all_pos(self, mock_sqlalchemy):
        to_test = [1, 2, 3]

        mock_cursor = Mock()
        cursor_attrs = {'fetchall.return_value': to_test}
        mock_cursor.configure_mock(**cursor_attrs)

        mock_execute = Mock()
        engine_attrs = {'execute.return_value': mock_cursor}
        mock_execute.configure_mock(**engine_attrs)

        mock_sqlalchemy.return_value = mock_execute

        args = {'DB': 'test'}
        rows = db.get_all_pos(args)

        mock_sqlalchemy.assert_called_once()
        mock_sqlalchemy.assert_called_with({'DB': 'test'})
        self.assertEqual(to_test, rows)

no problem, however this test doesn't really test the outcome of `get_all_pos` so I would suggest implementing behavior for `execute` and `fetchall` that way you can check your two logic branches (`try/except`) — gold_cy, Feb 17 '20 at 22:11
@user1050619 check the update, it should provide what you are looking for, you should add a test to test for the exception branch as well — gold_cy, Feb 18 '20 at 01:27

python mocking sqlalchemy connection

1 Answers1