how to unit test the database connection pymysql in python?

Question

I am trying to write unit test case to test the following method by mocking the database. How to mock the database connection without actually connecting to the real database server.I tried with sample test case. I am not sure if that is right way to do it. Please correct me if I am wrong.

//MySQL.py
    class MySQL():

        retry_interval = 20000 

        def link(self, server,port):
            try:
                return pymysql.connect(server, username='name', passwd='pwd', db='demo', int(port))
                sys.stdout.write('Connected to database at {}:{}\n'.format(server,port))
            except:
                sys.stderr.write('Retry database connection in {} ms...\n'.format(self.retry_interval))
                sleep(self.retry_interval/1000.0)
                continue

//test.py
from unittest.mock import Mock, MagicMock
from unittest.mock import patch
import MySQL

   @patch('MySQL.pymysql')
    def testLink(self, mysql_mock):
        mock_cursor = Mock.MagicMock()
        mysql_mock.connect.return_value.cursor.return_value.__enter__.return_value = mock_cursor

The limits of a unittest end where the edge of *your* code is reached. In your case `pymysql.connect` is out of the scope of a unittest and should be mocked. What you should test is if the mock is called properly. — Klaus D., Nov 06 '17 at 10:48

score 4 · Accepted Answer · answered Nov 06 '17 at 11:25

4

The only useful thing you can really test in MySQL.pymysql is the connect method, so you might as well patch that directly:

//test.py
from unittest.mock import Mock, MagicMock
from unittest.mock import patch, call
import MySQL

@patch('MySQL.pymysql.connect')
def testLink(self, connect_mock):
    # You can configure a return value for the connection if you need to...
    connect_mock.return_value = MagicMock(name='connection_return', return_value=...)

    # You can now test that your connection is being called...
    self.assertEqual(1, connect_mock.call_count)

    # You can also check the call parameters...
    self.assertEqual(connect_mock.call_args_list[0], call(server, username='name', passwd='pwd',...))

answered Nov 06 '17 at 11:25

DatHydroGuy

1,056
3
11
18

Hi Thanks for the help. I tried the same way as shown in the example code. But I am confused If i should mock the function "link" or only the call to "pymysql.connect" ? – user2301 Nov 07 '17 at 08:55
@patch('MySQL.pymysql.connect') gives unused import and SyntaxError: non-keyword arg after keyword arg – user2301 Nov 07 '17 at 09:29
Hi. I don't think you need to mock the `link` function to test the code you've given. You would mock the `pymysql.connect` function and then your test would _call_ the `link` function which would return the mocked connect object. – DatHydroGuy Nov 07 '17 at 10:28
The SyntaxError is most likely caused by the ordering of your arguments in your method declaration (see [here](https://stackoverflow.com/questions/14247732/python-syntaxerror-non-keyword-after-keyword-arg) for details). Try rearranging your pymysql.connect arguments so that the named arguments (usernam, password, db) come _after_ the positional arguments (server and port). – DatHydroGuy Nov 07 '17 at 10:31
Okay Thanks. I fixed that Syntax error. But I always get this error ImportError: No module named pymysql. I have installed the pymysql. Is this the right way to refer in patch @patch('MySQL.pymysql.connect')? – user2301 Nov 07 '17 at 10:43
An ImportError like the one you mention can be caused when your `$PYTHONPATH` doesn't contain the path to the module you are trying to import. This can be different to the path of the script you are trying to execute, so double-check them. More information can be found [here](https://stackoverflow.com/questions/2325923/how-to-fix-importerror-no-module-named-error-in-python). – DatHydroGuy Nov 07 '17 at 12:05
For people wondering why `@patch('MySQL.pymysql.connect')` does not wotk, you should try just `@patch('pymysql.connect')`. The `pymysql` could be already imported with `import MySQL` – Banik Aug 14 '22 at 10:39

how to unit test the database connection pymysql in python?

1 Answers1