How to TRUNCATE TABLE using Django's ORM?

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.

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

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!

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.

How to TRUNCATE TABLE using Django's ORM?

Related

Recent Posts