Monday, January 07, 2008

Cascading DROP TABLE with SQLAlchemy

A little quirk that can get you if you're using SQLAlchemy to create and drop your database is that PostgreSQL doesn't allow you to drop a table that has other tables referring to it via FOREIGN KEY constraints. PostgreSQL has a DROP TABLE ... CASCADE command that supports this (and drops all the dependent tables) but there's no easy way to use the DROP TABLE ... CASCADE statement. It's not too hard to make it available, though.

It turns out, however, that SQLAlchemy has a nice, pluggable database dialect system that is fairly simple to update. One part of this dialect system is a "SchemaDropper". So to cascade the DROP TABLE statements, I just created the following SchemaDropper (derived from the existing PostgreSQL PGSchemaDropper) and installed it as the default PostgreSQL dialect schemadropper. (Most of the code is copied from the base SchemaDropper class in sqlalchemy.sql.compiler)


from sqlalchemy.databases import postgres

class PGCascadeSchemaDropper(postgres.PGSchemaDropper):
def visit_table(self, table):
for column in table.columns:
if column.default is not None:
self.traverse_single(column.default)
self.append("\nDROP TABLE " +
self.preparer.format_table(table) +
" CASCADE")
self.execute()

postgres.dialect.schemadropper = PGCascadeSchemaDropper


And that's it!

3 comments:

  1. Howdy,

    What's sqlalchemy? I have been liking your blog, particularly enjoyed the 'Dynamic Language Weenies' post.

    Steve

    ReplyDelete
  2. Hey Steve, thanks for the comment!

    I use SQLAlchemy so much at work that sometimes I forget that some people haven't heard of it yet. SQLAlchemy is a high-level database library for Python that provides a way to map Python classes to SQL tables.

    There's a lot of info at http://www.sqlalchemy.org. Some other packages that provide similar functionality are Storm , SQLObject (both Python), and Hibernate (Java). My personal favorite is SQLAlchemy, as you may have guessed.

    I also have a book, Essential SQLAlchemy coming out soon (next few months) being published by O'Reilly, that you can pick up if this stuff interests you.

    ReplyDelete
  3. Hi Rick, enjoyed the book btw! (when will there be a new edition). Don't know if this particular annoyance ever got fixed, but seems to still be there in 0.6. Filed a bug and test script at http://www.sqlalchemy.org/trac/ticket/1801, fwiw

    ReplyDelete