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