CODEWITHSUNDEEP
mysqlormsqlalchemy
w00d
w00d

Reputation: 5596

SqlAlchemy truncate table with ORM

I have been using orm for my app, and try not to use sql to avoid sql syntax error when changing between different dbms.

At the moment I am testing with sqlite and mysql. I figured out to delete a table we can use:

db.session.query(models.MyTable).delete()

Unfortunately this method does not really "truncate" the table, because It does not reset the counting of autoincrement to 0.

Is there anyway to do the real truncate in SqlAlchemy orm ?

Upvotes: 15

Views: 20381

Answers (2)

rbuerki
rbuerki

Reputation: 36

As there is no truncate() operation, a simple workaround in sqlalchemy for deleting all entries from a table and resetting the autoincrement count is to drop and recreate the table like this:

Base.metadata.drop_all(engine, tables=[YourTable.__table__])
Base.metadata.create_all(engine, tables=[YourTable.__table__])

(for alternative sqlalchemy syntax examples for dropping a table check this stackoverflow post)

Upvotes: 1

Maksym Polshcha
Maksym Polshcha

Reputation: 18368

SQLAlchemy delete(...) method is translated into SQL DELETE FROM query. This query does not resets any AUTO_INCREMENT counters. To reset a counter in MySQL you have to run a query like that:

ALTER TABLE tbl AUTO_INCREMENT = 100;

If I'm not mistaking, it has no analogue in SQLAlchemy.

Upvotes: 3

Related Questions