I've been working in deployment scripts for my current project and sometimes I need to drop all the tables and sequences from the database so I can create everything from scratch. I had been doing a
DROP SCHEMA public CASCADE; CREATE SCHEMA public AUTHORIZATION bob; GRANT ALL ON SCHEMA public TO bob;
This was great but a little destructive and over the top. It removes stored procedures and triggers as well which isn't what I want. So I looked at the SQLAlchemy docs and there is a metadata command drop_all()
def drop_tables(): metadata.drop_tables()
Unfortunately this doesn't CASCADE so this isn't going to work in cases where tables have foreign keys (which is most of the time). It also leaves sequences in place.
So I looked at dropping each table in turn with a cascade. So how do you get a list of tables and sequences in the database? I could hard code the names of the tables and sequences but that seemed a little poor.
It turns out that PostgreSQL has a set of views that expose the inner workings of your database and a few queries can give you all the information you need.
def get_table_list_from_db(): """ return a list of table names from the current databases public schema """ sql="select table_name from information_schema.tables"\ "where table_schema='public'" execute = metadata.execute return [name for (name, ) in execute(text(sql))] def get_seq_list_from_db(): """return a list of the sequence names from the current databases public schema """ sql="select sequence_name from information_schema.sequences"\ "where sequence_schema='public'" execute = metadata.execute return [name for (name, ) in execute(text(sql))] def drop_all_tables_and_sequences(): execute = metadata.execute for table in get_table_list_from_db(): try: execute(text("DROP TABLE %s CASCADE" % table)) except SQLError, e: print e for seq in get_seq_list_from_db(): try: execute(text("DROP SEQUENCE %s CASCADE" % table)) except SQLError, e: print e
The information_schema is full of interesting information. It's actually a 'view' of lower level database tables. You can find all sorts of performance and configuration information in there. Quite handy 
Here are some docs.
Good luck.
