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.

This entry was posted on Friday, November 23rd, 2007 at 9:10 pm and is filed under PostgreSQL, Python, SQLAlchemy, Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.