UnitTest : Can't add many to many relations : Integrity error (seems delete do not do his job)

Question

I have two tables : Person & Team ; each has a Many-To-Many relation with the other.

I do this in the setUp function (called before each function of testing, 3 times in my case)

p = Person(fname=u"John", lname=u"Doe")
Team(name=u"Anon", persons=[p])
session.commit()

And my tearDown function (called after each function of testing)

Person.query.delete()
Team.query.delete()
session.commit()

The first test passes, but when setUp is called the second time, I got this : IntegrityError: (IntegrityError) columns person_id, team_id are not unique u'INSERT INTO team_persons__person_teams (person_id, team_id) VALUES (?, ?)' (5, 1)

It seems like delete did not really delete the rows the first time. I can't figure out why this happens.

Answer 1

The rows in your Person and Team tables are being deleted, but the row from the association table is not. This is because query.delete() does not handle relationship cascading. See Python's SQLAlchemy doesn't clean out the secondary (many-to-many) table? for a detailed explanation.

You should be able to solve this by using session.delete(obj) for each object you have added. Or better yet, for unit testing with an in-memory database, just drop and re-create all of your tables during tearDown and setUp.