Mock a method called on a mock object (execute method of cursor object generated by a mocked connection)

Question

I want to make a unit test for the following code.

But i can not try to correctly mock the cursor of my mocked connection. In the unit test, I mock first the connection of my tested code. But the cursor generated from this connection has never its execute method called in the unit test.

I just want to verify that the execute method is called once on the cursor object, but it see no call of this execute method. And i have a call of the execute method of the cursor object.

class DatabaseCaller:

    def __init__(self):
        self.host = os.getenv("HOSTNAME")
        self.port = os.getenv("PORT")
        self.service_name = os.getenv("SID")
        self.user = os.getenv("USER")
        self.password = os.getenv("PASSWORD")


    def user_exists(self, my_user: User) -> bool:
        this_user_exists: bool = True
        first_name_lowered = my_user.first_name.lower()
        last_name_lowered = my_user.last_name.lower()
        mail_lowered = my_user.mail.lower()
        sql_request = """SELECT email
            FROM users
            WHERE lower(firstname) = :first_name 
            AND lower(lastname) = :last_name 
            AND lower(emailaddress) = :mail"""
        bind_named_parameters = dict(first_name=first_name_lowered, last_name=last_name_lowered, mail=mail_lowered)
        try:
            with oracledb.connect(user=self.user, password=self.password,
                                  host=self.host, port=self.port, service_name=self.service_name) as connection:
                with connection.cursor() as cursor:
                    cursor.execute(sql_request, bind_named_parameters)
                    logger.info(f"Nb row users retrieved {cursor.rowcount}")
                    if cursor.rowcount == 0:
                        this_user_exists = False
        except Exception as exception:
            logger.info(f"Exception launched during the user exists sql method {exception}")
            raise exception
        return this_user_exists
    
    
    
    
    
    
    

For the test, i have this test case:

class DBTestCase(unittest.TestCase):

    def test_user_exists(self):
        with patch("oracledb.connect") as mock_connection:
            cursor = mock_connection.cursor.return_value
            cursor_bis = mock_connection.return_value.__enter__.cursor.return_value.__enter__
            print(type(mock_connection.return_value.__enter__.cursor.return_value.__enter__))
            print(type(mock_connection.__enter__.cursor.return_value.__enter__))

            mosaic_user = MosaicUser("Tom", "Rob", "[email protected]", "tomrob", 31)

            os.environ["HOSTNAME"] = "url_database"
            os.environ["PORT"] = "99"
            os.environ["SID"] = "sid"
            os.environ["USER"] = "user_for_database"
            os.environ["PASSWORD"] = "xxx"

            real_database_caller = MosaicDatabaseCaller()
            real_database_caller.user_exists(mosaic_user)

            assert real_database_caller.host == os.environ["HOSTNAME"]
            assert real_database_caller.port == os.environ["PORT"]
            assert real_database_caller.service_name == os.environ["SID"]
            assert real_database_caller.user == os.environ["USER"]
            assert real_database_caller.password == os.environ["PASSWORD"]

            mock_connection.assert_called_once_with(
                user=os.environ["USER"],
                password=os.environ["PASSWORD"],
                host=os.environ["HOSTNAME"],
                port=os.environ["PORT"],
                service_name=os.environ["SID"]
            )
            
            
            # this assert failed because the cursor is never called, the same thing with cursor_bis
            cursor.execute.assert_called_once()

The last line (even with cursor_bis) failed since the execute method on the cursor mock object is never called.

How can i make it works, like for the method connect of oracledb ?

Thank you very much,

Thomas