Create and return a CP932 CSV file for Excel with Chalice

Introduction

When creating a management system for Web applications, I often encounter the pattern "I want you to be able to download system data in a CSV file that can be opened in Excel". If you create CSV without thinking about anything in Python3 system, it will be UTF-8 character encoding, so if it is the default setting of Excel, the characters will be garbled. Of course, if you specify the reading encoding properly in Excel, you can open it without problems, but it is often said that "I do not understand that, so do not garble from the beginning". In that case, it is necessary to create a CSV file with the encoding for Windows (CP932, or Shift_JIS, SJIS).

This time I was using Chalice, but as a result of looking closely at the document, I got stuck, so I made a note.

version etc.

$ pipenv run chalice --version
chalice 1.21.2, python 3.8.2, linux 5.4.0-52-generic

Read the documentation

This time I want to return it with Response, so I thought that it would be possible if I put an appropriate value in the body of the Response class. However, the document at the time of writing the article (October 23, 2020) states:

class Response(body, headers=None, status_code=200) body: The HTTP response body to send back. This value must be a string.

Quoted and excerpted from https://aws.github.io/chalice/api.html#response. Partially emphasized.

Thinking when reading this.

"Eh ...? The character string encoded in CP932 is a bytes type, so I can't pass it here ...? If that's not possible, create a file, upload it to S3, and have it downloaded ..."

However, ** body passes even if bytes are entered **. The string in this document is meant to be a wider string than just the str type. When I looked it up again, A person with the same question asked me a question.

Implementation example: Direct response

You can also pass ** bytes type ** to Chalice's Response # body **. If you implement it based on that, it will look like this. If you access / with a browser, it will be downloaded as a CSV file.

app.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import csv
import tempfile

from chalice import Chalice, Response

app = Chalice(app_name='csvtest')


def csv_response(filename, encoding='utf8'):
    """How to return a CSV file 1:Direct response"""
    with tempfile.TemporaryFile(mode='r+', encoding=encoding) as fh:
        #Write with writer
        writer = csv.writer(fh, lineterminator='\r\n')
        writer.writerow(['username', 'Login date and time'])
        writer.writerow(['user01', '2000/01/01 00:00:00'])
        #Read all the written data into data
        fh.seek(0)
        data = fh.read()
    headers = {}
    headers['Content-Type'] = 'text/csv'
    headers['Content-Disposition'] = f'attachment;filename="{filename}"'
    return Response(body=data, status_code=200, headers=headers)


@app.route('/')
def index():
    return csv_response('test.csv', encoding='cp932')

Alternative: Upload files to S3

In the previous example, tmpfile is used to create a file on-memory and then read data. However, in the case of AWS Lambda, it is executed in an environment where the memory usage is limited, so if the file becomes large, it can be imagined that the memory usage will be significantly affected. Of course, this is a problem that can be avoided by raising the upper limit of memory usage, but if the charge doubles, you may hesitate. In such a case, you can temporarily create a file under / tmp provided by AWS Lambda and upload the created file to S3. After uploading to S3, you can access the file created via the communication of CloudFront --S3, or create and provide a temporary URL to let the user download it.

Note that TempfileContext is not the essence of the code because it is prepared so that the files under / tmp used in AWS Lambda can be deleted without considering error handling.

import os
import csv
import uuid

import boto3

s3 = boto3.client('s3')


class TempfileContext:
    """Provides a context to create and delete temporary files"""
    def __init__(self):
        tmpfile = str(uuid.uuid4())
        self.filename = f'/tmp/{tmpfile}'

    def __enter__(self):
        return self

    def __exit__(self, ex_type, ex_value, trace):
        try:
            if os.path.exists(self.filename):
                os.remove(self.filename)
        except Exception:
            pass


def create_and_upload_csv(filename, encoding='utf8'):
    """How to return a CSV file 2:Create CSV and upload it to S3"""
    with TempfileContext() as tmp:
        # 1. /Create a CSV file in the tmp area
        with open(tmp.filename, 'w', encoding=encoding) as fh:
            #Write with writer
            writer = csv.writer(fh, lineterminator='\n')
            writer.writerow(['username', 'Login date and time'])
            writer.writerow(['user01', '2000/01/01 00:00:00'])

        # 2.Upload to S3
        s3.upload_file(tmp.filename, BUCKETNAME, f'uploads/{filename}')