pymysql - Clear a table for testing

Question

I'm looking to write tests for my application. I would like to work with a clean database for all my tests. For various reasons, I cannot create a separate test database.

What I currently do is run everything in a transaction and never commit to the db. However some tests read from the db, so I'd like to delete all rows at the start of the transaction and start from there.

The problem I am running into is with foreign key constraints. Currently I just go through each table and do

cursor.execute("DELETE FROM %s" % tablename)

which gives me

IntegrityError: (1451, u'Cannot delete or update a parent row: a
foreign key constraint fails (`testing`.`app_adjust_reason`,
CONSTRAINT `app_adjust_reason_ibfk_2` FOREIGN KEY (`adjust_reason_id`)
REFERENCES `adjust_reason` (`id`))')

edit: I would like something generic that could be applied to any database. Otherwise I would specifically drop the constraints

Answer 1

A more general approach is to create a database from scratch before the test run and drop it after, use CREATE DATABASE_NAME and DROP DATABASE_NAME. This way, you are always starting with a clean database state and you would not worry about the foreign key or other constraints.

Note that you would also need to create your table schema and (possibly test data) after you create a database.

As a real world example, this is what Django does when you run your tests. The table schema is recreated by the Django ORM from your models, then the fixtures or/and the schema and data migrations are applied.