How to drop all tables including dependent object

Question

When I'm trying to remove all tables with:

base.metadata.drop_all(engine)

I'm getting following error:

ERROR:libdl.database_operations:Cannot drop table: (psycopg2.errors.DependentObjectsStillExist) cannot drop sequence <schema>.<sequence> because other objects depend on it
DETAIL:  default for table <schema>.<table> column id depends on sequence <schema>.<sequence>
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Is there an elegant one-line solution for that?

Does this answer your question? [How can I drop all the tables in a PostgreSQL database?](https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database) — Scoots, Feb 13 '20 at 12:58
I think it doesn't, this seems to be a question about sqlalchemy's [drop_all()](https://docs.sqlalchemy.org/en/13/core/metadata.html?highlight=drop_all#sqlalchemy.schema.MetaData.drop_all). — cglacet, Jun 04 '20 at 18:58

score 0 · Answer 1 · answered Feb 14 '20 at 10:30

import psycopg2
from psycopg2 import sql

cnn = psycopg2.connect('...')
cur = cnn.cursor()
cur.execute("""
    select s.nspname as s, t.relname as t
    from pg_class t join pg_namespace s on s.oid = t.relnamespace
    where t.relkind = 'r'
    and s.nspname !~ '^pg_' and s.nspname != 'information_schema'
    order by 1,2
    """)
tables = cur.fetchall()  # make sure they are the right ones

for t in tables:
    cur.execute(
        sql.SQL("drop table if exists {}.{} cascade")
        .format(sql.Identifier(t[0]), sql.Identifier(t[1])))

cnn.commit()  # goodbye

How to drop all tables including dependent object

1 Answers1