SqlAlchemy: How to implement DROP TABLE ... CASCADE?

Question

I need to drop tables in a PostgreSQL database that have foreign key constraints and require DROP TABLE ... CASCADE.

I could execute raw SQL: engine.execute("DROP TABLE %s CASCADE;" % table.name). However, I would like to implement this behaviour such that I can do table.drop(engine) for the postgresql dialect.

How would one approach this?

Answer 1

You can customize the compilation of constructs like this:

from sqlalchemy.schema import DropTable
from sqlalchemy.ext.compiler import compiles

@compiles(DropTable, "postgresql")
def _compile_drop_table(element, compiler, **kwargs):
    return compiler.visit_drop_table(element) + " CASCADE"

This appends CASCADE to the DROP TABLE statement issued for the postgresql dialect while keeping all other dialects the same.