今回は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のデータベースへアクセスすることができます。
まず初めにライブラリをインポートします。
import sqlite3
次にSqlite3のデータベースの作成、およびアクセスのコードを下記に記載します。
# データベースにアクセスする。データベースがない場合は作成される
conn_datadb = sqlite3.connect('data.sqlite3')
# カーソルを作成
cur = conn_datadb.cursor()
今回下記の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形式のデータに対する制御を行うことができます。詳しくはこちらに各種JSON形式データの操作を行う関数の記載があります。
まずJSON形式のデータを追加します。JSON形式のデータInsertは下記のようになります。
# テーブルの作成。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します。
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指定で削除する場合になります。）
# 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になっていることに注意）
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になっているため、修正しています。）
t = (delete_node_num[0].replace("$", "$.node"), )
cur.execute("update networkData set networkData=json_remove(networkData.networkData, ?)", t)
以上です。Sqlite3でもこのように気軽にJSONデータが取り扱えるのはうれしいですね :)
参考