【Python&Flask】パスワード管理アプリを作ってみる②【DB接続と非同期APIで作るメイン画面】

{
  "label": "pass_mng_db",
  "host": "db",
  "user": "dev",
  "port": 5432,
  "ssl": false,
  "database": "postgres",
  "password": "pass"
}

DROP TABLE IF EXISTS secret_data;

CREATE TABLE secret_data (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "service" TEXT NOT NULL,
    "account" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "memo" TEXT,
    "updated_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO secret_data (service, account, password, memo)
VALUES
('ツイッター','test-account','ABCD1234','ツイッターアカウント'),
('インスタグラム','insta-test',	'EFG67890', 'https://www.instagram.com/'),
('グーグル', 'test@gmail.com', 'HIJKL123', 'グーグルアカウント'),
('ヤフー', 'test@yahoo.co.jp', '456mnoP', 'ヤフーアカウント'),
('Zoom', 'zoom-test', '7QRstU', 'Zoomの仕事用アカウント'),
('Zoom', 'privae-zoom', '890VWXyz', 'Zoomのプライベート用アカウント');

from psycopg2._psycopg import connection
import psycopg2.extras as _ext

class SecretData:
    def __init__(self, data):
        self.id = data["id"]
        self.service = data["service"]
        self.account = data["account"]
        self.password = data["password"]
        self.memo = data["memo"]
        self.updated_at = data["updated_at"]
    
    @staticmethod
    def all(db: connection):
        result = []
        with db.cursor(cursor_factory=_ext.DictCursor) as cur:
            cur.execute("SELECT * FROM secret_data ORDER BY id")
            row = cur.fetchone()
            while row != None:
                result.append(SecretData(row))
                row = cur.fetchone()
        return result

    @staticmethod
    def get(db: connection, id: int):
        with db.cursor(cursor_factory=_ext.DictCursor) as cur:
            cur.execute("SELECT * FROM secret_data WHERE id=%(id)s", { "id": id })
            row = cur.fetchone()
            if row !=None:
                return SecretData(row)
            else:
                return None

import psycopg2
from flask import render_template, redirect, session, jsonify
from flask.views import MethodView
from models.secretdata import SecretData

class IndexView(MethodView):
    def get(self):
        if "login" in session and session["login"] == True:
            with psycopg2.connect("postgresql://dev:pass@db:5432/postgres") as db:
                data = SecretData.all(db)
            return render_template("index.html", title="トップ", data = data)
        else:
            return redirect("/login")

    @staticmethod
    def get_password(id: int):
        if "login" in session and session["login"] == True:
            with psycopg2.connect("postgresql://dev:pass@db:5432/postgres") as db:
                data = SecretData.get(db, id)
            if data == None:
                return "", 404
            else:
                return jsonify({"password": data.password})
        else:
            return "", 401

from flask import Flask, redirect, session
from loginview import LoginView
from indexview import IndexView # 追加
from datetime import timedelta

app = Flask(__name__)

# セッション準備
app.secret_key = 'AopWe-To$eu44r0l'
app.permanent_session_lifetime = timedelta(minutes=20) 

def register_routes():
    # ログイン画面
    app.add_url_rule("/login", view_func=LoginView.as_view("login"))
    #### 追加：ここから ####
    # メイン画面
    indexView = IndexView.as_view("index")
    app.add_url_rule("/", view_func=indexView)
    app.add_url_rule("/index", view_func=indexView)
    app.add_url_rule("/password/<int:id>", view_func=IndexView.get_password)
    #### 追加：ここまで ####

#### コメントアウト：ここから（削除予定） ####
# @app.route("/")
# def index():
#     if "login" in session and session["login"] == True:
#         return "<h1>ログイン成功</h1>"
#     else:
#         return redirect("/login")
#### コメントアウト：ここまで ####

@app.route("/logout")
def logout():
    session["login"] = False
    return redirect("/login")

register_routes()

if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0")

function getApi(url) {
    return new Promise((resolve, reject) => {
        let xhr = new XMLHttpRequest();
        xhr.open('GET', url, true);
        xhr.responseType = 'json';
        xhr.send(null);
        xhr.onload = function(e) {
            if (xhr.status == 200) {
                resolve(xhr.response);
            } else {
                reject(new Error(xhr.status));
            }
        };
        xhr.onerror = function(e) {
            reject(new Error(xhr.status));
        };
    });
};

<!DOCTYPE html>
{% set APP_NAME = "PASSマネ" %}
<html>
    <head>
        <title>{{title}} - {{APP_NAME}}</title>
        <link rel="stylesheet" href="/static/css/style.css">
        <script src="/static/js/script.js"></script><!-- 追加 -->
    </head>
    <body>
        <div class="header">
            <div class="content">
                <label style="margin-top: auto; margin-bottom: auto;">{{APP_NAME}}</label>
            </div>
        </div>
        <div class="main">
            {% block main %}
            {% endblock %}
        </div>
    </body>
</html>

・・・・（前回のstyle.cssの続き）・・・・

/* トップページ用スタイル */
table {
    margin: auto;
    border: solid 1px black;
}

table th {
    background-color: #c4d3f5;
    border: solid 1px black;
}

table td {
    border: solid 1px black;
    padding: 5px 10px;
    min-width: 120px;
}

table td.small {
    min-width: 80px;
}

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

div.plus {
    width: 60px;
    height: 60px;
    background-color: #d6d6f7;
    display: flex;
    border-radius: 25px;
}
div.plus img {
    width: 80%;
    margin: auto;
}

{% extends '_layout.html' %}
{% block main %}
    <script>
        function openPass(eventSource) {
            let txt = document.getElementById('txtDispPass');
            if (txt == null) {
                txt = document.createElement('input');
                txt.id = 'txtDispPass';
                txt.readOnly = true;
            } else {
                txt.parentNode.firstChild.style.display = 'inline';
                txt.remove();
            }
            getApi(`/password/${eventSource.dataset.id}`)
                .then((response) => {
                    txt.value = response.password;
                    txt.style.width = `${eventSource.offsetWidth}px`;
                    eventSource.parentNode.appendChild(txt);
                    eventSource.style.display = 'none';
                })
                .catch((status) => {
                    alert(`失敗:${status}`);
                });
        }
    </script>
    <h1>アカウント情報</h1>
    <table rules="none" style="padding: 5px;">
        <thead>
            <tr><th>サービス</th><th>アカウント</th><th>パスワード</th><th></th></tr>
        </thead>
        <tbody>
            {% for record in data %}
            <tr>
                <td>{{record.service}}</td>
                <td>{{record.account}}</td>
                <td><a href="javascript:void(0)" onclick="openPass(this);" data-id="{{record.id}}">＊＊＊＊＊＊</a></td>
                <td class="small center"><a href="/edit/{{record.id}}">変更</a></td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
    <a href="/new">
        <div class="plus" style="position: fixed; bottom: 50px; right: calc(50vw - 250px);">
            <img src="/static/img/plus.png">
        </div>
    </a>
{% endblock %}