[Django] Command to output QuerySet to csv

I wrote a code that issues select to a table that stores a large number of records, outputs the result to csv, and gzips it, so make a note.

models.py

from logging import getLogger
from django.db import models
import uuid

logger = getLogger(__name__)


class Analytics(models.Model):

    hit_type = models.CharField(max_length=20)

    category = models.CharField(max_length=255, null=True, blank=True)
    action = models.CharField(max_length=255, null=True)
    label = models.CharField(max_length=255, null=True, blank=True)
    value = models.IntegerField(null=True)
    url = models.CharField(max_length=255, null=True)
    created = models.DateTimeField(auto_now_add=True, db_index=True)
    tracking_user = models.ForeignKey('app.TrackingUser', related_name='analytics')

    def __str__(self):
        return '%s - %s' % (self.category, self.tracking_user.uuid)


class TrackingUser(models.Model):

    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    created = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return str(self.uuid)

There was a model that stores data that tracks user behavior on the website as described above, but this time I wanted to create a command that can delete after acquiring Analytics records for one month and storing them in csv. .. By the way, the data of Analytics increases by about 100,000 in one day, so it is about 3 million in one month. So, as a result of doing various things, the following code was a good feeling.

delete_analytics.py

from datetime import datetime, timedelta, date
from api.models import Analytics
from pytz import timezone, utc
from django.core.management.base import BaseCommand
from django.db import transaction
from dateutil.relativedelta import relativedelta
import csv
import gzip
import shutil
import os

class Command(BaseCommand):
    def add_arguments(self, parser):
        # Named (optional) arguments
        parser.add_argument('--target_date', metavar='target_date', type=str, nargs=None,
                            help='target date')

    @transaction.atomic
    def handle(self, *args, **options):
        if options['target_date']:
            delete_start_date = datetime.strptime(options['target_date'], '%Y-%m-%d').replace(day=1)
        else:
            this_month = date.today().replace(day=1)
            delete_start_date = this_month - relativedelta(months=3)
        delete_end_date = delete_start_date + relativedelta(months=1)

        queryset = Analytics.objects.select_related('user', 'tracking_user').filter(created__range=(delete_start_date, delete_end_date))

        #Output to csv
        filename_date = delete_start_date.strftime('%Y%m')
        filename = filename_date + "_event.csv"

        model = queryset.model
        writer = csv.writer(open(filename, 'w'))

        ##Writing the header part of csv
        headers = []
        for field in model._meta.fields:
            headers.append(field.name)
        writer.writerow(headers)

        ##Write the acquired record
        for obj in queryset:
            row = []
            for field in headers:
                val = getattr(obj, field)
                if callable(val):
                    val = val()
                row.append(val)
            writer.writerow(row)

        #Compress csv with gzip
        with open(filename, 'rb') as gzip_in:
            with gzip.open(filename + ".gz", 'wb') as gzip_out:
                shutil.copyfileobj(gzip_in, gzip_out)

        os.remove(filename)

        queryset.delete()

If you set ./manage.py delete_analytics.py -target_date = 2017-06-01 in the directory where manage.py is located, the data for June 2017 will be output to 201706_event.csv, compressed with gzip, and then , Deleted.
The following parts were the key this time.

python

queryset = Analytics.objects.select_related( 'tracking_user').filter(created__range=(delete_start_date, delete_end_date))

If you do not write select_related ('tracking_user')., the relation destination will also go to select, so it will take time. When I wrote it, the command ended in 5 minutes, but if I didn't write it, it took 50 minutes to finish.

Postscript

When I ran it on the server, it fell out due to lack of memory ... Think about what to do and write again if you can handle it

Reference link

django recipe: dump your queryset out as a csv file . palewire