Creating a web application in Python Flask to automate the issuance of certificates

Hi all! My name is Dmitry. I hope that the article will be useful and interesting for you (don’t push too hard, it’s my first experience, my thoughts are confused). The topic of my article is creating a web application in Python Flask to automate the issuance of certificates and was inspired to write it after reading Mail without the hassle: automating the sending of letters using Python

In the modern world, more and more processes are being automated to simplify people's lives and reduce time for performing routine tasks. One of these tasks is issuing certificates. Previously, in the organization where I work, they were issued in paper form, but with the advent of COVID-19, when they went remote, and all the courses were remote, I decided to write first a script and then a full-fledged application with a web interface for sending out certificates.

In this article, I will walk through the process of creating a Python/Flask web application that will be used to automate the process of issuing certificates. I won’t describe all the basics, so let’s get straight to the point and comment on pieces of code.

She looks like this:

As input I receive a table from the course curator which contains the following columns:

• Event name

• the date of the

• Scope of the program

• Surname

• Name

• Surname

• Email

• date of issue

• Training status

• Curator Full name

• Curator TEL

• E‑MAIL Curator

Application file app.py

import os
from flask import Flask, request, render_template

from parse_table import table_courses
from html2pdf import table_cert, tamplate_html
from send_mail import collection_info

UPLOAD_FOLDER = 'upload/'
app = Flask(__name__)

app.config['FILE_UPLOADS'] = UPLOAD_FOLDER

@app.route('/certificate', methods=['GET', 'POST'])
def cert():
    if request.method == 'POST':
        # Шаблон письма
        template_email="templates/sample_mail/sample_edu_doc.html"
        # Тема письма
        subject_email = request.form['subject_email']
        # Текст письма
        text_email = request.form['text_email']
        # таблица с ФИО и адресами
        table_email = request.files['table_email']
        table_email.save(os.path.join(app.config['FILE_UPLOADS'], table_email.filename))
        list_email = table_cert(table_email)
        # print(list_email)
        cert_list = tamplate_html(list_email)
        ok_list, err_list = collection_info(template_email,
                                            subject_email,
                                            list_email=list_email,
                                            file_name=cert_list)
        return render_template('list_spam.html', ok_list=ok_list, err_list=err_list)
    else:
        return render_template('dokuments.html')


if __name__ == '__main__':
    app.run()

The cert function describes the logic for transmitting data for sending, the letter template, subject, text and table, then the table is processed in the file html2pdf.py

import pandas as pd


list_col = ['Наименование мероприятия',
            'Дата проведения',
            'Объём программы',
            'Фамилия', 'Имя', 'Отчество',
            'E-mail',
            'Дата выдачи',
            'Статус обучения',
            'Регистрационный номер',
            'Куратор ФИО','Куратор ТЕЛ','Куратор E-MAIL']

def table_cert(file):
    table = pd.read_excel(file)
    df = pd.DataFrame(table)
    table_value = df[list_col].values
    # print(table_value)
    cert_values = []
    for i in table_value:
        cert_value = {}
        cert_value['event_name'] = i[0]
        cert_value['event_date'] = i[1]
        cert_value['program_size'] = i[2]
        cert_value['surname'] = i[3]
        cert_value['name'] = i[4]
        cert_value['second_name'] = i[5]
        cert_value['email'] = i[6]
        cert_value['date_of_issue'] = i[7]
        cert_value['status'] = i[8]
        cert_value['reg_number'] = i[9]
        cert_value['kyrator'] = i[10]
        cert_value['kyrator_tel'] = i[11]
        cert_value['kyrator_email'] = i[12]
        cert_value['cert_file'] = f'{i[3]}{i[4]}{i[5]}_cert.pdf'
        cert_values.append(cert_value)
    return cert_values

import pdfkit

def html2pdf(name):
    path_wkthmltopdf = b'wkhtmltopdf\\bin\wkhtmltopdf.exe'
    config = pdfkit.configuration(wkhtmltopdf=path_wkthmltopdf)
    css = b'templates/cert/style_new.css'
    options = {
        'orientation': 'Portrait',
        'page-size':'A5',
        'margin-bottom': '0mm',
        'margin-left': '0mm',
        'margin-right': '0mm',
        'margin-top': '0mm',
        # 'page-width': '7.12in',
    }
    try:
        pdfkit.from_file(f'{name}.html',
                         f'{name}_cert.pdf',
                         configuration=config,
                         options=options, css=css)
    except OSError:
        pass

from jinja2 import Template
import base64

def image_file_path_to_base64_string(filepath: str) -> str:
    with open(filepath, 'rb') as f:
        return base64.b64encode(f.read()).decode()

def tamplate_html(table):
    name_cert_list = []
    for cert_html in table:
        context = {
            'img_fon': image_file_path_to_base64_string('static/cert/new_fon.png'),
            'img_director': image_file_path_to_base64_string('static/cert/подпись.png'),
            'img_pechat': image_file_path_to_base64_string('static/cert/печать.png'),

            'surname': cert_html.get('surname'),
            'name': cert_html.get('name'),
            'second_name': cert_html.get('second_name'),
            'date': cert_html.get('event_date'),
            'subject':  cert_html.get('event_name'),
            'time': cert_html.get('program_size'),
            'number': cert_html.get('reg_number'),
            'date_in': cert_html.get('date_of_issue')
        }
        # print(context)
        html = open('templates/cert/index_new.html', encoding='utf-8').read()
        tmp = Template(html)
        out_file = tmp.render(context)
        name_file = f"{cert_html.get('surname')}{cert_html.get('name')}{cert_html.get('second_name')}"
        with open(name_file+'.html', 'w', encoding='utf-8') as f:
            f.write(out_file)
            f.close()
        html2pdf(name_file)
        name_cert_list.append(f'{name_file}_cert.pdf')
    return name_cert_list

In the table_cert function, the table is converted using pandas into a dictionary consisting of tuples including the listener data and the name of the certificate file.

The html2pdf function sets the parameters of the future certificate: size, indents, orientation, indicates the path to the executable file (it is needed for the pdfkit library to work) and the path to the CSS file in which the identity template settings are specified.

In the image_file_path_to_base64_string function, images are converted because without this it does not work.

In the tamplate_html function, an HTML certificate template is generated and converted to .pdf, recording it all in a list.

When all the lists are ready, two variables ok_list, err_list are declared; they will serve to display the final result in the web interface in two columns: successfully sent and sent with error, and the data collected above is sent to the collection_info function for sending.

import re
import os

from config import PORT, SERVER, LOGIN, PWD

import smtplib

from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from email.header import Header
from email.utils import formataddr

from bs4 import BeautifulSoup
from jinja2 import Template


def collection_info(template, subject, list_email, **params):
    ok_list, err_list = [], []
    for email in list_email:
        ok, error = send_email(template,
                               subject,
                               email.get('email'),
                               first_name=email.get('name'),
                               second_name=email.get('second_name'),
                               cert_name=email.get('cert_file'),
                               kyrator=email.get('kyrator'),
                               kyrator_tel=email.get('kyrator_tel'),
                               kyrator_email=email.get('kyrator_email'),
                               **params)
        ok_list.append(ok)
        err_list.append(error)
    return ok_list, err_list

def send_email(template, subject, email, **params):
    server = smtplib.SMTP_SSL(SERVER, PORT)
    server.login(LOGIN, PWD)
    print(f'Переменная email: {email}')
    print(f'Переменная params: {params}')
    msg = MIMEMultipart()
    msg['From'] = LOGIN
    msg['To'] = email
    msg['Subject'] = Header(subject, 'utf-8')
    html = open(template, encoding='utf-8').read()
    template_html = Template(html).render(subject_email=subject,
                                          first_name=params.get('first_name'),
                                          second_name=params.get('second_name'),
                                          text_email=params.get('text_email'),
                                          link_form=params.get('link_form'),
                                          link_teacher=params.get('link_teacher'),

                                          kyrator=params.get('kyrator'),
                                          kyrator_tel=params.get('kyrator_tel'),
                                          kyrator_email=params.get('kyrator_email'),
                                          )
    body = BeautifulSoup(template_html, 'html.parser')
    msg.attach(MIMEText(body, 'html', 'utf-8'))

# Проверяем если вложение есть до прикрепляем к письму
    name_attachment = params.get('file_email')
    cert_attachment = params.get('cert_name')
    if name_attachment:
        file_attachment = os.path.basename(name_attachment.filename)
        open_attach = MIMEApplication(open('upload/'+name_attachment.filename, 'rb').read())
        encoders.encode_base64(open_attach)
        open_attach.add_header('Content-Disposition', 'attachment', filename=file_attachment)
        msg.attach(open_attach)
    elif cert_attachment:
        print(cert_attachment)
        open_attach = MIMEApplication(open(cert_attachment, 'rb').read())
        encoders.encode_base64(open_attach)
        open_attach.add_header('Content-Disposition', 'attachment', filename=cert_attachment)
        msg.attach(open_attach)


    ok, error = [],[]
    try:
        server.sendmail(LOGIN, msg['To'], msg.as_string())
        server.quit()
        ok = f"{msg['To']} - OK"
    except BaseException as err:
        e = re.search('\(([^)]+)', str(err)).group(1)
        server.quit()
        error = f"{msg['To']} - {e}"
    return ok, error

From the received data, html templates are generated and sent to addresses from configured mail, and two lists are created which will subsequently be displayed on the screen in the columns of successful or unsuccessful sending.

Letter template

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta name="viewport" content="width=device-width"/>

    <!-- For development, pass document through inliner -->
    <!-- <link rel="stylesheet" href="https://habr.com/ru/articles/810757/css/simple.css">-->

    <style type="text/css">
        /* Your custom styles go here */
        * {
            margin: 0;
            padding: 0;
            font-size: 100%;
            font-family: 'Avenir Next', "Helvetica Neue", "Helvetica", Helvetica, Arial, sans-serif;
            line-height: 1.65;
        }

        img {
            max-width: 100%;
            margin: 0 auto;
            display: block;
        }

        body,
        .body-wrap {
            width: 100% !important;
            height: 100%;
            background: #f8f8f8;
        }

        a {
            color: #71bc37;
            text-decoration: none;
        }

        a:hover {
            text-decoration: underline;
        }

        .text-center {
            text-align: center;
        }

        .text-right {
            text-align: right;
        }

        .text-left {
            text-align: left;
        }

        .button {
            display: inline-block;
            color: #fff;
            background: #71bc37;
            border: solid #71bc37;
            border-width: 10px 20px 8px;
            font-weight: bold;
            border-radius: 4px;
        }

        .button:hover {
            text-decoration: none;
        }

        h1,
        h2,
        h3,
        h4,
        h5,
        h6 {
            margin-bottom: 20px;
            line-height: 1.25;
        }

        h1 {
            font-size: 32pt;
        }

        h2 {
            font-size: 28pt;
        }

        h3 {
            font-size: 24pt;
        }

        h4 {
            font-size: 20pt;
        }

        h5 {
            font-size: 16pt;
        }

        p,
        ul,
        ol {
            font-size: 14pt;
            font-weight: normal;
            margin-bottom: 20px;
        }

        .container {
            display: block !important;
            clear: both !important;
            margin: 0 auto !important;
            max-width: 580px !important;
        }

        .container table {
            width: 100% !important;
            border-collapse: collapse;
        }

        .container .masthead {
            padding: 50px 0;
            background: #71bc37;
            color: white;
        }

        .container .masthead h1 {
            margin: 0 auto !important;
            max-width: 90%;
            text-transform: uppercase;
        }

        .container .content {
            background: white;
            padding: 30px 35px;
        }

        .container .content.footer {
            background: none;
        }

        .container .content.footer p {
            margin-bottom: 0;
            color: #888;
            text-align: center;
            font-size: 14px;
        }

        .container .content.footer a {
            color: #888;
            text-decoration: none;
            font-weight: bold;
        }

        .container .content.footer a:hover {
            text-decoration: underline;
        }
    </style>
</head>

<body>
<table class="body-wrap">
    <tr>
        <td class="container">
            <!-- Message start -->
            <table>
                <tr>
                    <td align="center" class="masthead">
                        <!--                        <h1>Something Big...</h1>-->
                    </td>
                </tr>
                <tr>
                    <td class="content">
                        <h2>Уважаемый(ая) {{ first_name }} {{ second_name }}!</h2>
                        <p>Направляем <b>{{ subject_email }}</b>. </p>

                        {% if text_email != None %}
                        	<p>{{ text_email }}</p>
                        {% else %}
                        {% endif %}

                        <p>Если у вас остались вопросы, обращайтесь к куратору программы. </p>
                        <p>Куратор: {{ kyrator }}</p>
                        <p>Телефон: {{ kyrator_tel }}</p>
                        <p>E-mail <a href="https://habr.com/ru/articles/810757/mailto:{{ kyrator_email }}">{{ kyrator_email }}</a></p>
                        <br>
                         <hr>
                        <p style="margin: 0"><i>Данное письмо было сформировано автоматически.</i></p>
                        <p style="margin: 0"><i>Пожалуйста, не отвечайте на него. Электронный адрес **** не является адресом для переписки</i></p>

                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
</body>

</html>

Certificate template

<!DOCTYPE html>

<html lang="ru">

<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="https://habr.com/ru/articles/810757/style_new.css">

</head>

<body>
    <div class="container">
        <div class="image">
             <img src="data:image/png;base64,{{ img_fon }}"> 
        </div>
        <div class="text">
            <div class="q1">
                <p>************</p>
            </div>
            <div class="q2">
                <p>*************</p>
            </div>
            <!--
            <div class="q3">
                <p>*****************</p>
            </div>
-->
            <div class="q4">
                <p>СЕРТИФИКАТ</p>
            </div>

            <div class="q6">
                {% if second_name == 'nan' %}
                    <p>{{ surname }} {{ name }}</p>
                {% else %}
                    <p>{{ surname }} {{ name }} {{ second_name }} </p>
                {% endif %}
            </div>

            <div class="q8">
                 <p>{{ date }} принял(а) участие в</p> 

                 <p>{{ subject }}</p> 

                <p>в объёме {{ time }} академических часов</p>

            </div>
            <div class="q11">
                <p>М.П.</p>
                <p>Директор _______________ и.И. Иванов</p>

            </div>
            <div class="podpis_pechat">

                                <img src="data:image/png;base64,{{ img_director }}">
                                <img src="data:image/png;base64,{{ img_pechat }}">
            </div>
            <div class="q12">
                <p>г. Ярославль </p>
                <p>{{ date_in }} </p>

            </div>
        </div>
    </div>
</body></html>

That seems to be ALL