pythonsqlalchemy

How to clean the database, dropping all records using sqlalchemy?

I am using SQLAlchemy. I want to delete all the records efficiently present in database but I don't want to drop the table/database.

I tried with the following code:

con = engine.connect()
trans = con.begin()

con.execute(table.delete())
trans.commit()

It seems, it is not a very efficient one since I am iterating over all tables present in the database. Can someone suggest a better and more efficient way of doing this?

Solution

  • If you models rely on the existing DB schema (usually use autoload=True), you cannot avoid deleting data in each table. MetaData.sorted_tables comes in handy:

    for tbl in reversed(meta.sorted_tables):
    engine.execute(tbl.delete())

    If your models do define the complete schema, there is nothing simpler than drop_all/create_all (as already pointed out by @jadkik94).

    Further, TRUNCATE would anyways not work on the tables which are referenced by ForeignKeys, which is limiting the usage significantly.