SQLiteでJSON

要件とかで列が増えたりすると面倒ですよね、特にView表とかあると面倒すぎます。
「いっそデータベースにJSONそのまま突っ込んじゃえばいいんじゃん？」
って思ったらSQLite3ってばJSONにそもそも対応しているんですよね。

とりあえずクラスを用意。

ざっくりでっち上げました。
以前にポストしたDBを簡単に使う～の焼き直しみたいなものです。
以前のは普通のTableへ更新するためにdictから変換をしていましたが今回のはそのまま突っ込んでます。

database.py

import sqlite3
import json

class DB:
    def __init__(self, filePath=None):
        if filePath != None:
            self.filePath = filePath

    def open(self, filePath=None):
        if filePath != None:
            self.filePath = filePath
        self.connection = sqlite3.connect(self.filePath, isolation_level="DEFERRED")
        self.cursor = self.connection.cursor()

    def close(self):
        self.cursor.close()
        self.connection.close()

    def fetch(self, sql):
        for row in self.cursor.execute(sql):
            yield row

    def query(self, sql):
        self.cursor.execute(sql)

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    def __enter__(self):
        self.open()
        return self

    def __exit__(self, exctype, excvalue, traceback):
        self.close()


class JsonDB(DB):

    def queryString(self, sql, tablename, where, condition, column):
        if len(where) > 0:
            wstr = []
            for key in where.keys():
                if str(where[key]).find("%") != -1:
                    wstr.append(f"json_extract({column},'$.{key}') like '{where[key]}'")
                else:
                    if type(where[key]) == int:
                        wstr.append(f"json_extract({column},'$.{key}') = {where[key]}")
                    else:
                        wstr.append(f"json_extract({column},'$.{key}') = '{where[key]}'")
            wstr = f" {condition} ".join(wstr)
            sql = " where ".join((sql, wstr))

        return sql


    def get(self, tablename, where={}, condition="and", column="jsondata"):
        sql = f"select * from {tablename}"
        sql = self.queryString(sql, tablename, where, condition, column)
        for row in self.fetch(sql):
            yield {"id": row[0], "data": json.loads(row[1])}


    def count(self, tablename, where={}, condition="and", column="jsondata"):
        sql = f"select count(*) from {tablename}"
        sql = self.queryString(sql, tablename, where, condition, column)
        row = self.fetch(sql)
        return row.__next__()[0]


    def set(self, tablename, data={}, dataid="NULL"):
        data = str(data).replace("'", '"')
        sql = f"""replace into {tablename} values({dataid}, '{data}')"""
        self.query(sql)
        
    
    def create_table(self, tablename):
        sql = f"""create table if not exists "{tablename}" """
        sql += """("id" INTEGER, "jsondata" TEXT, PRIMARY KEY("id"))"""
        self.query(sql)
        self.commit()

プライマリキー問題

これで列の数が変わろうと何をしようとばっちりさ！　と言いたいところなんですが一つ問題があります。
単純にデータとしてJSONを突っ込んだだけではreplaceで更新をかける際に「同じ行」を認識できない為に次々と新しいレコードが増えてゆきます。
もちろんそれでよい設計もあるのだけどSQLiteだしなるべく小さく運用したい。

もしかするとJSONデータの内容に対してプライマリキーを設定出来るような冴えたやり方があるのかも知れないですがちょっと見つけられなかったので…（冴えたやり方をご存じな方にはぜひ助教授いただきたく）

とりあえず今回取った策としてはテーブルに「id列」を用意してプライマリキーに設定、とあるデータを更新したい場合は「idを指定」、新規で追加したいなら「idを指定しない」。という方法です。

使ってみる。

以前の記事と大体同じことをやってみます。

import database

with database.JsonDB("jsondb.db") as db:
    print("テーブルの作成")
    db.create_table("names")
    db.commit()
    
    print("テーブルにデータを挿入")
    data = {"name": "json", "age": 4}
    db.set("names", data)
    
    data = {"name": "python", "age": 0}
    db.set("names", data)


    print("全件取得")
    for row in db.get("names"):
        print(row)

    print("-" * 10)
    print("条件を指定してデータ取得")
    where = {"name": "python"}
    for row in db.get("names", where):
        print(row)

        # 取り出したデータはそのままdict型として利用可能
        rowid = row["id"]
        data = row["data"]

    # データを修正して更新(dataid指定が必要)
    data["age"] = 30
    db.set("names", data, dataid=rowid)

    # 確認の為全件取り出してみてみる
    print("-" * 10)
    print("全件取得")
    for row in db.get("names"):
        print(row)

    # データの件数を取得
    print("-" * 10)
    print("テーブルのデータ件数を取得")
    print(db.count("names"))

    # 条件付きで件数を取得
    
    # (count, get 共通事項として)
    # whereに % を使うとlike指定になる
    # またキーが複数あれば複数条件で検索が可能。
    # 基本はand検索、orにしたい場合 condition="or" と指定（混在は不可）
    
    print("-" * 10)
    print("テーブルのデータ件数を条件で取得")
    print(db.count("names", where={"name": "j%"})) 

    
    #ロールバックとかコミット
    db.rollback()
    #db.commit()

なにしろテーブルに列が2つしかないので考えることが少なくて楽です。
しかし気になるのはパフォーマンス、そうですよね。

そこでちょっと試してみることにします。

簡単なパフォーマンステスト

パフォーマンステストというには雑ですが雰囲気だけでも。

データ挿入

import local.database as database
import random
import time

dbname = "sample.sqlite3"

with database.JsonDB(dbname) as db:
    db.create_table("sample_table")
    db.commit()

    st = time.time()
    print(st)
    for i in range(1000000):
        dat = {"msg": "row=%d" % i,
               "rnd_value": random.randint(1,1000)}

        db.set("sample_table", dat)

        if (i % 10000) == 0:
            db.commit()
            print(i)

    db.commit()

print(time.time() - st)

このようなコードで1,000,000件のデータを登録してみました。
結果としては 16.01687216758728 ということで、おおよそ16秒。

さらに1,000,000件のデータを追加で約17秒という感じでまあ。件数は多いにせよデータ量的に少ないのでこんなもんでしょうか。

データ検索

import local.database as database
import time

dbname = "sample.sqlite3"

with database.DB(dbname) as db:
    sql = """select count(*) from sample_table """
    sql += """where json_extract("jsondata", "$.rnd_value") > 999"""
    st = time.time()
    for row in db.fetch(sql):
        print(row)

print(time.time() - st)

凄く遅そうなイメージがあるのですが実際はどうか。
先ほど挿入した2,000,000件のデータに対して処理します。

結果は1.1099739074707031、ということで約1秒。

何とも言い難いものの…件数が件数なので意外と速いのでは。
JSONデータがもっと大きくなったらどうなるかというのは興味があるところではあります。
マスターレコードを用意して参照して、みたいなことをすれば当然時間はじゃんじゃん浪費するのでしょうが。

郵便番号データベースでのケース

これを書いている現状での郵便番号データは124,229件の郵便番号が存在しています。
純粋にこれらをJSONで登録したDBに対して郵便番号を全件走査すると 61ms ほど。

では47都道府県を読み込んでみます。

select 
	json_extract("jsondata", "$.都道府県") as 都道府県,
	json_extract("jsondata", "$.都道府県カナ") as 都道府県カナ
from postal
group by json_extract("jsondata", "$.都道府県")
order by min(id)

これで 1383ms 、約1秒。

SELECT 
	field6 as "都道府県", 
	field3 as "都道府県カナ"
from work
group by field6
order by min(field1)

フツーにざっくりテーブルを作ってデータ登録したDBにsql発行してやると 370ms。
さすがにJSONの方が速いということはなかった。そりゃそっか。

DBファイルのサイズもJSONの方が大きくなりがちでした。むむ…。

フツーのDBがネットワークドライブ越し、SQLデータベースがUSBメモリ上からとかなり雑な検証です。（まじめにやｒ

ひとまずのまとめ

SQLiteのJSON対応あたりを軽く使ってみた感想として、

すごい！　手軽！　しかもそこそこ速い（無理ではない速さ）でした。

フィールド1つでデータ運用出来るのでテーブル仕様がころころ変わってしまうような案件などに対してかなり強力だと感じます。設計に時間が割かれないので思い付きで作れるのもいいですね。また、列構造が違うデータがひとつのテーブルに混在出来るというのは何だかとてつもない可能性を感じてしまいます。（とんでもない罠ともいうかも）

1msでも速く！　みたいなケースでは全く良さがなさそうですがとにかく手軽なのでパフォーマンスとか気にならない範囲で便利に使うのが正解なのでしょう。

こんな便利なものにずっと気づかずにいたとは、勉強不足を痛感しました。