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()

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.

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`")

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!

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.

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()

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

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

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.

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...