CODEWITHSUNDEEP
pythonsqlalchemy
Rakesh
Rakesh

Reputation: 4127

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?

Upvotes: 40

Views: 84468

Answers (2)

van
van

Reputation: 77012

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.

Upvotes: 50

chiffa
chiffa

Reputation: 2088

For me putting tbl.drop(engine) worked, but not engine.execute(tbl.delete())

SQLAlchemy 0.8.0b2 and Python 2.7.3

Upvotes: 10

Related Questions