2013-12-18

How to Export Your Data as CSV, XLS, or XLSX

There are times, when you need to export the data from your database to different formats. For example, you want to create some diagrams in Office program for a presentation. In this post I will show you how to create admin actions which export selected items as files for a spreadsheet application (like MS Excel, OpenOffice Calc, LibreOffice Calc, Gnumeric, or Numbers). I will cover the mostly used formats: Comma Separated Values (CSV), Binary Excel (XLS), and Office Open XML (XLSX).

First of all, have a look at the model we will be dealing with. It's a simple model with title, description, and - of course - the id.

# models.py
from django.db import models
class MyModel(models.Model):
    title = models.CharField(max_length=100)
    description = models.TextField(blank=True)

In the admininstration options, we'll define three admin actions: export_csv, export_xls, and export_xlsx.

# admin.py
from django.contrib import admin
from models import MyModel

# ... export functions will go here ...

class MyModelAdmin(admin.Admin):
    actions = [export_csv, export_xls, export_xlsx]

admin.site.register(MyModel, MyModelAdmin)

Now let's create functions for each of those actions!

Comma-Separated Values Format

CSV is the most common import and export format for spreadsheets and databases. It's a textual format which one could easily create or parse himself, but there is also a python built-in library csv for handy data manipulation.

def export_csv(modeladmin, request, queryset):
    import csv
    from django.utils.encoding import smart_str
    response = HttpResponse(mimetype='text/csv')
    response['Content-Disposition'] = 'attachment; filename=mymodel.csv'
    writer = csv.writer(response, csv.excel)
    response.write(u'\ufeff'.encode('utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly)
    writer.writerow([
        smart_str(u"ID"),
        smart_str(u"Title"),
        smart_str(u"Description"),
    ])
    for obj in queryset:
        writer.writerow([
            smart_str(obj.pk),
            smart_str(obj.title),
            smart_str(obj.description),
        ])
    return response
export_csv.short_description = u"Export CSV"

As you can see, HttpResponse is a file-like object and we used it to write data to.

Excel Binary File Format

XLS is the main spreadsheet format which holds data in worksheets, charts, and macros. We are going to use xlwt library to create a spreadsheet. There is analogous library xlrd to read XLS files. Note, that this format allows to have only 256 columns.

def export_xls(modeladmin, request, queryset):
    import xlwt
    response = HttpResponse(mimetype='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=mymodel.xls'
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet("MyModel")
    
    row_num = 0
    
    columns = [
        (u"ID", 2000),
        (u"Title", 6000),
        (u"Description", 8000),
    ]

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    for col_num in xrange(len(columns)):
        ws.write(row_num, col_num, columns[col_num][0], font_style)
        # set column width
        ws.col(col_num).width = columns[col_num][1]

    font_style = xlwt.XFStyle()
    font_style.alignment.wrap = 1
    
    for obj in queryset:
        row_num += 1
        row = [
            obj.pk,
            obj.title,
            obj.description,
        ]
        for col_num in xrange(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
            
    wb.save(response)
    return response
    
export_xls.short_description = u"Export XLS"

Here we created one worksheet, filled it with data, marked the first row in bold, and made the lines in the other cells wrapped. Also we set the width for each column. We'll do the same in the next format too.

Office Open XML Format

XLSX (a.k.a. OOXML or OpenXML) is a zipped, XML-based file format developed by Microsoft. It is fully supported by Microsoft Office 2007 and newer versions. OpenOffice 4.0, for example, can only read it. There is a python library openpyxl for reading and writing those files. This format is great when you need more than 256 columns and text formatting options.

def export_xlsx(modeladmin, request, queryset):
    import openpyxl
    from openpyxl.cell import get_column_letter
    response = HttpResponse(mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=mymodel.xlsx'
    wb = openpyxl.Workbook()
    ws = wb.get_active_sheet()
    ws.title = "MyModel"

    row_num = 0

    columns = [
        (u"ID", 15),
        (u"Title", 70),
        (u"Description", 70),
    ]

    for col_num in xrange(len(columns)):
        c = ws.cell(row=row_num + 1, column=col_num + 1)
        c.value = columns[col_num][0]
        c.style.font.bold = True
        # set column width
        ws.column_dimensions[get_column_letter(col_num+1)].width = columns[col_num][1]

    for obj in queryset:
        row_num += 1
        row = [
            obj.pk,
            obj.title,
            obj.description,
        ]
        for col_num in xrange(len(row)):
            c = ws.cell(row=row_num + 1, column=col_num + 1)
            c.value = row[col_num]
            c.style.alignment.wrap_text = True

    wb.save(response)
    return response

export_xlsx.short_description = u"Export XLSX"

Conclusion

So whenever you need to get your Django project data to some spreadsheet application, there are several ways to do that. If you are planning to import the data to some other database, CSV is probably the best, as it is simple, straightforward, and requires no third-party libraries. However, if you need your data with nice formatting and maybe some statistical formulas, you should export XLS or XLSX format. The maximum amount of columns in XLS format is limited to 256, whereas XLSX format allows more columns, but is not fully supported by all spreadsheet applications.

2013-12-07

How to Store Your Media Files in Amazon S3 Bucket

In this article, I will show you how to use Amazon Simple Storage Service (S3) to store your media files in the cloud. S3 is known and widely used for its scalability, reliability, and relatively cheap price. It is free to join and you only pay the hosting and bandwidth costs as you use it. The service is provided by Amazon.com. S3 tends to be attractive for start-up companies looking to minimize costs.

S3 uses a concept of buckets which is like a storage database. Each bucket has its own url. Inside the buckets you have folders and under that you have files. In fact, directories don't actually exist within S3 buckets. The entire file structure is actually just one flat single-level container of files. The illusion of directories is actually created based on having the file names like dirA/dirB/file.

If you want to browse the files in a folder-like structure, you can use Transmit FTP client on Mac OS X. It supports S3 services. Amazon browser-based console also has interface for browsing or uploading files.

OK. Now let's have a look how to set up a Django project which will use S3 for media files.

1. Create a bucket

At first you will need to create a bucket at S3 and make it accessible for all visitors. Login to your Amazon Web Services console. Click on "Services" in the menu, then on the "S3". Click on the button "Create bucket" and enter your bucket name like "mywebsite.media" or even better without dots like "mywebsitemedia". Choose a region there which is the closest to your target audience, for example, if your website is for Europeans, choose "Ireland". Go to the properties of the bucket, expand "Permissions" section, click on the "add bucket policy" button and enter the following:

{
    "Version": "2008-10-17",
    "Statement": [
        {
            "Sid": "AllowPublicRead",
            "Effect": "Allow",
            "Principal": {
                "AWS": "*"
            },
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::mywebsite.media/*"
        }
    ]
}

2. Install boto and django-storages

Amazon Web Services provide a python library called boto for accessing the API. There is a django app called django-storages which allows to use AWS S3 as the main storage. So your next step is to activate your virtual environment and install latest versions of boto and django-storages.

pip install boto==2.19.0
pip install django-storages==1.1.8

3. Set up django-storages for your project

Add the following to the settings.py:

INSTALLED_APPS = [
    # ...
    'storages'
    # ...
]
DEFAULT_FILE_STORAGE = 'storages.backends.s3boto.S3BotoStorage'
AWS_S3_SECURE_URLS = False       # use http instead of https
AWS_QUERYSTRING_AUTH = False     # don't add complex authentication-related query parameters for requests
AWS_S3_ACCESS_KEY_ID = '...'     # enter your access key id
AWS_S3_SECRET_ACCESS_KEY = '...' # enter your secret access key
AWS_STORAGE_BUCKET_NAME = 'mywebsite.media'

# the next monkey patch is necessary if you use dots in the bucket name
import ssl
if hasattr(ssl, '_create_unverified_context'):
   ssl._create_default_https_context = ssl._create_unverified_context

4. Create your models with FileField or ImageField

Let's create a Profile model with avatar field.

def upload_avatar_to(instance, filename):
    import os
    from django.utils.timezone import now
    filename_base, filename_ext = os.path.splitext(filename)
    return 'profiles/%s%s' % (
        now().strftime("%Y%m%d%H%M%S"),
        filename_ext.lower(),
    )

class Profile(models.Model):
    # ...
    avatar = models.ImageField(_("Avatar"), upload_to=upload_avatar_to, blank=True)
    # ...

Whenever you save an instance of the Profile with the new avatar picture, avatar will be uploaded to S3 bucket. To show it in a template, you will need something like <img src="{{ profile.avatar.url }}" alt="" /> where the image source will look like "http://mywebsite.media.s3.amazonaws.com/profiles/20140214203012.jpg".

5. Use the storage to manipulate file versions

If you need to create a thumbnail version of your image, it's probably best to overwrite the save method of the model and trigger the generation of the thumbs there. Let's add some methods to the Profile class:

class Profile(models.Model):
    # ...

    def save(self, *args, **kwargs):
        super(Profile, self).save(*args, **kwargs)
        self.create_avatar_thumb()

    def create_avatar_thumb(self):
        import os
        from PIL import Image
        from django.core.files.storage import default_storage as storage
        if not self.avatar:
            return ""
        file_path = self.avatar.name
        filename_base, filename_ext = os.path.splitext(file_path)
        thumb_file_path = "%s_thumb.jpg" % filename_base
        if storage.exists(thumb_file_path):
            return "exists"
        try:
            # resize the original image and return url path of the thumbnail
            f = storage.open(file_path, 'r')
            image = Image.open(f)
            width, height = image.size

            if width > height:
                delta = width - height
                left = int(delta/2)
                upper = 0
                right = height + left
                lower = height
            else:
                delta = height - width
                left = 0
                upper = int(delta/2)
                right = width
                lower = width + upper

            image = image.crop((left, upper, right, lower))
            image = image.resize((50, 50), Image.ANTIALIAS)

            f_thumb = storage.open(thumb_file_path, "w")
            image.save(f_thumb, "JPEG")
            f_thumb.close()
            return "success"
        except:
            return "error"

    def get_avatar_thumb_url(self):
        import os
        from django.core.files.storage import default_storage as storage
        if not self.avatar:
            return ""
        file_path = self.avatar.name
        filename_base, filename_ext = os.path.splitext(file_path)
        thumb_file_path = "%s_thumb.jpg" % filename_base
        if storage.exists(thumb_file_path):
            return storage.url(thumb_file_path)
        return ""

As you might have guessed, the avatar can be placed in the template using something like

{% if profile.get_avatar_thumb_url %}
    <img src="{{ profile.get_avatar_thumb_url }}" alt="" />
{% endif %}

where the image source will look like "http://mywebsite.media.s3.amazonaws.com/profiles/20140214203012_thumb.jpg".

Conclusion

When you have a basic overview about Amazon Simple Storage Service, it is quite easy to use it in Django projects with existing third-party libraries. For flexibility, if you need to modify uploaded files, storage object should be used instead of the default os methods. That way, you can simply switch to the default file storage for local development.