PostgreSQL, SQLAlchemy, Dropping All Tables and Sequences

NOTE: This is an old article I wrote in January 2007, it’s still relevant today. It was originally posted on luckydonkey.com which I am in the process of retiring.

There were a number of insightful comments in the original article that I have not copied across. They ended with The Ultimate DropEverything script from Mike Bayer that is now in the SqlAlchemy wiki

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

1
2
3
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()

1
2
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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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.

Comments