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

Question

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?

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''') — varela, Jun 27 '12 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. — Rakesh, Jun 27 '12 at 19:04
Then there is nothing better, you cannot omit iterating through tables and delete probably the only statement that works for all DBs. — varela, Jun 27 '12 at 19:08
What is wrong with `metadata.drop_all()` then `metadata.create_all()` — jadkik94, Jun 27 '12 at 19:28
See: http://stackoverflow.com/questions/4763472/sqlalchemy-clear-database-content-but-dont-drop-the-schema/25220958#25220958 — kolypto, Aug 09 '14 at 17:03

score 43 · Accepted Answer · edited Jan 31 '17 at 15:41

43

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.

edited Jan 31 '17 at 15:41

David

17,673
10
68
97

answered Jun 27 '12 at 20:16

van

74,297
13
168
171

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? – Alex Okrushko Aug 02 '12 at 14:03
5

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`](http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#sqlalchemy.schema.MetaData.reflect) first – van Aug 02 '12 at 14:23
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? – Alex Okrushko Aug 02 '12 at 14:51
I am not sure you are approaching this from the right angle. But please take a look at [Using Reflection with Declarative](http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#using-reflection-with-declarative) and [Declarative Reflection](http://docs.sqlalchemy.org/en/rel_0_7/orm/examples.html#declarative-reflection) example. These should give you an idea how to have only model classes defined without tables. – van Aug 02 '12 at 15:09
Thanks for pointing in the right direction. What I needed was `session.query(Tasks).delete()` – Alex Okrushko Aug 03 '12 at 21:34

score 8 · Answer 2 · answered Feb 25 '13 at 23:35

8

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

SQLAlchemy 0.8.0b2 and Python 2.7.3

answered Feb 25 '13 at 23:35

chiffa

2,026
3
26
41

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

2 Answers2