How to clear PostgresDB with SqlAlchemy in ORM way using CASCADE instruction

user_6893978 0
Viewed 395

Does anybody know how to clear all DB content with SqlAlchemy in ORM style and without knowing its structure?

Problem that user gives connection details (login, psw, DB name), and we have to prepare installation of our DB structure. So, I want to clear all old data in given DB.

Previous versions of columns might be created without usage of necessary statements (cascade="all, delete") in relationships. Also its structure might be unknown to us. Are there any secure way how to delete whole DB content? I can do this in raw SQL, using DROP TABLE ... CASCADE, but I need an ORM way.

For example these lines throws sqlalchemy.exc.IntegrityError due to absence CASCADE instruction in query:

for tbl in reversed(Base.metadata.sorted_tables):
    db.execute(tbl.delete())

and this line throws sqlalchemy.exc.InternalError also due to absence CASCADE instruction in query.

Base.metadata.drop_all(bind=db)

Here a lot of info how to do this, but every solution depends from column relationships, which are unknown to us. Thanks in advance!

sqlalchemy postgresql orm python
1 Answers

If you really wanted to drop all tables in a database with an arbitrary unknown structure using just ORM methods then you'd probably have to use something like sqlacodegen to generate the model code first. However, that should not be necessary if you can use Inspector.get_sorted_table_and_fkc_names to retrieve the table names and then drop them like so:

import sqlalchemy as sa

connection_uri = "postgresql+psycopg2://scott:tiger@192.168.0.199/mydb"
engine = sa.create_engine(connection_uri)

insp = sa.inspect(engine)
for table_entry in reversed(insp.get_sorted_table_and_fkc_names()):
    table_name = table_entry[0]
    if table_name:
        with engine.begin() as conn:
            conn.execute(sa.text(f'DROP TABLE "{table_name}"'))
user_21443900
Related