Python Testing with a Mock Database (SQL)

There are plenty of tutorials on the internet on using unittest but something I couldn’t find while doing a project was a tutorial on how to mock a database for testing.

I believe a similar method can be used for pytest as well.

When doing continual testing as the software is developed or improved upon, testing has to be done to ensure expected functionality.

There may be methods or functions that can alter the data in the database. When testing these functions, it’s best to use a separate database. It’s most definitely not recommended to use the production database while testing.

When testing is automated it’s not possible to manually change the database that each function is using. So, it’s necessary to patch in the test database to the production database. For that, we use the patch function available in the mock package. This is available in the Python standard library, available as unittest.mock, but for this tutorial, we’ll be using the mock package.

Requirements

You need the unittest package, patch from mock, and a mysql connector.

Setting up

I have two central functions that directly connect to the database, so all the patching will be done to these functions.

import mysql.connector
from mysql.connector import errorcode

config = {
    'host': MYSQL_HOST,
    'user': MYSQL_USER,
    'password': MYSQL_PASSWORD,
    'database': MYSQL_DB
}

def db_read(query, params=None):
    try:
        cnx = mysql.connector.connect(**config)
        cursor = cnx.cursor(dictionary=True)
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)

        entries = cursor.fetchall()
        cursor.close()
        cnx.close()

        content = []

        for entry in entries:
            content.append(entry)

        return content

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("User authorization error")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database doesn't exist")
        else:
            print(err)
    else:
        cnx.close()
    finally:
        if cnx.is_connected():
            cursor.close()
            cnx.close()
            print("Connection closed")
       
def db_write(query, params=None):
    try:
        cnx = mysql.connector.connect(**config)
        cursor = cnx.cursor(dictionary=True)
        try:
            cursor.execute(query, params)
            cnx.commit()
            cursor.close()
            cnx.close()
            return True

        except MySQLdb._exceptions.IntegrityError:
            cursor.close()
            cnx.close()
            return False

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("User authorization error")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database doesn't exist")
        else:
            print(err)
        return False
    else:
        cnx.close()
        return False
    finally:
        if cnx.is_connected():
            cursor.close()
            cnx.close()
            print("Connection closed")

The above code is part of the production code. Therefore the config information in it will be of the production database.

During testing, we will be patching the config variable with the details of the test database.

So, with that out of way, let’s look at how we can set up the mock database.

Creating the MockDB class

For this, you need to have a MySQL server running on the system you wish to run the test on.
Every time a test suite is run, a temporary database is created and once the tests are completed, it is dropped.

First the imports

import mysql.connector
from mysql.connector import errorcode
from unittest import TestCase
from mock import patch
import utils

Here utils is the code given above named as utils.py

Since you’ll probably need to use the same test database for several test cases, it is easier to create a superclass. This superclass itself needs to be a subclass of the unittest.TestCase class.

class MockDB(TestCase):

Through this, MockDB class inherits four methods
1. SetUpClass()
2. SetUp()
3.TearDownClass()
4.TearDown()

These methods will help us “setup” a temporary database and “teardown” that database at the end.

SetUp() method runs before every test within a single class or test case. TearDown() method runs after every test within that test case. These are instance methods.

SetUpClass() and TearDownClass() are class methods. They run once for a single test case. That is, SetUpClass() will run before all the tests in the relevant test case and TearDownClass() will run after all the tests are done.

You can choose to use either of these two methods depending on the requirement. If you need the database to set up for each test then the first method is best suited for you. If you can manage with the database being set up only once then the second method is better suited. One thing to note is that the first method will result in slower tests. Depending on the number of tests you have, it will continue to become slower. The second method in comparison will be significantly faster. For this tutorial, I’ll be using the second method.

Creating the SetUpClass method

@classmethod
def setUpClass(cls):
  cnx = mysql.connector.connect(
      host=MYSQL_HOST,
      user=MYSQL_USER,
      password=MYSQL_PASSWORD,
      port = MYSQL_PORT
  )
  cursor = cnx.cursor(dictionary=True)

First, we define our database connection. Then we check if the database is already created and drop it if it has been.

# drop database if it already exists
  try:
      cursor.execute("DROP DATABASE {}".format(MYSQL_DB))
      cursor.close()
      print("DB dropped")
  except mysql.connector.Error as err:

Afterwards, we create the database, create the necessary tables and insert data to those tables.

cursor = cnx.cursor(dictionary=True)
#create database
  try:
      cursor.execute(
          "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(MYSQL_DB))
  except mysql.connector.Error as err:
      print("Failed creating database: {}".format(err))
      exit(1)
  cnx.database = MYSQL_DB
  
#create table

  query = """CREATE TABLE `test_table` (
            `id` varchar(30) NOT NULL PRIMARY KEY ,
            `text` text NOT NULL,
            `int` int NOT NULL
          )"""
  try:
      cursor.execute(query)
      cnx.commit()
  except mysql.connector.Error as err:
      if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
          print("test_table already exists.")
      else:
          print(err.msg)
  else:
      print("OK")
      
#insert data

  insert_data_query = """INSERT INTO `test_table` (`id`, `text`, `int`) VALUES
                      ('1', 'test_text', 1),
                      ('2', 'test_text_2',2)"""
  try:
      cursor.execute(insert_data_query)
      cnx.commit()
  except mysql.connector.Error as err:
      print("Data insertion to test_table failed \n" + err)
  cursor.close()
  cnx.close()

Patching config

Once setting up the test database is done, we need to create a config variable with the test database information to patch the production database.

testconfig ={
      'host': MYSQL_HOST,
      'user': MYSQL_USER,
      'password': MYSQL_PASSWORD,
      'database': MYSQL_DB
  }
  cls.mock_db_config = patch.dict(utils.config, testconfig)

testconfig holds the details of the test database. mock_db_config is used to patch the config variable in the utils file with testconfig. Since these are dictionaries, patch.dict is used.

Creating the TearDownClass method

Next, we need the tearDownClass method. All this needs to do is drop the test database.

@classmethod
def tearDownClass(cls):
    cnx = mysql.connector.connect(
        host=MYSQL_HOST,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD
    )
    cursor = cnx.cursor(dictionary=True)

    # drop test database
    try:
        cursor.execute("DROP DATABASE {}".format(MYSQL_DB))
        cnx.commit()
        cursor.close()
    except mysql.connector.Error as err:
        print("Database {} does not exists. Dropping db failed".format(MYSQL_DB))
    cnx.close()

MockDB Class

Here is the entire MockDB class.

from unittest import TestCase
import mysql.connector
from mysql.connector import errorcode
from mock import patch
import utils


MYSQL_USER = "root"
MYSQL_PASSWORD = ""
MYSQL_DB = "testdb"
MYSQL_HOST = "localhost"
MYSQL_PORT = "3306"


class MockDB(TestCase):

    @classmethod
    def setUpClass(cls):
        cnx = mysql.connector.connect(
            host=MYSQL_HOST,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            port = MYSQL_PORT
        )
        cursor = cnx.cursor(dictionary=True)

        # drop database if it already exists
        try:
            cursor.execute("DROP DATABASE {}".format(MYSQL_DB))
            cursor.close()
            print("DB dropped")
        except mysql.connector.Error as err:
            print("{}{}".format(MYSQL_DB, err))

        cursor = cnx.cursor(dictionary=True)
        try:
            cursor.execute(
                "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(MYSQL_DB))
        except mysql.connector.Error as err:
            print("Failed creating database: {}".format(err))
            exit(1)
        cnx.database = MYSQL_DB

        query = """CREATE TABLE `test_table` (
                  `id` varchar(30) NOT NULL PRIMARY KEY ,
                  `text` text NOT NULL,
                  `int` int NOT NULL
                )"""
        try:
            cursor.execute(query)
            cnx.commit()
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("test_table already exists.")
            else:
                print(err.msg)
        else:
            print("OK")

        insert_data_query = """INSERT INTO `test_table` (`id`, `text`, `int`) VALUES
                            ('1', 'test_text', 1),
                            ('2', 'test_text_2',2)"""
        try:
            cursor.execute(insert_data_query)
            cnx.commit()
        except mysql.connector.Error as err:
            print("Data insertion to test_table failed \n" + err)
        cursor.close()
        cnx.close()

        testconfig ={
            'host': MYSQL_HOST,
            'user': MYSQL_USER,
            'password': MYSQL_PASSWORD,
            'database': MYSQL_DB
        }
        cls.mock_db_config = patch.dict(utils.config, testconfig)

    @classmethod
    def tearDownClass(cls):
        cnx = mysql.connector.connect(
            host=MYSQL_HOST,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD
        )
        cursor = cnx.cursor(dictionary=True)

        # drop test database
        try:
            cursor.execute("DROP DATABASE {}".format(MYSQL_DB))
            cnx.commit()
            cursor.close()
        except mysql.connector.Error as err:
            print("Database {} does not exists. Dropping db failed".format(MYSQL_DB))
        cnx.close()

Once MockDB class is done, we can inherit this class to create test cases.

Testing

We’ll write tests to test the functions in utils.py.

We need to import the file we are planning to test, the MockDB class and patch from import

import utils
from mock_db import MockDB
from mock import patch

When using the patch for the config variable, the following has to be used,

with self.mock_db_config:

Any code that is executed within this, will use the patched config variables.

Here is the completed test_utils code with some sample tests

from mock_db import MockDB
from mock import patch
import utils

class TestUtils(MockDB):

    def test_db_write(self):
        with self.mock_db_config:
            self.assertEqual(utils.db_write("""INSERT INTO `test_table` (`id`, `text`, `int`) VALUES
                            ('3', 'test_text_3', 3)"""), True)
            self.assertEqual(utils.db_write("""INSERT INTO `test_table` (`id`, `text`, `int`) VALUES
                            ('1', 'test_text_3', 3)"""), False)
            self.assertEqual(utils.db_write("""DELETE FROM `test_table` WHERE id='1' """), True)
            self.assertEqual(utils.db_write("""DELETE FROM `test_table` WHERE id='4' """), True)

I hope this provided you with a basic understanding of how to use a mock database for testing with unittest. This tutorial is aimed at providing users with a foundation on mocking a database for testing. Your implementation of this will depend on factors such as the use of an ORM for example.

Originally published by Minul Lamahewage at Medium

#mock #python #testing #sql