LuckyDonkey

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.