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.
