Mock a MySQL database in Python

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

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