2

What is the fastest way to truncate a table in the Django ORM based on the database type in a view? I know you can do this for example

Books.objects.all().delete()

but with tables containing millions of rows it is very slow. I know it is also possible to use the cursor and some custom SQL

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

However, the TRUNCATE command does not work with SQLite. And if the database moves to another db type, I need to account for that.

Any ideas? Would it be easier to just drop the table and recreate in my view?

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
titleistfour
  • 305
  • 3
  • 12
  • http://stackoverflow.com/questions/4532681/how-to-remove-all-of-the-data-in-a-table-using-django || http://stackoverflow.com/questions/2988997/how-do-i-truncate-table-using-django-orm – Ciro Santilli OurBigBook.com May 22 '16 at 20:24

1 Answers1

3

Django's .delete() method is indeed very slow, as it loads the IDs of each object being deleted so that a post_save signal can be emitted.

This means that a simple connection.execute("DELTE FROM foo") will be significantly faster than Foo.objects.delete().

If that's still too slow, a truncate or drop+recreate is definitely the way to go. You can get the SQL used to create a table with: output, references = connection.creation.sql_create_model(model, style), where style = django.core.management.color_style() (this is taken from https://github.com/django/django/blob/master/django/core/management/sql.py#L14).

David Wolever
  • 148,955
  • 89
  • 346
  • 502
  • Glad I could help :) NOW, my $0.02 on using SQLite: *if* you are *only* using it during development (ie, because it's simpler than running a "full" database like Postgres or MySQL), I would suggest not doing that. I love SQLite, and used to use it heavily in development, but eventually realized that I was spending more time maintaining SQLite compatibility than it saved me, so now I simply use Postgres everywhere, and I'm happier for it. Of course, there are good reasons to ignore what I'm saying. Just my $0.02. – David Wolever Mar 31 '13 at 02:59
  • Good point! Yes, I am using SQLite for development. However, I will take your advice and install something more robust like Postgres. Thanks again! – titleistfour Mar 31 '13 at 21:49
  • No problem! And, ya — Postgres in development isn't right for *everyone*, just make sure you're being sensible about it, and considering the time you're spending on supporting SQLite versus the time it takes to setup Postgres. – David Wolever Mar 31 '13 at 22:21