Mock a MySQL database in Python

Question

I use Python 3.4 from the Anaconda distribution. Within this distribution, I found the pymysql library to connect to an existing MySQL database, which is located on another computer.

import pymysql
config = {
      'user': 'my_user',
      'passwd': 'my_passwd',
      'host': 'my_host',
      'port': my_port
    }

    try:
        cnx = pymysql.connect(**config)
    except pymysql.err.OperationalError : 
        sys.exit("Invalid Input: Wrong username/database or password")

I now want to write test code for my application, in which I want to create a very small database at the setUp of every test case, preferably in memory. However, when I try this out of the blue with pymysql, it cannot make a connection.

def setUp(self):
    config = {
      'user': 'test_user',
      'passwd': 'test_passwd',
      'host': 'localhost'
    }
    cnx = pymysql.connect(**config)

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")

I have been googling around, and found some things about SQLite and MySQLdb. I have the following questions:

1. Is sqlite3 or MySQLdb suitable for creating quickly a database in memory?
2. How do I install MySQLdb within the Anaconda package?
3. Is there an example of a test database, created in the setUp? Is this even a good idea?

I do not have a MySQL server running locally on my computer.

Answer 1

You can mock a mysql db using testing.mysqld (pip install testing.mysqld)

Due to some noisy error logs that crop up, I like this setup when testing:

import testing.mysqld
from sqlalchemy import create_engine

# prevent generating brand new db every time.  Speeds up tests.
MYSQLD_FACTORY = testing.mysqld.MysqldFactory(cache_initialized_db=True, port=7531)


def tearDownModule():
    """Tear down databases after test script has run.
    https://docs.python.org/3/library/unittest.html#setupclass-and-teardownclass
    """
    MYSQLD_FACTORY.clear_cache()


class TestWhatever(unittest.TestCase):

    @classmethod
    def setUpClass(cls):
        cls.mysql = MYSQLD_FACTORY()
        cls.db_conn = create_engine(cls.mysql.url()).connect()

    def setUp(self):
        self.mysql.start()
        self.db_conn.execute("""CREATE TABLE `foo` (blah)""")

    def tearDown(self):
        self.db_conn.execute("DROP TABLE foo")

    @classmethod
    def tearDownClass(cls):
        cls.mysql.stop()  # from source code we can see this kills the pid

    def test_something(self):
        # something useful

Answer 2

Both pymysql, MySQLdb, and sqlite will want a real database to connect too. If you want just to test your code, you should just mock the pymysql module on the module you want to test, and use it accordingly (in your test code: you can setup the mock object to return hardcoded results to predefined SQL statements)

Check the documentation on native Python mocking library at: https://docs.python.org/3/library/unittest.mock.html

Or, for Python 2: https://pypi.python.org/pypi/mock