Export to CSV using the Django ORM

May, 17 2012

Django apps tend to be pretty data heavy. One common task is exporting sets of data to csv (comma separated values), a plain text file which can be loaded up in a spreadsheet and manipulated further. The first thing to do is make sure you know about the Python csv library.

If you are exporting all of you data with respect to one model, here is a quick way to do it and keep your code cleaner using values_list and the Django ORM query shorthand. Start by defining your export as a data structure.

export_info = [
    ("Role", "role__name"),
    ("Department", "department"),
    ("Last Name", "person__last_name"),
    ("First Name", "person__first_name"),
]

The first item in the tuple is the row header and the second item is an ORM path to the value you want. It will be passed in to values_list. One quirk to note is if you have a relationship and you don't specify the field on the relationship are you interested in you will just get the id for the object. The model's unicode method isn't called. For a better idea here is what the models would look like in this example.

class Position(models.Model):
    role = models.ForeignKey("Role")
    department = models.CharField(max_length=256)
    person = models.ForeignKey("Person")

class Role(models.Model):
    name = models.CharField(max_length=256)

class Person(models.Model):
    first_name = models.CharField(max_length=256)
    last_name = models.CharField(max_length=256)

The core logic looks like this.

global export_info
positions = Position.objects.all().order_by("role__name", 
                                            "person__last_name")
# The inverse of zip is zip
headers, fields = zip(*export_info)     
rows = [headers]
for position in positions:
    qs = Position.objects.filter(pk=position.id)    
    # convert values like datetimes into unicode objects
    rows.append([unicode(v) for v in qs.values_list(*fields)[0]])

The last line is where the magic happens. I suggest going through it in your interpreter if you are confused about what it does. When this code is done you will have the list of rows which are your csv file. All that is left is to write the result to a proxy file and return it in a response.

f = StringIO.StringIO()
writer = UnicodeWriter(f)
for row in rows:
    writer.writerow(row)
response = HttpResponse(f.getvalue(), mimetype="text/csv")  
response['Content-Disposition'] = 'attachment; filename=export.csv'
return response

To get StringIO do "import cStringIO as StringIO". The UnicodeWriter for csv is a bit of custom code inspired by this section of the Python docs. You can use the normal csv library writer if all your data is ASCII and different file proxy object if you like.

Is there an even better way?


Tweet comments, corrections, or high fives to @amjoconn