mdh mdh - 4 months ago 56
Python Question

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

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

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.

Comments