时间戳

首页 > 解决方案 > 如何删除所有表，包括依赖对象

python - 如何删除所有表，包括依赖对象

问题描述

当我尝试删除所有表时：

base.metadata.drop_all(engine)

我收到以下错误：

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.

有没有一种优雅的单线解决方案？

标签： pythonpostgresqlsqlalchemypsycopg2

解决方案

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

推荐阅读