A very important factor to determine before writing any unit test, is what its scope should be. In other words a unit test should be written with clear objectives in mind, as to what it’s going to test, and anything outside this scope should be ignored as far as possible. This means that if we would like to verify business rules applied to data, then we should not test anything else. This includes the low level data access methods responsible for returning and transforming the data from the database.
Mocks come in extremely handy in this scenario. They can be used to return generated data from my data access methods that reflect an ideal situation where result-sets are 100% correct. However this creates an overly artificial test setup, which completely ignores the role of data access methods. Mistakes in the underlaying queries also go unnoticed until its too late.
Testing data access code, and including them in the scope of unit tests is a very cumbersome process. Hence I give them very low priority in my unit tests.
The most straightforward option is to follow the following process:
- Insert generated objects with the expected initial state.
- Fetch the inserted objects from the database, and execute business logic operations on them.
- Update the objects.
- Re-fetched them again, and delete the returned objects to clean-up the database.
With this approach problems start when unit tests fail midway, and bits and pieces of the broken objects’ state remain scattered across the development database. Over time it gets messy, and often the broken data affects other queries and unit tests. This means that we lose the isolated characteristic of a good unit test, since one unit test can cause unwanted effects in other unit tests through the central database. Very bad indeed.
So what can we do to solve this problem? Luckily Hendry Luk has a whole series on this very problem and how we can solve it. He suggests the following smart ways to test data access code:
- In-memory database using Sqlite
- Transaction rollback
- Restorable file-based DB
- Preset state with NDBUnit
- Fake DB provider with Linq
MySQL is an excellent, very popular and open source database management system. A binary installation is available for Mac OS, and the process of installing MySQL on Mac OS is also very well documented. So just head over to MySQL.com, where you will find the binary installation, together with extensive installation, and usage documentation.
To connect to MySQL from Python use MySQL for Python (MySQLdb). Get MySQLdb from http://sourceforge.net/projects/mysql-python/.
- Download MySQL from mysql.com and install the DBMS as instructed in the documentation.
- Make sure “Library/Python/2.3” does not have:
- Directory “MySQL”
- File “_mysql.pyd”
- File “_mysql_exceptions.py”
- File “_mysql_exceptions.pyc”
Either uninstall MySQL for Python if a setup program was used to install it, otherwise manually delete them.
- Download and unpack MySQL for Python. At the time of writing this is MySQL-python-1.2.1_p2.tar.gz .
- Open Terminal and change to the directory where MySQLdb was unpacked to.
- Get rid of any previous builds that might interfere, by deleting the “build” directory if it exists.
- Edit the setup.py file, and change:
return popen(“mysql_config –%s” % what)
return popen(“/usr/local/mysql/bin/mysql_config –%s” % what)
- Cleanup any previous install attempts:
python setup.py clean
- Build MySQLdb:
python setup.py build
Viola! MySQL for Python is all setup, and ready for your Python-MySQL data access code. Enjoy!