8000 DropEverything · sqlalchemy/sqlalchemy Wiki · GitHub
[go: up one dir, main page]
Skip to content

DropEverything

Jump to bottom
mike bayer edited this page Jul 3, 2023 · 5 revisions

DropEverything

The simplest way to drop all tables in a database is to use metadata.reflect(), metadata.drop_all(). In modern versions of SQLAlchemy, this works in all cases, including that it will drop foreign key contraints between tables that are mutually dependent on each other:

from sqlalchemy import MetaData
m = MetaData()
m.reflect(e)

m.drop_all(e)

For more customizable approaches, the Inspector object can be used to get lists of schema objects from the database. Here, we pre-gather all named constraints and table names, and drop each object individually.

from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy.schema import (
    MetaData,
    Table,
    DropTable,
    ForeignKeyConstraint,
    DropConstraint,
)

engine = create_engine('mssql+pyodbc://scott:tiger@ms_2005/', echo=True)

# if targeting a specific schema name, set it here
my_schema = None

with engine.begin() as conn:

    inspector = inspect(conn)

    for tname, fkcs in reversed(
            inspector.get_sorted_table_and_fkc_names(schema=my_schema)):
        if tname:
            conn.execute(DropTable(
                Table(tname, MetaData(), schema=my_schema)
            ))
        elif fkcs:
            if not engine.dialect.supports_alter:
                continue
            for tname, fkc in fkcs:
                fk_constraint = ForeignKeyConstraint((), (), name=fkc)
                Table(tname, MetaData(), fk_constraint)
                conn.execute(DropConstraint(fk_constraint))
Clone this wiki locally
0