Mocking dbconnection fetchall using python

Question

I am trying to mock the fetchall() from dbconnection cursor object. I am trying the following code with expected return value. However, it was not returning the value. I have the answer now and edited the unit test to include the answer also db.py

def query_db(db_connection, query):
    """

    :param db_connection: dbconnection object
    :param query: query to be executed
    :return:
    """
    cur = db_connection.cursor()
    try:
        logging.info(f"Query to be executed : {query}")
        cur.execute(query)
        results = cur.fetchall()
        logging.info(f"Query Results : {results}")
    except Exception:
        logging.exception("Exception while executing \'query_db\' function")
        raise Exception(f"Error while executing query : {query}. Please check the logs for details")
    return results

Test Case:

    def test_get_client_object(self):
        dbconnection = Mock(name="dbconnection")
        mycursor = Mock(name="mycursor")
        mycursor.fetchall.return_value = "testing_return_value"
        dbconnection.cursor.return_value = mycursor  # I was doing dbconnection.cursor = mycursor .  ... which caused the error

        self.assertEqual("testing_return_value", utils.query_db(dbconnection, 12345))

I got the follwing assertion error. It returned a mock object instead of expected return value.

<Mock name='mycursor().fetchall()' id='4443879760'> != testing_return_value

Expected :testing_return_value
Actual   :<Mock name='mycursor().fetchall()' id='4443879760'>
<Click to see difference>

Try reading this https://realpython.com/python-mock-library/ to better understand mocks. — luistm, Dec 07 '19 at 14:16
It was a small mistake and it worked after making that change — SunilS, Dec 07 '19 at 14:30
It would be nice if you could provide the correction. Thanks — luistm, Dec 07 '19 at 14:30

Lin Du · Accepted Answer · 2019-12-10T08:59:56.480

Here is the unit test solution:

utils.py:

def query_db(db_connection, query):
    cur = db_connection.cursor()
    try:
        print(f"Query to be executed : {query}")
        cur.execute(query)
        results = cur.fetchall()
        print(f"Query Results : {results}")
    except Exception:
        print("Exception while executing \'query_db\' function")
        raise Exception(
            f"Error while executing query : {query}. Please check the logs for details")
    return results

test_utils.py:

import unittest
from unittest.mock import Mock
import utils


class TestUtils(unittest.TestCase):
    def test_get_client_object(self):
        dbconnection = Mock(name="dbconnection")
        mycursor = Mock(name="mycursor")
        mycursor.fetchall.return_value = "testing_return_value"
        dbconnection.cursor.return_value = mycursor

        self.assertEqual("testing_return_value",
                         utils.query_db(dbconnection, 12345))
        dbconnection.cursor.assert_called_once()
        mycursor.execute.assert_called_once_with(12345)
        mycursor.fetchall.assert_called_once()

    def test_query_db_exception(self):
        dbconnection = Mock(name="dbconnection")
        mycursor = Mock(name="mycursor")
        mycursor.fetchall.side_effect = Exception
        dbconnection.cursor.return_value = mycursor

        with self.assertRaises(Exception) as cm:
            utils.query_db(dbconnection, 12345)
        self.assertEqual(str(
            cm.exception), 'Error while executing query : 12345. Please check the logs for details')


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

Unit test result with 100 coverage report:

test_get_client_object (__main__.TestUtils) ... Query to be executed : 12345
Query Results : testing_return_value
ok
test_query_db_exception (__main__.TestUtils) ... Query to be executed : 12345
Exception while executing 'query_db' function
ok

----------------------------------------------------------------------
Ran 2 tests in 0.002s

OK

Name                                       Stmts   Miss  Cover   Missing
------------------------------------------------------------------------
src/stackoverflow/59226762/test_utils.py      22      0   100%
src/stackoverflow/59226762/utils.py           11      0   100%
------------------------------------------------------------------------
TOTAL                                         33      0   100%

Source code: https://github.com/mrdulin/python-codelab/tree/master/src/stackoverflow/59226762

I had to remove the ```.return_value``` when setting the dbconnection cursor equal to mycursor to get the expected behavior, like this: ```dbconnection.cursor= mycursor``` — Adventure-Knorrig, May 16 '23 at 10:02

score 1 · Answer 2 · answered Nov 03 '20 at 16:16

The accepted answer didn't work for me and I was still getting chained calls to fetchall(). I mocked all three levels including fetchall as well and then it worked, maybe it helps someone:

test:

expected = ["{\"parameter\":\"1337\"}"]
myconnection = mocker.Mock(name="dbconnection")
mycursor = mocker.Mock(name="mycursor")
myfetchall = mocker.Mock(name="myfetchall")  # I needed to mock this as well
myfetchall.fetchall.return_value = expected
mycursor.execute.return_value = myfetchall
myconnection.cursor.return_value = mycursor

function:

cursor = connection.cursor()
return cursor.execute(sqlstatement).fetchall()

Mocking dbconnection fetchall using python

2 Answers2