CODEWITHSUNDEEP
pythonsqlalchemypylons
Juliusz Gonera
Juliusz Gonera

Reputation: 4958

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.

Upvotes: 77

Views: 66578

Answers (4)

Krzysztof Madejski
Krzysztof Madejski

Reputation: 8018

Can you simply run the SQL file with the schema to create database before each test and then create another script to drop all tables that would be run on teardown?

Upvotes: 0

aknuds1
aknuds1

Reputation: 68077

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.

Upvotes: 79

kolypto
kolypto

Reputation: 35453

For PostgreSQL using TRUNCATE:

with contextlib.closing(engine.connect()) as con:
    trans = con.begin()
    con.execute('TRUNCATE {} RESTART IDENTITY;'.format(
        ','.join(table.name 
                 for table in reversed(Base.metadata.sorted_tables))))
    trans.commit()

Note: RESTART IDENTITY; ensures that all sequences are reset as well. However, this is slower than the DELETE recipe by @aknuds1 by 50%.

Another recipe is to drop all tables first and then recreate them. This is slower by another 50%:

Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

Upvotes: 19

Joe L.
Joe L.

Reputation: 1908

How about using truncate:

TRUNCATE [ TABLE ] name [, ...]

(http://www.postgresql.org/docs/8.4/static/sql-truncate.html)

This will delete all the records in the table, but leave the schema in tact.

Upvotes: -5

Related Questions