Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sqlalchemy: Truncate all tables
def truncate_db(engine):
# delete all table data (but keep tables)
# we do cleanup before test 'cause if previous test errored,
# DB can contain dust
meta = MetaData(bind=engine, reflect=True)
con = engine.connect()
trans = con.begin()
con.execute('SET FOREIGN_KEY_CHECKS = 0;')
for table in meta.sorted_tables:
con.execute(table.delete())
con.execute('SET FOREIGN_KEY_CHECKS = 1;')
trans.commit()
@sochi

This comment has been minimized.

Copy link

sochi commented Dec 9, 2019

Might be worth noting down that FOREIGN_KEY_CHECKS options is supported MySQL, though other databases commonly used with SQLAlchemy might not support it (e.g., PostgreSQL).

@sloria

This comment has been minimized.

Copy link

sloria commented Dec 10, 2019

For Postgres, you can temporarily disable triggers, something like

def truncate_db(engine):
    # delete all table data (but keep tables)
    # we do cleanup before test 'cause if previous test errored,
    # DB can contain dust
    meta = MetaData(bind=engine, reflect=True)
    con = engine.connect()
    trans = con.begin()
    for table in meta.sorted_tables:
        con.execute(f'ALTER TABLE "{table.name}" DISABLE TRIGGER ALL;')
        con.execute(table.delete())
        con.execute(f'ALTER TABLE "{table.name}" ENABLE TRIGGER ALL;')
    trans.commit()
@sochi

This comment has been minimized.

Copy link

sochi commented Dec 10, 2019

Surely there are ways to replicate the same functionality in other databases. For PostgreSQL specifically one might want to opt for using SET session_replication_role (https://www.postgresql.org/docs/12/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE, or https://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql).

@sloria replying to your snippet specifically as your code is effectively different from the original gist. Note you disable the triggers (and foreign key checks) only on a particular table, then deleting its content. In case a value from the table which is being deleted from is referenced as a foreign key constraint from any other table, the operation might fail. (Obviously this would be depending on ON DELETE option set on the other table but still it's error prone especially in a scenarios where the database model is managed by more people.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.