素人がWebアプリケーション制作の過程を書いてみる（Sqlite3+JSONデータ形式のDB操作）

今回はSqlite3を使ったJSON形式の取り扱いについて記載してみます。

今回作成するアプリケーションにおいては、データ形式が不定（用途に応じてKey:value形式のデータを追加）することから、NoSQLのDBを使用することにします。NoSQL用のDBには下記のようなものがあります。

* mongoDB
* Kassandra
* Redis
* HBase
* tinyDB

また調べてみると、最近はRDBについてもJSON形式のデータをサポートしています。

* postgreSQL
* mySQL
* Sqlite3

今回はデータベースをポータブルに使用したいということと、アプリケーションの性質上DBへの複数同時アクセスがないことから、簡易なSqlite3を使用することとしました。（tinyDBという選択肢もあったのですが、配列を含むJSON形式のデータをうまく扱えなかったため除外しています。）

### PythonからSqlite3へのアクセス

PythonではのSqlite3のライブラリを使用することによって、Sqlite3のデータベースへアクセスすることができます。
まず初めにライブラリをインポートします。

```python
import sqlite3
```

次にSqlite3のデータベースの作成、およびアクセスのコードを下記に記載します。

```python
# データベースにアクセスする。データベースがない場合は作成される
conn_datadb = sqlite3.connect('data.sqlite3')
# カーソルを作成
cur = conn_datadb.cursor()
```

今回下記のJSONデータを保存、検索、更新かけられるようにします。

```json
{
    "node": [
        {
            "id": 1,
            "x": 50,
            "y": 40,
            "r": 10,
            "text": "node-a",
            "scale": 2,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.1"
            }
        },
        {
            "id": 2,
            "x": 100,
            "y": 40,
            "r": 10,
            "text": "node-b",
            "scale": 1,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.2"
            }
        },
        {
            "id": 3,
            "x": 500,
            "y": 500,
            "r": 10,
            "text": "node-c",
            "scale": 1,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.3"
            }
        }
    ],
    "link": [
        {
            "id":1,
            "source": 1,
            "target": 3,
            "l": 150,
            "stroke": "red",
            "width": 10,
            "text":"Link1",
            "arrowtype": "circle",
            "linetype": "dot",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "prop1": "prop11"
            }
        },
        {
            "id": 2,
            "source": 2,
            "target": 3,
            "l": 150,
            "stroke": "red",
            "width": 10,
            "text":"Link2",
            "arrowtype": "arrow",
            "linetype": "arrow_solid",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "prop2": "prop22"
            }
        }
    ]
}
```

### DBへのデータ追加

今回Sqlite3では下記のJSON形式のデータに対する制御を行うことができます。詳しくは[こちら](https://www.sqlite.org/json1.html#jins)に各種JSON形式データの操作を行う関数の記載があります。

まずJSON形式のデータを追加します。JSON形式のデータInsertは下記のようになります。

```python
# テーブルの作成。json形式のデータを格納する場合は、型をjsonで指定
cur.execute("create table networkData(id integer primary key autoincrement, networkData json)")

t = ('''{
    "node": [
        {
            "id": 1,
            "x": 50,
            "y": 40,
            "r": 10,
            "text": "node-a",
            "scale": 2,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.1"
            }
        },
        {
            "id": 2,
            "x": 100,
            "y": 40,
            "r": 10,
            "text": "node-b",
            "scale": 1,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.2"
            }
        },
        {
            "id": 3,
            "x": 500,
            "y": 500,
            "r": 10,
            "text": "node-c",
            "scale": 1,
            "elementType": "test",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "IPAddress": "10.0.0.3"
            }
        }
    ],
    "link": [
        {
            "id":1,
            "source": 1,
            "target": 3,
            "l": 150,
            "stroke": "red",
            "width": 10,
            "text":"Link1",
            "arrowtype": "circle",
            "linetype": "dot",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "prop1": "prop11"
            }
        },
        {
            "id": 2,
            "source": 2,
            "target": 3,
            "l": 150,
            "stroke": "red",
            "width": 10,
            "text":"Link2",
            "arrowtype": "arrow",
            "linetype": "arrow_solid",
            "state": {
                "isSelected": false,
                "isFlaged": false,
                "isVisible": true
            },
            "property": {
                "prop2": "prop22"
            }
        }
    ]
}''',)

# データのインサートを実施。プレースホルダを使ってinsertを行うが、JSON形式のデータを投入する場合はjson()関数を使用する。
cur.execute("insert into networkData(networkData) values(json(?))", t)

# データの更新をコミット
conn_datadb.commit()
conn_datadb.close()
```

またデータをappendする場合は下記のようになります。（この例ではnode配列の一番最後にデータを追加しています。）
まず、json_array_length()およびjson_extract()を使用して、node配列の最後尾のPathを割り出しています。
ここでは、json_extract()は指定したPath（ここでは\$.node。\$はrootのPathになります。配列の場合は[]を用いて表示し、辞書型の場合は.を用いて表示します。例：$.node[0].id）以下にあるデータを抜き出しています。またjson_array_length()は配列の要素数を返します。結果的に\$.node[n]が産出されます。（nは格納されている最後尾の配列の次のインデックス）
そののちにjson_insert()関数を用いてデータを格納し、更新されたデータを再度DBに格納するためにnetworkData=...として、データをUpdateします。

```python
t = (json.dumps(request_json), )

cur.execute("update networkData set networkData=json_insert(networkData.networkData, '$.node[' || json_array_length(json_extract(networkData.networkData, '$.node')) || ']', json(?))", t)
conn.commit()
```
### DBへのデータ検索、削除

JSON形式のデータ検索および削除は例えば下記のようになります。（例として、node配列の要素をid指定で削除する場合になります。）

```python
# idはnode配列内の削除したいidを数字で指定
t = (id, )

# 事前にそのidがnode配列内に存在するかどうかを確認。
cur.execute("select json_tree.path from networkData,json_tree(json_extract(networkData.networkData,'$.node')) where json_tree.key='id' and json_tree.value=?", t)

delete_node_num = cur.fetchone()

if delete_node_num == None:
    print 'error: This id is not exist'
    return false

# そのidを削除
t = (delete_node_num[0].replace("$", "$.node"), )  
cur.execute("update networkData set networkData=json_remove(networkData.networkData, ?)", t)
conn.commit()
```

まず1つ目のクエリ発行（検索）について説明します。
ここにあるjson_tree()は引数のJSON形式データを処理し、json_treeオブジェクト化する関数です。下記では具体的にwhare句を使用して、json_tree.key、json_tree.valueで特定のkeyに特定の値が入っているものを抜き出すことができます。
またjson_extract()関数で全体として、\$.node以下にあるid=?のデータのPathを抜き出しています。（例えばid=1の場合\$[0]：rootが\$.nodeになっていることに注意）


```python
cur.execute("select json_tree.path from networkData,json_tree(json_extract(networkData.networkData,'$.node')) where json_tree.key='id' and json_tree.value=?", t)
```

２つ目のクエリでデータを削除します。json_remove()関数を使うことで、指定されたPathのデータを削除することができます。また削除されたデータを再度DBに格納するためにnetworkData=...として、データをUpdateします。
（削除の前にdelete_node_numの\$が\$.nodeになっているため、修正しています。）

```python
t = (delete_node_num[0].replace("$", "$.node"), )  
cur.execute("update networkData set networkData=json_remove(networkData.networkData, ?)", t)
```

以上です。Sqlite3でもこのように気軽にJSONデータが取り扱えるのはうれしいですね　:)


参考
- https://www.sqlite.org/json1.html
- https://stackoverflow.com/questions/33432421/sqlite-json1-example-for-json-extract-set
- https://stackoverflow.com/questions/49451777/sqlite-append-a-new-element-to-an-existing-array