AWS Lambda와 SQS를 이용한 대용량 엑셀 처리

# 코드 일부 발췌

config['requestInfo'] = template_data['requestInfo'] # 템플릿의 requestInfo부분
request_info = config['requestInfo']
match = re.search('^[a-zA-Z0-9+-_.]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$',
                   request_info['emailAddress']['sender'])
if match is None:
    raise ApiException('VL00003', '발신 메일이 이메일 형식이 아닙니다.')

# 코드 일부 발췌

if 'stepRange' in request_info['downloadStep']:
    # 끊어서 저장하는 범위가 있는지 확인
    step_range = request_info['downloadStep']['stepRange']
    # 템플릿 내 시간 범위가 잘못 설정된 경우 발생
    if 'days' in step_range:
        if step_range['days'] < 0:
            raise ApiException('VL00002', '다운로드 유효기간 일자 설정이 잘못되었습니다.')
    elif 'hours' in step_range:
        if step_range['hours'] not in range(0, 24):
            raise ApiException('VL00002', '다운로드 유효기간 시간 설정이 잘못되었습니다.')
    elif 'minutes' in step_range:
        if step_range['minutes'] not in range(0, 60):
            raise ApiException('VL00002', '다운로드 유효기간 분 설정이 잘못되었습니다.')
    elif 'seconds' in step_range:
        if step_range['seconds'] not in range(0, 60):
            raise ApiException('VL00002', '다운로드 유효기간 초 설정이 잘못되었습니다.')
    else:
        raise ApiException('VL00001', '다운로드 유효기간 내 기간 설정이 되어있지 않습니다.')
else:
    raise ApiException('VL00001', '다운로드 가능한 유효기간이 설정되어있지 않습니다.')

# 코드 일부 발췌

config['validation'] = template_data['validation'] # 템플릿의 검증 부분
for key in config['validation']:
    column = config['validation'][key]
    validation_keys = config['validation'].keys()
    val = self.get_json_param(parameter, key, None)
    plain_param = self.__json_to_plan_dict(parameter)
    if 'required' in column and column['required'] is True:
        if val is None:
            raise ApiException('VL00001', '필수 칼럼인 ' + column.get('name')+' 값이 누락되었습니다.')
    elif val is None:
        continue

# 코드 일부 발췌

# 헤더 데이터 작성
if is_header_write is False and query_list is not None and len(query_list) > 0:
    header_list = query_list[0].keys()
    with open(folder_name + '/header.data', 'wt', encoding="utf-8") as w:
        for k in header_list:
            w.write(k)
            w.write("\t")
    is_header_write = True

# 결과 리스트 생성
with open(folder_name + '/data' + str(i).zfill(3) + '.data', "w") as f:
    if query_list is not None:
        for item in query_list:
            for x in item.values():
                if type(x) is str:
                    if '\t' in x:
                        # 상품명에 탭이 들어가는 경우 탭 제거
                        x = x.replace('\t', ' ')
                f.write(str(x))
                f.write("\t")
            f.write("\n")
    else:
        pass

import os
import glob
import pandas as pd
import xlsxwriter

# 코드 일부 발췌
class MassiveHelper:
    """대용량 처리 헬퍼
    """

    def merge_csv(self, folder_name, output_format, template_name):
        """분리 되어있는 데이터 csv로 머징

        :param folder_name:
        :param output_format:
        :return int:
        """
        file_list = sorted(glob.glob(folder_name + '/data*'), key=os.path.getctime)
        total = 0
        x = 0
        with open(folder_name + '/' + template_name + '.' + output_format, 'wt', encoding='utf-8') as w:
            with open(folder_name + '/header.data', 'rt', encoding="utf-8") as r:
                if total == 0:
                    w.write(r.readline())
                    w.write('\n')

            for file_name in file_list:
                if file_name.find('data') == -1:
                    continue
                with open(file_name, 'rt', encoding='utf-8') as rf:
                    while True:
                        line = rf.readline()
                        if not line:
                            break
                        w.write(line)
                        total += 1

    def merge_xlsx(self, folder_name, output_format, template_name):
        """분리 되어있는 데이터 xlsx로 머징

        :param folder_name:
        :param output_format:
        :return int:
        """
        total = 0
        file_list = sorted(glob.glob(folder_name + '/data*'), key=os.path.getctime)

        with open(folder_name + '/' + template_name + '.' + output_format, 'wb') as fout:
            writer = pd.ExcelWriter(fout, engine='xlsxwriter', options=dict(constant_memory=True, in_memory=True))

            # taken from the original question
            workbook = writer.book
            worksheet = workbook.add_worksheet()
            header_format = workbook.add_format(
                {'bold': True, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#D7E4BC'})

            cell_widths = []  # 셀 너비
            font_width = 1  # 글자 너비
            font_width_h = 1.88  # 한글 한글자
            padding = 1.12
            y = 0

            # header write
            with open(folder_name + '/header.data', 'rt', encoding="utf-8") as rf:
                datas = rf.readline().strip().split('\t')
                for x, d in enumerate(datas):
                    text_cnt = self.__count_text_size(str(d))
                    cell_widths.append(text_cnt[0] * font_width_h + text_cnt[1] * font_width + padding)
                    worksheet.write(y, x, d, header_format)

            y = 1

            for file_name in file_list:
                if file_name.find('data') == -1:
                    continue
                with open(file_name, 'rt', encoding="utf-8") as rf:
                    while True:
                        line = rf.readline()
                        if not line:
                            break
                        datas = line.strip().split('\t')
                        for x, d in enumerate(datas):
                            worksheet.write(y, x, d)

                            text_cnt = self.__count_text_size(str(d))
                            width = text_cnt[0] * font_width_h + text_cnt[1] * font_width + padding
                            try:
                                if cell_widths[x] < width:
                                    cell_widths[x] = width
                            except Exception as e:
                                raise e
                        total += 1
                        y += 1
            x = 0
            for w in cell_widths:
                worksheet.set_column(x, x, w)  # Column  A   width set to 20.
                x += 1

            writer.close()

s3.upload_file(folder_name + '/' + template_name + '.' + output_format,
                                       constants.MASSIVE_EXCEL_UPLOAD_BUCKET,
                                       constants.MASSIVE_EXCEL_UPLOAD_FOLDER
                                       + '/' + str(uuid_name) + '/' + template_name
                                       + '.' + output_format
                                       , ExtraArgs={'ACL': 'public-read'})

# 코드 일부 발췌
finally:
    try:
        if db:
            db.close()
    except Exception as e2:
        raise ApiException('VL00001', '잘못된 추출 포맷입니다.')
    finally:
        # 엑셀 파일 포맷으로 추출 완료 후 기존 데이터 및 헤더 파일 삭제
        file_list = sorted(glob.glob(folder_name + '/data*'), key=os.path.getctime)
        file_list.append(folder_name + '/header.data')
        for v in file_list:
            if os.path.exists(v):
                os.unlink(v)
        self._logger.info('[헤더 및 데이터 삭제 처리 완료]', log_type='info')

import uuid
import smtplib
import email.utils
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import boto3

# 코드 일부 발췌
def __send_mail(...):
        # Replace sender@example.com(opens in new tab) with your "From" address.
        # This address must be verified.
        sender = constants.SENDER
        sender_name = constants.SENDER_NAME
        # Replace recipient@example.com(opens in new tab) with a "To" address. If your account
        # is still in the sandbox, this address must be verified.
        recipient = mail_info['email']
        # Replace smtp_username with your Amazon SES SMTP user name.
        username_smtp = constants.USERNAME_SMTP
        # Replace smtp_password with your Amazon SES SMTP password.
        password_smtp = constants.PASSWORD_SMTP
        # (Optional) the name of a configuration set to use for this message.
        # If you comment out this line, you also need to remove or comment out
        # the "X-SES-CONFIGURATION-SET:" header below.
        # CONFIGURATION_SET = "ConfigSet"
        # If you're using Amazon SES in an AWS Region other than 미국 서부(오레곤),
        # replace email-smtp.us-west-2.amazonaws.com with the Amazon SES SMTP
        # endpoint in the appropriate region.
        host = constants.SMTP_HOST
        port = constants.SMTP_PORT
        # PORT = 465
        # The subject line of the email.
            body_text = """
안녕하세요. {0}입니다.
{1}에 요청하신 파일을 아래 URL을 통해 다운로드 가능합니다.
{2}
다운로드 기간 : {3} ~ {4}

* 본 메일은 발신 전용으로 회신이 불가합니다.
  문의사항은 카카오 플러스친구 @{0}셀러를 이용해 주시기 바랍니다.
감사합니다.
""".format(...)

        # Create message container - the correct MIME type is multipart/alternative.
        msg = MIMEMultipart('alternative')
        msg['Subject'] = subject
        msg['From'] = email.utils.formataddr((sender_name, sender))
        msg['To'] = recipient
        # Comment or delete the next line if you are not using a configuration set
        # msg.add_header('X-SES-CONFIGURATION-SET',CONFIGURATION_SET)
        # Record the MIME types of both parts - text/plain and text/html.
        part1 = MIMEText(body_text, 'plain')
        part2 = MIMEText(body_html, 'html')
        # Attach parts into message container.
        # According to RFC 2046, the last part of a multipart message, in this case
        # the HTML message, is best and preferred.
        msg.attach(part1)
        msg.attach(part2)
        # Try to send the message.
        try:
            server = smtplib.SMTP(host, port)
            server.ehlo()
            server.starttls()
            # stmplib docs recommend calling ehlo() before & after starttls()
            server.ehlo()
            server.login(username_smtp, password_smtp)
            server.sendmail(sender, recipient, msg.as_string())
            server.close()
        # Display an error message if something goes wrong.
        except Exception as e:
            raise ApiException('VL00001', '메일전송에 오류가 발생하였습니다.')
        else:
            # 메일 전송 완료에 대해선 로그 처리
            self._logger.info('[메일 전송 완료]', log_type='info')