I am writing unit tests for testing querying a database using SQLAlchemy. I seem to have the mocks setup correctly to mock the create_engine, connection, execute, and all methods for the query, the results return with the proper return value in the .all() return, but it should be a two dimensional list, but it keeps returning the Mock object itself instead of the return_value.

My unit test:

import unittest
import datetime
from unittest.mock import patch, MagicMock
from costing.modules._common import db_query as dbq
from sqlalchemy.engine.base import Engine, Connection
from sqlalchemy.engine.result import Result
from costing.modules._common.db_url import DatabaseClient


class TestDbQuery(unittest.TestCase):
    @patch.dict('os.environ', {
        'PSQL_URL': 'localhost:5432/testdb',
        'PSQL_USER': 'testuser',
        'PSQL_PASS': 'testpass'
    })
    @patch.object(dbq, 'create_engine')
    @patch.object(Engine, 'begin')
    @patch.object(Connection, 'execute')
    @patch.object(Result, 'all')
    def test_query_date_range(self, mock_result, mock_execute, mock_begin, mock_create_engine):
        # Arrange
        start_date = datetime.datetime(2021, 1, 1)
        end_date = datetime.datetime(2021, 1, 31)
        dc = DatabaseClient()
        mock_test = mock_create_engine().begin().__enter__().execute().all()
        mock_test.__getitem__(0).__getitem__(0).return_value = start_date
        mock_result.side_effect = [[start_date], [end_date]]  # Not used during debugging
        mock_create_engine.begin.return_value = mock_begin
        mock_begin.execute.return_value = mock_execute
        mock_execute.return_value.all.return_value = mock_test

        de = dbq.DatabaseEngine(db_url=dc.psql_url)
        result_return = [
            {
                'subscription_id': '123',
                'start_date': '2021-01-01',
                'end_date': '2021-01-31'
            }
        ]

        # Act
        result = de.query_date_range(subscription_id='123')

        # Assert
        
        self.assertIsNotNone(result)
        assert result[0] == result_return[0] 

Portion of the code being tested:

        with self.engine.begin() as conn:
            start_date: list = conn.execute(start_query).all()
            end_date: list = conn.execute(end_query).all()

        results = [
            {
                'subscription_id': subscription_id,
                'start_date': dt.strftime(start_date[0][0], date_format),
                'end_date': dt.strftime(end_date[0][0], date_format)
            }
        ]

I’ve tried using side_effect as well as different mock objects. In the debug console I can hit start_date[0][0] and it returns a mock object but if I hit start_date[0][0].return_value it gives me my datetime object.