Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 the very efficient one since I am iterating over all table present in the database. Can some one suggest me the better and efficient way of doing this?

Any kind of help greatly appreciated here.

like image 985
Rakesh Avatar asked Jun 27 '12 18:06

Rakesh


People also ask

What does SQLAlchemy flush do?

flush() communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction.

How do I delete a SQLAlchemy database?

Delete table elements in SQLAlchemy. Get the books table from the Metadata object initialized while connecting to the database. Pass the delete query to the execute() function and get all the results using fetchall() function.

How do I delete records in flask-SQLAlchemy?

To delete a record by id in Python Flask-SQLAlchemy, we can call the session delete method. to call session. delete to mark obj1 and obj2 to be deleted. And then we call session.


1 Answers

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.

like image 117
van Avatar answered Oct 11 '22 19:10

van