40

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?

Improve this question
5
  • For some databases exists 'truncate' statement. It recreates table and works faster and not transactional. You can execute it with raw SQL with engine.execute('''truncate tablename''') Commented Jun 27, 2012 at 18:56
  • @Varela Thanks for sharing your thoughts I want to write the method/function in such a way that it should work on all databases. Since truncate is DB dependent, it wouldn't be my first choice. Commented Jun 27, 2012 at 19:04
  • 1
    Then there is nothing better, you cannot omit iterating through tables and delete probably the only statement that works for all DBs. Commented Jun 27, 2012 at 19:08
  • 18
    What is wrong with metadata.drop_all() then metadata.create_all() Commented Jun 27, 2012 at 19:28
  • 1
    See: stackoverflow.com/questions/4763472/… Commented Aug 9, 2014 at 17:03

3 Answers 3

Reset to default
51

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi. my meta.tables or meta.sorted_tables is an empty list for some reason. I have meta = sqlalchemy.MetaData(bind=engine). Why does it show empty while database has tables?
If you have no models defined bound to this meta, there would not be anything. But if you do not want to define models, and would like to reflect them from the database, you need to call MetaData.reflect first
Wow, thanks for your help! Just one more question, if you don't mind. Now to get the table instance I can do meta.tables['tasks'], but that means that I have to remember the class name (Tasks) and the table name 'tasks'. Is there any way to avoid that?
I am not sure you are approaching this from the right angle. But please take a look at Using Reflection with Declarative and Declarative Reflection example. These should give you an idea how to have only model classes defined without tables.
Thanks for pointing in the right direction. What I needed was session.query(Tasks).delete()
10

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

SQLAlchemy 0.8.0b2 and Python 2.7.3

Improve this answer

1 Comment

Same here. Although that does drop the tables instead of clearing them.
1

Solution for dropping tables with foreign key constraints in SQLAlchemy

If you're having trouble deleting tables because of foreign key constraints, here's what worked for me:

metadata = MetaData()
metadata.reflect(bind=self.engine, resolve_fks=False)
metadata.drop_all(self.engine)
self.session.commit()
self.session.close()
self.engine.dispose()

The key is using resolve_fks=False when reflecting the database metadata. This tells SQLAlchemy to ignore foreign key relationships during reflection, which allows you to drop tables that would otherwise be constrained.

Improve this answer

1 Comment

The OP does not want to drop the tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.