Python Unit Test : How to unit test the module which contains database operations?

Question

I am using pymysql client library to connect to the real database. I have a function in module, where I connect to the database using pymysql and do only database insert operations.How to unit test this function in python without hitting the real database?

import pymysql

def connectDB(self):

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db')

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('newuser@some.com', 'newpassword'))


    connection.commit()

My python version is 2.7.

Mocking the database would be an option `https://docs.python.org/3/library/unittest.mock.html` — mbieren, Nov 07 '17 at 14:18
Quick remark: Maybe `connectDB` may not be the best name for a method also performing queries :) — Eduardo, Jul 17 '18 at 09:34

uwevil · Accepted Answer · 2017-11-07T14:42:46.373

17

You can use patch, like this:

from unittest.mock import patch, MagicMock

@patch('mypackage.mymodule.pymysql')
def test(self, mock_sql):
    self.assertIs(mypackage.mymodule.pymysql, mock_sql)

    conn = Mock()
    mock_sql.connect.return_value = conn

    cursor      = MagicMock()
    mock_result = MagicMock()

    cursor.__enter__.return_value = mock_result
    cursor.__exit___              = MagicMock()

    conn.cursor.return_value = cursor

    connectDB()

    mock_sql.connect.assert_called_with(host='localhost',
                                        user='user',
                                        password='passwd',
                                        db='db')

    mock_result.execute.assert_called_with("sql request", ("user", "pass"))

edited Nov 07 '17 at 14:42

answered Nov 07 '17 at 14:35

uwevil

660
6
12

My python version is 2.7. " unittest.mock" is available from Python 3.3 onwards. – user2301 Nov 07 '17 at 15:03
You can use package Mock for python 2.7: https://pypi.python.org/pypi/mock – uwevil Nov 07 '17 at 15:06
What is 'mock_sql' though? More context in the code is lacking. – Banik Aug 14 '22 at 09:29
1

mock_sql is your package which contains your sql driver you need test. – uwevil Sep 12 '22 at 06:58

robert · Answer 2 · 2017-11-07T14:18:07.827

You need a series of fake databases, called stubs, which return hardcoded values. During the test these stubs are used instead of the real database. I am not familiar with Python, but one way to do this in C++ is to make your object to receive the database as a constructor parameter. In production code you use a real database parameter, in the test the stub. This can be done because the constructor expects a pointer to a common base class. Even it is not written for Python I suggest to read the first chapters from Roy Osherove: The art of unit testing. The book clearly explains why these fake databases are stubs and not mocks.

Jared Smith · Answer 3 · 2021-04-02T14:46:35.517

You've just rediscovered one of the most compelling reasons why testing is important: it tells you when your design is bad.

To put it slightly differently, testability is a good first-order proxy for quality. Consider the following:

class DB(object):
    def __init__(self, **credentials):
        self._connect = partial(pymysql.connect, **credentials)

    def query(self, q_str, params):
        with self._connect as conn:
            with conn.cursor() as cur:
                cur.execute(q_str, params)
                return cur.fetchall()

# now for usage

test_credentials = {
    # use credentials to a fake database
}

test_db = DB(**test_credentials)
test_db.query(write_query, list_of_fake_params)
results = test_db.query(read_query)
assert results = what_the_results_should_be

If you work with multiple databases you could use polymorphism or depending on API similarity make the specific DB be a constructor parameter to your object.

Python Unit Test : How to unit test the module which contains database operations?

3 Answers3

Linked