How to mock cursor.execute

Question

I am trying to write a test for the below code without integrating a mysql database. Is it possible?

def sql_query_select(select, table, condition, variable):
    cursor = database_connection("cursor")
    sql_query = "SELECT " + select + " from " + table + " where " + condition + " = " + "'" + variable + "'"
    cursor.execute(sql_query)
    try:
        response = cursor.fetchone()[0]
    except:
        response = cursor.fetchone()
    cursor.close()
    return response

I have tried the following code as a test

@mock.patch("lambda_function.database_connection")
    @mock.patch("pymysql.cursors.Cursor.execute")
    def test_sql_query_select(self, mock_database_connection, mock_execute):
        mock_database_connection.return_value = "cursor"
        mock_execute.return_value = "test"
        self.assertEqual(lambda_function.sql_query_select("select", "table", "condition", "variable"), "execute")

But get the following error and am not sure how to proceed.

E       AttributeError: 'str' object has no attribute 'execute'

Answer 1

You are already trying to mock database_connection(). You don't need to also mock cursor.execute() since you can inject the behavior you want to the return value of database_connection(). The problem is that you need to mock objects where they are used rather than where they are defined. It looks like sql_query_select() is in a file named lambda_function.py, so the mock looks like this:

@mock.patch(lambda_function.database_connection)
def test_sql_query_select(self, mock_database_connection):
    mock_record = ('record one', 'record two')
    mock_database_connection.return_value.fetchone.return_value = mock_record
    result = lambda_function.sql_query_select("select", "table", "condition", "variable")
    self.assert_equal(mock_record, result)

One problem with this kind of test is that it completely ignores the parameters to sql_query_select(). One solution is to verify that database_connection.return_value.execute was called.

This is also unsatisfying since you are testing implementation rather than behavior.