[SOLVED] SQLAlchemy, clear database content but don't drop the schema

SQLAlchemy, clear database content but don't drop the schema

I'm developing a Pylons app which is based on exisitng database, so I'm using reflection. I have an SQL file with the schema that I used to create my test database. That's why I can't simply use drop_all and create_all.

I would like to write some unit tests and I faced the problem of clearing the database content after each test. I just want to erase all the data but leave the tables intact. Is this possible?

The application uses Postgres and this is what has to be used also for the tests.

Solution

I asked about the same thing on the SQLAlchemy Google group, and I got a recipe that appears to work well (all my tables are emptied). See the thread for reference.

My code (excerpt) looks like this:

import contextlib
from sqlalchemy import MetaData

meta = MetaData()

with contextlib.closing(engine.connect()) as con:
    trans = con.begin()
    for table in reversed(meta.sorted_tables):
        con.execute(table.delete())
    trans.commit()

Edit: I modified the code to delete tables in reverse order; supposedly this should ensure that children are deleted before parents.