Flask-SQLAlchemy truncate/delete table records and bypassing/ignoring IntegrityError

Question

I found this cool post regarding deletion of record of a single table. However I'm trying to clear all my tables for testing purpose and thus I encountered some annoying IntegrityError for testing sake.

Is there anyway how to bypass this?

IntegrityError: (psycopg2.IntegrityError) update or delete on table
"table_1" violates foreign key constraint "fk_table_2_primary_table_1"
on table "table_2"

NOTE: I wouldn't delete the tables, only the records.

Possible duplicate of [SqlAlchemy: How to implement DROP TABLE ... CASCADE?](https://stackoverflow.com/questions/38678336/sqlalchemy-how-to-implement-drop-table-cascade) — mad_, Aug 24 '18 at 17:57
@mad_ postgres sir, and not really a duplicate, since im only trying to truncate all the table or delete the records not the table. :) — Roel, Aug 27 '18 at 07:38
https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete — mad_, Aug 27 '18 at 14:17

score -1 · Answer 1 · answered Aug 24 '18 at 08:12

Using PosgreSQL, to empty a database, I used this code :

def delete_tables_and_indexes(engine):
    """ Delete all tableis and indexes """
    if engine.name == 'postgresql':
        # Drop tables
        sql_raw_query = 'select \'drop table if exists "\' || tablename || \'" cascade;\' from pg_tables where schemaname=\'public\';'
        for result in engine.execute(sql_raw_query):
            engine.execute(result[0])
        # Drop types
        sql_raw_query = 'select \'drop type if exists "\' || t.typname || \'";\' '\
            'FROM pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace '\
            'WHERE (t.typrelid = 0 OR (SELECT c.relkind = \'c\' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) '\
            'AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid) '\
            'AND n.nspname NOT IN (\'pg_catalog\', \'information_schema\');'
        for result in engine.execute(sql_raw_query):
            engine.execute(result[0])

This use some raw SQL query, to drop things in a way postgresql understand it, unlinking all schema dependencies.

With SQLite, which does not handle those king of dependencies in a "hard" way, you can easily use :

metadata.drop_all(engine)

The question was specifically referring to truncating tables and removing rows while keeping the tables and database structure. This answer is instead suggesting how to drop tables. — Darius Cosden, Oct 25 '21 at 08:50

Flask-SQLAlchemy truncate/delete table records and bypassing/ignoring IntegrityError

1 Answers1