69

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.

Juliusz Gonera
  • 4,658
  • 5
  • 32
  • 35
  • You should use transactions. http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#joining-a-session-into-an-external-transaction – charlax Sep 12 '12 at 09:35

4 Answers4

71

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.

aknuds1
  • 65,625
  • 67
  • 195
  • 317
  • 6
    It seems to work! Thank you. The exact code I use in Pylons is: `for table in reversed(meta.Base.metadata.sorted_tables): meta.Session.execute(table.delete()); meta.Session.commit()` – Juliusz Gonera Feb 24 '11 at 23:36
  • 12
    Where does `engine` come from? And `contextlib`? Is it the standard library? This answer would be better if the imports were complete. – Zelphir Kaltstahl Jun 08 '17 at 14:23
  • 2
    @MatthieuRodic, I suspect that your edit here increases the risk of a reader being confused by this answer instead of decreasing it. To a reader who's previously done the initial setup of an SQLAlchemy application, I think it was already implicitly clear what `meta` was and so the edit has no effect on their understanding. But to someone who's never seen `MetaData()` before and is just fumbling around in an application someone else created, I fear that the answer now makes it look like they're supposed to create a *new* `MetaData()` instance to use with this code, which of course won't work. – Mark Amery Nov 23 '18 at 12:08
  • The metadata part as appeared in the answer didn't work. First, the meta object created isn't used anywhere and I had no clue how to make it work. However, as a workaround, replacing the _"for"_ loop with `for table in engine.table_names(): con.execute("DROP table " + table)` – Afsan Abdulali Gujarati Dec 04 '18 at 18:49
  • 4
    Cons is this approach won't work for those table with foreign key constraint. – Shift 'n Tab Jun 15 '20 at 11:36
18

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)
kolypto
  • 31,774
  • 17
  • 105
  • 99
  • 2
    `metadata.Base.metadata.drop_all(bind=engine)` gives the error `AttributeError: 'MetaData' object has no attribute 'Base'` – danio Jul 25 '16 at 12:46
  • But this can cause deadlocks – Shift 'n Tab Jun 15 '20 at 10:30
  • @danio where did you find `metadata.Base`? it's the other way around: `Base.metadata`.... :D – kolypto Jul 24 '20 at 22:55
  • @kolypto good question, its 4 years ago so I'm struggling to remember! But the comment above on https://stackoverflow.com/a/5003705/12663 also uses `meta.Base.metadata.sorted_tables` and your answer doesn't explain where Base comes from so I guess I was trying to work out where you got `Base` from and used that! Maybe the SQLAlchemy API changed between the 0. and 1. series? – danio Jul 27 '20 at 06:54
  • @danio `Base` is the declarative base for your models :) – kolypto Aug 24 '20 at 15:15
0

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?

Kangur
  • 7,823
  • 3
  • 30
  • 32
-5

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.

Joe L.
  • 1,888
  • 12
  • 14
  • 3
    This would be OK but how can I use it in SQLAlchemy? `for table in meta.Base.metadata.tables.keys(): meta.Session.execute('truncate %s' % table); meta.Session.commit()` causes such error message: "InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block 'truncate data.subkeywords' {}" – Juliusz Gonera Jan 21 '11 at 21:32
  • 1
    I fixed it by doing `truncate %s cascade` but it's painfully slow. Too slow to run it after every unit test... – Juliusz Gonera Jan 21 '11 at 22:03
  • 4
    The question is asking for sqlalchemy, not sql. – lnhubbell Nov 24 '15 at 18:56