PythonからSQL Serverを動かそう！～webアプリ構築編～

#目次
[0.この記事を書いた経緯](#0-この記事を書いた経緯)
[1.対象読者](#1-対象読者)
[2.環境](#2-環境)
[3.手順](#3-手順)
[4.苦労した点](#4-苦労した点)
[5.まとめ](#5-まとめ)
[6.参考サイト](#6-参考サイト)

#0-この記事を書いた経緯
[前回記事](https://qiita.com/wiskerpaddy/items/4268dfab09a6c53a64d1)の続編になります。
この記事を読むと、WEBから値をSQL Serverに登録して作成したDBの値をPythonで取り出して表示することができるようになります。

#1-対象読者
[前回記事](https://qiita.com/wiskerpaddy/items/4268dfab09a6c53a64d1)を読んだ方
SQL ServerとPythonの接続方法を知りたい方
SQLAlchemyを使用した簡単なWEBアプリを作成してみたい方
今回作成するアプリは**「交通費精算アプリ」**です！
※なお今回はDBのチューニングは考えないこととしています。

#2-環境
Python 3.9.4
Flask 1.1.2
SQLAlchemy 1.4.7
Flask-SQLAlchemy 2.5.1
SQL Server 2019 (Express)
SSMS(SQL Server Management Studio) 18.9(日本語版)
pyodbc 4.0.30
※すべてlocal環境(Windows10 64bit)で動作させています。
　ソースコードエディタはVisual Studio Code(ver 1.57.1)

#3-手順
**(1)環境構築**
・「Python」,「SQL Server」,「SSMS(SQL Server Management Studio)」のインストール
([前回記事](https://qiita.com/wiskerpaddy/items/4268dfab09a6c53a64d1)と同様)

・SQLserver構成マネージャーの設定
([前回記事](https://qiita.com/wiskerpaddy/items/4268dfab09a6c53a64d1)と同様)

・下記のライブラリをpipでインストール
Flask
SQLAlchemy
Flask-SQLAlchemy
pyodbc

```
pip install ~
```

・DBの準備
![db_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/b232276b-d6db-77b1-6323-8280aa9819f8.png)
上記の画像を参考にDBを作成してください。


**(2)コード・ディレクトリの用意**
ディレクトリ構造は以下です。

```
├── python_sqlalchemy_R06
│   ├── static
|   |   └ design.css
│   └── templates
│       ├ delete_result.html
|       ├ layout.html
|       ├ result.html
|       └ view.html
└── flask_app.py
```

コードは以下を参照してください。
https://github.com/wiskerpaddy/py_flask_heroku_template/tree/main/python_sqlalchemy_R06
コードの簡単な説明は以下です。

|ファイル|内容|
|---|---|
|design.css|デザインを決めています。|
delete_result.html|レコードを削除した際に遷移する画面です。|
|layout.html|すべてのhtmlファイルに共通する部分を記述しています。|
|result.html|レコードを追加した際に遷移する画面です。|
|view.html|アプリを立ち上げた際に最初に立ち上がる画面です。|
|flask_app.py|アプリを動かしているコードです。|

**(3)コードの内容**
ローカルで**「flask_app.py」**を実行して**「http://127.0.0.1:5000/」**にアクセスします。アクセスすると以下の画面が立ち上がります。
![app_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/1254f850-64f2-8c96-41fa-993c8820488b.png)
**列の説明**
画面の列とDBの値の関係を説明します。以下のようにそれぞれ対応しています。

|画面の値|DBの値|値の説明|
|---|---|---|
|id|id|他の値が追加されると自動で追加される一意の値です。|
|名前|name|交通費の対象者です。|
|交通費|travel_costs|交通費の金額(円)です。|
|日付|date|交通費が発生した日付です。「YYYY-MM-DD」で登録します。<br>(YYYYは年、MMは月、DDは日が入ります。)|
|プロジェクトコード|pjcode|交通費が発生した項目を入力します(通勤等)。|
**WEBアプリの使い方**
**・追加**
フォームに適当な値を入力して「SUBMIT」ボタンを押下してDBに値を追加します。
![app_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/9b254e0b-65f9-3bd3-358a-f83b19263fd2.png)
押下すると以下の画面に遷移します。正常に書き込みが完了すると下記のようなメッセージが出ます。
![app_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/ab3e1da1-5de5-3a24-3255-ce822fac3817.png)
赤枠で囲まれたリンクを押下すると
![app_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/6f384e78-3002-51fb-c0c1-a6d5a05372e3.png)
詳細画面に遷移します。
![app_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/42842970-0414-07b4-696b-f29c61dff462.png)
**・例外処理**
何も値を入れない状態で「SUBMIT」を押下すると
![app_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/29d5b289-eb3e-4179-b5e9-b0d231c6715d.png)
不正な値が書き込まれたとして、下記のような画面に遷移します。
![app_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/687b75ec-a091-8065-7d88-10cfb9589a65.png)
ここでいう「不正な値」とは以下のように定義しており、「不正な値」を入力するとDBに追加される前に置き換えるようにしています。
※これ以外に不正な値がある場合教えていただけると幸いです。

|DBの項目|不正な値の例|置き換え後の値|
|---|---|---|
|名前|null|名無し|
|交通費|記号等の数値に変換不能な値,null|0|
|日付|dateフォーマットに変換不能な値,null|9999-12-31|
|プロジェクトコード|null|99999999|

**・削除**
レコードを削除する際はURLフォームに「http://127.0.0.1:5000/delete/id」
と入力します。idには削除したい行のidを入力します。画面上では「22」を指定しています。指定したら「Enter」を押下します。
![app_8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/618165cf-6b66-1677-24d9-d971be05ea02.png)
押下すると下記の画面に遷移します。
![app_9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/561cf4c8-9ac9-8824-c07a-8bb5438f7809.png)
メニューに戻るとレコードが削除されているのが確認できます。
![app_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/fff39a3b-7419-1567-5ba1-031e80a879be.png)
SSMSを立ち上げてテーブルを確認すると、対象レコードが削除されているのがわかります。
![app_11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677151/6e98a4b9-2580-8282-babf-86f09e09c4b4.png)


#4-苦労した点
不正な値を入力した際の、例外処理を考えるのが大変でした。
また「SQL Server」と「Python」を接続するコード(SQLAlchemyで言う「engine」)の作成が大変でした。ちなみに以下です。

```flask_app.py
connection_string ='DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';PORT=1433;Trusted_Connection='+trusted_connection+';'
db_uri = URL.create('mssql+pyodbc', query={'odbc_connect': connection_string})
app.config['SQLALCHEMY_DATABASE_URI'] = db_uri
db = SQLAlchemy(app)
```

#5-まとめ
次回はMS-SQLの代わりにPostgreSQLを実装して、heroku（サーバー）に上げることを想定したアプリを作成したいと思っています。
(いつやるかは未定です。)
何かと至らぬ点もあると思いますので、その際はアドバイスを頂けたら幸いです。

#6-参考サイト
[Flask SQLAlchemy (with Examples)](https://pythonbasics.org/flask-sqlalchemy/)
[SQLAlchemy](https://www.sqlalchemy.org/)