WebブラウザからDB登録する

# 仕様
画面から名前、年齢、Eメールアドレスを入力し、登録ボタンを押すとDBに登録される。
### 入力画面
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3850581/883b82e9-c5ff-7fd0-b338-16b403bcfc7a.png)
### 登録完了画面
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3850581/bb635b5b-f331-c903-43c9-ba04734cbe39.png)
### DB登録状況
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3850581/5f7327e4-7eff-29a7-0931-ae25ece93916.png)

### 動作設計
```plantuml
@startuml
box "Port 8080" #LightBlue
    participant ブラウザ as client
end box
box "Port 8081" #LightGreen
    participant Webサーバ as web_server
    participant DBサーバ as db_server
end box
box "Local" #LightYellow
    participant DB as database
end box

client -> web_server : 入力
activate web_server
    web_server -> db_server : 通知
    activate db_server
        db_server -> database : 登録
        activate database
            database -> db_server : 通知
        deactivate
        db_server -> web_server : 通知
    deactivate
    web_server -> client : 通知
deactivate
@enduml
```


# 実装
## HTML
### index.html
今回、pythonの簡易的にサーバを立ち上げる方法を使用するため、ブラウザからアクセス時の画面はindex.htmlで実装する。
```html
<!DOCTYPE html>
<html lang="ja">
 <head>
    <meta charset="UTF-8">
    <script src="js/http.js" async></script>
    <link rel="stylesheet" href="css/sample.css" type="text/css">
    <title>HTML入門-JavaScriptの基本</title>
 </head>
 <body>
    <div class=container>
    <h1>文書の主見出し</h1>
    <div class="flexbox">
            <label>Name</label>
            <input id="name" class="nice-textbox" type="text"/>    

            <label>Age</label>
            <input id="age" class="nice-textbox" type="text"/>

            <label>Email</label>
            <input id="email" class="nice-textbox" type="text"/>

            <a href="page.html" class="btn_04" onclick="HttpPostRequest();">登録</a>
        </div>
    </div>
 </body>
</html>
```

### page.html
```html
<!DOCTYPE html>
<html lang="ja">
 <head>
    <meta charset="UTF-8">
    <script src="js/http.js" async></script>
    <title>HTML入門-JavaScriptの基本</title>
 </head>
 <body>
    <h1>登録完了</h1>
 </body>
</html>
```

<details>
<summary>一応今回のCSSも。</summary>

```css
h1 {
    /*線の種類（二重線）太さ 色*/
    border-bottom: double 5px #FFC778;
  }

.container {
    font-family: arial;
    font-size: 24px;
    margin-left: 250px;
    margin-right: 250px;
    outline: solid 2px gray;
  }

.flexbox {
    display: flex;
    flex-direction: column;
    /*text-align: center;*/
}

a.btn_04 {
	display: block;
	text-align: center;
	vertical-align: middle;
	text-decoration: none;
	width: 180px;
    /*margin: auto;*/
    margin-top: 1px;
    margin-bottom: 1px;
	padding: 1rem 4rem;
	font-weight: bold;
	border: 2px solid #27acd9;
	background: #27acd9;
	color: #fff;
	border-radius: 100vh;
	transition: 0.5s;
}
a.btn_04:hover {
	color: #27acd9;
	background: #fff;
}

/* テキストボックス */
.nice-textbox{
    position: relative;
    display: block;
    /*width: 500px;*/
    margin-top: 5px;
    margin-bottom: 15px;
    margin-left: 5px;
    margin-right: 10px;
    padding: 15px;
    border: solid;
    border-radius: 5px;
    border-color: #a0a0a0;
    font-size: 16px;
    /*outline: solid;*/
}
```

</details>

## JavaScript
POSTリクエストの実装
```js
function HttpPostRequest() {
    let txt_name = document.getElementById('name');
    let txt_age = document.getElementById('age');
    let txt_email = document.getElementById('email');

    const xhr = new XMLHttpRequest();
    xhr.open("POST", "http://127.0.0.1:8081/");
    xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
    const body = JSON.stringify({
        name: txt_name.value,
        age: txt_age.value,
        email: txt_email.value,
    });
    xhr.onload = () => {
    if (xhr.readyState == 4 && xhr.status == 201) {
        console.log(JSON.parse(xhr.responseText));
    } else {
        console.log(`Error: ${xhr.status}`);
    }
    };
    xhr.send(body);
}
```

## Python
サーバとDB登録をいっぺんに書いている。
本来は分けた方がわかりやすいが、今回は時間がないため。
```python
import socket
import time
import mysql.connector
import json
import re

HOST = "127.0.0.1"
PORT = 8081
buff = 1024

def select_all_customer():
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",
        database="sample_db",
        auth_plugin="mysql_native_password"
    )

    mycursor = mydb.cursor()

    mycursor.execute("SELECT * FROM customer")
    myresult = mycursor.fetchall()
    return myresult

def register_customer(_name, _code, _age, _address):
    print('START CREATE TABLES')

    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",
        database="sample_db",
        auth_plugin="mysql_native_password"
    )

    mycursor = mydb.cursor()

    sql = "INSERT INTO customer(name, code, age, address) VALUES (%s, %s, %s, %s)"
    val = [_name, _code, _age, _address]
    mycursor.execute(sql, val)
    mydb.commit()

    select_list = select_all_customer()
    print('-------------------------')
    for record in select_list:
        print(record)
    print('-------------------------')
    print('END CREATE TABLES')

def register(_msg):
    sub_msg = re.sub('b\'|\'', '', str(_msg))
    msg = sub_msg.split("\\r\\n")
    print('Parse\n-------------------------')
    for http_info in msg:
        print(http_info)
    print('-------------------------\n')

    jsonData = json.loads(msg[-1])
    select_list = select_all_customer()
    customer_code = int(select_list[-1][2]) + 1
    register_customer(jsonData["name"], customer_code, jsonData["age"], jsonData["email"])

def connect_server():
    server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    server.bind((HOST, PORT))
    print('host:' + HOST + ' port:' + str(PORT))

    while True:
        print('Listen')
        server.listen(5)
        print('Waiting for connection')
        client, addr = server.accept()
        print('Established connection\n')

        try:
            msg = client.recv(buff)
            register(msg)
            client.send('Hello,World'.encode("UTF-8"))
            time.sleep(1)
        except socket.error:
            print('ERROR')
            client.close()
            server.close()
            break

    print('SERVER END')

connect_server()
```