MySQL Database Layout in Python

Question

I am using Python 3.4 from the Anaconda distribution. As part of 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") 

Now I want to write test code for my application in which I want to create a very small database in the setUp each test case, preferably in memory. However, when I try to do this in blue using pymysql , it cannot establish 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 searched for search queries and found something about SQLite and MySQLdb . I have the following questions:

• Is sqlite3 or MySQLdb suitable for quickly creating a database in memory?
• How to install MySQLdb in Anaconda package?
• Is there an example test database created in setUp ? Is that even a good idea?

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

Answer 1

Both pymysql, MySQLdb and sqlite will want to connect to a real database. If you want to just check your code, you just have to make fun of the pymysql module in the module you want to test and use it accordingly (in your test code: you can configure the mock object to return hardcoded results to predefined SQL statements)

Check out the documentation for the Python mocking built-in library at: https://docs.python.org/3/library/unittest.mock.html

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

Answer 2

You can pip install testing.mysqld mysql using testing.mysqld ( pip install testing.mysqld )

Due to some noisy error logs that occur , I like this setting 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