How to TRUNCATE TABLE using Django's ORM?

Question

To empty a database table, I use this SQL Query:

TRUNCATE TABLE `books`

How to I truncate a table using Django's models and ORM?

I've tried this, but it doesn't work:

Book.objects.truncate()

Claraclarabella

While you have several good answers below, you should realize that the table in your sql db will have appname_ prepended to the tablename from your model. – Extrabold

With any SQL, not just TRUNCATE: #4533181 – Kallick

Answer 1

The closest you'll get with the ORM is Book.objects.all().delete().

There are differences though: truncate will likely be faster, but the ORM will also chase down foreign key references and delete objects in other tables.

Saltworks

Thank you. This does the trick. Speed is not important at my case. – Claraclarabella

this seems not to work for every database backend - I got a DatabaseError "too many SQL variables" when I tried the above on a Table with aproximately 3000 records on sqlite3. Maybe I am only missing a configuration value for batching/sqlite. – Curio

@BernhardKircher: see my addition as a seperate answer – Coblenz

In MySQL it's also a DELETE not a TRUNCATE. No index reset with DELETE. – Illegible

Sqlite3 also a delete not a truncate. – Freyah

Most databases implement this command as truncate in DB level. So it seems there should be no performance issues, according to this link: code.djangoproject.com/ticket/16427 – Twotime

Answer 2

You can do this in a fast and lightweight way, but not using Django's ORM. You may execute raw SQL with a Django connection cursor:

from django.db import connection
cursor = connection.cursor()
cursor.execute("TRUNCATE TABLE `books`")

Sporophyte

Thank you, but I prefer to make my apps to work with as much database engines as possible and not to use raw sql's – Claraclarabella

TRUNCATE TABLE is a part of the "typical" SQL syntax, although only officially only part of SQL:2008. Django supports Postgres, MySQL, SQLite*, and Oracle. postgresql.org/docs/8.1/interactive/sql-truncate.html dev.mysql.com/doc/refman/4.1/en/truncate-table.html download.oracle.com/docs/cd/B19306_01/server.102/b14200/… *SQLite is missing TRUNCATE [TABLE] support, you need to use DELETE FROM for it. This of course, is really only applicable if you need performance. – Galoot

There also may be a problem with truncate (depending on the used database system). E.g. Microsoft SQL Server dows not allow a truncate on tables that is referenced by Foreignkeys. I just wanted to mention this, maybe other DB Systems have a different behaviour. – Curio

On most databases you'll need to call django.db.transaction.commit_unless_managed() after the execute() call. – Incurrence

Be careful, as many RDBMS like Oracle and MySQL handles TRUNCATE as a DDL, not DML, and it is not part of transaction handling! – Custombuilt

from django.db import connection; from psycopg2 import sql; connection.cursor().execute(sql.SQL("TRUNCATE TABLE {} RESTART IDENTITY").format(sql.Identifier(Model.objects.model._meta.db_table))) If there exist tables that reference the given one, you might need to add CASCADE, which would delete related records. – Germinal

Answer 3

You can use the model's _meta property to fill in the database table name:

from django.db import connection
cursor = connection.cursor()
cursor.execute('TRUNCATE TABLE "{0}"'.format(MyModel._meta.db_table))

Important: This does not work for inherited models as they span multiple tables!

Terrigenous

Answer 4

In addition to Ned Batchelder's answer and refering to Bernhard Kircher's comment:

In my case I needed to empty a very large database using the webapp:

Book.objects.all().delete()

Which, in the development SQLlite environment, returned:

too many SQL variables

So I added a little workaround. It maybe not the neatest, but at least it works until the truncate table option is build into Django's ORM:

countdata = Book.objects.all().count()
logger.debug("Before deleting: %s data records" % countdata)
while countdata > 0:
    if countdata > 999:
        objects_to_keep = Book.objects.all()[999:]
        Book.objects.all().exclude(pk__in=objects_to_keep).delete()
        countdata = Book.objects.all().count()
    else:
        Book.objects.all().delete()
        countdata = Book.objects.all().count()

By the way, some of my code was based on "Django Delete all but last five of queryset".

I added this while being aware the answer was already answered, but hopefully this addition will help some other people.

Coblenz

Nice, but on my MySQL: NotSupportedError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") – Visitant

and what version is that? (both mysql and django) Googling on this, i come up with mysql 5.0 (is that correct?) But for a very old not supported anymore django version.. (code.djangoproject.com/ticket/10099) – Coblenz

again Book.objects.all().delete() is not a truncate – Vauntcourier

Answer 5

I know this is a very old Question and few corrects answer is in here is as well but I can't resist myself to share the most elegant and fastest way to serve the purpose of this question.

class Book(models.Model):
    # Your Model Declaration

    @classmethod
    def truncate(cls):
        with connection.cursor() as cursor:
            cursor.execute('TRUNCATE TABLE {} CASCADE'.format(cls._meta.db_table))

And now to truncate all data from Book table just call

Book.truncate()

Since this is directly interact with your Database it will perform much faster than doing this

Book.objects.all().delete()

Kassel

Answer 6

Now there's a library to help you truncate a specific TABLE in your Django project Database, It called django-truncate.

It's simple just run python manage.py truncate --apps myapp --models Model1 and all of the data in that TABLE will be deleted!

Learn more about it here: https://github.com/KhaledElAnsari/django-truncate

Sterilize

This management command does not appear to truncate tables. It's implementation appears to be calling _model.objects.all().delete(), which is definitely NOT a truncate. github.com/KhaledElAnsari/django-truncate/blob/master/… – Concoct

Answer 7

For me the to truncate my local sqllite database I end up with python manage.py flush.

What I have initial tried is to iterate over the models and delete all to rows one by one:

models = [m for c in apps.get_app_configs() for m in c.get_models(include_auto_created=False)]

        for m in models:
            m.objects.all().delete()

But becuse I have Protected foreign key the success of the operation depended on the order of the models.

So, I am using te flush command to truncate my local test database and it is working for me https://docs.djangoproject.com/en/3.0/ref/django-admin/#django-admin-flush

Sartin

This solution will not truncate. – Vauntcourier

Answer 8

This code uses PosgreSQL dialect. Leave out the cascade bits to use standard SQL.

---

Following up on Shubho Shaha's answer, you could also create a model manager for this.

class TruncateManager(models.Manager):
    def truncate(self, cascade=False):
        appendix = " CASCADE;" if cascade else ";"
        raw_sql = f"TRUNCATE TABLE {self.model._meta.db_table}{appendix}"
        cursor = connection.cursor()
        cursor.execute(raw_sql)

class Truncatable(models.Model):
    class Meta:
        abstract = True

    objects = TruncateManager()

Then, you can extend the Truncatable to create truncatable objects:

class Book(Truncatable):
    ...

That will allow you to call truncate on all models that extend from Truncatable.

Book.objects.truncate()

I added a flag to use cascade as well, which (danger zone) will also: "Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.", which is obviously more destructive, but will allow the code to run inside an atomic transaction.

Juliettajuliette

Answer 9

This is doesn't directly answer the OP's question, but is nevertheless a solution one might use to achieve the same thing - differently.

---

Well, for some strange reason (while attempting to use the suggested RAW methods in the other answers here), I failed to truncate my Django database cache table until I did something like this:

import commands
cmd = ['psql', DATABASE, 'postgres', '-c', '"TRUNCATE %s;"' % TABLE]
commands.getstatusoutput(' '.join(cmd))

Basically, I had to resort to issuing the truncate command via the database's utility commands - psql in this case since am using Postgres. So, automating the command line might handle such corner cases.

Might save someone else some time...

Conjugal