SqlAlchemy truncate table with ORM

Question

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 ?

the need to reset sequences is suspicious, why do you want this? — SingleNegationElimination, Nov 28 '12 at 15:33
I want the same behavior between sqlite and mysql because it seems that for sqlite the counter is reset while mysql is not. — w00d, Nov 29 '12 at 13:21
resetting the sequence is more a defect of sqlite's sequence implemenation, I consider it to be a bug! — SingleNegationElimination, Nov 29 '12 at 15:08
There are times people throw in development data in a database for testing purposes, then want to truncate the table and do it again. To me this is a reasonable time to have the counter reset. It's like a "do-over" — Jonathan, Mar 08 '17 at 22:43

score 3 · Answer 1 · answered Nov 28 '12 at 15:31

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.

score 2 · Answer 2 · answered Feb 16 '21 at 20:19

2

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)

answered Feb 16 '21 at 20:19

rbuerki

38
5

1

This would drop the tables (their schema included), which is slower. Correct me if I'm wrong. – Ramon Dias Aug 01 '22 at 03:23

SqlAlchemy truncate table with ORM

2 Answers2